none
Export the results of a stored procedure in Excel

    Question

  • Hi,

    I am calling a stored procedure from C# program and storing the results in a data table. I use those results to create a pipe delimited .txt file and store it in a directory.

    Now i would like to know how i can store the results of the stored procedure in an excel (calling the stored procedure from C#).

    If there are 10 columns in the output of the stored procedure then the output should be put in 10 different columns in the xls and saved.

    Any examples would be much appeciated.

    Thanks.

    Thursday, March 22, 2012 11:49 PM

Answers

  • From database create (fill) datatable, using connection command and dataadapter classes.

    Then do the following to create an excel file:

    // TO USE:
                // 1) include COM reference to Microsoft Excel Object library
                // add namespace...
                // 2) using Excel = Microsoft.Office.Interop.Excel;
                private static void Excel_FromDataTable(DataTable dt)
                {
                    // Create an Excel object and add workbook...
                    Excel.ApplicationClass excel = new Excel.ApplicationClass();
                    Excel.Workbook workbook = excel.Application.Workbooks.Add(true); // true for object template???
                    // Add column headings...
                    int iCol = 0;
                    foreach (DataColumn c in dt.Columns)
                    {
                        iCol++;
                        excel.Cells[1, iCol] = c.ColumnName;
                    }
                    // for each row of data...
                    int iRow = 0;
                    foreach (DataRow r in dt.Rows)
                    {
                        iRow++;
                        // add each row's cell data...
                        iCol = 0;
                        foreach (DataColumn c in dt.Columns)
                        {
                            iCol++;
                            excel.Cells[iRow + 1, iCol] = r[c.ColumnName];
                        }
                    }
                    // Global missing reference for objects we are not defining...
                    object missing = System.Reflection.Missing.Value;
                    // If wanting to Save the workbook...
                    workbook.SaveAs("MyExcelWorkBook.xls",
                        Excel.XlFileFormat.xlXMLSpreadsheet, missing, missing,
                        false, false, Excel.XlSaveAsAccessMode.xlNoChange,
                        missing, missing, missing, missing, missing);
                    // If wanting to make Excel visible and activate the worksheet...
                    excel.Visible = true;
                    Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
                    ((Excel._Worksheet)worksheet).Activate();
                    // If wanting excel to shutdown...
                    ((Excel._Application)excel).Quit();
                }


    Mitja

    Friday, March 23, 2012 12:49 AM
  • You can download the working sample here:

    http://chanmingman.wordpress.com/2011/10/19/how-to-export-mysql-table-to-excel/

    then insert the similar loop at the middle like Mitja code.

    chanmm


    chanmm

    Friday, March 23, 2012 8:51 AM

All replies

  • From database create (fill) datatable, using connection command and dataadapter classes.

    Then do the following to create an excel file:

    // TO USE:
                // 1) include COM reference to Microsoft Excel Object library
                // add namespace...
                // 2) using Excel = Microsoft.Office.Interop.Excel;
                private static void Excel_FromDataTable(DataTable dt)
                {
                    // Create an Excel object and add workbook...
                    Excel.ApplicationClass excel = new Excel.ApplicationClass();
                    Excel.Workbook workbook = excel.Application.Workbooks.Add(true); // true for object template???
                    // Add column headings...
                    int iCol = 0;
                    foreach (DataColumn c in dt.Columns)
                    {
                        iCol++;
                        excel.Cells[1, iCol] = c.ColumnName;
                    }
                    // for each row of data...
                    int iRow = 0;
                    foreach (DataRow r in dt.Rows)
                    {
                        iRow++;
                        // add each row's cell data...
                        iCol = 0;
                        foreach (DataColumn c in dt.Columns)
                        {
                            iCol++;
                            excel.Cells[iRow + 1, iCol] = r[c.ColumnName];
                        }
                    }
                    // Global missing reference for objects we are not defining...
                    object missing = System.Reflection.Missing.Value;
                    // If wanting to Save the workbook...
                    workbook.SaveAs("MyExcelWorkBook.xls",
                        Excel.XlFileFormat.xlXMLSpreadsheet, missing, missing,
                        false, false, Excel.XlSaveAsAccessMode.xlNoChange,
                        missing, missing, missing, missing, missing);
                    // If wanting to make Excel visible and activate the worksheet...
                    excel.Visible = true;
                    Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
                    ((Excel._Worksheet)worksheet).Activate();
                    // If wanting excel to shutdown...
                    ((Excel._Application)excel).Quit();
                }


    Mitja

    Friday, March 23, 2012 12:49 AM
  • You can download the working sample here:

    http://chanmingman.wordpress.com/2011/10/19/how-to-export-mysql-table-to-excel/

    then insert the similar loop at the middle like Mitja code.

    chanmm


    chanmm

    Friday, March 23, 2012 8:51 AM
  • Thank you Mitja and chanmm. The examples worked for me !
    Monday, March 26, 2012 9:52 PM
  • From database create (fill) datatable, using connection command and dataadapter classes.

    Then do the following to create an excel file:

    // TO USE:
                // 1) include COM reference to Microsoft Excel Object library
                // add namespace...
                // 2) using Excel = Microsoft.Office.Interop.Excel;
                private static void Excel_FromDataTable(DataTable dt)
                {
                    // Create an Excel object and add workbook...
                    Excel.ApplicationClass excel = new Excel.ApplicationClass();
                    Excel.Workbook workbook = excel.Application.Workbooks.Add(true); // true for object template???
                    // Add column headings...
                    int iCol = 0;
                    foreach (DataColumn c in dt.Columns)
                    {
                        iCol++;
                        excel.Cells[1, iCol] = c.ColumnName;
                    }
                    // for each row of data...
                    int iRow = 0;
                    foreach (DataRow r in dt.Rows)
                    {
                        iRow++;
                        // add each row's cell data...
                        iCol = 0;
                        foreach (DataColumn c in dt.Columns)
                        {
                            iCol++;
                            excel.Cells[iRow + 1, iCol] = r[c.ColumnName];
                        }
                    }
                    // Global missing reference for objects we are not defining...
                    object missing = System.Reflection.Missing.Value;
                    // If wanting to Save the workbook...
                    workbook.SaveAs("MyExcelWorkBook.xls",
                        Excel.XlFileFormat.xlXMLSpreadsheet, missing, missing,
                        false, false, Excel.XlSaveAsAccessMode.xlNoChange,
                        missing, missing, missing, missing, missing);
                    // If wanting to make Excel visible and activate the worksheet...
                    excel.Visible = true;
                    Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
                    ((Excel._Worksheet)worksheet).Activate();
                    // If wanting excel to shutdown...
                    ((Excel._Application)excel).Quit();
                }


    Mitja

    How is it possible to incorporate custom Sheet Name in the above code ?

    Btw, nice and useful method.


    Don't miss to 'Vote As Helpful' or 'Mark As Answer' a deserving post.

    Thursday, June 14, 2012 10:41 AM