mysql - 将Gridview连接MYSQL的数据导出到excel

Question

我在 Visual Studio (VB) 中创建了一个简单的表单，其中有一个数据 GridView 连接到 MySQL 中的表(托管在远程服务器中)。

我有以下代码将 GridView 导出到 Excel，但导出需要很长时间(大约 15 分钟)。

MySQL 中的表非常小(1000 行和 60 列)。

是否有更好的方法将完整的 MySQL 表导出到 Excel？

请帮忙

代码:

 Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer


        xlApp = New Microsoft.Office.Interop.Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        For i = 0 To DataGridView1.Rows.Count - 1
            For j = 0 To DataGridView1.Columns.Count - 1
                For k As Integer = 1 To DataGridView1.Columns.Count
                    On Error Resume Next
                    xlWorkSheet.Cells(1, k) = DataGridView1.Columns(k - 1).HeaderText
                    xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString()
                Next
            Next
        Next
        xlWorkSheet.SaveAs("C:\Users\USERNAME\Desktop\vbexcel.xlsx")
        xlWorkBook.Close()

Answer 1

有两种方法可以做到这一点:

1. 使用 rdlc 报告。
2. 使用 gridview 导出。

第一种方式，您需要创建 rdlc 报告，然后将数据检索到数据表中，然后调用以下代码:

Dim MyDataSource As ReportDataSource = New ReportDataSource("ReportDataSet", MyDataTable)
        reportviewer1.LocalReport.ReportPath = Server.MapPath("MyrdlcReportPath")
        rvSmartCardsIssues.LocalReport.EnableExternalImages = True
        rvSmartCardsIssues.LocalReport.DataSources.Clear()
        rvSmartCardsIssues.LocalReport.DataSources.Add(MyDataSource )
        rvSmartCardsIssues.LocalReport.Refresh()

        Dim warnings As Warning() = Nothing
        Dim streamids As String() = Nothing
        Dim mimeType As String = Nothing
        Dim encoding As String = Nothing
        Dim extension As String = Nothing
        Dim bytes As Byte()
        bytes = rvSmartCardsIssues.LocalReport.Render("Excel", Nothing, mimeType, encoding, extension, streamids, warnings)
        HttpContext.Current.Response.Buffer = True
        HttpContext.Current.Response.Clear()
        HttpContext.Current.Response.ContentType = mimeType
        HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=ExportedFileName.xls")
        HttpContext.Current.Response.BinaryWrite(bytes)
        HttpContext.Current.Response.Flush()
        HttpContext.Current.Response.End()

基于此函数的第二种方式:

Public Shared Sub ExportGridViewToExcelGridView(ByVal Filename As String, ByRef gvr As GridView, ByRef currentPage As Page)

    Dim HtmlForm As System.Web.UI.HtmlControls.HtmlForm = New System.Web.UI.HtmlControls.HtmlForm()
    currentPage.Controls.Add(HtmlForm)
    HtmlForm.Controls.Add(gvr)

    currentPage.Response.Clear()
    currentPage.Response.Buffer = True
    currentPage.Response.AddHeader("Content-Disposition", "attachment; filename=" & Filename)
    currentPage.Response.ContentType = "application/vnd.ms-excel"
    currentPage.Response.ContentEncoding = System.Text.Encoding.UTF8
    currentPage.Response.Charset = ""
    currentPage.EnableViewState = False
    Using strwriter As New StringWriter
        Dim htmlwrt As HtmlTextWriter = New HtmlTextWriter(strwriter)
        HtmlForm.RenderControl(htmlwrt)
        htmlwrt.Flush()
        currentPage.Response.Write(strwriter.ToString)
        currentPage.Response.End()
    End Using
End Sub

确保当您使用第二种方式时将 gridview 分页属性设置为 false。