如何优化SQL转Excel报表生成代码？将3万行数据生成时间压至1分钟内

我的项目需要每4小时基于SQL数据库生成Excel报表，当前4小时的数据包含30000行7列，但现有VB.NET代码生成报表耗时超15分钟，希望优化后将生成时间控制在1分钟以内。

原代码如下：

Private Sub ButtonReport_Click(sender As Object, e As EventArgs) Handles ButtonReport.Click


        '打开前清除连接和数据集
        con.Close()
        ds.Clear()
        ' 用于Excel报表
        Dim r, c As Integer
        ' 此处使用Try Catch处理异常

        ' 显示进度条
        ' ProgBarReport.Visible = True
        ProgBarReport.Value = 0
        LabelReport.ResetText()

        Try
            ' 打开连接
            con.Open()

            Dim StartDateRpt = Format(DateTimePickerRptStrt.Value, "yyyy-MM-dd HH:mm:ss")
            Dim EndDateRpt = Format(DateTimePickerRptEnd.Value, "yyyy-MM-dd HH:mm:ss")

            Dim query As String = "SELECT * FROM [ReportDatabase03].[dbo].[Past03] WHERE Date_Time Between '" + StartDateRpt + "'  and '" + EndDateRpt + "' order by Date_Time desc"


            adpt.SelectCommand = New SqlCommand(query, con)
            ds = New DataSet("wincc")
            adpt.Fill(ds)

            Dim i As Integer

            ' Excel应用程序标准配置
            Dim xlApp As Excelr.Application
            Dim xlWorkBook As Excelr.Workbook
            Dim xlWorkSheet As Excelr.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value
            xlApp = New Excelr.Application

            ' ------- 从文本文件读取示例报表路径 ------
            'Dim Srpath As String = "c:\mysettxtup\samplereport.txt"
            'Dim Srobjectreader As New System.IO.StreamReader(Srpath)
            'Dim Srpathstring As String = Srobjectreader.ReadLine


            ' ------- 从文本文件读取示例报表路径 ------
            ' xlWorkBook = xlApp.Workbooks.Add(Srpathstring & "\SampleReport")

            '****** 将上述硬编码修改为从D盘sampleRepport读取Excel模板 ********

            xlWorkBook = xlApp.Workbooks.Add("D:\mysettxtup\ParagReportSamplePast03")

            ' ------- 从文本文件读取报表保存路径 ------
            '   Dim Slrpath As String = "c:\mysettxtup\Savereport.txt"
            ' Dim Slrobjectreader As New System.IO.StreamReader(Slrpath)
            ' Dim Slrpathstring As String = Slrobjectreader.ReadLine
            xlWorkSheet = xlWorkBook.Sheets("ParagReport")

            r = ds.Tables(0).Rows.Count ' 此处加7是因为从第8行开始记录
            c = ds.Tables(0).Columns.Count

            ProgBarReport.Step = ds.Tables(0).Rows.Count * 2
            ProgBarReport.Maximum = ds.Tables(0).Rows.Count

            ' MessageBox.Show(r, c)
            ' 将数据表行和列打印到工作表
            For i = 0 To ds.Tables(0).Rows.Count - 1
                Label_R.Text = r
                Label_C.Text = c
                Label_I.Text = i
                Dim dateValue = ds.Tables(0).Rows(i).Item(0)
                Dim xxx = Format(dateValue, "dd/MMM/yy HH:mm:ss.fff")
                xlWorkSheet.Cells(i + 13, 1) = Format(dateValue, "dd-MM-yyyy HH:mm:ss:fff") '列B- "日期与时间"
                xlWorkSheet.Cells(i + 13, 2) = Format(ds.Tables(0).Rows(i).Item(1), "") '列E - "客户零件编号"
                xlWorkSheet.Cells(i + 13, 3) = Format(ds.Tables(0).Rows(i).Item(2), "") '列F - "Tenneco FG SAP零件编号"
                xlWorkSheet.Cells(i + 13, 4) = Format(ds.Tables(0).Rows(i).Item(3), "") '列G - "Tenneco FG增量序列号"
                xlWorkSheet.Cells(i + 13, 5) = Format(ds.Tables(0).Rows(i).Item(4), "") '列H - "Tenneco罐装SAP零件编号1"
                xlWorkSheet.Cells(i + 13, 6) = Format(ds.Tables(0).Rows(i).Item(5), "") '列H - "Tenneco罐装SAP零件编号2"
                xlWorkSheet.Cells(i + 13, 7) = Format(ds.Tables(0).Rows(i).Item(6), "") '列I - "扫描获取的罐装序列号"
                xlWorkSheet.Cells(i + 13, 8) = Format(ds.Tables(0).Rows(i).Item(7), "") '列I - "扫描获取的罐装序列号"
                xlWorkSheet.Cells(i + 13, 9) = Format(ds.Tables(0).Rows(i).Item(8), "") '列I - "扫描获取的罐装序列号"
                xlWorkSheet.Cells(i + 13, 10) = Format(ds.Tables(0).Rows(i).Item(9), "") '列I - "扫描获取的罐装序列号"
                xlWorkSheet.Cells(i + 13, 11) = Format(ds.Tables(0).Rows(i).Item(10), "") '列I - "扫描获取的罐装序列号"
                xlWorkSheet.Cells(i + 13, 12) = Format(ds.Tables(0).Rows(i).Item(11), "") '列I - "扫描获取的罐装序列号"
                xlWorkSheet.Cells(i + 13, 13) = Format(ds.Tables(0).Rows(i).Item(12), "") '列I - "扫描获取的罐装序列号"
                xlWorkSheet.Cells(i + 13, 14) = Format(ds.Tables(0).Rows(i).Item(13), "") '列I - "扫描获取的罐装序列号"
                xlWorkSheet.Cells(i + 13, 15) = Format(ds.Tables(0).Rows(i).Item(14), "") '列I - "扫描获取的罐装序列号"
                xlWorkSheet.Cells(i + 13, 16) = Format(ds.Tables(0).Rows(i).Item(15), "") '列I - "扫描获取的罐装序列号"
                xlWorkSheet.Cells(i + 13, 17) = Format(ds.Tables(0).Rows(i).Item(16), "") '列I - "扫描获取的罐装序列号"
                '~~> 进度条
                ' ProgBarReport.PerformStep()
                ProgBarReport.Increment(1)
            Next

            ' Excel样式设置
            With xlWorkSheet
                xlWorkSheet.Cells(6, 2) = Format(Now, "dd/MMM/yy HH:mm")
                xlWorkSheet.Cells(7, 2) = Format(ds.Tables(0).Rows(0).Item(0), "dd/MMM/yy HH:mm:ss")
                xlWorkSheet.Cells(8, 2) = Format(ds.Tables(0).Rows(r - 1).Item(0), "dd/MMM/yy HH:mm:ss")
                xlWorkSheet.Columns("A:Q").EntireColumn.AutoFit()
                .Protect()
            End With

            ' 保存Excel工作表
            '~~> 将工作表保存至以下路径
            Dim currentdate As String = String.Format("{0:ddMMyy-HHmm}", DateTime.Now)

            ' ------- 从文本文件读取报表保存路径 ------
            '****** 将上述硬编码修改为将Excel报表保存至D盘Report文件夹 ********           
            xlWorkSheet.SaveAs("D:\ReportPast03" & "\ParagDailyReportPast03" & currentdate & ".xlsx")

            xlWorkBook.Close()
            xlApp.Quit()
            'objExcel.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
            xlApp = Nothing

            ProgBarReport.Value = ProgBarReport.Maximum

            '  ProgBarReport.Value = 0
            LabelReport.Text = "报表生成成功"

        Catch ex As Exception

            MsgBox(ex.ToString())

        End Try
    End Sub

