java.lang.OutOfMemoryError: Java heap space issue- upon combining 4 workbook of 4.3 MB each

Aspose.Cells Product Family
1

Hi ,

Problem: Unable to create a Excel Workbook with 533 worksheet data using aspose api-- due to java.lang.OutOfMemoryError: Java heap space issue.

I have tried 3 different approach of creating a workbook with 533 worksheet data (No pivot table, chats etc... )

Approach # 1: Create a workbook - Loop to copy worksheet data from source worksheet data for 533 times fails on

com.aspose.cells.Worksheet(srcworksheet)

Approach # 2:

Open and Save Workbook with a source file1 containing 300 worksheet data.

And Open 2nd Workbook with source file 2 containing remaining 233 worksheet data.

But workbook.open fails with out of memory

Approach #3

Create and save data into bunch of files (6 files) Workbook files and save to file system.

Use workbook.combine api to combine all workbook data into one File

But same issue - Java heap space issue. -out of memory issue.(as Combine calls copy eventually...)

at java.lang.OutOfMemoryError.()V (Unknown Source)
at com.aspose.cells.ad.(II)V (Unknown Source)
at com.aspose.cells.Row.(Lcom/aspose/cells/Worksheet;I)V (Unknown Source)
at com.aspose.cells.Rows.a(Lcom/aspose/cells/Rows;)V (Unknown Source)
at com.aspose.cells.Worksheet.copy(Lcom/aspose/cells/Worksheet;)V (Unknown Source)
at com.aspose.cells.Workbook.combine(Lcom/aspose/cells/Workbook;)V (Unknown Source)
at aces.reporting.outputhandlers.XLSReportWorkbookService.splitDataIntoWorksheet(Ljava/lang/String;Laces/external/excel/Worksheet;Ljava/util/Map;Ljava/lang/Integer;Ljava/util/List;)V (XLSReportWorkbookService.java:315)

Here is the snippet of the code

String finalFile = "C:/LOADTEST/FinalCombinedReport.xlsx";

Workbook finalWorkbook = new AsposeWorkbook();

//listOfWorkbooks.size()== 5

finalWorkbook.getInternalWorkbook().open(listOfWorkbooks.get(0),FileFormatType.XLSX);

for ( int bookCount = 1; bookCount < listOfWorkbooks.size(); bookCount++){

Workbook srcWorkbook = new AsposeWorkbook();

srcWorkbook.getInternalWorkbook().open(listOfWorkbooks.get(bookCount),FileFormatType.XLSX);

finalWorkbook.getInternalWorkbook().combine(srcWorkbook.getInternalWorkbook());

}

finalWorkbook.getInternalWorkbook().save(finalFile,FileFormatType.XLSX);

}

Thanks for the help in advance!

2

Hi,

Please download and try our latest version: Aspose.Cells for Java 7.0.0 and let us know your feedback.

3

Hi Shakeel,

Seems like this new version of ASPOSE 7.0.0 is slower than previous one- as it seems like taking longer in copying the worksheet and + I still get out of memory issue. FYI- max heap size is 1GB on local JVM. Here is my prototype code. And sourceFile is 10KB which has 1 worksheet. My intention is to create a new workbook and copy from sourceFile's worksheet with smaller sets of data as delete cells after copy worksheet. If you like to try the code then could provide you the sample test file.

Thanks for your help in advance.

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

String srcFilePath = "C:/ACES/LOADTEST/Elemental_Cost_Summary_(633-4)_08_30_11_03_53_46_045_PM.xls";

String finalFilePath = "C:/ACES/LOADTEST/FinalReport.xls";

com.aspose.cells.Workbook srcWorkbook = new com.aspose.cells.Workbook(srcFilePath);

com.aspose.cells.Worksheet srcWorksheet = srcWorkbook.getWorksheets().get(0);

System.out.println("Intial Max worksheet "+srcWorkbook.getWorksheets().getCount());

//srcWorkbook1.getInternalWorkbook().save(srcFilePath,FileFormatType.XLSX);

com.aspose.cells.Workbook finalWorkbook = new com.aspose.cells.Workbook(finalFilePath);

for (int i = 0; i < 300 ; i++){

com.aspose.cells.Worksheet newSheet = finalWorkbook.getWorksheets().add("MyTest_9_9_"+(i+1));

newSheet.copy(srcWorksheet);

newSheet.getCells().deleteRange(50,0,15978,10,ShiftType.UP);

System.out.println("Worksheet #"+i);

}

finalWorkbook.save(finalFilePath);

System.out.println("After combine Max worksheet "+finalWorkbook.getWorksheets().getCount());

System.out.println("Saved !");

}

--- error stack

Worksheet #298

Worksheet #299

Exception in thread "main" java.lang.OutOfMemoryError: Java heap space

at com.aspose.cells.b.a.d.h.a(Unknown Source)

at com.aspose.cells.b.a.d.h.b(Unknown Source)

at com.aspose.cells.b.a.d.h.b(Unknown Source)

at com.aspose.cells.dU.a(Unknown Source)

at com.aspose.cells.Z.b(Unknown Source)

at com.aspose.cells.Cells.a(Unknown Source)

at com.aspose.cells.Cells.a(Unknown Source)

at com.aspose.cells.Worksheet.a(Unknown Source)

at com.aspose.cells.WorksheetCollection.b(Unknown Source)

at com.aspose.cells.WorksheetCollection.a(Unknown Source)

at com.aspose.cells.WorksheetCollection.a(Unknown Source)

at com.aspose.cells.Workbook.a(Unknown Source)

at com.aspose.cells.Workbook.save(Unknown Source)

at com.aspose.cells.Workbook.save(Unknown Source)

at aces.reporting.outputhandlers.XLSReportWorkbookService.main(XLSReportWorkbookService.java:482)

4
<!–[if gte mso 10]>

/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman","serif";}

<![endif]–>

Hi,


To build the complete Workbook model that contains large amount of worksheets and data, it is sure that lot of memory will be required. If you mainly want to combine cells data from source template file to the final excel file, we think you can try LightCellsDataProvider which allows you to save cell data into the resultant XLSX file directly without building the complete data model for all cells (You may download the latest fixed version v7.0.1.1 here). If there are many other objects such as formulas, drawings and so on that will cost most of the memory, I am afraid there is no better solution for the memory issue.


Thank you.