我知道这应该很容易，但是如何导出/保存DataGridView以使其出色呢？

解决方案

回答

是否需要是本机XLS文件？最好的选择就是将数据导出到CSV文件，该文件是纯文本格式，并且很容易生成。默认情况下，大多数用户会在Excel中打开CSV，因此他们不会知道两者之间的区别。

回答

在这里，我们使用一些代码在许多应用程序中执行此操作。我们有一种特殊的方法来清理"不可导出"列。此外，我们不会导出没有标题的cols，但是我们可以根据需要调整该逻辑。

编辑：代码格式化程序不喜欢vb.net，我们可以将其复制/粘贴到Visual Studio中，这会很好。

Public Overloads Shared Function BuildExcel(ByVal gView As System.Web.UI.WebControls.GridView) As String

            PrepareGridViewForExport(gView)

            Dim excelDoc As New StringBuilder

            Dim startExcelXML As String = "  " + _
                 " " + _
                 " " + _
                 "   " + _
                 "   " + _
                 "   " + _
                 "      " + _
                 "      " + _
                 " "
            Dim endExcelXML As String = ""

            Dim rowCount As Int64 = 0
            Dim sheetCount As Int16 = 1

            excelDoc.Append(startExcelXML)
            excelDoc.Append("")
            excelDoc.Append("")

            ' write out column headers
            excelDoc.Append("")

            For x As Int32 = 0 To gView.Columns.Count - 1

                'Only write out columns that have column headers.
                If Not gView.Columns(x).HeaderText = String.Empty Then
                    excelDoc.Append("")
                    excelDoc.Append(gView.Columns(x).HeaderText.ToString)
                    excelDoc.Append("")
                End If
            Next

            excelDoc.Append("")

            For r As Int32 = 0 To gView.Rows.Count - 1

                rowCount += rowCount

                If rowCount = 64000 Then
                    rowCount = 0
                    sheetCount += sheetCount
                    excelDoc.Append("")
                    excelDoc.Append(" ")
                    excelDoc.Append("")
                    excelDoc.Append("")
                End If

                excelDoc.Append("")

                For c As Int32 = 0 To gView.Rows(r).Cells.Count - 1

                    'Don't write out a column without a column header.

                    If Not gView.Columns(c).HeaderText = String.Empty Then
                        Dim XMLstring As String = gView.Rows(r).Cells(c).Text

                        XMLstring = XMLstring.Trim()
                        XMLstring = XMLstring.Replace("&", "&")
                        XMLstring = XMLstring.Replace(">", ">")
                        XMLstring = XMLstring.Replace("" + "")
                        excelDoc.Append(XMLstring)
                        excelDoc.Append("")
                    End If

                Next

                excelDoc.Append("")
            Next

            excelDoc.Append("")
            excelDoc.Append(" ")
            excelDoc.Append(endExcelXML)

            Return excelDoc.ToString

        End Function

        Shared Sub PrepareGridViewForExport(ByVal gview As System.Web.UI.Control)
            ' Cleans up grid for exporting.  Takes links and visual elements and turns them into text.
            Dim lb As New System.Web.UI.WebControls.LinkButton
            Dim l As New System.Web.UI.WebControls.Literal
            Dim name As String = String.Empty

            For i As Int32 = 0 To gview.Controls.Count - 1

                If TypeOf gview.Controls(i) Is System.Web.UI.WebControls.LinkButton Then
                    l.Text = CType(gview.Controls(i), System.Web.UI.WebControls.LinkButton).Text
                    gview.Controls.Remove(gview.Controls(i))
                    gview.Controls.AddAt(i, l)
                ElseIf TypeOf gview.Controls(i) Is System.Web.UI.WebControls.DropDownList Then
                    l.Text = CType(gview.Controls(i), System.Web.UI.WebControls.DropDownList).SelectedItem.Text
                    gview.Controls.Remove(gview.Controls(i))
                    gview.Controls.AddAt(i, l)
                ElseIf TypeOf gview.Controls(i) Is System.Web.UI.WebControls.CheckBox Then
                    l.Text = CType(gview.Controls(i), System.Web.UI.WebControls.CheckBox).Checked.ToString
                    gview.Controls.Remove(gview.Controls(i))
                    gview.Controls.AddAt(i, l)
                End If

                If gview.Controls(i).HasControls() Then
                    PrepareGridViewForExport(gview.Controls(i))
                End If

            Next
        End Sub

回答

尝试一下，它比Brendans触摸简单，但不如"功能丰富"：

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

回答

我们可以使用Crystal，因为它是VS内置的。用适当的列预定义一个水晶报表，然后我们可以使用任何用于数据网格或者网格视图的数据源。

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")

回答

我再次警告要进行两次for循环，以提取每个数据单元格的数据，并将其分别写到excel单元格中。而是使用2D对象数组，并遍历数据网格，将所有数据保存在那里。然后，我们将能够设置一个等于该2D对象数组的excel范围。

这将比逐单元写入excel快几个数量级。我一直在处理的报告过去仅花费两个小时就导出了，现在已减少到一分钟以内。

回答

我设置了gridview，然后使用html文本编写器对象将其吐到.xls文件中，如下所示：

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

回答

我们可以使用该库进行更详细的格式化
http://www.carlosag.net/Tools/ExcelXmlWriter/

页面中有示例。

回答

我经常用这个：

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