You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by tahasharif2000 <ta...@tcs.com> on 2011/01/18 13:25:02 UTC
Apache POI creating excel having row greater than 65535
Hi,
Can you please help me how to create an excel file having rows more than
65535, my requirement includes rows more than 1 lakh, i tried poi 3.6 and
got exception of exceeding the limit (0-65535). Whether poi 3.7 allows rows
greater than the limit. Please help me. Is it possible using XSSFWorkbook.
Also am getting exception when creating object of XSSFWorkbook. The
exception coming from the code below,
Workbook wb = new XSSFWorkbook();
The exception is follows,
Exception in thread "main" java.lang.ExceptionInInitializerError
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Unknown Source)
at
org.apache.xmlbeans.impl.schema.SchemaTypeLoaderBase.getMethod(SchemaTypeLoaderBase.java:73)
at
org.apache.xmlbeans.impl.schema.SchemaTypeLoaderBase.<clinit>(SchemaTypeLoaderBase.java:66)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.xmlbeans.XmlBeans.getNoType(XmlBeans.java:856)
at org.apache.xmlbeans.XmlBeans.<clinit>(XmlBeans.java:881)
at
org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook$Factory.newInstance(Unknown
Source)
at
org.apache.poi.xssf.usermodel.XSSFWorkbook.onWorkbookCreate(XSSFWorkbook.java:256)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:169)
at Test.main(Test.java:13)
Caused by: java.lang.NullPointerException
at org.apache.xmlbeans.impl.store.Path.<clinit>(Path.java:65)
... 14 more
Thanks in advance,
Taha Sharif
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-creating-excel-having-row-greater-than-65535-tp3346090p3346090.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: Apache POI creating excel having row greater than 65535
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
You're welcome and all the best with the project. BigGridDemo has not been
the target of a lot of development since Yegor first posited the idea and I
am sorry to say that I have not worked with it. I am guessing that most of
the challenge will be working out how to manipulate the template file so
that it confroms to your requirements and it may be worth adopting a two
stage approach. Once you know which columns have to be merged, use POI to do
this on the template file. Then stream the data to the modified template as
second step.
If you do manage to take the concept forward at all, would you be willing to
post your solutions to the list so that others could benefit from your work?
Of course, this assumes that your employer/client is happy with this
suggestion and it is important that you check with them as any and all
information posted here is regarded as public property.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-creating-excel-having-row-greater-than-65535-tp3346090p3349457.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: Apache POI creating excel having row greater than 65535
Posted by tahasharif2000 <ta...@tcs.com>.
Thanks again Mark, I executed the code and it works well, created more than
1,00000 records, and hope it will help me to the development. Only thing am
curious about is that i need to merge multiple colums, so i need to start my
investigation, also change and create template according to my requirements.
Thanks
Taha Sharif
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-creating-excel-having-row-greater-than-65535-tp3346090p3349441.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: Apache POI creating excel having row greater than 65535
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Ah, well the honest answer is that I do not know. Looking at the source for
the createRow() method, there is no explicit check made on the row index
value you pass to it (but I have not yet been able to drill right down into
the factory methods). That value is passed into a parameter of type int and
so it should be able to accomodate higher values. So, all loks fine but the
only way to be certain of course is to test it out using version 3.7 or 3.8
and that should be very easy to do - all you need to do is to try to create
a cell with an index of 70,000 say and see if it throws an exception - there
is no need to create all of the rows up to that point, just that row alone
so the test case will consist of just a few lines of code. One other thing
you will need to be aware of is performance. Owing to the way POI is
constructed - there is an underlying layer that handles all of the xml
parsing for the api - lots of objects need to be created, placing a fairly
high demand on memory. That was the main reason Yegor wrote the BigGridDemo
proof I understand, users were encountering out of memory exceptions when
creating large workbooks.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-creating-excel-having-row-greater-than-65535-tp3346090p3349433.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: Apache POI creating excel having row greater than 65535
Posted by tahasharif2000 <ta...@tcs.com>.
No Mark, i didnt meant that, i asked you about using earlier versions of poi
i got exceptions regarding "exceeded the row limit of 0-65535", so by using
BigGridDemo.java we are able to create more than the row limit barrier of 64
k. Hencei mean there is no limit for row creation in poi 3.7 or we are doing
it thru another method of creating template and xml file, which just
populates the excel sheet, am i ryt...?
Thanks
Taha Sharif
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-creating-excel-having-row-greater-than-65535-tp3346090p3349280.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: Apache POI creating excel having row greater than 65535
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Yes there is; Micrsosoft imposed a maximum of 1 million rows per sheet I
think. If you need to check, they do publish this sort of information over
at the MSDN, just do a google search and that should link you to the
relevant section of Bill's website.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-creating-excel-having-row-greater-than-65535-tp3346090p3349269.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: Apache POI creating excel having row greater than 65535
Posted by tahasharif2000 <ta...@tcs.com>.
Hi Mark,
I know the fields that needs to be merged before building the code, but the
thing is that actually there is a 64 k limit, how we removed the limit.... I
think we are creating the templates and just populating the data with the
.xml file created, and theres is no actual row creation is there. Am i
right...?
Thanks in Advance
Taha Sharif
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-creating-excel-having-row-greater-than-65535-tp3346090p3349263.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: Apache POI creating excel having row greater than 65535
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Yes, there will be limits but these are imposed by the information you have
before you begin building the workbook. If you look at the technique Yegor
uses, he builds a template workbook before merging the data into it. If you
know in advance which cells should be merged, the formats to be applied to
them and so on, then these steps can be included in the template building
stage. If you only know this information once you begin parsing the data
then this will limit what you can do when building the template.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-creating-excel-having-row-greater-than-65535-tp3346090p3349195.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: Apache POI creating excel having row greater than 65535
Posted by tahasharif2000 <ta...@tcs.com>.
Hi Mark,
Thank you very much...executed the creation of rows more than 1,00000. Is
there any restriction in using this method of creation, can we able to do
all alignmens and formating like merging.
Thanks,
Taha Sharif
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-creating-excel-having-row-greater-than-65535-tp3346090p3349085.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: Apache POI creating excel having row greater than 65535
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
If you are using XSSF to create SpreadsheetML files, then I suggest that you
take a look at Yegor's BigGridCemo class that you can find in the
org.apache.poi.xssf.usermodel.examples folder. It is a proof that you will
likely need to extend and enhance in order to develop a complete
application, but it does demonstrate an alternative approach to creating
large file - I think Yegor uses a motional limit of 100,000 rows in one of
the for loops.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-creating-excel-having-row-greater-than-65535-tp3346090p3348208.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: Apache POI creating excel having row greater than 65535
Posted by tahasharif2000 <ta...@tcs.com>.
Hi,
1. I hav already specified that creating rows greater than 65535 limit,
hence there is no issues regarding 1 lakh.
2.
The exception comes from the creation of XSSFWorkbook object, i heard this
object supports latest MS office version like xlsx files,
Please help me.
Taha Sharif
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-creating-excel-having-row-greater-than-65535-tp3346090p3347271.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: Apache POI creating excel having row greater than 65535
Posted by JOSE L MARTINEZ-AVIAL <jl...@gmail.com>.
A lot of people don't know what a lakh is (One hundred thousand). If you
want to get help, you should make it easier for the rest of the people to
help you.
I don't use XSSFWorkbook, so I can't help you, but it seems to be lacking a
path(see where the NPE is being thrown). Have you checked the API?
2011/1/18 tahasharif2000 <ta...@tcs.com>
>
> Hi,
> Can you please help me how to create an excel file having rows more than
> 65535, my requirement includes rows more than 1 lakh, i tried poi 3.6 and
> got exception of exceeding the limit (0-65535). Whether poi 3.7 allows rows
> greater than the limit. Please help me. Is it possible using XSSFWorkbook.
>
> Also am getting exception when creating object of XSSFWorkbook. The
> exception coming from the code below,
>
> Workbook wb = new XSSFWorkbook();
>
> The exception is follows,
>
> Exception in thread "main" java.lang.ExceptionInInitializerError
> at java.lang.Class.forName0(Native Method)
> at java.lang.Class.forName(Unknown Source)
> at
>
> org.apache.xmlbeans.impl.schema.SchemaTypeLoaderBase.getMethod(SchemaTypeLoaderBase.java:73)
> at
>
> org.apache.xmlbeans.impl.schema.SchemaTypeLoaderBase.<clinit>(SchemaTypeLoaderBase.java:66)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
> at java.lang.reflect.Method.invoke(Unknown Source)
> at org.apache.xmlbeans.XmlBeans.getNoType(XmlBeans.java:856)
> at org.apache.xmlbeans.XmlBeans.<clinit>(XmlBeans.java:881)
> at
>
> org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook$Factory.newInstance(Unknown
> Source)
> at
>
> org.apache.poi.xssf.usermodel.XSSFWorkbook.onWorkbookCreate(XSSFWorkbook.java:256)
> at
> org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:169)
> at Test.main(Test.java:13)
> Caused by: java.lang.NullPointerException
> at org.apache.xmlbeans.impl.store.Path.<clinit>(Path.java:65)
> ... 14 more
>
> Thanks in advance,
>
> Taha Sharif
>
>
>
> --
> View this message in context:
> http://apache-poi.1045710.n5.nabble.com/Apache-POI-creating-excel-having-row-greater-than-65535-tp3346090p3346090.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
>
>