Hitachi Vantara Pentaho Community Forums
Results 1 to 38 of 38

Thread: fields truncated when exported to Excel.

  1. #1
    Join Date
    Aug 2007
    Posts
    26

    Default fields truncated when exported to Excel.

    Hello,

    I used Pentaho to extract lots of data into an Excel file ; it has 20 columns and extracts 2000 lines.
    When I used Pentaho 1.6.GA, it worked fine, but now I am using 1.7.GA and the fields are truncated.

    For exemple, in a column I have the data "New York".
    When I open the file with Excel, I see : "Ne..".
    Before, I simply extended the column in Excel and then "New York" appeared.
    But now, even when I extend the column, is see "Ne..".

    Am I missing a configuration for Excel in 1.7.GA ?

    Best regards,
    Van Hoang

  2. #2
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    a well known bug, although pentaho claim it's a "feature" Lets not go into that now!

    the workaround is to make the page size bigger, so the layout engine has room to fit the columns in. So use A0 paper size, that often works

  3. #3
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    a well known bug, although pentaho claim it's a "feature" Lets not go into that now!
    So far I havent seen a bugreport on that, so I'm safe claiming it not a bug. If you would think its a bug, you would have opened a JIRA case, right? Give me a case, and we can schedule time for working on a solution that fits everyone. No case, no solution.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  4. #4
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    I opened a call with support. In the end they said a new feature was coming soon which would do what i want - something about an unformatted data dump mode where all the data does actually make it into excel. not really what i wanted, but that was how it was left! they also pointed me to one of your blogs on layouting as well as the reasoning behind this issue.

  5. #5
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Well, our Support-department - of course - has the aim to get your problem solved (as far as possible) with the current version you are using. Most businesses don't like the idea of seeing their issues on a far-away roadmap; they need solutions today. And as soon as it comes to delicate decisions like messing with the layout computation, they really cannot make promises that may turn out to be impossible to keep.

    So for all issues where you would start with "In a ideal world, it would be nice to have ..." it is better to start a feature discussion here. This way, we (the developers) know that you have a need for a feature, and chances are high that other users have the same or a similar problem too. More input gives better solutions, and more voices make sure your call does not pass by unheard. And if you create a JIRA case (with or without a discussion in the forum) we wont lose the feature-request. Hey, and if the request can be solved by existing means (usually through obscure hacks or config-settings or side-effects of the same) then at least you'll hear the solution along with the comments when we close the request as "already-solved".

    For the Excel-issue: The behavior of the table-exports right now is well-defined to be a emulation of the print-output. So not behaving like the print-output would be a violation of that rule. So to me that answer of pointing you back to change your layout and/or pointing you to the upcoming data-dump feature made sense under these circumstances. However, I can also think of good usecases where the current print-like output is not optimal and why you need that behavior changed - and as soon as you create me the JIRA case, you shall get it.

    And for the future: If you are unhappy with our specs, help us change the specs. After all, we produce our software to solve your needs - and if you see us heading down a wrong track, make yourself heard.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  6. #6
    Join Date
    Aug 2007
    Posts
    26

    Default

    Hello,

    So this is a new feature in Pentaho ; fine with me.
    I set A0 landscape and got my data ; this workaround is enough for me.

    For sure the specs have to be respected.
    But I think having the option to export full data to Excel is essential.
    Then I would gladly ask for this evolution.

    Can somebody explain me how should I proceed, in order to register this demand ?

  7. #7

    Default

    post a request on the jira
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)

    http://mattlittle.files.wordpress.co...-bananaman.jpg

  8. #8
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    and then post the jira reference back here, so we can all set our watches

  9. #9
    Join Date
    Aug 2007
    Posts
    26

    Default

    I set a request for enhancement in JRA.
    I hope we can get at least an option in order to get full data, while exportiong to Excel.

    The JRA reference is : http://jira.pentaho.com/browse/PRE-388

  10. #10

    Default

    *bugg* sets his watch
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)

    http://mattlittle.files.wordpress.co...-bananaman.jpg

  11. #11
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    OK, here we are again.

    I've resurrected some of the old code to make the "overflow" for cells work again. The style-property "overflow-x" now controls whether content is truncated when it violates the boundaries of the element. On Graphics2D/PDF/PlainText: If set to true, the content will be printed without any clipping enabled. The result may or may not look visually appealing. On HTML/RTF/Excel/Table-CSV: The cell will contain the full content, and how this affects the visual rendering is (as usual with table-outputs) up to the rendering application. In Excel, you'll get the desired overflow marker, in HTML and RTF the cell will probably expand, and Table-CSV you'll end up with a lot of data for a single cell.

    The "overflow-x" style defaults to false, set it to "true" to see the full content.

    The changes for that are in the SVN for both the 0.8.9 codeline and the trunk codeline, and if these changes do not wake up some scary monsters elsewhere in the code, then these changes become officially part of 0.8.9-8 at the end of the week.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  12. #12
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    Ok, thats great, and a really quick response, impressed!

    However I think there's still a scenario where we'll have a problem - and flinches around, it's with ad-hoc.

    So If you default that property to true, so you can get full data in excel, the only way to do that with ad-hoc is to make it a platform level default. But from the description it sounds like you dont really want that for e.g. pdf.

    Have i understood how it works correctly? or have i missed something. I probably have missed something

    Finally; My usual question with report engine numbers, will that version be compatible with platform 1.7.1? I presume not with 1.7.0!

    Thanks,
    Dan

  13. #13
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    So If you default that property to true, so you can get full data in excel, the only way to do that with ad-hoc is to make it a platform level default. But from the description it sounds like you dont really want that for e.g. pdf.
    Right, making the overflow a global default is not a good thing. However, I'm currently searching a sweet spot where I can inject the global default only for a specific output target. As WAQR is a story on its own, we need a global setting, otherwise we would have to change WAQR to apply the setting somehow. (Messing with WAQR is not a fun option in my world, so I *have* to solve that on the engine level.)

    The 0.8.9 line is a suitable replacement for all Platform 1.6 and 1.7 versions. Just replace the reporting jars (and the libraries related to reporting) in both the platform and your designtools.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  14. #14
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    ok, well once the sweet spot is found, let us know

  15. #15
    Join Date
    Aug 2007
    Posts
    26

    Default

    Well, thanks for the upgrade !
    It is good to see somebody listen to our remarks.

    But since I am a newbie convcerning Pentaho, I do not know the procedure to update a server.
    So if somebody can give me the procedure, it would be great.

    Once the new version is validated, where can we download it ?
    How do we proceed for the delivery ? Is it simply done by replacing the jar by the new version ? Is there some references to upgrade ?

    Sorry if theses are basic questions ; but until now, I only used GA complete version. I never modified it...

  16. #16
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Here comes the sweet spot.

    I've added a new configuration setting called
    Code:
    org.jfree.report.modules.output.table.xls.AssumeOverflowX=false
    that defines the default value for the style-key "overflow-x" in case no explicit definition is given. This is a report-local setting, so you can define it on a per-report base by specifying the setting in the report-configuration of the report-definition (that even works in WAQR-templates) or globally for all reports via the global "jfreereport.properties" file. In Pentaho-Platform deployments, the file can be found in "WEB-INF/classes".

    Code:
    But since I am a newbie convcerning Pentaho, I do not know the procedure to update a server. So if somebody can give me the procedure, it would be great.
    Updating the reporting engine means to delete all old reporting jars from "WEB-INF/lib" and to drop in the new ones. Same goes for the report-designer, there you replace the jars in the "lib" directory. Whenever we make a release of the reporting-engine, it is sufficient to download the "classic-engine-ext" project and to update the platform with the jars found in the "lib" directory from the distribution-zip file.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  17. #17
    Join Date
    Jun 2008
    Posts
    8

    Default

    Hi all!

    Where i can get jar with fixed problem "fields truncated when exported to Excel"?

    I try to use sources from svn://source.pentaho.org/pentaho-reporting but cann't build jar - there are some errors.

    thanks.

    Best regards,
    Ilja.

  18. #18
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    I dont think it's out yet.

    look out for 0.8.9.8 here:

    http://sourceforge.net/project/showf...group_id=51669

    Also see here, there seems to be 3 issues left to be resolved:

    http://jira.pentaho.com/secure/Issue...1&fixfor=10551

  19. #19
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    For 0.8.9-8 I'm waiting for a updated release of JCommon, which is due somewhere this week. This fixes a native memory leak in the PNG-Encoder (nasty little bug!).

    Hmm. .. and I should mark the excel-thing fixed, as with the overlap-x style set, the export now behaves as expected.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  20. #20
    Join Date
    Jun 2008
    Posts
    8

    Default

    Thanks for reply!

    But can i get source code with fixed "fields truncated when exported to Excel" to build jar?

    Best regards,
    Ilja.


  21. #21
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    There is a branch for the 0.8.9-sources.

    http://source.pentaho.org/pentaho-re....8.9-bugfixes/
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  22. #22
    Join Date
    Jun 2008
    Posts
    8

    Default

    Hmm.... I'm build jar, but there no effect.

    I'm not use JDK1.2, so i set in build.properties:
    build.target=1.5
    build.source=1.5

    MyCode:
    Code:
         public JFreeReport createXlsReportDefinition() {
    
            JFreeReport report = new JFreeReport();
    
            report.setName("A Very Simple Report");
    
            report.getReportConfiguration()
                    .setConfigProperty(
                    "org.jfree.report.modules.output.table.xls.AssumeOverflowX",
                    "true");
    
    
            setPageFormat(report);
    
            calculateColumnWidth();
    
            setXlsReportHeader(report);
            setXlsItems(report);
    
    
            return report;
        }
    Attached Images Attached Images  

  23. #23
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    hmmm ... the testcase works here. Can you verify that you really,really,really work with the latest and greatest?
    (a sysout of "JFreeReportInfo.getInstance().getVersion()" should give you the correct version.)
    Last edited by Taqua; 09-09-2008 at 03:35 PM.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  24. #24
    Join Date
    Jun 2008
    Posts
    8

    Default

    I'm sorry, it was a my problem.

    Everything work fine.

    I'm very grateful to you.

    Best regards,
    Ilja.

  25. #25

    Default

    8.9.9 is now available on http://sourceforge.net/project/showf...group_id=51669 but how is it properly deployed? These jars are just for the ReportDesigner. Are there any other jars needed on the server to fix this issue or is it just a matter of copying pentaho-reporting-engine-classic-0.8.9.9.jar and pentaho-reporting-engine-classic-ext-0.8.9.9.jar to ReportDesigner\lib?

    Any help would be greatly appreciated!

    Thanks,

  26. #26
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    If your report-designer comes with a recent (ie 0.8.9-6 or higher) version of the engine, then just the copying the pentaho-reporting-engine-classic-0.8.9.9.jar, pentaho-reporting-engine-classic-ext-0.8.9.9.jar and jcommon-1.0.14.jar to the report-designer (lib-dir)and platform-installation (pentaho/WEB-INF/lib) is enough.

    If you are upgrading from an older version, some more libraries (like libformula) may have changed. Compare what libraries come with the reporting-engine and upgrade where necessary.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  27. #27

    Default

    Thanks Thomas,

    Will jcommon-1.0.12 work for the column truncated issue? jcommon-1.0.14 doesn't appear to be available yet at http://www.jfree.org/jcommon/download/

  28. #28
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Sure it does. JCommon has nothing to do with that issue; the fix in JCommon is only needed to make the mem-leak go away.
    The jfree.org site is not up-to-date. As so often, SourceForge is the primary source for all downloads: http://sourceforge.net/projects/jfreechart
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  29. #29
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    Like the person who posted to the jira, i'm also struggling to get this to work.

    I took the ext jars for 0.8.9.9 and copied them into my 1.7.0ga platform, removing the old ones.

    I then set this in my template: (in the parser config section)

    <property name="org.jfree.report.modules.output.table.xls.AssumeOverflowX">true</property>

    Is that right? It hasnt worked for me... I still get .. on the end of fields in excel.

    I know my jars have been picked up because another fix relating to grouping is now working.

    I also see this, but dont think it's related?

    13:38:24,459 WARN [JCommon] Unknown tag <http://jfreereport.sourceforge.net/namespaces/reports/legacy/simple:font>: Start to ignore this element and
    all of its childs.

    Ah ive just worked out where thats coming from - its html code i've put in and works fine, so i dont think it's causing any issue.

    Thanks,
    Dan
    Last edited by codek; 09-22-2008 at 08:44 AM.

  30. #30

    Default

    Dan,

    Did you put this element in the *.xml report file? Like this?

    <report-definition ....>
    <parser-config>
    .......
    <property name="org.jfree.report.modules.output.table.xls.AssumeOverflowX">true</property>
    </parser-config>

    I'm still trying to get it to work. Thanks!

  31. #31
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    No not directly in the report xml, i put it in the template. I need it to work from a template as it's for waqr...

    I believe Taqua tested this and made it work so it must be something ive done wrong!?

  32. #32

    Default

    Here's what I've tried thus far (without success):

    1. Add the following to pentaho.war\WEB-INF\classes\jfreereport.properties (global setting):
    org.jfree.report.modules.output.table.xls.AssumeOverflowX=true

    2. Copied the latest pentaho.war\WEB-INF\lib from the PCI 1.7.1 release

    3. Add the following jars to pentaho.war\WEB-INF\lib (and removed the same 0.8.9.5 jars):
    pentaho-reporting-engine-classic-ext-0.8.9.9.jar
    pentaho-reporting-engine-classic-0.8.9.9.jar

    4. Tried local setting in report *.xml file:
    <report-config>
    ...........
    <configuration>
    ............
    <property name="org.jfree.report.modules.output.table.xls.AssumeOverflowX">true</property>
    </configuration>
    </report-config>

    5. Checked version: Pentaho BI Platform 1.7.1.1117

    *************

    6. Moved jcommon-1.0.14.jar to pentaho.war\WEB-INF\lib and removed jcommon-1.0.12 and the Excel report does not truncate anymore! However, the HTML output does truncate still.
    Last edited by eggsurplus; 09-22-2008 at 02:05 PM.

  33. #33

    Default

    WORKING!

    If you edit the .xml report definition and add the following you can get both Excel and HTML to not truncate columns. HTML will wrap the text though instead of making the column wider which is fine.

    <report-config>
    <simple-page-definition width="1" height="1">
    <page orientation="portrait" topmargin="10" leftmargin="10" bottommargin="10" rightmargin="10" pageformat="LETTER"/>
    </simple-page-definition>
    <configuration>
    <property name="org.jfree.report.NoPrinterAvailable">true</property>
    <property name="org.jfree.report.modules.output.table.xls.AssumeOverflowX">true</property>
    <property name="org.jfree.report.modules.output.table.html.AssumeOverflowX">true</property>
    </configuration>
    </report-config>

    The global setting also works. Just add another property for each output type you'd like to overflow.
    Last edited by eggsurplus; 09-22-2008 at 02:08 PM.

  34. #34
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    ok confirmed it works if you put the setting in jfreereport.properties

    I cannot get it to work in the waqr template however. But thats fine - i'm happy to put it in the properties file.

  35. #35
    Join Date
    Jul 2009
    Posts
    3

    Unhappy problem - xls export

    Hello,
    i have a problem with PDF / HTML / Excel - export
    It is truncated my strings when try to export the data
    we are using pentaho bi 1.7.0
    i had tried everything but without effect...

    1. stoped BI server
    2. copied - pentaho.war\WEB-INF\lib from 1.7.1 (used http://sourceforge.net/projects/pentaho/files/Business%20Intelligence%20Server/1.7.1-stable/pentaho_demo_mysql5-1.7.1.zip/download)
    3. copied - pentaho-reporting-engine-classic-ext-0.8.9.9.jar, pentaho-reporting-engine-classic-0.8.9.9.jar, jcommon-1.0.14.jar into ...\pentaho.war\WEB-INF\lib and deleted there old versions ...-ext-0.8.9.5.jar, ...-0.8.9.5.jar and jcommon-1.0.12.jar
    4. added
    org.jfree.report.modules.output.table.xls.AssumeOverflowX=true
    org.jfree.report.modules.output.table.html.AssumeOverflowX=true
    into ..\pentaho.war\WEB-INF\classes\jfreereport.properties
    5. started Pentaho Bi server and tried to report it but first i saw there was the old version on login screen (1.7.0...) and when tried to export to excel it's still truncated my strings :-(

    guided by http://jira.pentaho.com/browse/PRE-3...mment-tabpanel
    also checked http://forums.pentaho.org/showpost.p...1&postcount=32

    i saw Jason has added jars to ReportDesigner/lib and then i added them there too and tried the server again but still the same
    can anyone help me please, Realy have to fix this
    thanks in advance,
    regards.

  36. #36
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    what application server are you running?

    It sounds like your update to the jfreereport.properties in the war hasnt got deployed. So; check under webapps\pentaho\... for jfreereport.properties...

    In fact, if youve updated everything in the war file, you'll need to delete the unpaked pentaho folder so that the new ones from the war get re-extracted.

    or just make all the changes in the unpacked folder.

    Makes sense to me

  37. #37
    Join Date
    Jul 2009
    Posts
    3

    Default

    Hello,
    we are using JBoss
    http://217.75.140.158:8085/web-console/

    i have searched "jfreereport.properties" in whole "c:\pentaho"
    and added

    org.jfree.report.modules.output.table.xls.AssumeOverflowX=true
    org.jfree.report.modules.output.table.html.AssumeOverflowX=true

    to the following "jfreereport.properties"

    C:\pentaho\report-designer\resources\jfreereport.properties

    C:\pentaho\design-studio\plugins\org.pentaho.designstudio.editors.reportwizard_1.7.0.906\resources\jfreereport.properties

    C:\pentaho\bi-server\jboss\server\default\deploy\pentaho.war\WEB-INF\classes\jfreereport.properties

    #
    # The minimum loglevel that is logged
    org.jfree.base.LogLevel=Warn

    #
    # Where to log. Give a classname of a valid LogTarget implementation.
    org.jfree.base.LogTarget=org.jfree.logger.log4j.Log4JLogTarget

    org.jfree.report.NoPrinterAvailable=true

    #
    # Undefines the gui modules
    #
    org.jfree.report.modules.gui.base.Module=NoClass
    org.jfree.report.modules.gui.config.Module=NoClass
    org.jfree.report.modules.gui.converter.Module=NoClass
    org.jfree.report.modules.gui.csv.Module=NoClass
    org.jfree.report.modules.gui.html.Module=NoClass
    org.jfree.report.modules.gui.pdf.Module=NoClass
    org.jfree.report.modules.gui.plaintext.Module=NoClass
    org.jfree.report.modules.gui.print.Module=NoClass
    org.jfree.report.modules.gui.rtf.Module=NoClass
    org.jfree.report.modules.gui.xls.Module=NoClass
    org.jfree.report.modules.output.table.xls.AssumeOverflowX=true
    org.jfree.report.modules.output.table.html.AssumeOverflowX=true

    they all have the same structure only the one in WEB-INF\class\... have additional line

    #org.pentaho.web.ContentHandler=getImage?image={0}



    regards.

    p.s. jboss is from pentaho 1.7.0 opensource installer
    Last edited by kizmo; 07-29-2009 at 06:19 AM.

  38. #38
    Join Date
    Jul 2009
    Posts
    3

    Default pentaho.war

    checked http://www.jboss.org/community/wiki/JBossDeployWAR
    I think it's not a problem because when i try to change any jar from pentaho.war when my BI is started it drop me error that it is used...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.