Optimizing Memory Usage while Working with Big Files having Large Datasets

When building a workbook with large data sets, or reading a big Microsoft Excel file, the total amount of RAM the process will take is always a concern. There are measures which can be adapted to cope with the challenge. Aspose.Cells provides some relevant options and API calls to lower, reduce and optimize memory use. Also, it can help the process to work more efficiently and run faster.

Use MemorySetting.MEMORY_PREFERENCE option to optimize memory used for cells data to decrease the overall memory cost. When building large data set for cells, it can save a certain amount of memory compared to using the default setting MemorySetting.NORMAL.

Optimizing Memory

Reading Large Excel Files

The following example shows how to read a large Microsoft Excel file in optimized mode.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getDataDir(ReadLargeExcelFiles.class);
// Specify the LoadOptions
LoadOptions opt = new LoadOptions();
// Set the memory preferences
opt.setMemorySetting(MemorySetting.MEMORY_PREFERENCE);
// Instantiate the Workbook
// Load the Big Excel file having large Data set in it
Workbook wb = new Workbook(dataDir + "Book1.xlsx", opt);

Writing Large Excel Files

The following example shows how to write a large dataset to a worksheet in optimized mode.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(WritingLargeExcelFiles.class) + "articles/";
// Instantiate a new Workbook
Workbook wb = new Workbook();
// Set the memory preferences
// Note: This setting cannot take effect for the existing worksheets that are created before using the below line of code
wb.getSettings().setMemorySetting(MemorySetting.MEMORY_PREFERENCE);

/*
* Note: The memory settings also would not work for the default sheet i.e., "Sheet1" etc. automatically created by the
* Workbook. 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);
// Input large dataset into the cells of the worksheet.Your code goes here.

// Get cells of the newly created Worksheet "Sheet2" whose memory setting is same with the one defined in
// WorkbookSettings:
cells = wb.getWorksheets().add("Sheet2").getCells();

Caution

The default option, MemorySetting.NORMAL is applied for all versions. For some situations, such as building a workbook with a large data set for cells, the MemorySetting.MEMORY_PREFERENCE option may optimize the memory use and decrease the memory cost for the application. However, this option may degrade performance in some special cases such as follow.

1. Accessing Cells Randomly and Repeatedly: The most efficient sequence for accessing the cells collection is cell by cell in one row, and then row by row. Especially, if you access rows/cells by the Enumerator acquired from Cells, RowCollection and Row, the performance would be maximized with MemorySetting.MEMORY_PREFERENCE.
2. Inserting & Deleting Cells & Rows: Please note that if there are lots of insert/delete operations for Cells/Rows, the performance degradation will be notable for MemorySetting.MEMORY_PREFERENCE mode as compared to the MemorySetting.NORMAL mode.
3. Operating on Different Cell Types: If most of the cells contain string values or formulas, the memory cost will be the same as MemorySetting.NORMAL mode but if there are lots of empty cells, or cell values are numeric, bool and so on, the MemorySetting.MEMORY_PREFERENCE option will give better performance.