Writing 1 million rows in Excel within 5 mins or less

Aspose.Cells Product Family
1

Hi,

I am using Aspose.Cells 7.1.0.0.
Machine config:
Quad Core
16 GB
To write 1 million rows ( each row having 26 columns) to xlsx file, it takes 9 mins:
_worksheetInstance.Cells.ImportDataTable(currentTable, IsHeaderRequired, (IsHeaderRequired ? 15 : 0), 0, initialTable.Rows.Count, initialTable.Columns.Count, false, “yyyy/MM/dd”, true);

I have divided 1 million rows into 4 chunks and tried to write using TPL in the same worksheet which gave different errors like Index out of Array, System .Out.of Memory Exception at different times.

Using Light Cells API , time taken has reduced to 6 mins.

Is there any way to reduce the time for excel generation further?

Also how to use LightCells API for xls, because when I am providing SaveFormatType of xls, data is not saved in excel.
ooxmlSaveOptions = new OoxmlSaveOptions(SaveFormat.Excel97To2003);
ooxmlSaveOptions.LightCellsDataProvider = new TestDataProvider(_workbook, resultSet.Tables[0].Rows.Count +(IsHeaderRequired?16: 0), resultSet.Tables[0].Columns.Count,resultSet.Tables[0],tableHeader,IsHeaderRequired);

Regards,
Ranadeep
2

Hi Randeep,


Thank you for considering Aspose APIs.

We have evaluated your presented scenario against the latest version of Aspose.Cells for .NET 8.3.0 & the following piece of code. We have noticed that the latest revision of Aspose.Cells APIs take almost 4 mins & 34 secs to import 1 million rows of data and write the resultant spreadsheet onto the disc. Please note, I have a 1st generation Core i7 with 6 GB of RAM, and I believe your system specifications are better than mine so the performance would be even better on your end. Please give a try to the latest version of Aspose.Cells for .NET 8.3.0 (link shared above) with both methods (plain data import & LightCells APIs), and let us know if your feedback.

C#

var book = new Workbook();
var sheet = book.Worksheets[0];
sheet.Cells.ImportDataTable(table, true, 0, 0, table.Rows.Count, table.Columns.Count, false, “yyyy/MM/dd”, true);
book.Save(“D:/temp/output.xls”, SaveFormat.Excel97To2003);


static DataTable GetTable() { DataTable table = new DataTable(); table.Columns.Add("Col1", typeof(int)); table.Columns.Add("Col2", typeof(string)); table.Columns.Add("Col3", typeof(string)); table.Columns.Add("Col4", typeof(DateTime)); table.Columns.Add("Col5", typeof(int)); table.Columns.Add("Col6", typeof(string)); table.Columns.Add("Col7", typeof(string)); table.Columns.Add("Col8", typeof(DateTime)); table.Columns.Add("Col9", typeof(int)); table.Columns.Add("Col10", typeof(string)); table.Columns.Add("Col11", typeof(string)); table.Columns.Add("Col12", typeof(DateTime)); table.Columns.Add("Col13", typeof(int)); table.Columns.Add("Col14", typeof(string)); table.Columns.Add("Col15", typeof(string)); table.Columns.Add("Col16", typeof(DateTime)); table.Columns.Add("Col17", typeof(int)); table.Columns.Add("Col18", typeof(string)); table.Columns.Add("Col19", typeof(string)); table.Columns.Add("Col20", typeof(DateTime)); table.Columns.Add("Col21", typeof(int)); table.Columns.Add("Col22", typeof(string)); table.Columns.Add("Col23", typeof(string)); table.Columns.Add("Col24", typeof(DateTime)); table.Columns.Add("Col25", typeof(int)); table.Columns.Add("Col26", typeof(string));
for (int index = 0; index <= 1000000; index++) { table.Rows.Add(25, "Indocin", "David", DateTime.Now, 25, "Indocin", "David", DateTime.Now, 25, "Indocin", "David", DateTime.Now, 25, "Indocin", "David", DateTime.Now, 25, "Indocin", "David", DateTime.Now, 25, "Indocin", "David", DateTime.Now, 25, "Indocin"); } return table; }

3

Thanks Babar, for your reply.
I was able to generate xlsx files really fast using Aspose Light Cells API but unable to generate xls using Aspose Light Cells.

Little Background:
I need to support both xls and xlsx in my application and app should be able to generate 5 million plus records. So if user selects xls/xlsx then multiple xls/xlsx files will be generated each having 65,000/1 million rows. I am using TPL and each thread will call sp to return 65,000/1 million rows based on xls/xlsx and write into files.
Aspose .Light Cells helps me to generate 5 million xlsx files ( i.e. 5 xlsx files) in 17 mins, using TPL,which is pretty good.
I would like to use Aspose Light Cells for xls as well but unable to.
Whenever I use:
var ooxmlSaveOptions = new OoxmlSaveOptions(SaveFormat.Excel97To2003);
ooxmlSaveOptions.LightCellsDataProvider = new TestDataProvider(_workbook, resultSet.Tables[0].Rows.Count +(IsHeaderRequired?16: 0), resultSet.Tables[0].Columns.Count,resultSet.Tables[0],tableHeader,IsHeaderRequired);
I get blank file even for Aspose 8.3.0.0.
4

Hi Randeep,


Thank you for trying out the latest version of Aspose.Cells for .NET 8.3.0, and we are glad to know that you are able to observe the improvement in performance while generating XLSX file.

In order to troubleshoot your recently shared scenario (creation of blank XLS), we would request you to please submit an executable standalone application replicating the problem with latest version of Aspose.Cells for .NET 8.3.0. Please note, we need the sample application to re-create the exact sequence of routines that is causing the problem on your side. Moreover, if you are importing the data directly from database, we would request you to dynamically create an equivalent/similar data source within your code/sample application so that we do not require the database connectivity to investigate the matter on our end.

Thank you for your cooperation & understanding.
5

Hi Babar,


Please find attached a console application using xls and Light cells API.

Regards,
Ranadeep
6

Hi Ranadeep,


Many thanks for the sample application. We are currently looking into the matter, and we will shortly get back to you with updates in this regard.
7

Hi Ranadeep,


Thank you for your patience.

Please make the following changes in your code (highlighted in red) and it should work perfectly for the XLS spreadsheet format.

C#

var saveOptions = new Aspose.Cells.XlsSaveOptions(); //new OoxmlSaveOptions(SaveFormat.Excel97To2003);
saveOptions.LightCellsDataProvider = new TestDataProvider(book, table.Rows.Count, table.Columns.Count, table);
book.Save(“D:/output.xls”, saveOptions);

8

Thanks a ton, Babar!!! You guys are really prompt and it worked!!!

However it will be good if the same class is giving both xls and xlsx light cells option as that will help to write cleaner code.

Regards,
Ranadeep
9

Hi Ranadeep,


Good to know that you are up & running.

Each Aspose.Cells’ SaveOptions class represents a distinct file format so it’s best to keep the format specific traits in separate classes. This is also because Aspose.Cells APIs provide the conversion to non-spreadsheet file formats such as follow.

  • HtmlSaveOptions
  • ImageSaveOptions
  • PdfSaveOptions
  • TxtSaveOptions
  • PdfSaveOptions
  • XpsSaveOptions

Please note that specifying SaveFormat parameter is optional while using any of the SaveOptions classes.