Hitachi Vantara Pentaho Community Forums
Results 1 to 17 of 17

Thread: Export in Excel 2007: limit of 65K rows

  1. #1
    Join Date
    Apr 2008
    Posts
    25

    Default Export in Excel 2007: limit of 65K rows

    Hello

    I am facing a limitation using Pentaho 2.0. I would like to export a huge amount of rows (more than 65k) using Pentaho Report Designer, but Pentaho refuses:

    java.lang.IndexOutOfBoundsException: Row number must be between 0 and 65535, was <65536>

    As I'm using Excel 2007, I can now work with more than 65k rows (until 1M), so my question is: where can I change this parameter to increase the maximum number of exported rows?

    Thanks a lot for your help
    Last edited by siroy; 01-07-2009 at 11:52 AM.

  2. #2

    Default

    Your not working with Excel 2007, your working with a Jar that creates an XLS, and that doesn't support Excel 2007, unlucky!
    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

  3. #3
    pstoellberger Guest

    Default

    I wouldn't recommend creating an excel file with more than 65k rows anyway
    CSV files are nice

  4. #4
    Join Date
    Apr 2008
    Posts
    25

    Default

    You're right, CSV is certainly better than XLS. But the XLS(X) format is a request I can't ignore (for many reasons).

    But is there a way to just modify this parameter 65535? It seems to be doable, on condition that I know where to modify it.
    Last edited by siroy; 01-07-2009 at 02:08 PM.

  5. #5
    pstoellberger Guest

    Default

    I don't think Excel 2003 can handle more than 65k rows
    And what you are producing is an Excel 2003 file not 2007, because that's not supported (yet?).
    Maybe you can split your output into different worksheets?
    I don't think that this is just a parameter :P Probably Reporting Chief Taqua has an opinion on this topic....

  6. #6
    Join Date
    Apr 2008
    Posts
    25

    Default

    OK

    You're certainly right. That's one of the differences between XLS and XLSX (i.e. the row limit). For the moment Pentaho only knows XLS so it is certainly not possible to export more than 65k rows.

    Thanks

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

    Default

    If you do such a large export, you probably dont expect humans to look at it, right?

    So what you are aiming for is a data-transformation exercise. You should use Kettle for such tasks. If you hunt the data through the reporting engine, then we perform layouting on top of it, which is expensive and the more data you have, the more you time you waste. Kettle, on the other hand is optimized to push data from one system to another (including Excel files), and no Kettle developer would waste a single processor cycle on layout-decisions.

    And this being said, I can conclude: If you run into a 65K limit, you are using the wrong tool. I will not loose a night's sleep on solving the problem for now
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  8. #8
    Join Date
    Apr 2008
    Posts
    25

    Default

    Actually, I need Excel in order to build pivot table (that's why I can have more than 65k rows). Of course Kettle can perform this easily but I would like to see my Excel file displayed automatically through my web browser (with just a click 'Open file with Excel' or 'Save it'...). Is it possible to run a kettle job through the platform (until here OK) and to get the file directly online ??

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

    Default

    Yes, you can run Kettle through the platform. However, Kettle uses the same libraries we use (POI) and has the option to use JExcelAPI as alternative. Both libraries do not support the OfficeOpen fileformats introduced in MS-Office 2007. POI is currently on its way to get support for that, and once they have a stable version we will be able to support it too.

    Have you considered simply to use a DataBase-query inside of Excel (not sure how the terminology is, it has been 10 years since I've seen a MS-Office product)?
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

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

    Default

    Hi,

    I see apache POI is now in beta.

    Isnt it therefore time that Pentaho looked at taking the latest version and testing/integrating it?

    Thanks,
    Dan

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

    Default

    Apache POI is always in Beta

    In our current state of development for Citrus, we are not going to rock the boat to bring in new libraries (or new versions of existing libraries). An POI upgrade is on the list for post-Citrus, as they now also support PPT and DOC export, which would be a nice addition to our export-capabilities.
    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

    Ah; Bit like gmail then.

    Ok. Is there a jira to track this? I do think it's an important thing which shouldnt get missed. Even though us tech's know that xls output is perfectly fine, when business types see the warning about compatability mode they see it as a problem - caused by Pentaho.

    ( I have to fight hard to stop the business team moaning about Pentaho, blaming the software, when often it's nothing to do with the software itself. )

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

    Default

    There doesnt appear to be a jira, so i raised one:

    http://jira.pentaho.com/browse/PRE-496

  14. #14
    Join Date
    Jul 2010
    Posts
    6

    Unhappy hi

    Hi,
    Can anyone tell me whats the final solution for data with more than 65k rows ..please

  15. #15

    Default

    csv
    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

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

    Default

    Hey, I almost forgot about that case. In PRD-3.7 we upgraded the POI library to POI-3.6, which now also supports the XML fileformats. By adding a configuration switch, we should be able to expose that feature in a sane manor.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  17. #17
    Join Date
    Jul 2010
    Posts
    6

    Default hi

    hi could u tell how.as i m new into this.

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.