Excel Export Limited to 65K Rows

[Daniel Bigham]

When exporting to Excel, we get the following error:

java.lang.IllegalArgumentException: Invalid row number (65536) outside of allowable range (0..65535)

(Where 65535 is 2^16)

* 65536 is the maximum number of spreadsheet rows supported by Excel 97, Excel 2000, Excel 2002 and Excel 2003. Text files that are larger than 65536 rows cannot be imported to these versions of Excel. (Excel 2007, 2010 and 2013 support 1,048,576 rows).

* The exception text seems to point to a Java library called "HSSF"

* HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format.

* Stack Overflow says this about the error: "HSSF targets a version of Excel (Excel 2003) which only supports a maximum of 65536 rows. You could try using the newer XSSF API instead, which supports later versions of Excel which have a more generous row limit."

* XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format

The conclusion seems to be that iDempiere may use a Java library called HSSF that only supports 65K rows.

Can anyone confirm?

If so, it seems odd that the project wouldn't have migrated to newer libraries that would properly support Excel 2007 and beyond.

Any workarounds?  If not, do people know who might be contacted about trying to get iDempiere enhanced to support this capability?

[Hiep Lq]

you figure out correct reason.

about improve, you can do it or sponsor to do it.

for tracking, open ticket here: https://idempiere.atlassian.net/secure/Dashboard.jspa

--
You received this message because you are subscribed to the Google Groups "iDempiere" group.
To unsubscribe from this group and stop receiving emails from it, send an email to idempiere+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/idempiere/bfa83d59-d5c0-40be-9154-f12a8c8b68af%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Lê Quý Hiệp
Email: hie...@hasuvimex.vn
Skype: admin.hasuvimex

Company: Thanh Hoa Fishery Import - Export J.s.c  (Hasuvimex) DL 47

Add: Lot E, Le Mon Industrial Zone, Thanh Hoa, Vietnam

[norber...@multimageweb.com]

https://idempiere.atlassian.net/browse/IDEMPIERE-2924

[Anh Hàn]

On the other hand, CSV import is: 1) slow; 2) cannot handle big data; 3) problem with separator ( coma, semicolon etc. In our case, some data in a field has comma or semicolon too); 4) encoding (cannot directly save csv file from Excel with default UTF-8).

We are thinking of replacing csv import with Excel Import (xls and xlsx). Will check if we can include xlsx exporting in the development.

Regards,

Anh Han

On Thu, Sep 28, 2017 at 11:23 AM, <norber...@multimageweb.com> wrote:

https://idempiere.atlassian.net/browse/IDEMPIERE-2924

This e-mail is confidential and may contain legally privileged information. It is intended only for the addressees and may not be reviewed or used in any way by other recipients. If you have received this e-mail in error, kindly notify us immediately by telephone or e-mail and delete the message and any attachments thereto from your system.

--
You received this message because you are subscribed to the Google Groups "iDempiere" group.
To unsubscribe from this group and stop receiving emails from it, send an email to idempiere+unsubscribe@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/idempiere/8e8805cb-833d-4164-b64c-fb6c3ee8d49e%40googlegroups.com.

[Hiep Lq]

3. How about put content inside ""?

4. Excel is such,  use Libre office or other csv tool

1,2. How xlsx can overcome csv?

Sent from my BlackBerry - the most secure mobile device

From: anh...@gmail.com Sent: September 28, 2017 12:17 PM To: idem...@googlegroups.com Reply-to: idem...@googlegroups.com Subject: Re: [idempiere] Re: Excel Export Limited to 65K Rows

To unsubscribe from this group and stop receiving emails from it, send an email to idempiere+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/idempiere/CAA0GRGFVb%3DX1bEb-qHponF5qume0Sm%2BAon09Aaogc5623jiytg%40mail.gmail.com.

[Heng Sin Low]

Slow import of big data into db is not due to the csv format, csv format is much more space efficient than xls.

