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

Free Support Forum - aspose.com

Problem with export to excel 97 to 2003 hudge amount of data

Hi,

My user request to have excels files with hudge amount of data (over 200.000 rows and 40 columns).
Data I receive directly from data base using iDB2DataReader.
To create aspose workbook I use below code:

Workbook wbook = new Workbook();
Worksheet sheet = wbook.Worksheets[0];
sheet.Cells.ImportDataReader(dr, true, 0, 0, false,“yyyy-MM-dd”,false);
wbook.Save(fileName,SaveFormat.Excel97To2003);

But I have problem becouse in excel file data are cut (in one excel sheet is only 64k rows - excel restictions). I know that in newest excel (*.xslt) this restriction does not exists, but user must have files in old excel format.
Is there any method to create in excel file many workseets each with 64k rows to have all data?

Hi,


Well, as you know XLS (MS Excel 97-2003) file format only allows about 65k rows with 256 columns to be filled in a single worksheet, we cannot change this limitation for the format.

Well, you have to devise your own codes accordingly. For example, to split the data into multiple excel files (i.e…, you may create multiple excel files having worksheets filled with data) with ImportDataRow() method (the performance might be affected but there is no better way to cope with it). Anyways, following sample code provides some hints and shows how to split huge list of data to fill multiple worksheet in a single workbook:
e.g
Sample code:

Workbook excel = new Workbook();
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\splitdatatest1.xls”);


Hope, this helps a bit.

Thank you.