We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Out of Memory issue while importing datatable to Excel Aspose Cells

Hi,

I am trying to import the data from a DataTable to Excel using Aspose Cells, which is quite large, has more than 1200000 rows. Since excel worksheet can only accommodate 1048576 rows, I am splitting the DataTable in multiple DataTable and then importing the data to excel. But I am getting the out of memory Exception.

My code and stack trace are below.

private void SplitIntoExcel(DataTable dt, Workbook wb, int worksheetNumber, int sheetSize, string fileSaveLocation)
{
try
{
string strLicCells = ConfigurationManager.AppSettings["Lic_Cells"];
Aspose.Cells.License c_license = new Aspose.Cells.License();
c_license.SetLicense(strLicCells);


if (dt.Rows.Count > 0)
{
int dtRowCount = dt.Rows.Count;

if (dtRowCount > sheetSize)
{
int startIndex = 0;
int endIndex = sheetSize;

DataTable dtClone = dt.Clone();

for (int j = startIndex; j <= endIndex; j++)
{
dtClone.ImportRow(dt.Rows[j]);
dt.Rows[j].Delete();
}

dt.AcceptChanges();

if (dtClone.Rows.Count > 0)
{
SaveOptions SaveOptions = new Aspose.Cells.XlsSaveOptions(SaveFormat.Xlsx);
SaveOptions.CachedFileFolder = "C:\\ExFile"; ;

Worksheet worksheet = null;
try
{
worksheet = wb.Worksheets[worksheetNumber];
}
catch (Exception ex)
{
worksheetNumber = wb.Worksheets.Add();
worksheet = wb.Worksheets[worksheetNumber];
}

worksheet.Cells.ImportDataTable(dtClone, true, 0, 0, dtClone.Rows.Count, dtClone.Columns.Count, true, "mm/dd/yyyy");
worksheet.AutoFitColumns();
wb.Save(fileSaveLocation, SaveOptions);
}

dtClone.Clear();
dtClone.Dispose();

worksheetNumber += 1;

if (dt.Rows.Count > 0)
{
SplitIntoExcel(dt, wb, worksheetNumber, sheetSize, fileSaveLocation);
}
}
else
{
Worksheet worksheet = null;
try
{
worksheet = wb.Worksheets[worksheetNumber];
}
catch (Exception ex)
{
worksheetNumber = wb.Worksheets.Add();
worksheet = wb.Worksheets[worksheetNumber];
}
worksheet.Cells.ImportDataTable(dt, true, 0, 0, dt.Rows.Count, dt.Columns.Count, true, "mm/dd/yyyy");
worksheet.AutoFitColumns();
}
}
}

catch (Exception ex)
{
throw new Exception(ex.Message);
}
}

Stack Trace: at Aspose.Cells.Cell. ()
at Aspose.Cells.Cells.ImportDataTable(DataTable dataTable, Boolean isFieldNameShown, Int32 firstRow, Int32 firstColumn, Int32 rowNumber, Int32 columnNumber, Boolean insertRows, String dateFormatString, Boolean convertStringToNumber)
at Aspose.Cells.Cells.ImportDataTable(DataTable dataTable, Boolean isFieldNameShown, Int32 firstRow, Int32 firstColumn, Int32 rowNumber, Int32 columnNumber, Boolean insertRows, String dateFormatString)
at ExcelCreationWF.Form1.SplitIntoExcel(DataTable dt, Workbook wb, Int32 worksheetNumber, Int32 sheetSize, String fileSaveLocation) in c:\Demo Projects\ExcelCreationWF\ExcelCreationWF\Form1.cs:line 404

Can you please help.

Thanks

Hi Nikhil,


Thank you for contacting Aspose support.

Please enable the Memory Preferences before importing the DataTable to the worksheet cells. For more details, please refer to the sub-heading Writing Large Excel Files on the linked article. In case the problem persists, please provide us a simple standalone console application along with your template spreadsheet replicating the issue.