Optimizing Memory Usage while Working with Big Files having Large Datasets with C++

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 that can be adapted to cope with the challenge. Aspose.Cells provides some relevant options and API calls to lower, reduce, and optimize memory use. It can also help the process work more efficiently and run faster.

Use the MemorySetting.MemoryPreference option to optimize memory use for cell data and decrease the overall memory cost. When building a 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.

#include <iostream>  
#include "Aspose.Cells.h"  
using namespace Aspose::Cells;  

int main()  
{  
    Aspose::Cells::Startup();  

    // Source directory path  
    U16String srcDir(u"..\\Data\\01_SourceDirectory\\");  

    // Specify the LoadOptions.  
    LoadOptions opt;  

    // Set the memory preferences.  
    opt.SetMemorySetting(MemorySetting::MemoryPreference);  

    // Instantiate the Workbook  
    // Load the big Excel file containing a large dataset.  
    Workbook wb(srcDir + u"Book1.xlsx", opt);  

    std::cout << "Workbook loaded successfully with memory preference setting!" << std::endl;  

    Aspose::Cells::Cleanup();  
}  

Writing Large Excel Files

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

#include <iostream>  
#include "Aspose.Cells.h"  

using namespace Aspose::Cells;  

int main()  
{  
    Aspose::Cells::Startup();  

    // Instantiate a new Workbook  
    Workbook wb;  

    // Set the memory preferences  
    // Note: This setting cannot take effect for worksheets created before this line of code.  
    wb.GetSettings().SetMemorySetting(MemorySetting::MemoryPreference);  

    // Note: The memory settings also would not work for the default sheet, i.e., "Sheet1", automatically created by the Workbook.  

    // To change the memory setting of existing sheets, please change the memory setting for them manually:  
    Cells cells = wb.GetWorksheets().Get(0).GetCells();  
    cells.SetMemorySetting(MemorySetting::MemoryPreference);  

    // 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 the same as the one defined in WorkbookSettings:  
    cells = wb.GetWorksheets().Add(u"Sheet2").GetCells();  

    // Input large dataset into the cells of the worksheet.  
    // Your code goes here.  
    // .........  

    Aspose::Cells::Cleanup();  
    return 0;  
}  

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.MemoryPreference option may optimize memory use and decrease the memory cost for the application. However, this option may degrade performance in some special cases as follows.

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.MemoryPreference.
2. Inserting & Deleting Cells & Rows: Please note that if there are many insert/delete operations for cells/rows, the performance degradation will be notable for MemoryPreference mode compared to the 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 Normal mode, but if there are many empty cells or cell values are numeric, boolean, etc., the MemorySetting.MemoryPreference option will give better performance.