How to export c# datagridview into Excel

Question

In my application, there is requirement to export datagridview into Excel.

I am using the following source code. I wanted expert advice on following questions.

1. Is my code is correct or not? because i am not getting any file saved at the selected path.

2. Is there any performance issue while exporting data from grid, because there could be as many as data available in grid?

3. I am using Namespace "Microsoft.Office.Interop.Excel", not sure if that is right?

  private void btnSaveResult_Click(object sender, EventArgs e)
        {
            try
            {
                if (this.saveFileDialog.ShowDialog() == DialogResult.OK)
                {
                    saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
                    saveFileDialog.FilterIndex = 0;
                    saveFileDialog.RestoreDirectory = true;
                    saveFileDialog.CreatePrompt = true;
                    saveFileDialog.Title = "Export Excel File To";
                    Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                    ExcelApp.Application.Workbooks.Add(Type.Missing);
                    ExcelApp.Columns.ColumnWidth = 30;
                    for (int i = 0; i < grdResult.Rows.Count; i++)
                    {
                        DataGridViewRow row = grdResult.Rows[i];
                        for (int j = 0; j < row.Cells.Count; j++)
                        {
                            ExcelApp.Cells[i + 1, j + 1] = row.Cells[j].ToString();
                        }
                    }                    
                    ExcelApp.ActiveWorkbook.Saved = true;
                    ExcelApp.Quit();
                    MessageBox.Show("The Save button was clicked or the Enter key was pressed" + "\nThe file would have been saved as " + this.saveFileDialog.FileName);

                }
                else MessageBox.Show("The Cancel button was clicked or Esc was pressed");

            }
            catch (Exception ex)
            {
                MessageBox.Show("Cancelled Save Operation");
                this.Close();
            }


        }

Answer 1

Try Following class

    using System;
    using System.Data;
    using System.Configuration;
    using System.IO;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Web.UI.HtmlControls;

    /// <summary>
    /// Summary description for GridViewExportUtil
    /// </summary>
    public class GridViewExportUtil
    {
public GridViewExportUtil()
{
    //
    // TODO: Add constructor logic here
    //
}
    public static void ExportGridView(string fileName, GridView gv, Label header, Label date)
        {
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
            HttpContext.Current.Response.ContentType = "application/ms-excel";

            using (StringWriter sw = new StringWriter())
            {
                using (HtmlTextWriter htw = new HtmlTextWriter(sw))
                {
                    gv.AllowPaging = false;
                    //  Create a table to contain the grid
                    Table table = new Table();

                    //  include the gridline settings
                    table.GridLines = gv.GridLines;

                    gv.Style["font-family"] = "Tahoma";
                    //  add the header row to the table

                    if (gv.HeaderRow != null)
                    {
                        GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
                        gv.HeaderRow.BackColor = System.Drawing.Color.Lavender;
                        gv.HeaderRow.ForeColor = System.Drawing.Color.Green;

                        table.Rows.Add(gv.HeaderRow);
                    }
                    //  add each of the data rows to the table
                    foreach (GridViewRow row in gv.Rows)
                    {
                        GridViewExportUtil.PrepareControlForExport(row);
                        table.Rows.Add(row);
                    }
                    //  add the footer row to the table
                    if (gv.FooterRow != null)
                    {
                        GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
                        table.Rows.Add(gv.FooterRow);
                    }
                    htw.WriteLine("<br>");
                    // htw.WriteLine("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;");
                    if (header.Text != null)
                    {
                        header.Font.Size = 15;
                        header.Font.Bold = true;
                        header.ForeColor = System.Drawing.Color.Blue;
                        header.RenderControl(htw);
                    }
                    htw.WriteLine("</p>");
                    //  render the table into the htmlwriter
                    table.RenderControl(htw);
                    htw.WriteLine("<br>");
                    htw.WriteLine("Report taken on :", System.Drawing.FontStyle.Bold);
                    if (date.Text != null)
                    {
                        date.ForeColor = System.Drawing.Color.Blue;
                        date.RenderControl(htw);
                    }
                    //  render the htmlwriter into the response
                    HttpContext.Current.Response.Write(sw.ToString());
                    HttpContext.Current.Response.End();
                }
            }
        }

    /// <summary>
    /// Replace any of the contained controls with literals
    /// </summary>
    /// <param name="control"></param>
   private static void PrepareControlForExport(Control control)
        {
            for (int i = 0; i < control.Controls.Count; i++)
            {
                Control current = control.Controls[i];
                if (current is LinkButton)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
                }
                else if (current is ImageButton)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
                }
                else if (current is HyperLink)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
                }
                else if (current is DropDownList)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
                }
                else if (current is CheckBox)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
                }
                else if (current is Label)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as Label).Text));
                }

                if (current.HasControls())
                {
                    GridViewExportUtil.PrepareControlForExport(current);
                }
            }
        }
    }

And use it as below

   protected void Button1_Click(object sender, EventArgs e)
       {
    Label1.Visible = true;
    Label2.Visible = true;

    Label1.Text = "Login Information Data";
    Label2.Text = Convert.ToString(System.DateTime.Now);

    if (GridView1.Visible == true)
    {
        // GridViewExportUtil.Export("StateReport.xls", GridView1);
        GridViewExportUtil.ExportGridView("LoginInformation.xls", GridView1, Label1, Label2);
    }

       }

Remove labels if you do not want them.. make changes as per your need.

Answer 2

Implementation weaknesses: - you don't free resouces you use; - you export items one by one (it's extremely slow), there's range designed for that, where you can set object[,] (of boxed int, strings, ...); - you don't handle formatting of text (Excel's decisions about format are not right), - you mix view and export logic.

Answer 3

get handle to workbook whe you add, and call Workbook.SaveCopyAs(filePath);