VB.NET实现MS SQL数据导出至多工作表Excel指定单元格

安全阿岳

2026-8-28

Excel目标结构截图1
Excel目标结构截图2

需求说明

开发语言：VB.NET
核心目标：将MS SQL Server中test_import表的数据，按自定义字段映射规则，填充到同一Excel工作簿的6个指定工作表的对应列中，例如将SQL返回的ProjectCode字段写入RQNHSEQ列、ItemCode字段写入Type列。
当前已有基础：已完成Excel文件结构生成代码，可自动创建指定名称的工作表、写入表头、定义列命名区域，缺少数据库连接、数据读取、映射填充、资源释放相关逻辑。

实现步骤

项目引入依赖：添加.NET自带的System.Data.SqlClient用于SQL Server操作，添加Microsoft Excel Interop COM引用用于Excel操作
提前定义字段映射关系，后续调整列对应关系只需要修改映射字典，不需要改动核心业务逻辑
使用SqlDataReader逐行读取SQL查询结果，内存占用低，兼容大数据量导出场景
数据从第2行开始写入（第1行已预设表头），逐行逐列按映射规则填充
修正原代码的保存bug，使用工作簿对象保存而非单个工作表保存，最后按顺序释放COM对象，避免后台残留Excel进程。

完整实现代码

' 先在代码文件顶部引入所需命名空间
Imports System.Data.SqlClient
Imports System.Runtime.InteropServices
Imports Microsoft.Office.Interop

