Exporting DataTable to Excel

Question

I have a small web application that creates two datatables from a jquery datepicker. I am able to export those datatables to excel of course if they are on the same page.

I've changed my application to render the datatables on new webforms.

Here is my code to export to excel:

    protected void ExportToExcel(object sender, EventArgs e)
    {
        Response.Clear();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
        Response.Charset = "";
        Response.ContentType = "application/vnd.ms-excel";
        using (StringWriter sw = new StringWriter())
        {
            HtmlTextWriter hw = new HtmlTextWriter(sw);

            //To Export all pages
            this.BindGrid1(TextDateFrom.Text, TextDateTo.Text);

            GridView2.HeaderRow.BackColor = Color.White;
            foreach (TableCell cell in GridView2.HeaderRow.Cells)
            {
                cell.BackColor = GridView2.HeaderStyle.BackColor;
            }
            foreach (GridViewRow row in GridView2.Rows)
            {
                row.BackColor = Color.White;
                foreach (TableCell cell in row.Cells)
                {
                    if (row.RowIndex % 2 == 0)
                    {
                        cell.BackColor = GridView2.AlternatingRowStyle.BackColor;
                    }
                    else
                    {
                        cell.BackColor = GridView2.RowStyle.BackColor;
                    }
                    cell.CssClass = "textmode";
                }
            }

            GridView2.RenderControl(hw);

            //style to format numbers to string
            string style = @"<style> .textmode { } </style>";
            Response.Write(style);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
        }
    }

Here is where I am having trouble:

    this.BindGrid1(TextDateFrom.Text, TextDateTo.Text);

Of course my BindGrid1() is an another form. To call my datatables in my new forms I created a session.

If I have the code on the web form where the data tables are:

     DataTable dt = (DataTable)Session["GridData"];
     if (dt.Rows.Count > 0)
        {
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }

I'm not sure exactly how to call that this. to export all pages. Should I have created a global variable that takes the String values from the BindGrid1 method to then use on the new page?

Answer 1

Pertinent to your task, the data export from DataTable dt to Excel can be achieved using the following procedure, which utilizes Microsoft.Office.Interop.Excel object library:

/// <summary>
/// export DataTable to Excel (C#)
/// </summary>
internal static void Export2Excel(DataTable dataTable)
{
    object misValue = System.Reflection.Missing.Value;
    Microsoft.Office.Interop.Excel.Application _appExcel = null;
    Microsoft.Office.Interop.Excel.Workbook _excelWorkbook = null;
    Microsoft.Office.Interop.Excel.Worksheet _excelWorksheet = null;
    try
    {
        // excel app object
        _appExcel = new Microsoft.Office.Interop.Excel.Application();

        // excel workbook object added to app
        _excelWorkbook = _appExcel.Workbooks.Add(misValue);

        _excelWorksheet = _appExcel.ActiveWorkbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;

        // column names row (range obj)
        Microsoft.Office.Interop.Excel.Range _columnsNameRange;
        _columnsNameRange = _excelWorksheet.get_Range("A1", misValue).get_Resize(1, dataTable.Columns.Count);

        // column names array to be assigned to _columnNameRange
        string[] _arrColumnNames = new string[dataTable.Columns.Count];

        for (int i = 0; i < dataTable.Columns.Count; i++)
        {
            // array of column names
            _arrColumnNames[i] = dataTable.Columns[i].ColumnName;
        }

        // assign array to column headers range, make 'em bold
        _columnsNameRange.set_Value(misValue, _arrColumnNames);
        _columnsNameRange.Font.Bold = true;

        // populate data content row by row
        for (int Idx = 0; Idx < dataTable.Rows.Count; Idx++)
        {
            _excelWorksheet.Range["A2"].Offset[Idx].Resize[1, dataTable.Columns.Count].Value =
            dataTable.Rows[Idx].ItemArray;
        }

        // Autofit all Columns in the range
        _columnsNameRange.Columns.EntireColumn.AutoFit();
    }
    catch { throw; }
}

just pass dt as an argument.

Hope this may help.