Read/Write huge excel files(1M rows)

ayush217 · October 27, 2015, 4:26am

thanx,

I will explore both these options

Also could u tell if lightcells can be used for insert and append rows in an existing excel file??

and in memory preference option of aspose.cells do we need to provide more memory for large files??

amjad.sahi · October 27, 2015, 4:55am

Hi,

Well, you can build Workbook from an existing template file and then save the workbook using LightCells APIs. In this way, existing data in the workbook will be saved too, you require to implement the LightCellsDataProvider interface to accommodate your scenario.

Also, using MemoryPreference option would consume less memory comparing loading/saving files in normal mode.

Thank you.

ayush217 · October 27, 2015, 5:29am

thank you

aspose is looking very promising for my usecase

Could you provide java sample code for both these approaches.

amjad.sahi · October 27, 2015, 5:47am

Hi,

Sure, see the documents for your reference:

http://www.aspose.com/docs/display/cellsjava/Using+LightCells+API

http://www.aspose.com/docs/display/cellsjava/Optimizing+Memory+Usage+while+Working+with+Big+Files+having+Large+Datasets

Hope, this helps a bit.

Thank you.

ayush217 · October 28, 2015, 5:21am

Hi Amjad,

Thanks for the prompt response and details. Just wanted to be absolutely sure that our business requirement is catered by Aspose.Cells:

We have following requirement:

Insert rows in existing excel file. for. e.g. - we have an excel file which already has one million records and using java Aspose.Cells we would like to insert two new rows at some starting position, let’s say B1 of sheet 1.
Append rows is existing excel file (i.e. at the end of sheet)
Overwrite rows in existing excel file
Excel file has approx. one million records & multiple sheets. We are looking for an approach which is memory optimized and does not load full workbook object in memory.

Can above requirements be catered by Aspose light cells. If yes, can you provide some details around what is the right way to implement LightCellsDataProvider interface (any sample code) .

PS: Earlier we were using apache POI user model based approach, which was causing a lot of memory issues, as this approach loads full workbook in the memory.

amjad.sahi · October 28, 2015, 6:04am

Hi,

Thanks for providing further details.

All your mentioned requirements can be accomplished by Aspose.Cells APIs. I think you should first go for MemoryPreference option. If you find any issue, then you may try using LightCells APIs, for reference, you should check the example in the topic here (you may add/update your code accordingly):

http://www.aspose.com/docs/display/cellsjava/Using+LightCells+API

By the way, Aspose.Cells also supports to load your desired worksheets (only) in the workbook, this may enhance performance and consumes less RAM, see the document for your reference:

http://www.aspose.com/docs/display/cellsjava/Load+Specific+Worksheets+in+a+Workbook

In any case, if you find any issue (regarding memory or other aspects), kindly do create a sample JAVA program (runnable) and provide us with all the details and files (e.g input + output etc.), we will check it soon.

Hope, this helps a bit.

Thank you.

ayush217 · October 28, 2015, 7:00am

hi,

thanks for the prompt reply

I explored the sample code for aspose.cells memory preference, there it was written that "This setting cannot take effect for the existing worksheets that are created before using the below line of code"

Hence it seems that we cant use this approach for existing spreadsheets but in our usecase we have existing spreadsheets which are read and write.

Please suggest if there is any work around to it.

amjad.sahi · October 28, 2015, 7:20am

Hi,

Well, you did not check the Note: (comments) below this in the code segment pasted in the document. That means by default, memory preference option won’t not set for existing sheet even you use the line of code:

i.e.,

workbook.getSettings().setMemorySetting(MemorySetting.MEMORY_PREFERENCE);

So, you would need to specify the MemoryPreference option for the invidual cells of the sheet(s), see the note (I copy/paste comments and some lines of code from the original example code which sets MemoryPreference option fine for the first worksheet cells):

//To change the memory setting of existing sheets, please change memory setting for them manually:

Cells cells = wb.getWorksheets().get(0).getCells();

