Exporting the SQL Server database to Excel: Table column names will not be transferred as Excel column headers

advertisements

I am trying to export content of a SQL Server database table to an Excel document. The problem I have is that the table column names are not showing up as column headers in Excel.

public partial class Reports : System.Web.UI.Page
{
    protected void reportbtn_Click(object sender, EventArgs e)
    {
        SqlConnection cnn;
        string connectionString = null;
        string sql = null;
        string data = null;
        int i = 0;
        int j = 0;

        //Creates the Excel Workbook
        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;

        xlApp = new Excel.Application();
        xlWorkBook = xlApp.Workbooks.Add(misValue);

        Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
        ExcelApp.ScreenUpdating = true;
        ExcelApp.Visible = true;
        ExcelApp.Interactive = true;
        ExcelApp.IgnoreRemoteRequests = false;

        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        //Connects to Database
        connectionString = "data source=LocalHost;initial catalog=KPMG;Integrated Security=True";
        cnn = new SqlConnection(connectionString);
        cnn.Open()

        //Sql Command Statement
        sql = "Select FirstName, LastName From EmployeeData Where EmployeeID = 1"; //sql string needs to be adjusted to pull all necessary information
        SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
        DataSet ds = new DataSet();
        dscmd.Fill(ds);

        // This for loop populates the excel document, but can't figure out how to
        //  include the table names as headers for the columns 

        for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
        {
            for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
            {
                data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
                xlWorkSheet.Cells[i + 1, j + 1] = data;
            }
        }

        //saves excel workbook to C:
        xlWorkBook.SaveAs("TravelReport.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();

        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlApp);

        MessageBox.Show("Excel file created , you can find the file C:/Users/LabPatron/Documents/TravelReport.xls");
    }

    private void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
        }
        finally
        {
            GC.Collect();
        }
    }
}


In Excel, column headings are part of the data itself, taking up one row in the worksheet. In Sql Server result sets, column headings are part of the metadata; the headings are not seen as a "row" in the actual results. If you want column headings in Excel here, you'll need to do something extra to add them to the worksheet. This could mean using an sql UNION where the first part selects the names of the columns as literals (if the types are compatible or can be made compatible), or it could mean a extra loop at the beginning of your results to set the first row... or it could mean something else, depending on how you want to do it. There are as many potential solutions to this as there are programmers, and the best option will depend on context not included with the question.

While I'm here, I see context in the code that this is running in an ASP.Net web page. This code will NOT work at all the way you expect after you deploy it to a web server. The VB.Net code in ASP.Net runs entirely on the web server, not on the computer hosting the web browser. The MessageBox.Show() code only works right now because your web server and your web browser are on the same computer. The same is true for the xlWorkBook.SaveAs() call. You will need to completely re-think what you are doing here, because the current approach cannot possibly do what you want it to. You should check the answers to the question linked below for options for generating an Excel file in ASP.Net:

Generating an Excel file in ASP.NET