Insert SQL query result into Excel worksheet using VSTO

Question

I'm trying to write Excel application-level add-in. It makes SQL query to the database and populates worksheet with its results. I thought, it should be simple... But not. Is there any way to insert DataTable into excel worksheet? Something like this:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    string cmdString = "SELECT * FROM [Table];" // Simplifyed query, in my add-in I need to JOIN 3 tables
    SqlCommand cmd = new SqlCommand(cmdString, connection);
    SqlDataAdapter sda = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    sda.Fill(dt);

    // What I need to write here to insert my DataTable contents into worksheet?
}

Maybe, I should use another approach (not DataTable)? However, my query can return up to 100000 rows of data with 4 columns. So, cell-by-cell pasting will not work, I think.

This solution worked for me http://www.clear-lines.com/blog/post/Write-data-to-an-Excel-worksheet-with-C-fast.aspx. I made 2D array of objects and assigned it to the range. — Roman Kolbovich, Oct 27 '14 at 05:41

score 0 · Answer 1 · answered Oct 29 '14 at 12:23

If you are prepared to jump through a few hoops then CopyFromRecordset is hands-down the fastest way I have seen to handle this:

http://msdn.microsoft.com/en-us/library/office/ff839240(v=office.15).aspx

I can populate a million rows from SQL Server into Excel in a few seconds using CopyFromRecordset.

Basically you need to create a recordset instead of a data table and you need to use ADO (or DAO). Personally I like to keep this code separate from everything else and only use ADO for this function as it has inherent weaknesses. For example you can't use "using" with ADO connections, etc.

Here is a more complete example (in VB but easy enough to change to C#):

http://support.microsoft.com/kb/246335/

Insert SQL query result into Excel worksheet using VSTO

1 Answers1