Sub ExportSQLToMultiSheetExcel()
    ' ========== 配置项 按需修改 ==========
    Dim sqlConnStr As String = "Server=你的SQL服务器地址;Database=你的库名;User Id=你的账号;Password=你的密码;"
    Dim exportSql As String = "SELECT ProjectCode, ItemCode, 其他需要的字段 FROM test_import WHERE 筛选条件" ' 替换为实际业务查询SQL
    ' 字段映射：Key是SQL查询返回的字段名，Value是对应要写入的Excel列标
    Dim colMapping As New Dictionary(Of String, String) From {
        {"ProjectCode", "A"}, ' 对应RQNHSEQ列
        {"ItemCode", "E"}, ' 对应Type列，其他映射按实际需求追加
        {"VDCODE", "B"}
    }
    ' ========== 配置项结束 ==========

    Dim oExcel As Excel.Application = Nothing
    Dim oBook As Excel.Workbook = Nothing
    Dim oSheet As Excel.Worksheet = Nothing
    Dim sqlConn As SqlConnection = Nothing
    Dim sqlCmd As SqlCommand = Nothing
    Dim dataReader As SqlDataReader = Nothing

    Try
        ' 初始化Excel
        oExcel = New Excel.Application()
        oExcel.Visible = False ' 导出过程不显示Excel窗口
        oExcel.DisplayAlerts = False ' 关闭文件覆盖提示
        oBook = oExcel.Workbooks.Add()

        ' --------------- 原有生成工作表和表头的代码 ---------------
        If oExcel.Sheets.Count() < 1 Then
            oSheet = CType(oBook.Worksheets.Add(), Excel.Worksheet)
        Else
            oSheet = oExcel.Worksheets(1)
        End If
        oSheet.Name = "Requisition_Vendors"
        oSheet.Range("A1").Value = "RQNHSEQ"
        oSheet.Range("B1").Value = "VDCODE"
        oSheet.Range("C1").Value = "CURRENCY"
        oSheet.Range("D1").Value = "RATE"
        oSheet.Range("E1").Value = "SPREAD"
        oSheet.Range("F1").Value = "RATETYPE"
        oSheet.Range("G1").Value = "RATEMATCH"
        oSheet.Range("H1").Value = "RATEDATE"
        oSheet.Range("I1").Value = "RATEOPER"

        If oExcel.Sheets.Count() < 2 Then
            oSheet = CType(oBook.Worksheets.Add(), Excel.Worksheet)
        Else
            oSheet = oExcel.Worksheets(2)
        End If
        oSheet.Name = "Requisition_Detail_Opt__Fields"
        oSheet.Range("A1").Value = "RQNHSEQ"
        oSheet.Range("B1").Value = "RQNLREV"
        oSheet.Range("C1").Value = "OPTFIELD"
        oSheet.Range("D1").Value = "VALUE"
        oSheet.Range("E1").Value = "TYPE"
        oSheet.Range("F1").Value = "LENGTH"
        oSheet.Range("G1").Value = "DECIMALS"
        oSheet.Range("H1").Value = "ALLOWNULL"
        oSheet.Range("I1").Value = "VALIDATE"
        oSheet.Range("J1").Value = "SWSET"
        oSheet.Range("K1").Value = "VALINDEX"
        oSheet.Range("L1").Value = "VALIFTEXT"
        oSheet.Range("M1").Value = "VALIFMONEY"
        oSheet.Range("N1").Value = "VALIFNUM"
        oSheet.Range("O1").Value = "VALIFLONG"
        oSheet.Range("P1").Value = "VALIFBOOL"
        oSheet.Range("Q1").Value = "VALIFDATE"
        oSheet.Range("R1").Value = "VALIFTIME"
        oSheet.Range("S1").Value = "FDESC"
        oSheet.Range("T1").Value = "VDESC"

        If oExcel.Sheets.Count() < 3 Then
            oSheet = CType(oBook.Worksheets.Add(), Excel.Worksheet)
        Else
            oSheet = oExcel.Worksheets(3)
        End If
        oSheet.Name = "Requisition_Header_Opt__Fields"
        oSheet.Range("A1").Value = "RQNHSEQ"
        oSheet.Range("B1").Value = "OPTFIELD"
        oSheet.Range("C1").Value = "VALUE"
        oSheet.Range("D1").Value = "TYPE"
        oSheet.Range("E1").Value = "LENGTH"
        oSheet.Range("F1").Value = "DECIMALS"
        oSheet.Range("G1").Value = "ALLOWNULL"
        oSheet.Range("H1").Value = "VALIDATE"
        oSheet.Range("I1").Value = "SWSET"
        oSheet.Range("J1").Value = "VALINDEX"
        oSheet.Range("K1").Value = "VALIFTEXT"
        oSheet.Range("L1").Value = "VALIFMONEY"
        oSheet.Range("M1").Value = "VALIFNUM"
        oSheet.Range("N1").Value = "VALIFLONG"
        oSheet.Range("O1").Value = "VALIFBOOL"
        oSheet.Range("P1").Value = "VALIFDATE"
        oSheet.Range("Q1").Value = "VALIFTIME"
        oSheet.Range("R1").Value = "FDESC"
        oSheet.Range("S1").Value = "VDESC"

        If oExcel.Sheets.Count() < 4 Then
            oSheet = CType(oBook.Worksheets.Add(), Excel.Worksheet)
        Else
            oSheet = oExcel.Worksheets(4)
        End If
        oSheet.Name = "Requisition_Comments"
        oSheet.Range("A1").Value = "RQNHSEQ"
        oSheet.Range("B1").Value = "RQNCREV"
        oSheet.Range("C1").Value = "RQNCSEQ"
        oSheet.Range("D1").Value = "COMMENTTYP"
        oSheet.Range("E1").Value = "COMMENT"

        If oExcel.Sheets.Count() < 5 Then
            oSheet = CType(oBook.Worksheets.Add(), Excel.Worksheet)
        Else
            oSheet = oExcel.Worksheets(5)
        End If
        oSheet.Name = "Requisition_Lines"
        oSheet.Range("A1").Value = "RQNHSEQ"
        oSheet.Range("B1").Value = "RQNLREV"
        oSheet.Range("C1").Value = "RQNLSEQ"
        oSheet.Range("D1").Value = "RQNCSEQ"
        oSheet.Range("E1").Value = "OEONUMBER"
        oSheet.Range("F1").Value = "VDCODE"
        oSheet.Range("G1").Value = "ITEMNO"
        oSheet.Range("H1").Value = "LOCATION"
        oSheet.Range("I1").Value = "ITEMDESC"
        oSheet.Range("J1").Value = "EXPARRIVAL"
        oSheet.Range("K1").Value = "VENDITEMNO"
        oSheet.Range("L1").Value = "HASCOMMENT"
        oSheet.Range("M1").Value = "ORDERUNIT"
        oSheet.Range("N1").Value = "OQORDERED"
        oSheet.Range("O1").Value = "HASDROPSHI"
        oSheet.Range("P1").Value = "DROPTYPE"
        oSheet.Range("Q1").Value = "IDCUST"
        oSheet.Range("R1").Value = "IDCUSTSHPT"
        oSheet.Range("S1").Value = "DLOCATION"
        oSheet.Range("T1").Value = "DESC"
        oSheet.Range("U1").Value = "ADDRESS1"
        oSheet.Range("V1").Value = "ADDRESS2"
        oSheet.Range("W1").Value = "ADDRESS3"
        oSheet.Range("X1").Value = "ADDRESS4"
        oSheet.Range("Y1").Value = "CITY"
        oSheet.Range("Z1").Value = "STATE"
        oSheet.Range("AA1").Value = "ZIP"
        oSheet.Range("AB1").Value = "COUNTRY"
        oSheet.Range("AC1").Value = "PHONE"
        oSheet.Range("AD1").Value = "FAX"
        oSheet.Range("AE1").Value = "CONTACT"
        oSheet.Range("AF1").Value = "EMAIL"
        oSheet.Range("AG1").Value = "PHONEC"
        oSheet.Range("AH1").Value = "FAXC"
        oSheet.Range("AI1").Value = "EMAILC"
        oSheet.Range("AJ1").Value = "MANITEMNO"
        oSheet.Range("AK1").Value = "CONTRACT"
        oSheet.Range("AL1").Value = "PROJECT"
        oSheet.Range("AM1").Value = "CCATEGORY"
        oSheet.Range("AN1").Value = "UNITCOST"
        oSheet.Range("AO1").Value = "UCISMANUAL"
        oSheet.Range("AP1").Value = "CPCOSTTOPO"
        oSheet.Range("AQ1").Value = "EXTENDED"
        oSheet.Range("AR1").Value = "DISCOUNT"
        oSheet.Range("AS1").Value = "DISCPCT"
        oSheet.Range("AT1").Value = "UNITWEIGHT"
        oSheet.Range("AU1").Value = "EXTWEIGHT"
        oSheet.Range("AV1").Value = "WEIGHTUNIT"
        oSheet.Range("AW1").Value = "WEIGHTCONV"
        oSheet.Range("AX1").Value = "DEFUWEIGHT"
        oSheet.Range("AY1").Value = "DEFEXTWGHT"
        oSheet.Range("AZ1").Value = "NETXTENDED"
        oSheet.Range("BA1").Value = "DETAILNUM"

        If oExcel.Sheets.Count() < 6 Then
            oSheet = CType(oBook.Worksheets.Add(), Excel.Worksheet)
        Else
            oSheet = oExcel.Worksheets(6)
        End If
        oSheet.Name = "Requisitions"
        oSheet.Range("A1").Value = "RQNHSEQ"
        oSheet.Range("B1").Value = "ISPRINTED"
        oSheet.Range("C1").Value = "DATE"
        oSheet.Range("D1").Value = "RQNNUMBER"
        oSheet.Range("E1").Value = "VDCODE"
        oSheet.Range("F1").Value = "VDNAME"
        oSheet.Range("G1").Value = "ONHOLD"
        oSheet.Range("H1").Value = "ORDEREDON"
        oSheet.Range("I1").Value = "EXPARRIVAL"
        oSheet.Range("J1").Value = "EXPIRATION"
        oSheet.Range("K1").Value = "DESCRIPTIO"
        oSheet.Range("L1").Value = "REFERENCE"
        oSheet.Range("M1").Value = "COMMENT"
        oSheet.Range("N1").Value = "REQUESTBY"
        oSheet.Range("O1").Value = "DOCSOURCE"
        oSheet.Range("P1").Value = "STCODE"
        oSheet.Range("Q1").Value = "STDESC"
        oSheet.Range("R1").Value = "APPROVER"
        oSheet.Range("S1").Value = "ENTEREDBY"
        oSheet.Range("T1").Value = "HASJOB"
        oSheet.Range("U1").Value = "DETAILNEXT"

        ' 定义命名区域
        Dim requisitions As Excel.Worksheet = oBook.Sheets("Requisitions")
        Dim range1 As Excel.Range = CType(requisitions.Range("$A:$U"), Excel.Range)
        range1.Name = "Requisitions"

        Dim requisitionLines As Excel.Worksheet = oBook.Sheets("Requisition_Lines")
        Dim range2 As Excel.Range = CType(requisitionLines.Range("$A:$BA"), Excel.Range)
        range2.Name = "Requisition_Lines"

        Dim requisitionComments As Excel.Worksheet = oBook.Sheets("Requisition_Comments")
        Dim range3 As Excel.Range = CType(requisitionComments.Range("$A:$E"), Excel.Range)
        range3.Name = "Requisition_Comments"

        Dim requisitionHOF As Excel.Worksheet = oBook.Sheets("Requisition_Header_Opt__Fields")
        Dim range4 As Excel.Range = CType(requisitionHOF.Range("$A:$S"), Excel.Range)
        range4.Name = "Requisition_Header_Opt__Fields"

        Dim requisitionDOF As Excel.Worksheet = oBook.Sheets("Requisition_Detail_Opt__Fields")
        Dim range5 As Excel.Range = CType(requisitionDOF.Range("$A:$T"), Excel.Range)
        range5.Name = "Requisition_Detail_Opt__Fields"

        Dim requisitionVendors As Excel.Worksheet = oBook.Sheets("Requisition_Vendors")
        Dim range6 As Excel.Range = CType(requisitionVendors.Range("$A:$I"), Excel.Range)
        range6.Name = "Requisition_Vendors"
        ' --------------- 原有表头生成代码结束 ---------------

        ' ========== 数据填充逻辑 ==========
        ' 切换到要填充数据的工作表，其他工作表填充逻辑一致，替换目标Sheet、对应SQL、字段映射即可
        oSheet = CType(oBook.Sheets("Requisitions"), Excel.Worksheet)
        Dim currentRow As Integer = 2 ' 从第2行开始写入，第1行为表头

        ' 连接SQL读取数据
        sqlConn = New SqlConnection(sqlConnStr)
        sqlConn.Open()
        sqlCmd = New SqlCommand(exportSql, sqlConn)
        dataReader = sqlCmd.ExecuteReader()

        ' 逐行读取数据写入Excel
        While dataReader.Read()
            ' 遍历映射关系，将对应字段写入指定列
            For Each map In colMapping
                Dim sqlFieldName As String = map.Key
                Dim excelCol As String = map.Value
                ' 处理数据库NULL值，避免写入报错
                Dim cellValue As Object = If(IsDBNull(dataReader(sqlFieldName)), "", dataReader(sqlFieldName))
                oSheet.Range($"{excelCol}{currentRow}").Value = cellValue
            Next
            currentRow += 1
        End While

        ' 关闭数据库连接
        dataReader.Close()
        sqlConn.Close()

        ' 保存文件
        Dim SaveFileDialog1 As New SaveFileDialog()
        SaveFileDialog1.Filter = "Excel files (*.xlsx)|*.xlsx"
        SaveFileDialog1.FilterIndex = 2
        SaveFileDialog1.RestoreDirectory = True
        If SaveFileDialog1.ShowDialog() = DialogResult.OK Then
            oBook.SaveAs(SaveFileDialog1.FileName)
            MsgBox("Excel文件导出成功！")
        Else
            Exit Sub
        End If

    Catch ex As Exception
        MsgBox($"导出出错：{ex.Message}")
    Finally
        ' 按顺序释放所有资源，避免后台残留Excel进程
        If dataReader IsNot Nothing Then dataReader.Dispose()
        If sqlCmd IsNot Nothing Then sqlCmd.Dispose()
        If sqlConn IsNot Nothing Then sqlConn.Dispose()
        If oSheet IsNot Nothing Then Marshal.ReleaseComObject(oSheet)
        If oBook IsNot Nothing Then
            oBook.Close()
            Marshal.ReleaseComObject(oBook)
        End If
        If oExcel IsNot Nothing Then
            oExcel.Quit()
            Marshal.ReleaseComObject(oExcel)
        End If
        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Try
End Sub

注意事项

如果需要给多个工作表填充不同数据，只需要重复「切换目标工作表→编写对应查询SQL→定义对应字段映射→逐行写入」的逻辑即可
添加Excel