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

Free Support Forum - aspose.com

1 million records

Hi Team,

I have datatable with 1 million records and I want to export the data to excel. I am using below line to do this.

Worksheet.cells.importdatatable(dtExport,False,0,0)

It throws System out of memory Exception.

Do you have any solution for this issue?

Note: This is working fine for 2-3 lakhs records.

Regards,

Niveditha


Hi,


Well, if you are filling large volume of data (e.g 1 million rows etc.), it would demand more and more memory to process the big task. It would be needed least 10 times or more RAM of the size of the final file. It is not final and it can depend on the complexity of your workbook and its data etc. So, I am afraid, we have no better way for ImportDataTable method when you are using it for your requirements (probably a 64 bit machine with a good amount of RAM might process the task fine finally and without producing the out of memory error). We have LightCells API and if you are creating the workbook (from the scratch) on the fly, it may suit your needs which is a more efficient approach to accomplish your task. I have written a sample .NET program for your requirement using the LightCells APIs, please refer to it and you may try to use this approach if it works fine for your needs. I have filled 1 million records to fill into the worksheet from the datatable (which I have already filled this long list of dataset in it).

Sample code:

using System;
using System.Collections.Generic;
using System.Text;
using Aspose.Cells;
using System.Data;

namespace LightOoxmlTest
{
class Program
{
public static void Main(string[] args)
{
//Specify your desired matrix
int rowsCount = 1000000;
int colsCount = 3;

var workbook = new Workbook();
var ooxmlSaveOptions = new OoxmlSaveOptions();

ooxmlSaveOptions.LightCellsDataProvider = new TestDataProvider(workbook, rowsCount, colsCount);

workbook.Save(“e:\test2\myoutput_test1.xlsx”, ooxmlSaveOptions);
}
}

class TestDataProvider : LightCellsDataProvider
{
DataTable dt;
public void FillTable()
{
dt = new DataTable();
dt.Columns.Add(“Column1”, typeof(int));
dt.Columns.Add(“Column2”, typeof(string));
dt.Columns.Add(“Column3”, typeof(int));
for (int i = 0; i < maxRows; i++)
{
dt.Rows.Add(i / 10 + 1, String.Format(“Item {0}”, i), i);
}
}
private int _row = -1;
private int _column = -1;

private int maxRows;
private int maxColumns;

private Workbook _workbook;
public TestDataProvider(Workbook workbook, int maxRows, int maxColumns)
{
this._workbook = workbook;
this.maxRows = maxRows;
this.maxColumns = maxColumns;
FillTable();
}

#region LightCellsDataProvider Members

public bool IsGatherString()
{
return false;
}

public int NextCell()
{
++_column;
if (_column < this.maxColumns)
return _column;
else
{
_column = -1;
return -1;
}
}
public int NextRow()
{
++_row;
if (_row < this.maxRows)
{
_column = -1;
return _row;
}
else
return -1;
}

public void StartCell(Cell cell)
{
cell.PutValue(dt.Rows[_row][_column]);//_row + _column);
}

public void StartRow(Row row)
{
}

public bool StartSheet(int sheetIndex)
{
if (sheetIndex == 0)
{
return true;
}
else
return false;
}

#endregion
}
}



Thanks for your understanding!