Datatable directly to Excel C#

Question

I run a stored procedure that I want to dump on an Excel template.

It currently works, but just takes way too long. In SQL Server Management Studio, the query runs fine, but when I am just writing to the template it is really slow.

Can anyone suggest a more efficient way to achieve the same result?

Here is part of my code:

sdate = StartDate.Value.ToString();
edate = EndDate.Value.ToString();

Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet aSheet;

try
{
//Start Excel and get Application object.
oXL = new Excel.Application();
oXL.Visible = true;

//open the excel template
oWB = oXL.Workbooks.Open("C:\\TEMP\\template.xlsm");

//oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));

//Call to service
//aSheet = (Excel._Worksheet)oWB.Worksheets.get_Item(1);
aSheet = (Excel._Worksheet)oWB.ActiveSheet;
//backgroundWorker1.ReportProgress(i++);
writedata_from_proc(aSheet, "dbo.CODE_RED_2017");
//backgroundWorker1.ReportProgress(i++);


 //Make sure Excel is visible and give the user control
//of Microsoft Excel's lifetime.
//backgroundWorker1.ReportProgress(i++);
MessageBox.Show("Data extraction complete");
oXL.Visible = true;
oXL.UserControl = true;
//SaveExcel(oWB);

//clean up the COM objects to remove them from the memory
Marshal.FinalReleaseComObject(aSheet);
Marshal.FinalReleaseComObject(oWB);
Marshal.FinalReleaseComObject(oXL);
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);

MessageBox.Show(errorMessage, "Error");
}
    }

Here is the code I initially missed:

public void writedata_from_proc(Excel._Worksheet oWS,string sentproc)
        {
            int rowCount = 0;
            SqlCommand cmd = new SqlCommand(sentproc.ToString());
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@start_date", SqlDbType.DateTime).Value = getsdate();
            cmd.Parameters.Add("@end_date",SqlDbType.DateTime).Value=getedate();
            cmd.CommandTimeout = 0;

            DataTable dt = GetData(cmd);
            oWS.UsedRange.Rows.Count.ToString();
            if (sentproc.Contains("CODE_RED"))
            {
               rowCount = 1;
            }
            else
            {
                rowCount = oWS.UsedRange.Rows.Count;
        }
        foreach (DataRow dr in dt.Rows)
        {
            rowCount += 1;
            for (int i = 1; i < dt.Columns.Count + 1; i++)
            {
                // Add the header the first time through 
                if (rowCount == 2)
                {
                    oWS.Cells[1, i] = dt.Columns[i - 1].ColumnName;
                }
                oWS.Cells[rowCount, i] = dr[i - 1];
            }
        }

    }

Answer 1

Excel already has a built in tool that does exactly this, and it takes no longer than it would to run the query and fetch the results. It's called MS Query. In a nutshell, you would:

1. Go to the "Data" tab on the ribbon of Excel
2. Select "Get External Data" (ribbon group) -> "From Other Sources" -> "From SQL Server." I assume this is SQL Server. From your syntax, it's possible it could be Sybase, in which case you can still do this via ODBC (a few options below SQL Server)
3. Bypass all of the designers, and you will land in the MS Query Window. From here, you can edit the SQL Directly and enter your SQL -- exec dbo.CODE_RED_2017
4. When you close MS Query, it will ask you where you want to put the data. Pick a cell.
5. Voila

Best of all, when it's time to refresh, you right-click on the table and select "Refresh" and it will re-execute your procedure (or query). In my experience, Excel actually renders the data faster than most database browsers.

Here's a Microsoft link with more details:

https://support.office.com/en-us/article/Use-Microsoft-Query-to-retrieve-external-data-42a2ea18-44d9-40b3-9c38-4c62f252da2e?ui=en-US&rs=en-US&ad=US&fromAR=1

So, you don't need C# at all. That said, if you're automating this somehow through C#, this can also be done that way as well. Here is an example:

string sql = "exec dbo.CODE_RED_2017";
string source = "your connection string here";
Excel.Range r = activeSheet.Range["A1"];

Excel.ListObject lo = sheet.ListObjects.AddEx(Excel.XlListObjectSourceType.xlSrcQuery,
    source, true, Excel.XlYesNoGuess.xlGuess, r);

lo.QueryTable.CommandText = sql;
lo.Refresh();