Hi
I am a licenses user using aspose cells. As per my understanding aspose cells loads the whole workbook to memory before reading. This leads consuming lots of memory on server. Is there any way or any other aspose product available where we can read the excel line by line or by steams without loading the whole work book to memory.
I need to read around 2-4gb size file and insert into database.
Awaiting your replay , appreciate your prompt response
Thanks
Tushar.
Hi,
Thanks for your posting and using Aspose.Cells for .NET.
I am afraid, you cannot read xls/xlsx file line by line and this feature cannot be implemented because of the nature of these formats.
Now, you have two options highlighted as Red below.
1 - Try loading your workbook partially
a) - If you just want to load data of one worksheet, please try the following 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”)
b) - If you just want to get the all sheets’ name, please use the following code:
C#
dataOption.SheetIndexes = new int[] { };
2 - Try increasing your physical memory.
To get rid of memory issue, you should increase your memory. For 32-bit process, memory limitation is around 4gb but for 64-bit process, memory limitation is more than terabytes, it is unlimited memory almost.
Memory issue occurs for complex and large files. If you find some memory issue is occurring for simpler or smaller files, then you should provide us your sample code and sample xls/xlsx files, we will look into your issue and resolve it.