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

[{'answer_id': 27304, 'author': 'Matt Hamilton', 'author_id': 615, 'author_profile': 'https://Stackoverflow.com/users/615', 'pm_score': 1, 'selected': False, 'text': "<p>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.</p>\n"}, {'answer_id': 27306, 'author': 'brendan', 'author_id': 225, 'author_profile': 'https://Stackoverflow.com/users/225', 'pm_score': -1, 'selected': False, 'text': '<p>Here some code we use to do it across lots of our apps.  We have a special method to clean up "not exportable" column.  Additionally, we don\'t export cols without headers but you can adjust that logic to your needs.</p>\n\n<p>Edit:  The code formatter doesn\'t love vb.net - you can copy/paste into visual studio and it will be fine.</p>\n\n<pre><code>\n  Public Overloads Shared Function BuildExcel(ByVal gView As System.Web.UI.WebControls.GridView) As String\n\n            PrepareGridViewForExport(gView)\n\n\n            Dim excelDoc As New StringBuilder\n\n            Dim startExcelXML As String = "  " + _\n                 " " + _\n                 " " + _\n                 "   " + _\n                 "   " + _\n                 "   " + _\n                 "      " + _\n                 "      " + _\n                 " "\n            Dim endExcelXML As String = ""\n\n            Dim rowCount As Int64 = 0\n            Dim sheetCount As Int16 = 1\n\n\n            excelDoc.Append(startExcelXML)\n            excelDoc.Append("")\n            excelDoc.Append("")\n\n            \' write out column headers\n            excelDoc.Append("")\n\n            For x As Int32 = 0 To gView.Columns.Count - 1\n\n                \'Only write out columns that have column headers.\n                If Not gView.Columns(x).HeaderText = String.Empty Then\n                    excelDoc.Append("")\n                    excelDoc.Append(gView.Columns(x).HeaderText.ToString)\n                    excelDoc.Append("")\n                End If\n            Next\n\n            excelDoc.Append("")\n\n            For r As Int32 = 0 To gView.Rows.Count - 1\n\n                rowCount += rowCount\n\n                If rowCount = 64000 Then\n                    rowCount = 0\n                    sheetCount += sheetCount\n                    excelDoc.Append("")\n                    excelDoc.Append(" ")\n                    excelDoc.Append("")\n                    excelDoc.Append("")\n                End If\n\n                excelDoc.Append("")\n\n                For c As Int32 = 0 To gView.Rows(r).Cells.Count - 1\n\n                    \'Don\'t write out a column without a column header.\n\n                    If Not gView.Columns(c).HeaderText = String.Empty Then\n                        Dim XMLstring As String = gView.Rows(r).Cells(c).Text\n\n                        XMLstring = XMLstring.Trim()\n                        XMLstring = XMLstring.Replace("&", "&")\n                        XMLstring = XMLstring.Replace(">", ">")\n                        XMLstring = XMLstring.Replace("" + "")\n                        excelDoc.Append(XMLstring)\n                        excelDoc.Append("")\n                    End If\n\n                Next\n\n                excelDoc.Append("")\n            Next\n\n            excelDoc.Append("")\n            excelDoc.Append(" ")\n            excelDoc.Append(endExcelXML)\n\n\n\n            Return excelDoc.ToString\n\n\n        End Function\n\n        Shared Sub PrepareGridViewForExport(ByVal gview As System.Web.UI.Control)\n            \' Cleans up grid for exporting.  Takes links and visual elements and turns them into text.\n            Dim lb As New System.Web.UI.WebControls.LinkButton\n            Dim l As New System.Web.UI.WebControls.Literal\n            Dim name As String = String.Empty\n\n\n            For i As Int32 = 0 To gview.Controls.Count - 1\n\n                If TypeOf gview.Controls(i) Is System.Web.UI.WebControls.LinkButton Then\n                    l.Text = CType(gview.Controls(i), System.Web.UI.WebControls.LinkButton).Text\n                    gview.Controls.Remove(gview.Controls(i))\n                    gview.Controls.AddAt(i, l)\n                ElseIf TypeOf gview.Controls(i) Is System.Web.UI.WebControls.DropDownList Then\n                    l.Text = CType(gview.Controls(i), System.Web.UI.WebControls.DropDownList).SelectedItem.Text\n                    gview.Controls.Remove(gview.Controls(i))\n                    gview.Controls.AddAt(i, l)\n                ElseIf TypeOf gview.Controls(i) Is System.Web.UI.WebControls.CheckBox Then\n                    l.Text = CType(gview.Controls(i), System.Web.UI.WebControls.CheckBox).Checked.ToString\n                    gview.Controls.Remove(gview.Controls(i))\n                    gview.Controls.AddAt(i, l)\n                End If\n\n\n                If gview.Controls(i).HasControls() Then\n                    PrepareGridViewForExport(gview.Controls(i))\n                End If\n\n            Next\n        End Sub\n\n</code></pre>\n'}, {'answer_id': 27313, 'author': 'Dhaust', 'author_id': 242, 'author_profile': 'https://Stackoverflow.com/users/242', 'pm_score': 0, 'selected': False, 'text': '<p>Try this out, it\'s a touch simpler than Brendans but not as \'feature rich\':  </p>\n\n<pre><code>    Protected Sub btnExport_Click(ByVal sender As Object, ByVal e As System.EventArgs)\n    \'Export to excel\n    Response.Clear()\n    Response.Buffer = True\n    Response.ContentType = "application/vnd.ms-excel"\n    Response.Charset = ""\n    Me.EnableViewState = False\n    Dim oStringWriter As System.IO.StringWriter = New System.IO.StringWriter\n    Dim oHtmlTextWriter As System.Web.UI.HtmlTextWriter = New System.Web.UI.HtmlTextWriter(oStringWriter)\n    Me.ClearControls(gvSearchTerms)\n    gvSearchTerms.RenderControl(oHtmlTextWriter)\n    Response.Write(oStringWriter.ToString)\n    Response.End()\nEnd Sub\n\n\n\nPrivate Sub ClearControls(ByVal control As Control)\n    Dim i As Integer = (control.Controls.Count - 1)\n    Do While (i &gt;= 0)\n        ClearControls(control.Controls(i))\n        i = (i - 1)\n    Loop\n    If Not (TypeOf control Is TableCell) Then\n        If (Not (control.GetType.GetProperty("SelectedItem")) Is Nothing) Then\n            Dim literal As LiteralControl = New LiteralControl\n            control.Parent.Controls.Add(literal)\n            Try\n                literal.Text = CType(control.GetType.GetProperty("SelectedItem").GetValue(control, Nothing), String)\n            Catch ex As System.Exception\n\n            End Try\n            control.Parent.Controls.Remove(control)\n        ElseIf (Not (control.GetType.GetProperty("Text")) Is Nothing) Then\n            Dim literal As LiteralControl = New LiteralControl\n            control.Parent.Controls.Add(literal)\n            literal.Text = CType(control.GetType.GetProperty("Text").GetValue(control, Nothing), String)\n            control.Parent.Controls.Remove(control)\n        End If\n    End If\n    Return\nEnd Sub\n\n\n\nPublic Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)\n    Return\nEnd Sub\n</code></pre>\n'}, {'answer_id': 27341, 'author': 'Jas', 'author_id': 777, 'author_profile': 'https://Stackoverflow.com/users/777', 'pm_score': 0, 'selected': False, 'text': '<p>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.</p>\n\n<pre><code> Dim report_source As CrystalDecisions.Web.CrystalReportSource\n report_source.ReportDocument.SetDataSource(dt) \'DT IS A DATATABLE\n report_source.Report.FileName = "test.rpt"\n report_source.ReportDocument.Refresh()\n report_source.ReportDocument.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.Excel, "c:\\test.xls")\n</code></pre>\n'}, {'answer_id': 33570, 'author': 'Jeffrey', 'author_id': 3259, 'author_profile': 'https://Stackoverflow.com/users/3259', 'pm_score': 1, 'selected': False, 'text': "<p>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.</p>\n\n<p>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.</p>\n"}, {'answer_id': 44138, 'author': 'Industrial Themes', 'author_id': 4222, 'author_profile': 'https://Stackoverflow.com/users/4222', 'pm_score': 1, 'selected': False, 'text': '<p>I setup the gridview and then used the html text writer object to spit it out to a .xls file, like so:</p>\n\n<pre><code>Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load\n    \'get the select command of the gridview\n    sqlGridview.SelectCommand = Session("strSql")\n    gvCompaniesExport.DataBind()\n    lblTemp.Text = Session("strSql")\n\n    \'do the export\n    doExport()\n\n    \'close the window\n    Dim closeScript As String = "&lt;script language=\'javascript\'&gt; window.close() &lt;/scri"\n    closeScript = closeScript &amp; "pt&gt;"\n    \'split the ending script tag across a concatenate to keep it from causing problems\n    \'this will write it to the asp.net page and fire it off, closing the window\n    Page.RegisterStartupScript("closeScript", closeScript)\nEnd Sub\nPublic Sub doExport()\n    Response.AddHeader("content-disposition", "attachment;filename=IndianaCompanies.xls")\n    Response.ContentType = "application/vnd.ms-excel"\n    Response.Charset = ""\n    Me.EnableViewState = False\n    Dim objStrWriter As New System.IO.StringWriter\n    Dim objHtmlTextWriter As New System.Web.UI.HtmlTextWriter(objStrWriter)\n    \'Get the gridview HTML from the control\n    gvCompaniesExport.RenderControl(objHtmlTextWriter)\n    \'writes the dg info\n    Response.Write(objStrWriter.ToString())\n    Response.End()\nEnd Sub\n</code></pre>\n'}, {'answer_id': 127010, 'author': 'Eduardo Molteni', 'author_id': 2385, 'author_profile': 'https://Stackoverflow.com/users/2385', 'pm_score': 2, 'selected': False, 'text': '<p>You can use this library for more detailed formatting<br>\n<a href="http://www.carlosag.net/Tools/ExcelXmlWriter/" rel="nofollow noreferrer">http://www.carlosag.net/Tools/ExcelXmlWriter/</a></p>\n\n<p>There are samples in the page.</p>\n'}, {'answer_id': 1370416, 'author': 'Merritt', 'author_id': 60385, 'author_profile': 'https://Stackoverflow.com/users/60385', 'pm_score': 1, 'selected': False, 'text': '<p>I use this all the time:</p>\n\n<pre><code>public static class GridViewExtensions\n    {\n        public static void ExportToExcel(this GridView gridView, string fileName, IEnumerable&lt;string&gt; excludeColumnNames)\n        {\n            //Prepare Response\n            HttpContext.Current.Response.Clear();\n            HttpContext.Current.Response.AddHeader("content-disposition",\n                string.Format("attachment; filename={0}", fileName));\n            HttpContext.Current.Response.ContentType = "application/ms-excel";\n\n\n\n            using (StringWriter sw = new StringWriter())\n            {\n                using (HtmlTextWriter htw = new HtmlTextWriter(sw))\n                {\n                    //  Create a table to contain the grid\n                    Table table = new Table();\n\n                    //  include the gridline settings\n                    table.GridLines = gridView.GridLines;\n\n                    //  add the header row to the table\n                    if (gridView.HeaderRow != null)\n                    {\n                        PrepareControlForExport(gridView.HeaderRow);\n                        table.Rows.Add(gridView.HeaderRow);\n                    }\n\n                    //  add each of the data rows to the table\n                    foreach (GridViewRow row in gridView.Rows)\n                    {\n                        PrepareControlForExport(row);\n                        table.Rows.Add(row);\n                    }\n\n                    //  add the footer row to the table\n                    if (gridView.FooterRow != null)\n                    {\n                        PrepareControlForExport(gridView.FooterRow);\n                        table.Rows.Add(gridView.FooterRow);\n                    }\n\n                    // Remove unwanted columns (header text listed in removeColumnList arraylist)\n                    foreach (DataControlField column in gridView.Columns)\n                    {\n                        if (excludeColumnNames != null &amp;&amp; excludeColumnNames.Contains(column.HeaderText))\n                        {\n                            column.Visible = false;\n                        }\n                    }\n\n                    //  render the table into the htmlwriter\n                    table.RenderControl(htw);\n\n                    //  render the htmlwriter into the response\n                    HttpContext.Current.Response.Write(sw.ToString());\n                    HttpContext.Current.Response.End();\n                }\n            }\n        }\n\n        /// &lt;summary&gt;\n        /// Replace any of the contained controls with literals\n        /// &lt;/summary&gt;\n        /// &lt;param name="control"&gt;&lt;/param&gt;\n        private static void PrepareControlForExport(Control control)\n        {\n            for (int i = 0; i &lt; control.Controls.Count; i++)\n            {\n                Control current = control.Controls[i];\n\n                if (current is LinkButton)\n                {\n                    control.Controls.Remove(current);\n                    control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));\n                }\n                else if (current is ImageButton)\n                {\n                    control.Controls.Remove(current);\n                    control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));\n                }\n                else if (current is HyperLink)\n                {\n                    control.Controls.Remove(current);\n                    control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));\n                }\n                else if (current is DropDownList)\n                {\n                    control.Controls.Remove(current);\n                    control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));\n                }\n                else if (current is CheckBox)\n                {\n                    control.Controls.Remove(current);\n                    control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));\n                }\n\n                if (current.HasControls())\n                {\n                    PrepareControlForExport(current);\n                }\n            }\n        }\n    }\n</code></pre>\n'}, {'answer_id': 23004284, 'author': 'GoroundoVipa', 'author_id': 3471643, 'author_profile': 'https://Stackoverflow.com/users/3471643', 'pm_score': 0, 'selected': False, 'text': '<p>First Import COM library Microsoft Excel Object </p>\n\n<p>Sample Code:</p>\n\n<pre><code>Public Sub exportOfficePCandWorkstation(ByRef mainForm As Form1, ByVal Location As String, ByVal WorksheetName As String)\n        Dim xlApp As New Excel.Application\n        Dim xlWorkBook As Excel.Workbook\n        Dim xlWorkSheet As Excel.Worksheet\n        Dim misValue As Object = System.Reflection.Missing.Value\n        Dim Header(23) As String\n        Dim HeaderCell(23) As String\n        Header = {"No.", "PC Name", "User", "E-mail", "Department/Location", "CPU Model", "CPU Processor", "CPU Speed", "CPU HDD#1", "CPU HDD#2", "CPU Memory", "CPU OS", "CPU Asset Tag", "CPU MAC Address", "Monitor 1 Model", "Monitor Serial Number", "Monitor2 Model", "Monitor2 Serial Number", "Office", "Wi-LAN", "KVM Switch", "Attachment", "Remarks", "Date and Time"}\n        HeaderCell = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X"}\n        xlWorkBook = xlApp.Workbooks.Add\n        xlWorkSheet = xlWorkBook.Sheets("Sheet1")\n        xlWorkSheet.Name = WorksheetName\n        xlApp.Visible = True\n        xlWorkSheet.Application.ActiveWindow.SplitRow = 1\n        xlWorkSheet.Application.ActiveWindow.SplitColumn = 3\n        xlWorkSheet.Application.ActiveWindow.FreezePanes = True\n        With xlWorkSheet\n            For count As Integer = 0 To 23\n                .Range(HeaderCell(count) &amp; 1).Value = Header(count)\n            Next\n            With .Range("A1:X1")\n                .Interior.Color = 1\n                With .Font\n                    .Size = 16\n                    .ColorIndex = 2\n                    .Name = "Times New Roman"\n                End With\n            End With\n            For i = 0 To mainForm.DataGridView1.RowCount - 1\n                For j = 0 To mainForm.DataGridView1.ColumnCount - 1\n                    If mainForm.DataGridView1(j, i).Value.ToString = "System.Byte[]" Then\n                        xlWorkSheet.Cells(i + 2, j + 2) = "Attached"\n                    Else\n                        xlWorkSheet.Cells(i + 2, j + 2) = mainForm.DataGridView1(j, i).Value.ToString()\n                    End If\n                Next\n                .Range("A" &amp; i + 2).Value = (i + 1).ToString\n            Next\n            With .Range("A:Z")\n                .EntireColumn.AutoFit()\n            End With\n            With .Range("B2:X" &amp; mainForm.DataGridView1.RowCount + 1)\n                .HorizontalAlignment = Excel.XlVAlign.xlVAlignJustify\n            End With\n            With .Range("A1:A" &amp; mainForm.DataGridView1.RowCount + 1)\n                .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter\n            End With\n            \'-----------------------------------Insert Border Lines--------------------------------------\n            With .Range("A1:X" &amp; mainForm.DataGridView1.RowCount + 1)\n                With .Borders(Excel.XlBordersIndex.xlEdgeLeft)\n                    .LineStyle = Excel.XlLineStyle.xlDouble\n                    .ColorIndex = 0\n                    .TintAndShade = 0\n                    .Weight = Excel.XlBorderWeight.xlThin\n                End With\n                With .Borders(Excel.XlBordersIndex.xlEdgeTop)\n                    .LineStyle = Excel.XlLineStyle.xlContinuous\n                    .ColorIndex = 0\n                    .TintAndShade = 0\n                    .Weight = Excel.XlBorderWeight.xlThin\n                End With\n                With .Borders(Excel.XlBordersIndex.xlEdgeBottom)\n                    .LineStyle = Excel.XlLineStyle.xlContinuous\n                    .ColorIndex = 0\n                    .TintAndShade = 0\n                    .Weight = Excel.XlBorderWeight.xlThin\n                End With\n                With .Borders(Excel.XlBordersIndex.xlEdgeRight)\n                    .LineStyle = Excel.XlLineStyle.xlContinuous\n                    .ColorIndex = 0\n                    .TintAndShade = 0\n                    .Weight = Excel.XlBorderWeight.xlThin\n                End With\n                With .Borders(Excel.XlBordersIndex.xlInsideVertical)\n                    .LineStyle = Excel.XlLineStyle.xlContinuous\n                    .ColorIndex = 0\n                    .TintAndShade = 0\n                    .Weight = Excel.XlBorderWeight.xlThin\n                End With\n                With .Borders(Excel.XlBordersIndex.xlInsideHorizontal)\n                    .LineStyle = Excel.XlLineStyle.xlContinuous\n                    .ColorIndex = 0\n                    .TintAndShade = 0\n                    .Weight = Excel.XlBorderWeight.xlThin\n                End With\n            End With\n        End With\n            xlWorkSheet.SaveAs(Location)\n            xlWorkBook.Close()\n        xlApp.Quit()\n        MsgBox("Export Record successful", MsgBoxStyle.Information, "Export to Excel")\nEnd Sub\n</code></pre>\n\n<p>I Use SaveFileDialog to Create the excel in the specific location</p>\n'}]
['https://Stackoverflow.com/questions/27288', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/359/']