We are trying to load an excel (xlsx) file with data in columns A - CI and over 200,000 rows.
It fails with an out of memory exception.
How do we load/save large excel files?
Hi,
Thanks for your posting and using Aspose.Cells for .NET.
Please download and try the latest version:
Aspose.Cells
for .NET v7.1.1.5
If the problem occurs again, please provide us your source file throwing exception.
Please also share your Sytem/Runtime Environment.
If the out of memory error is thrown while saving workbook (e.g Workbook.Save), you may try to set SaveOptions.CachedFileFolder
while saving the workbook/file. When this option is not set, we have to
save something to memory first, then save to the file. Setting this
option will directly save to the file.
Please see the sample code below
VB.NET
Dim SaveOptions As SaveOptions = New Aspose.Cells.XlsSaveOptions(SaveFormat.Excel97To2003)
SaveOptions.CachedFileFolder = stringFilePath
worbkook.Save(stringFileName, SaveOptions)
OR
Dim SaveOptions As SaveOptions = New Aspose.Cells.OoxmlSaveOptions(SaveFormat.Xlsx)
SaveOptions.CachedFileFolder = stringFilePath
worbkook.Save(stringFileName, SaveOptions)
The problem is not in saving. If we have an xlsx file over 100meg the Aspose Workbook process goes abve 2 gig in memory usage. Then we get out of memory. Are you able to load large xlxs files?
Hi,
Aspose.Cells for .NET itself does not have any limitation of loading files. The bigger you create the Excel file, the more memory you may require.
Also, if your file has lot of images then those images will also take lot of memory.
a) - If you just want to load data of one worksheet, please try the following code with the latest version: Aspose.Cells
for .NET v7.1.1.5
Please download the source.xlsx file from the given link to try this code.
C#
LoadOptions loadOptions = new LoadOptions(LoadFormat.Xlsx);
LoadDataOption dataOption = new LoadDataOption();
dataOption.SheetIndexes = new int[] { 0 };
dataOption.ImportFormula = true;
loadOptions.LoadDataOnly = true;
//Specify the LoadDataOption
loadOptions.LoadDataOptions = dataOption;
//Create a Workbook object and opening the file from its path
Workbook wb = new Workbook(Constants.sourcePath + “Source.xlsx”, loadOptions);
Worksheet sheet1 = wb.Worksheets[0];
Worksheet sheet2 = wb.Worksheets[1];
Worksheet sheet3 = wb.Worksheets[2];
Cell cellA1OfSheet1 = sheet1.Cells[“A1”];
Cell cellA1OfSheet2 = sheet2.Cells[“A1”];
Cell cellA1OfSheet3 = sheet3.Cells[“A1”];
Debug.Assert(cellA1OfSheet2.Type == CellValueType.IsNull, “This message should not appear”);
Debug.Assert(cellA1OfSheet3.Type == CellValueType.IsNull, “This message should not appear”);
Dim filePath As String = "F:\Shak-Data-RW\Downloads\source.xlsx"
Dim loadOptions As LoadOptions = New LoadOptions(LoadFormat.Xlsx)
Dim dataOption As LoadDataOption = New LoadDataOption()
dataOption.SheetIndexes = New Integer() {0}
dataOption.ImportFormula = True
loadOptions.LoadDataOnly = True
'Specify the LoadDataOption
loadOptions.LoadDataOptions = dataOption
'Create a Workbook object and opening the file from its path
Dim wb As Workbook = New Workbook(filePath, loadOptions)
Dim sheet1 As Worksheet = wb.Worksheets(0)
Dim sheet2 As Worksheet = wb.Worksheets(1)
Dim sheet3 As Worksheet = wb.Worksheets(2)
Dim cellA1OfSheet1 As Cell = sheet1.Cells("A1")
Dim cellA1OfSheet2 As Cell = sheet2.Cells("A1")
Dim cellA1OfSheet3 As Cell = sheet3.Cells("A1")
Debug.Assert(cellA1OfSheet2.Type = CellValueType.IsNull, "This message should not appear")
Debug.Assert(cellA1OfSheet3.Type = CellValueType.IsNull, "This message should not appear")
Dim loadOptions As LoadOptions = New LoadOptions(LoadFormat.Xlsx)
Dim dataOption As LoadDataOption = New LoadDataOption()
dataOption.SheetIndexes = New Integer() {0}
dataOption.ImportFormula = True
loadOptions.LoadDataOnly = True
'Specify the LoadDataOption
loadOptions.LoadDataOptions = dataOption
'Create a Workbook object and opening the file from its path
Dim wb As Workbook = New Workbook(filePath, loadOptions)
Dim sheet1 As Worksheet = wb.Worksheets(0)
Dim sheet2 As Worksheet = wb.Worksheets(1)
Dim sheet3 As Worksheet = wb.Worksheets(2)
Dim cellA1OfSheet1 As Cell = sheet1.Cells("A1")
Dim cellA1OfSheet2 As Cell = sheet2.Cells("A1")
Dim cellA1OfSheet3 As Cell = sheet3.Cells("A1")
Debug.Assert(cellA1OfSheet2.Type = CellValueType.IsNull, "This message should not appear")
Debug.Assert(cellA1OfSheet3.Type = CellValueType.IsNull, "This message should not appear")
b) - If you just want to get the all sheets’ name, please use the following code:
C#
dataOption.SheetIndexes = new int[] { };