cells.setMemorySetting(MemorySetting.MEMORY_PREFERENCE);

Let us know if you still have any issue or confusion.

Thank you.

amjad.sahi · October 29, 2015, 2:02am

Hi,

Adding more to my previous reply. Please note the following for your requirements:

1) Because the memory issue may be caused when loading the template file into memory completely, so user might have no chance to use Cells.setMemorySettings() for the large workbook. Instead, kindly use LoadOptions.setMemorySettings() to load/build the workbook in MEMORY_PREFERENCE mode. When the workbook was loaded in MEMORY_PREFERENCE mode, then all its worksheets will use this mode as the default to build cells model. You should try this way firstly to test whether it can solve your memory issue.

2) If MEMORY_PREFERENCE mode cannot solve the issue, you may consider the usage of LightCells. However, I think it is very complicated, and even impossible for users to implement all the logic by themselves if there are other things which need to be changed when inserting rows, such as formulas, named ranges, …etc. To achieve the goal of reducing memory cost for large workbook in template file, you need to combine the usage of LightCellsDataHandler and LightCellsDataProvider. That is, you should load the template file in light cells mode too. your implementation of LightCellsDataProvider will use cells data provided by LightCellsDataHandler to fill the workbook accordingly. It needs two threads to work together, like pipe stream, the loading process provides data and the saving process consumes data. And because you need to insert rows, then he should handle the row index modification too in LightCellsDataProvider, such as, if you insert one row before the third row (A3, B3, the row index is 2), for all row/cells got from LightCellsDataHandler, when their row index is greater than 2, you should fill them into the next row in LightCellsDataProvider(row index 2 becomes 3, 3 becomes 4, …).

Hope, this helps as well.

Thank you.

ayush217 · December 8, 2015, 1:58am

hi,

Thanx for your prompt replies.

We are now implementing aspose at our end and will be purchasing the library soon.

There is just one issue

While loading workbook object we need to give absolute path of file in string. Is there a way to provide a stream of file path while loading workbook object?

amjad.sahi · December 8, 2015, 2:20am

Hi,

Good to know that it’s working on your end now.

Regarding your new issue, well, Aspose.Cells also supports to load/read Excel file from streams, you may pick relevant overload for your requirements, see the document for your reference:

http://www.aspose.com/docs/display/cellsnet/Opening+Files

Thank you.

ayush217 · December 11, 2015, 5:13am

hi,

Thank you for reply. Streaming is working fine now.

There is just one error :

When I load a workbook object an error appears on our server by aspose api.

This doesnot affect the functioning of the API but still it needs to be removed to make it bug free.

I am mentioning the error below:

ERROR [Aj] com/aspose/cells/a/f/zb

INFO | jvm 1 | 2015/12/11 12:17:04 | java.lang.StringIndexOutOfBoundsException: String index out of range: -1

INFO | jvm 1 | 2015/12/11 12:17:04 | at java.lang.String.substring(String.java:1955) ~[na:1.8.0_45]

INFO | jvm 1 | 2015/12/11 12:17:04 | at org.aspectj.weaver.bcel.BcelObjectType.getOuterClass(BcelObjectType.java:904) ~[aspectjweaver-1.8.6.jar:1.8.6]

amjad.sahi · December 11, 2015, 6:52am

Hi,

Good to know that it is working better now.

Regarding your recent error you mentioned, could you try our latest version/fix: Aspose.Cells for Java v8.6.2.4 if it makes any difference.

If you still find the issue, kindly do provide sample JAVA console program (runnable) to reproduce the issue on our end, we will check it soon.

Thank you.

ayush217 · December 14, 2015, 12:19am

hi,

I am not able to deploy your latest jar. Also its only 6 mb as compared to 20 mb for 8.6.0 version.

Also I am using java 1.8 version 45. So can the error be related to that?

ayush217 · December 14, 2015, 12:55am

I have now checked for the latest jar you mentioned but I am still getting an error.

can this be a problem of java version??

