Sequential reading/writing Excel files

Aspose.Cells Product Family
1


We have a business requirement reading/writing huge Excel files sequentially, and we are interested in Aspose.Cells streaming. Please provide a sample Java file that sequenctially read/write Excel files that do not need a lot of memory, e.g. reading 100k rows x 100 colums with all numbers, how to write a Java program that calculate summation of all rows by columns without loading entire data into memory.

Thank you very much

2

Hi,

Thank you for considering Aspose.

For reading and processing cells in Excel file sequentially with less memory cost, you may try our LightCells APIs. LightCells provides a way for users to read and process cells in Excel file in event-driven mode and it needs much less memory than loading the whole Workbook into memory. Here is a simple example to show how to calculate summation of all cells in every Worksheet:

class SumHandler implements CellHandler

{

private double sum = 0;


public double getSum()

{

return sum;

}


public boolean startSheet(int index)

{

sum = 0;

return true;

}

public boolean startCell(int row, int col)

{

return true;

}

public void process(LightCell lightCell)

{

switch(lightCell.getValueType())

{

case CellValueType.INT:

{

sum += (Integer)lightCell.getValue();

break;

}

case CellValueType.DOUBLE:

{

sum += (Double)lightCell.getValue();

break;

}

}

}

}

public class Test

{

public static void main(String[] args) throws Exception

{

LightCells lightCells = LightCells.getInstance("template.xls", FileFormatType.EXCEL2003);

SumHandler handler1 = new SumHandler();

int count = lightCells.getSheetCount();

for(int i=0; i<count; i++)

{

lightCells.processWorksheet(i, handler1);

System.out.println("Sum of sheet[" + lightCells.getSheetName(i) + "]: " + handler1.getSum());

}

}

}

However, LightCells only supports to fetch simple cell values from template file. It does not support other features such as calculating formulas, saving modified values back to excel file, etc. So, if you need to modify one template file and re-save it, I am afraid you have to load the template file completely (by Workbook.open(…) methods).

And as a reference, following are some other examples of using LightCells:

  1. Performance of ASPOSE

  2. Selectively reading sheets from an Excel dump

  3. Performance of ASPOSE

  4. Does aspose.cell for java support &quot;event-driven&quot; parsing

  5. LightCells API giving the exception - Aspose Cells for Java

Thank You & Best Regards,

3


Thanks, this is great!!! Could you also provide a small Java program writing huge Excel 2007 files with LightCells API?

Thanks a lot.
Hiroshi

4

Hi,

Thank you for considering Aspose.

Please modify the following line of code in the above sample code and use FileFormatType.EXCEL2007 for Excel 2007 xlsx file.

LightCells lightCells = LightCells.getInstance("template.xlsx", FileFormatType.EXCEL2007);

Thank You & Best Regards,

5

Thanks!!! How to write Excel 2003/2007 files with LightCells API?

6

Hi,

Thank you for considering Aspose.

Well, as mentioned in one of my previous posts, LightCells only supports to fetch simple cell values from template file. It does not support other features such as calculating formulas, saving modified values back to excel file, etc. So, if you need to modify one template file and re-save it or create a new file, you will not be able to use LightCells APIs. Please see the following documentation link for your requirement:

Also, I have attached the latest version of Aspose.Cells. If you need any further assistance, please feel free to contact us. We will be happy to help you out.

Thank You & Best Regards,

7

Please let me see LightCells documents including javadoc. Thank you very much!!!

8

Hi,

Please check the following API Reference (classes/interfaces) links regarding LightCells:
http://www.aspose.com/documentation/java-components/aspose.cells-for-java/com/aspose/cells/lightcell.html
http://www.aspose.com/documentation/java-components/aspose.cells-for-java/com/aspose/cells/lightcells.html
http://www.aspose.com/documentation/java-components/aspose.cells-for-java/com/aspose/cells/lightcellssettings.html
http://www.aspose.com/documentation/java-components/aspose.cells-for-java/com/aspose/cells/cellhandler.html


And, we will soon create an article for the LightCells and include in our Aspose.Cells for Java documentation.


Thank you.

9

Hi,

I am evaluating version “aspose-cells-7.4.0-java” but it seams the LightCells class is not available on this version, the links above are no longer valid, so my questions are:



1) do you still support reading huge excel files with LightCells?



2) If not do you have a different approach and some more up to date samples?



Thanks for any help/clues you guys can provide to me.

Iván

10

Hi,

Thanks for your posting and using Aspose.Cells for Java.

Please see the following article relating to LightCells API in Java for your more help.

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

11

Hi,

Thanks for using Aspose.Cells.

For reading large template file with LightCells APIs, it is not supported by
Aspose.Cells from V7.0.0. We may support this feature in later versions when we
finish some other important tasks. But we are afraid it cannot be supported very
soon.

12

Ok, It’s a shame, it was a promising option to use Aspose.Cells to read huge excel files using the event approach that LightCells seemed to provide.



Thanks any way for the prompt responses.

13

Hi,

Thanks for your posting and using Aspose.Cells.

You are welcome. We are sorry for the inconvenience caused.