You would have to modify the csv import process or find way to split your csv file into smaller pieces.

Problem with separator can be resolved by always double quote your data.

To view this discussion on the web visit https://groups.google.com/d/msgid/idempiere/CAA0GRGFVb%3DX1bEb-qHponF5qume0Sm%2BAon09Aaogc5623jiytg%40mail.gmail.com.

[Anh Hàn]

If you work with 100+ end users with basic copy & paste knowledge, you will know the struggle of teaching them how to "double quote". Not sure about Libre, but in Excel, saving to csv is dependent on computer's localization (csv separator for English language is comma, but csv separator for Vietnamese is semicolon).

We ended up writing our own Excel add-in to fix it and save UTF-8 csv directly from Excel.

For speeding the import process, yes, we also think that we have to modify the csv process.

Regards,

Anh Han

To view this discussion on the web visit https://groups.google.com/d/msgid/idempiere/CAC%3D6jXH7eRzoGrvz3gXqhSh5TkGr-GofAttA1J%2BMXGU5ViM04g%40mail.gmail.com.

[Hiep Lq]

you don't need re-write csv import, just improve and i inheritance.

1. inheritance

    allow your user upload xlsx, you can use api convert it to csv with quote, utf-8

2. improve

    now idempiere read all csv file to memory. modify it to keep small part on memory

    now idempiere open a virtual window and insert every record to it. try to reset window after done X record.

if you plan to do it and contribute, please commit at IDEMPIERE-3480

To view this discussion on the web visit https://groups.google.com/d/msgid/idempiere/CAA0GRGECHL2Tv3ttbhfNHjvWWgk%2BfsUDV6kXx0HHoVQgNET0xQ%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

--

[Carlos Antonio Ruiz Gomez]

Excel is really bad for CSV.

Most of the times I'm called for support about CSV it always lead to the same answer - use libreoffice.
Most people don't get it until they save it and compare the results.

El 28.09.2017 a las 10:58, Anh Hàn escribió:

[Daniel Bigham]

Thanks Norbert!

On Thursday, September 28, 2017 at 12:23:00 AM UTC-4, norber...@multimageweb.com wrote:

https://idempiere.atlassian.net/browse/IDEMPIERE-2924

[Daniel Bigham]

Anh,

I'm trying to understand your reply in relation to my question.  It seems you are talking about some kind of CSV import feature in iDempiere whereas I'm asking about exporting features.

Or did I misunderstand you?

On Thursday, September 28, 2017 at 1:17:59 AM UTC-4, Anh Han wrote:

On the other hand, CSV import is: 1) slow; 2) cannot handle big data; 3) problem with separator ( coma, semicolon etc. In our case, some data in a field has comma or semicolon too); 4) encoding (cannot directly save csv file from Excel with default UTF-8).

We are thinking of replacing csv import with Excel Import (xls and xlsx). Will check if we can include xlsx exporting in the development.

Regards,

Anh Han

On Thu, Sep 28, 2017 at 11:23 AM, <norber...@multimageweb.com> wrote:

https://idempiere.atlassian.net/browse/IDEMPIERE-2924

This e-mail is confidential and may contain legally privileged information. It is intended only for the addressees and may not be reviewed or used in any way by other recipients. If you have received this e-mail in error, kindly notify us immediately by telephone or e-mail and delete the message and any attachments thereto from your system.

--
You received this message because you are subscribed to the Google Groups "iDempiere" group.

To unsubscribe from this group and stop receiving emails from it, send an email to idempiere+...@googlegroups.com.

[norber...@multimageweb.com]

Carlos Approve replace xls with xlsx. 

We have done this code, we need cleanup and issue patch for testing.

No ETA. will try in next week.

norbert

[Daniel Bigham]

Wonderful!  Thank you!

[redhuan d. oon]

Here is an example of handling 82000 rows of CSV http://wiki.idempiere.org/en/Plugin:_US_Zip_Code . Solved in the SYSTEM CONFIG window setting as explained.