amjad.sahi · December 14, 2015, 1:45am

Hi,

Well, this does not seem to be a problem with JDK version (e.g v1.8), it should work fine. As requested earlier, kindly do provide a sample JAVA console program (runnable) to reproduce the issue on our end, we will check it soon.

Thank you.

ayush217 · December 29, 2015, 5:26am

hi,

Thank you for the reply

We are trying to reproduce it in a separate sample project but meanwhile could tell is a way to suppress the logs of aspose jar??

Do you provide any properties file for the same?

amjad.sahi · December 29, 2015, 5:45am

Hi,

I am afraid, as we have no clue about your issue so far, so we cannot provide any means to suppress the logs you are facing. In some cases, we do provide debug versions for the user to trace the issue but your scenario seems to me a different one. As requested earlier, kindly do provide sample JAVA console program (runnable) to reproduce the issue on our end, we will check it soon.

Thank you.

ayush217 · January 7, 2016, 12:49am

hi,

This error is occurring when we load workbook object within our product flow exception and is not reproducible in a sample standalone. We have ensured that we are passing the right parameters (path etc.), however it is giving following error in logs.

Although the functionlaity is working fine. There are two options you can help us with here:

In class “com/aspose/cells/a/f/zb” during initialization/loading of workbook object, what could be the reason behind “StringIndexOutOfBoundsException”. Is there any missing/wrong parameter?
Suppress the logs error in following class com/aspose/cells/a/f/zb using log4j.properties file( or relevant log file)

jvm 1 | ERROR [Aj] com/aspose/cells/a/f/zb

jvm 1 | java.lang.StringIndexOutOfBoundsException: String index out of range: -1

jvm 1 | at java.lang.String.substring(String.java:1955) ~[na:1.8.0_45]

jvm 1 | at org.aspectj.weaver.bcel.BcelObjectType.getOuterClass(BcelObjectType.java:904) ~[a

spectjweaver-1.8.6.jar:1.8.6]

jvm 1 | at org.aspectj.weaver.AbstractReferenceTypeDelegate.getFormalTypeParametersFromOuter

Class(AbstractReferenceTypeDelegate.java:108) ~[aspectjweaver-1.8.6.jar:1.8.6]

jvm 1 | at org.aspectj.weaver.bcel.BcelObjectType.ensureGenericSignatureUnpacked(BcelObjectT

ype.java:772) ~[aspectjweaver-1.8.6.jar:1.8.6]

jvm 1 | at org.aspectj.weaver.bcel.BcelObjectType.getDeclaredInterfaces(BcelObjectType.java:

~[aspectjweaver-1.8.6.jar:1.8.6]

jvm 1 | at org.aspectj.weaver.ReferenceType.getDeclaredInterfaces(ReferenceType.java:707) ~[

aspectjweaver-1.8.6.jar:1.8.6]

jvm 1 | at org.aspectj.weaver.ResolvedType.getDirectSupertypes(ResolvedType.java:82) ~[aspec

tjweaver-1.8.6.jar:1.8.6]

jvm 1 | at org.aspectj.weaver.patterns.TypePattern.matchesSubtypes(TypePattern.java:178) ~[a

spectjweaver-1.8.6.jar:1.8.6]

jvm 1 | at org.aspectj.weaver.patterns.TypePattern.matchesStatically(TypePattern.java:130) ~

[aspectjweaver-1.8.6.jar:1.8.6]

jvm 1 | at org.aspectj.weaver.loadtime.ClassLoaderWeavingAdaptor.accept(ClassLoaderWeavingAd

aptor.java:861) ~[aspectjweaver-1.8.6.jar:1.8.6]

jvm 1 | at org.aspectj.weaver.tools.WeavingAdaptor.weaveClass(WeavingAdaptor.java:337) ~[asp

ectjweaver-1.8.6.jar:1.8.6]

jvm 1 | at org.aspectj.weaver.loadtime.Aj.preProcess(Aj.java:121) ~[aspectjweaver-1.8.6.jar:

1.8.6]

jvm 1 | at org.aspectj.weaver.loadtime.ClassPreProcessorAgentAdapter.transform(ClassPreProce

ssorAgentAdapter.java:54) [aspectjweaver-1.8.6.jar:1.8.6]

jvm 1 | at sun.instrument.TransformerManager.transform(TransformerManager.java:188) [na:1.8.

0_45]

jvm 1 | at sun.instrument.InstrumentationImpl.transform(InstrumentationImpl.java:428) [na:1.

8.0_45]

jvm 1 | at java.lang.ClassLoader.defineClass1(Native Method) [na:1.8.0_45]

jvm 1 | at java.lang.ClassLoader.defineClass(ClassLoader.java:760) [na:1.8.0_45]

jvm 1 | at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:142) [na:1.8.0

_45]

