HUGE data export to Excel. Multiple sheets

Question

I would like to export more than 2,000,000 rows to Excel (it will get larger in the future). One sheet handles 1,048,576 rows. I would like to put 300,000 on each sheet (so that Excel could work properly).

I tried creating OLE object to put data on different sheets. It worked for small number of data but it took too long and it caused an error for large number of data. Then I tried component of exporting to Excel, and it took less time but I don't know how to distribute data on sheets in this case. Using SQL for distributing on sheets causes memory filling error.

Can you suggest any other ways?

Answer 1

We cannot see your code, but we can only imagine that you are writing one cell at a time. Instead you should do the following:

1. Place all of your data into a variant array.
2. Create an Excel Range object containing the range of cells to be populated.
3. Assign your variant array to that Range object.

This is the efficient way to perform block assignment using Excel automation.

Furthermore, automating Excel is probably not the most effective way to generate Excel files. You are better of generating the files directly without invoking the Excel application. For very large files the most efficient format for writing and reading is the binary OpenXML format, with the .xlsb extension. Either find a component to generate such files, or write your own code to the OpenXML format specification.