How to speed up dumping a DataTable into an Excel worksheet?

If you have a recordset, the fastest way to write to Excel is CopyFromRecordset.

Interop is inherently very slow. There is a large overhead associated with each call. To speed it up try writing back an object array of data to a range of cells in one assignment statement.

Or if this is a serious problem try using one of the Managed Code Excel extensions that can read/write data using managed code via the XLL interface. (Addin Express, Managed XLL etc.)

Instead of setting cell values one by one, do it in a batch.

Step 1. Transfer the data from your DataTable into an array with the same dimensions.

Step 2. Define an Excel Range object that spans the appropriate range.

Step 3. Set the Range.Value to the array.

This will be a lot faster because you will have a total two calls across the Interop boundary (one to get the Range object, one to set its value), instead of two per cell (get cell, set value).

There is some sample code at MSDN KB article 302096.