How do I save a datagrid to excel in vb.net?

Question

I know that this should be easy but how do I export/save a DataGridView to excel?

Solution 1

You can use this library for more detailed formatting
http://www.carlosag.net/Tools/ExcelXmlWriter/

There are samples in the page.

Solution 2

I use this all the time:

public static class GridViewExtensions
    {
        public static void ExportToExcel(this GridView gridView, string fileName, IEnumerable<string> excludeColumnNames)
        {
            //Prepare Response
            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))
                {
                    //  Create a table to contain the grid
                    Table table = new Table();

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

                    //  add the header row to the table
                    if (gridView.HeaderRow != null)
                    {
                        PrepareControlForExport(gridView.HeaderRow);
                        table.Rows.Add(gridView.HeaderRow);
                    }

                    //  add each of the data rows to the table
                    foreach (GridViewRow row in gridView.Rows)
                    {
                        PrepareControlForExport(row);
                        table.Rows.Add(row);
                    }

                    //  add the footer row to the table
                    if (gridView.FooterRow != null)
                    {
                        PrepareControlForExport(gridView.FooterRow);
                        table.Rows.Add(gridView.FooterRow);
                    }

                    // Remove unwanted columns (header text listed in removeColumnList arraylist)
                    foreach (DataControlField column in gridView.Columns)
                    {
                        if (excludeColumnNames != null && excludeColumnNames.Contains(column.HeaderText))
                        {
                            column.Visible = false;
                        }
                    }

                    //  render the table into the htmlwriter
                    table.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"));
                }

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

Solution 3

I'd warn again doing a double for loop to pull out each datacell's data, and writing out individually to an excel cell. Instead, use a 2D object array, and loop through your datagrid saving all your data there. You'll then be able to set an excel range equal to that 2D object array.

This will be several orders of magnitude faster than writing excel cell by cell. Some reports that I've been working on that used to take two hours simply to export have been cut down to under a minute.

Solution 4

I setup the gridview and then used the html text writer object to spit it out to a .xls file, like so:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    'get the select command of the gridview
    sqlGridview.SelectCommand = Session("strSql")
    gvCompaniesExport.DataBind()
    lblTemp.Text = Session("strSql")

    'do the export
    doExport()

    'close the window
    Dim closeScript As String = "<script language='javascript'> window.close() </scri"
    closeScript = closeScript & "pt>"
    'split the ending script tag across a concatenate to keep it from causing problems
    'this will write it to the asp.net page and fire it off, closing the window
    Page.RegisterStartupScript("closeScript", closeScript)
End Sub
Public Sub doExport()
    Response.AddHeader("content-disposition", "attachment;filename=IndianaCompanies.xls")
    Response.ContentType = "application/vnd.ms-excel"
    Response.Charset = ""
    Me.EnableViewState = False
    Dim objStrWriter As New System.IO.StringWriter
    Dim objHtmlTextWriter As New System.Web.UI.HtmlTextWriter(objStrWriter)
    'Get the gridview HTML from the control
    gvCompaniesExport.RenderControl(objHtmlTextWriter)
    'writes the dg info
    Response.Write(objStrWriter.ToString())
    Response.End()
End Sub

Solution 5

Does it need to be a native XLS file? Your best bet is probably just to export the data to a CSV file, which is plain text and reasonably easy to generate. CSVs open in Excel by default for most users so they won't know the difference.

View more solutions

Comments

[Eduardo Molteni]

I think your code was sanitized or something, because it does not make sense.