65536 rows limitation

[Michael Schimmel <mi...@gmx.de>]

Hello everyone,
I have a problem concerning poi. I want to generate xls-files with more than
65536 rows. I want to know if this is possible with current poi 3.6 version?
I also tried to develope some java code, but I only got errors with this
65536 row-limitation. 
 
Can anyone help me, please?
Thank you.
 
Best wishes,
Mike

Re: 65536 rows limitation

[MSB <ma...@tiscali.co.uk>]

Which version of the Excel file format are you targetting? 

If you are trying to build workbooks using the older binary format - HSSF -
then it will not be possible to create a workbook with more that 65536 rows
owing to compatibility concerns; HSSF targets a version of the file format
that predates Excel 2007 and on. As a result, it creates files that can be
successfully opened using - for example - Excel 2003 and so cannot take
advantage of the more recent modifications to the binary file format. It may
be possible for you to modify the source code to produce a version of POI
that supports an extended number of rows but I would hesitate to recommend
this owing to the complexity of the binary file formaty, the relative
paucity of the documentation that Microsoft made available and concerns that
your workbooks could not be opened by earlier versions of Excel.

I Think - and that is think because I know that the format supports it but
am unsure about XSSF - that it should be possible to create much larger xml
based files but have never tried to do anything like this myself. Is the
size limitation built into the code - by this, I mean does the XSSFSheet
class 'complain' when you try to create rows with indices larger than 65536?
If this is the case, then it could be worth your while downloading the
source for the api and removing this check - if it exists and this is what
is causing you problems - to create your own version of POI. This should be
easier to accomplish as the xml file format is better documented and much
more 'logical' IMO.

Yours

Mark B

PS That does seem like a very large worksheet Michael. Have you though about
structuring the data so that it could be written into multiple sheets? Have
you considered using a different tool to store and manipulate the data, a
database could make it far easier to access and manipulate the data using
SQL for example?


Michael Schimmel wrote:
> 
> Hello everyone,
> I have a problem concerning poi. I want to generate xls-files with more
> than
> 65536 rows. I want to know if this is possible with current poi 3.6
> version?
> I also tried to develope some java code, but I only got errors with this
> 65536 row-limitation. 
>  
> Can anyone help me, please?
> Thank you.
>  
> Best wishes,
> Mike
> 
> 

-- 
View this message in context: http://old.nabble.com/65536-rows-limitation-tp28133967p28137722.html
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: 65536 rows limitation

[MSB <ma...@tiscali.co.uk>]

Hello Mike,

With regard to my PS in the earlier message, I was speaking to a Biologist
today who explained that many researchers use Excel to move data into a
statistical analysis package called R and that it is often not possible to
extract the data from many database packages in a suitable format hence the
use of Excel. The fact that the number of rows is limited to 65536 in older
versions of the package is a problem. As always, 'you live and you learn'.

Yours

Mark B


Michael Schimmel wrote:
> 
> Hello everyone,
> I have a problem concerning poi. I want to generate xls-files with more
> than
> 65536 rows. I want to know if this is possible with current poi 3.6
> version?
> I also tried to develope some java code, but I only got errors with this
> 65536 row-limitation. 
>  
> Can anyone help me, please?
> Thank you.
>  
> Best wishes,
> Mike
> 
> 

-- 
View this message in context: http://old.nabble.com/65536-rows-limitation-tp28133967p28141290.html
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org