核心优化方向 #

• 替换低效的Excel COM互操作，改用直接读写Excel文件的开源库
• 优化SQL查询，减少数据读取耗时
• 批量写入数据，避免逐单元格操作的性能损耗
• 移除冗余UI操作

具体优化措施 #

1. 替换Excel操作库
   原代码使用Excel COM互操作，每次单元格操作都要跨进程调用，性能极低。改用EPPlus（需通过NuGet安装），它直接读写xlsx文件，速度提升数十倍。

2. 优化SQL查询

   • 使用参数化查询，避免字符串拼接的SQL注入风险，同时让SQL Server缓存执行计划
   • 只查询需要的列，替换SELECT *，减少数据传输量
   • 确保Date_Time字段创建非聚集索引，加速范围查询

3. 批量写入数据
   利用EPPlus的LoadFromDataTable方法，一次性将DataTable数据写入Excel，避免逐行循环赋值的巨大开销。

4. 移除冗余操作

   • 删除循环中频繁更新Label的代码，避免UI频繁刷新拖慢速度
   • 去掉不必要的Format调用，直接通过EPPlus设置单元格格式

Private Sub ButtonReport_Click(sender As Object, e As EventArgs) Handles ButtonReport.Click
    ' 初始化进度条
    ProgBarReport.Value = 0
    LabelReport.ResetText()

    Try
        ' 1. 优化SQL查询：参数化+指定查询列
        Dim startDate = DateTimePickerRptStrt.Value
        Dim endDate = DateTimePickerRptEnd.Value

        ' 确保Date_Time字段已创建非聚集索引
        Dim query As String = "SELECT Date_Time, Col1, Col2, Col3, Col4, Col5, Col6, Col7 " & _
                              "FROM [ReportDatabase03].[dbo].[Past03] " & _
                              "WHERE Date_Time BETWEEN @StartDate AND @EndDate " & _
                              "ORDER BY Date_Time DESC"

        ' 使用Using块自动释放资源
        Using con As New SqlConnection("你的数据库连接字符串")
            Using cmd As New SqlCommand(query, con)
                cmd.Parameters.AddWithValue("@StartDate", startDate)
                cmd.Parameters.AddWithValue("@EndDate", endDate)

                con.Open()
                Dim dt As New DataTable()
                Using adpt As New SqlDataAdapter(cmd)
                    adpt.Fill(dt)
                End Using
                con.Close()

                ProgBarReport.Maximum = dt.Rows.Count

                ' 2. 使用EPPlus批量生成Excel
                Dim templatePath = "D:\mysettxtup\ParagReportSamplePast03.xlsx" ' 确保模板为xlsx格式
                Dim savePath = $"D:\ReportPast03\ParagDailyReportPast03{DateTime.Now:ddMMyy-HHmm}.xlsx"

                Using package As New ExcelPackage(New FileInfo(templatePath))
                    Dim worksheet = package.Workbook.Worksheets("ParagReport")

                    ' 批量写入DataTable数据，从第13行开始
                    worksheet.Cells(13, 1).LoadFromDataTable(dt, False)

                    ' 设置日期列格式（第1列）
                    worksheet.Cells(13, 1, 13 + dt.Rows.Count - 1, 1).Style.Numberformat.Format = "dd-MM-yyyy HH:mm:ss.fff"

                    ' 更新报表头部信息
                    worksheet.Cells(6, 2).Value = DateTime.Now.ToString("dd/MMM/yy HH:mm")
                    If dt.Rows.Count > 0 Then
                        worksheet.Cells(7, 2).Value = DirectCast(dt.Rows(0)("Date_Time"), DateTime).ToString("dd/MMM/yy HH:mm:ss")
                        worksheet.Cells(8, 2).Value = DirectCast(dt.Rows(dt.Rows.Count - 1)("Date_Time"), DateTime).ToString("dd/MMM/yy HH:mm:ss")
                    End If

                    ' 自动调整列宽
                    worksheet.Cells("A:Q").AutoFitColumns()
                    worksheet.Protect()

                    ' 保存文件
                    package.SaveAs(New FileInfo(savePath))
                End Using

                ProgBarReport.Value = ProgBarReport.Maximum
                LabelReport.Text = "报表生成成功"
            End Using
        End Using

    Catch ex As Exception
        MsgBox(ex.ToString())
    End Try
End Sub

额外注意事项 #

• 通过NuGet安装EPPlus包（版本4.x无需商业许可证，5+版本需注意许可证要求）
• 替换代码中的你的数据库连接字符串为实际连接字符串
• 若模板为xls格式，建议转换为xlsx格式以获得最佳性能
• 可将报表生成逻辑放在后台线程执行，避免阻塞UI

内容的提问来源于stack exchange，提问作者Manish Choudhari