How to store data to Excel from DataSet without going cell-by-cell?

Question

Duplicate of: What’s the simplest way to import a System.Data.DataSet into Excel?

Using c# under VS2008, we can create an excel app, workbook, and then worksheet fine by doing this:

        Application excelApp = new Application();
        Workbook excelWb = excelApp.Workbooks.Add(template);
        Worksheet excelWs = (Worksheet)this.Application.ActiveSheet;

Then we can access each cell by "excelWs.Cells[i,j]" and write/save without problems. However with large numbers of rows/columns, we are expecting a loss in efficiency.

Is there a way to "data bind" from a DataSet object into the worksheet without using the cell-by-cell approach? Most of the methods we have seen at some point revert to the cell-by-cell approach. Thanks for any suggestions.

Answer 1

Why not use ADO.NET using the OLEDB provider?

See: Tips for reading Excel spreadsheets using ADO.NET

Answer 2

This is a duplicate question.

See: What's the simplest way to import a DataSet into Excel

(The answer is, use the OleDbConnection and read the dataset from Excel).

Answer 3

Instead of going cell by cell, it is faster to set a 2-dimensional array of objects to an excel range.

object[][] values = ...
ws.Range("A1:F2000").Value = values;

However, the OleDb way is still much faster than above.

Answer 4

You could also render a GridView to a string and save the results to the client as HTML or XLS. If you use the XLS file extension to associate the data with Excel, you'll see an error when you open the file in Excel. It doesn't hurt anything and your HTML data will open and look perfect in Excel...