Problem with export to excel hudge amount of data

Aspose.Cells Product Family
1

Hi,


when we try to import directly from datareader over 1,048,576 rows to xlsx (excel) file the data are cut to 1,048,576 - maximum row limit for excel sheet.
Workbook wbook = new Workbook();
Worksheet sheet = wbook.Worksheets[0];
sheet.Cells.ImportDataReader(dr, true, 0, 0, false,“yyyy-MM-dd”,false);
sheet.AutoFitColumns();

Is any method to split data into more than one sheet during import from datareader (not from datatable)? In this case excel is not going to cut data to 1,048,576 rows.
Thanks for help.



2

Hi,


Well, for XLSX file format, MS Excel only allows 1048576 rows and 16384 columns in a single worksheet. In short, you cannot insert more rows than its capacity in MS Excel either, it is the limitation put forth by MS Excel and nothing to do with Aspose.Cells. Well, for your task, you may split the data into multiple sheets filled with data) but you have to use ImportDataRow() method to insert one record at a time as you cannot do that using ImportDataReader method which imports all the rows/ records in one go. I think you may simply copy/ convert your DataReader object’s data to fill a DataTable first by using your own .NET codes. Please see the following sample code that provides some hints and shows how to split huge list of data to fill multiple worksheet in a single workbook, you may change/ update the code segment according to your needs:
e.g
Sample code:

Workbook excel = new Workbook(FileFormatType.Xlsx);
excel.Worksheets.Clear();
DataTable dt = new DataTable(“Products”);
dt.Columns.Add(“Col_ID”, typeof(Int32));
dt.Columns.Add(“Col_Name”, typeof(string));

for (int x = 0; x <= 120000; x++)
{
DataRow dr = dt.NewRow();
dr[“Col_ID”] = x;
dr[“Col_Name”] = “Data” + x.ToString();
dt.Rows.Add(dr);
}
int rownum = 0;
int i;
int row = 0;
int ct = 0;
Worksheet worksheet;

//Insert 40000 records in each worksheet (total rows: 120000)
for (int cnt = 0; cnt <= 3; cnt++)
{
worksheet = excel.Worksheets[excel.Worksheets.Add()];
row = ct;
rownum = 0;
for (i = row; i <= row + 40000; i++)
{

if (i > 120000)
{
break;
}
else
{
worksheet.Cells.ImportDataRow(dt.Rows[i], rownum++, 0);
ct++;

}
}

}
excel.Save(“e:\test2\out1.xlsx”);

Hope, this helps a bit.

Thank you.
3

Hi,

It is impossible to load first this data into datatable, becouse it is web application working on IIS and with this amount of data we have out of memory exception (especially if many users are working). For us the best solution would be if aspose software during direct import from datareader were able to automatically split data into few worksheets (if amout of data is greater than 1048576 rows). Is possible in the future Aspose prepare this kind of fix?
4

Hi,


Well, you may try to convert the DataReader object to fill a DataTable, see the document for your reference:
http://www.dotnetcurry.com/showarticle.aspx?ID=143
And even if you use ImportDataReader() method to import it to fill the worksheet in an Asp.NET web application, it would also demand certain amount of memory as you are importing huge list of data into the worksheet.

Regarding your requirement to support splitting data in ImportDataReader() method, we need to discuss this with relevant developers to check its feasibility if we can support it in future versions. Once we have done it and if it is feasible enough, we will log a ticket for it.

We will get back to you soon.

Thank you.


5

Hi,


We have evaluated your requested feature. I am afraid, we may not implement it in the product. So, you have to simply implement it by yourself (you may also devise the approach mentioned in the above posts). You should write your query to get the count of the data and then split the data using .NET APIs by yourself with that count etc.

Thank you.