Exporting SQL Server Database to Excel: Table Column Names wont transfer as Excel Column Headers

Question

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();
        }
    }
}

Answer 1

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

Answer 2

Answer to your question "why are there no column headers?"

What you need to do, is to also explicitly write the column names from the DataTable in your DataSet to the desired (first) row in the Excel document.

You can retrieve column names using the ColumnName property on a selected column of your DataTable, as in ds.Tables[0].Columns[ci].ColumnName;

For your code, modify the part that writes to the Excel document to this:

// This for loop adds the column names to the first row of the Excel document.
for (ci = 0; ci < ds.Tables[0].Columns.Count; ci++)
    xlWorkSheet.Cells[1, ci + 1] = ds.Tables[0].Columns[ci].ColumnName;

// This for loop populates the excel document with values.
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();
        // +2 because we already inserted the header row.
        xlWorkSheet.Cells[i + 2, j + 1] = data;
    }
}

More issues: server side Excel document generation

As noted by @JoelCoehoorn in the second section of his answer, your code will probably not work as you intended. You are generating the Excel file on the server side of an ASP.NET application, and saving it on the server. Unless you have saved the file on a file-share that is accessible to the user, the user will not have access to the Excel file you generated. Also refer to this quote from microsoft:

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

And these questions on server side Excel document generation and some issues with it.

Excel dll for Microsoft.Office.Interop.Excel

Excel file generation on server without Excel installed

Alternative to Office Interop for document generation

Microsoft's recommended way to generate Excel documents appears to be to use the Open XML SDK. You could use it to generate your Excel document and return it's contents as a file for download to the user.

You can also look at this answer from the question page that @JoelCoehoorn referred to, for an alternative solution that works directly with a data table.