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).
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
}
}