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?

Answer 1

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.

Answer 2

Try this out, it's a touch simpler than Brendans but not as 'feature rich':

    Protected Sub btnExport_Click(ByVal sender As Object, ByVal e As System.EventArgs)
 'Export to excel
 Response.Clear()
 Response.Buffer = True
 Response.ContentType = "application/vnd.ms-excel"
 Response.Charset = ""
 Me.EnableViewState = False
 Dim oStringWriter As System.IO.StringWriter = New System.IO.StringWriter
 Dim oHtmlTextWriter As System.Web.UI.HtmlTextWriter = New System.Web.UI.HtmlTextWriter(oStringWriter)
 Me.ClearControls(gvSearchTerms)
 gvSearchTerms.RenderControl(oHtmlTextWriter)
 Response.Write(oStringWriter.ToString)
 Response.End()
End Sub



Private Sub ClearControls(ByVal control As Control)
 Dim i As Integer = (control.Controls.Count - 1)
 Do While (i >= 0)
  ClearControls(control.Controls(i))
  i = (i - 1)
 Loop
 If Not (TypeOf control Is TableCell) Then
  If (Not (control.GetType.GetProperty("SelectedItem")) Is Nothing) Then
   Dim literal As LiteralControl = New LiteralControl
   control.Parent.Controls.Add(literal)
   Try
    literal.Text = CType(control.GetType.GetProperty("SelectedItem").GetValue(control, Nothing), String)
   Catch ex As System.Exception

   End Try
   control.Parent.Controls.Remove(control)
  ElseIf (Not (control.GetType.GetProperty("Text")) Is Nothing) Then
   Dim literal As LiteralControl = New LiteralControl
   control.Parent.Controls.Add(literal)
   literal.Text = CType(control.GetType.GetProperty("Text").GetValue(control, Nothing), String)
   control.Parent.Controls.Remove(control)
  End If
 End If
 Return
End Sub



Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
 Return
End Sub

Answer 3

You could use crystal since it is built into VS. Predefine a crystal report with the appropriate columns and then you can use any datasource you would use for a datagrid or gridview.

 Dim report_source As CrystalDecisions.Web.CrystalReportSource
 report_source.ReportDocument.SetDataSource(dt) 'DT IS A DATATABLE
 report_source.Report.FileName = "test.rpt"
 report_source.ReportDocument.Refresh()
 report_source.ReportDocument.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.Excel, "c:\test.xls")

Answer 4

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.

Answer 5

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

Answer 6

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

There are samples in the page.

Answer 7

follow the link

http://vb.net-informations.com/excel-2007/vb.net_export_from_datagridview_to_excel.htm

Answer 8

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