jvm 1 | at java.net.URLClassLoader.defineClass(URLClassLoader.java:467) [na:1.8.0_45]

jvm 1 | at java.net.URLClassLoader.access$100(URLClassLoader.java:73) [na:1.8.0_45]

jvm 1 | at java.net.URLClassLoader$1.run(URLClassLoader.java:368) [na:1.8.0_45]

jvm 1 | at java.net.URLClassLoader$1.run(URLClassLoader.java:362) [na:1.8.0_45]

jvm 1 | at java.security.AccessController.doPrivileged(Native Method) [na:1.8.0_45]

jvm 1 | at java.net.URLClassLoader.findClass(URLClassLoader.java:361) [na:1.8.0_45]

jvm 1 | at com.pb.spectrum.platform.server.common.deploy.car.CarClassLoader.loadClass(CarCla

ssLoader.java:84) [spectrum-server-common-deploy-car-0-SNAPSHOT.jar:0-SNAPSHOT]

jvm 1 | at java.lang.ClassLoader.loadClass(ClassLoader.java:357) [na:1.8.0_45]

jvm 1 | at com.aspose.cells.a.f.za.b(Unknown Source) [aspose-cells-8.6.0.jar:8.6.0.0]

jvm 1 | at com.aspose.cells.a.f.zi.iterator(Unknown Source) [aspose-cells-8.6.0.jar:8.6.0.0]

jvm 1 | at com.aspose.cells.zajx.e(Unknown Source) [aspose-cells-8.6.0.jar:8.6.0.0]

jvm 1 | at com.aspose.cells.zajx.a(Unknown Source) [aspose-cells-8.6.0.jar:8.6.0.0]

jvm 1 | at com.aspose.cells.zajw.a(Unknown Source) [aspose-cells-8.6.0.jar:8.6.0.0]

jvm 1 | at com.aspose.cells.Workbook.a(Unknown Source) [aspose-cells-8.6.0.jar:8.6.0.0]

jvm 1 | at com.aspose.cells.Workbook.(Unknown Source) [aspose-cells-8.6.0.jar:8.6.0.0]

jvm 1 | at com.pb.spectrum.edi.spreadsheet.impl.SpreadsheetWorkbookAsposeFactory.createWorkb

ook(SpreadsheetWorkbookAsposeFactory.java:42) [edi-0-SNAPSHOT.jar:0-SNAPSHOT]

jvm 1 | at com.pb.spectrum.edi.spreadsheet.impl.SpreadsheetDataSourceRuntimeManagerAsposeImp

l.getWorkbookInfo(SpreadsheetDataSourceRuntimeManagerAsposeImpl.java:158) [edi-0-SNAPSHOT.jar:0-SNAP

SHOT]

We have a critical delivery next week and looking for some quick solution. If required, let us discuss this on call.

amjad.sahi · January 7, 2016, 2:08am

Hi,

Thanks for providing us further details.

Well, from the stack trace of the exception you pasted, it seems the issue occurred when reading a zip file (e.g XLSX etc.). So, could you give us your template file so we can test and investigate whether we can find the issue on our end or not.

Thank you.