如何保存数据网格以在 vb.net 中表现出色？

它需要是原生 XLS 文件吗？最好的办法可能是将数据导出到 CSV 文件，该文件是纯文本且相当容易生成。默认情况下，大多数用户在 Excel 中打开 CSV，因此他们不知道其中的区别。

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

编辑：代码格式化程序不喜欢 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 中。使用适当的列预定义 crystal 报表，然后可以使用将用于 datagrid 或 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")

我会再次警告执行双 for 循环以提取每个数据单元的数据，并单独写入 excel 单元格。相反，请使用 2D 对象数组，并遍历您的数据网格，将所有数据保存在那里。然后，您将能够设置等于该 2D 对象数组的 excel 范围。

这将比逐个单元格编写 excel 快几个数量级。我一直在处理的一些报告，过去只需两个小时即可导出，现在已经缩短到一分钟以内。

我设置了网格视图，然后使用 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);
                }
            }
        }
    }

首次导入 COM 库 Microsoft Excel 对象

示例代码：

Public Sub exportOfficePCandWorkstation(ByRef mainForm As Form1, ByVal Location As String, ByVal WorksheetName As String)
        Dim xlApp As New Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim Header(23) As String
        Dim HeaderCell(23) As String
        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"}
        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"}
        xlWorkBook = xlApp.Workbooks.Add
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")
        xlWorkSheet.Name = WorksheetName
        xlApp.Visible = True
        xlWorkSheet.Application.ActiveWindow.SplitRow = 1
        xlWorkSheet.Application.ActiveWindow.SplitColumn = 3
        xlWorkSheet.Application.ActiveWindow.FreezePanes = True
        With xlWorkSheet
            For count As Integer = 0 To 23
                .Range(HeaderCell(count) & 1).Value = Header(count)
            Next
            With .Range("A1:X1")
                .Interior.Color = 1
                With .Font
                    .Size = 16
                    .ColorIndex = 2
                    .Name = "Times New Roman"
                End With
            End With
            For i = 0 To mainForm.DataGridView1.RowCount - 1
                For j = 0 To mainForm.DataGridView1.ColumnCount - 1
                    If mainForm.DataGridView1(j, i).Value.ToString = "System.Byte[]" Then
                        xlWorkSheet.Cells(i + 2, j + 2) = "Attached"
                    Else
                        xlWorkSheet.Cells(i + 2, j + 2) = mainForm.DataGridView1(j, i).Value.ToString()
                    End If
                Next
                .Range("A" & i + 2).Value = (i + 1).ToString
            Next
            With .Range("A:Z")
                .EntireColumn.AutoFit()
            End With
            With .Range("B2:X" & mainForm.DataGridView1.RowCount + 1)
                .HorizontalAlignment = Excel.XlVAlign.xlVAlignJustify
            End With
            With .Range("A1:A" & mainForm.DataGridView1.RowCount + 1)
                .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
            End With
            '-----------------------------------Insert Border Lines--------------------------------------
            With .Range("A1:X" & mainForm.DataGridView1.RowCount + 1)
                With .Borders(Excel.XlBordersIndex.xlEdgeLeft)
                    .LineStyle = Excel.XlLineStyle.xlDouble
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = Excel.XlBorderWeight.xlThin
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeTop)
                    .LineStyle = Excel.XlLineStyle.xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = Excel.XlBorderWeight.xlThin
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeBottom)
                    .LineStyle = Excel.XlLineStyle.xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = Excel.XlBorderWeight.xlThin
                End With
                With .Borders(Excel.XlBordersIndex.xlEdgeRight)
                    .LineStyle = Excel.XlLineStyle.xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = Excel.XlBorderWeight.xlThin
                End With
                With .Borders(Excel.XlBordersIndex.xlInsideVertical)
                    .LineStyle = Excel.XlLineStyle.xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = Excel.XlBorderWeight.xlThin
                End With
                With .Borders(Excel.XlBordersIndex.xlInsideHorizontal)
                    .LineStyle = Excel.XlLineStyle.xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = Excel.XlBorderWeight.xlThin
                End With
            End With
        End With
            xlWorkSheet.SaveAs(Location)
            xlWorkBook.Close()
        xlApp.Quit()
        MsgBox("Export Record successful", MsgBoxStyle.Information, "Export to Excel")
End Sub

我使用 SaveFileDialog 在特定位置创建 excel