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.

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

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

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


        }

Brijesh Mishra · Accepted Answer · 2011-12-29 11:36:07Z

1

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

answered Dec 29, 2011 at 11:36

Brijesh Mishra

2,7581 gold badge21 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

prem30488 · Accepted Answer · 2014-03-03 11:37:53Z

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.

Siarhei Kuchuk · Accepted Answer · 2011-12-29 21:01:51Z

0

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.

answered Dec 29, 2011 at 21:01

Siarhei Kuchuk

5,5761 gold badge31 silver badges31 bronze badges

Collectives™ on Stack Overflow

How to export c# datagridview into Excel

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related