Microsoft.Office.Interop.Excel读写数据到Excel表

Murphy
59 阅读1分钟

当然，这里是一个完整的 VB.NET 示例，它演示了如何根据数据从数据读取器中填充 Excel 工作表，并设置偶数行的背景色。此示例假设你已经有了一个数据源和一个 Excel 模板，代码中用到的方法 Template.setMeisai 和 Template.setEvenBackColor 是自定义的。

Imports Microsoft.Office.Interop.Excel
Imports System.Data

Module Module1
    Private Const meisaiNo As String = "${#明細番号#}"
    Private Const seibun As String = "${#成分#}"
    Private Const hiritu As String = "${#比率#}"
    Private Const youto As String = "${#用途#}"
    Private Const seizoti As String = "${#製造地#}"
    Private Const kigenBussitu As String = "${#起源物質#}"
    Private Const gensanti As String = "${#原材料#}"
    Private Const allerBussitu As String = "${#アレルゲン物質#}"
    Private Const allerHyoji As String = "${#アレルゲン表示#}"
    Private Const gmo As String = "${#GMO#}"
    Private Const kyogyubyo As String = "${#教科書#}"

    Sub Main()
        ' 初始化 Excel 应用程序
        Dim excelApp As New Application()
        Dim workbook As Workbook = Nothing
        Dim worksheet As Worksheet = Nothing

        Try
            ' 打开现有的 Excel 文件
            Dim filePath As String = "C:\path\to\your\file.xlsx"
            workbook = excelApp.Workbooks.Open(filePath)
            worksheet = workbook.Sheets(1) ' 选择第一个工作表

            ' 模拟从数据源获取数据行
            Dim dataTable As DataTable = GetDataTable()
            Dim dr As DataRow
            Dim strPosition As Range = worksheet.Cells(1, 1) ' 示例：假设明细开始行在 A1 单元格

            For Each dr In dataTable.Rows
                Dim rowCount As Integer = Template.setMeisai(worksheet, strPosition)

                ' 执行替换操作
                worksheet.Cells.Replace(meisaiNo, dr("KIKAKU_MEISAI_CD").ToString())
                worksheet.Cells.Replace(seibun, dr("KIKAKU_SEIBUN").ToString())
                worksheet.Cells.Replace(hiritu, dr("KIKAKU_HAIGO").ToString())
                worksheet.Cells.Replace(youto, Common.SpaceToHyphen(dr("KIKAKU_YOUTO").ToString()))
                worksheet.Cells.Replace(seizoti, Common.SpaceToHyphen(dr("KIKAKU_SEIZOTI").ToString()))
                worksheet.Cells.Replace(kigenBussitu, Common.SpaceToHyphen(dr("KIKAKU_KIGENBUSITU").ToString()))
                worksheet.Cells.Replace(gensanti, Common.SpaceToHyphen(dr("KIKAKU_KIGENGENSANTI").ToString()))
                worksheet.Cells.Replace(allerBussitu, Common.SpaceToHyphen(dr("KIKAKU_ALLERGIES_BUSITU").ToString()))
                worksheet.Cells.Replace(allerHyoji, Common.SpaceToHyphen(dr("KIKAKU_ALLERGIES_HYOJI").ToString()))
                worksheet.Cells.Replace(gmo, Common.SpaceToHyphen(dr("KIKAKU_GMO").ToString()))
                worksheet.Cells.Replace(kyogyubyo, Common.SpaceToHyphen(dr("KIKAKU_KYOGYUBYO").ToString()))

                ' 更新下一个明细位置
                strPosition = worksheet.Cells(strPosition.Row + rowCount, strPosition.Column)
            Next

            ' 设置偶数行背景色
            Template.setEvenBackColor(worksheet, strPosition)

            ' 保存并关闭工作簿
            workbook.Save()
            workbook.Close()
        Catch ex As Exception
            Console.WriteLine("错误: " & ex.Message)
        Finally
            ' 退出 Excel 应用程序
            excelApp.Quit()

            ' 清理 COM 对象
            ReleaseObject(worksheet)
            ReleaseObject(workbook)
            ReleaseObject(excelApp)
        End Try

        Console.WriteLine("完成!")
        Console.ReadLine()
    End Sub

    ' 获取示例数据表的方法
    Function GetDataTable() As DataTable
        Dim table As New DataTable()
        table.Columns.Add("KIKAKU_MEISAI_CD", GetType(String))
        table.Columns.Add("KIKAKU_SEIBUN", GetType(String))
        table.Columns.Add("KIKAKU_HAIGO", GetType(String))
        table.Columns.Add("KIKAKU_YOUTO", GetType(String))
        table.Columns.Add("KIKAKU_SEIZOTI", GetType(String))
        table.Columns.Add("KIKAKU_KIGENBUSITU", GetType(String))
        table.Columns.Add("KIKAKU_KIGENGENSANTI", GetType(String))
        table.Columns.Add("KIKAKU_ALLERGIES_BUSITU", GetType(String))
        table.Columns.Add("KIKAKU_ALLERGIES_HYOJI", GetType(String))
        table.Columns.Add("KIKAKU_GMO", GetType(String))
        table.Columns.Add("KIKAKU_KYOGYUBYO", GetType(String))

        ' 添加示例数据
        Dim row As DataRow = table.NewRow()
        row("KIKAKU_MEISAI_CD") = "001"
        row("KIKAKU_SEIBUN") = "成分1"
        row("KIKAKU_HAIGO") = "50%"
        row("KIKAKU_YOUTO") = "用途1"
        row("KIKAKU_SEIZOTI") = "製造地1"
        row("KIKAKU_KIGENBUSITU") = "起源物質1"
        row("KIKAKU_KIGENGENSANTI") = "原材料1"
        row("KIKAKU_ALLERGIES_BUSITU") = "アレルゲン物質1"
        row("KIKAKU_ALLERGIES_HYOJI") = "アレルゲン表示1"
        row("KIKAKU_GMO") = "GMO1"
        row("KIKAKU_KYOGYUBYO") = "教科書1"
        table.Rows.Add(row)

        Return table
    End Function

    ' 清理 COM 对象的方法
    Sub ReleaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

    ' Template 类的示例实现
    Public Class Template
        ' 设置明细行的示例实现
        Public Shared Function setMeisai(ByVal sheet As Worksheet, ByVal strPosition As Range) As Integer
            ' 示例: 返回处理的行数（可以根据实际需要修改）
            Return 1
        End Function

        ' 设置偶数行背景色的示例实现
        Public Shared Sub setEvenBackColor(ByVal sheet As Worksheet, ByVal strPosition As Range)
            Dim lastRow As Integer = sheet.Cells(sheet.Rows.Count, strPosition.Column).End(XlDirection.xlUp).Row
            For i As Integer = 2 To lastRow Step 2 ' 从第二行开始，设置偶数行的背景色
                sheet.Rows(i).Interior.Color = RGB(240, 240, 240) ' 设置背景色为浅灰色
            Next
        End Sub
    End Class

    ' Common 类的示例实现
    Public Class Common
        ' 将空格替换为连字符的示例实现
        Public Shared Function SpaceToHyphen(ByVal input As String) As String
            Return input.Replace(" ", "-")
        End Function
    End Class
End Module

下面是一个适用于 VB.NET 代码中的 Excel 模板的示例。你可以将这个模板用作你代码中的数据填充和格式设置的基础。

Excel 模板结构

Excel 文件: template.xlsx

工作表: Sheet1

模板内容:

ABCDEFGHIJ
明細番号成分比率用途製造地起源物質原材料アレルゲン物質アレルゲン表示GMO
${#明細番号#}${#成分#}${#比率#}${#用途#}${#製造地#}${#起源物質#}${#原材料#}${#アレルゲン物質#}${#アレルゲン表示#}${#GMO#}
${#明細番号#}${#成分#}${#比率#}${#用途#}${#製造地#}${#起源物質#}${#原材料#}${#アレルゲン物質#}${#アレルゲン表示#}${#GMO#}
${#明細番号#}${#成分#}${#比率#}${#用途#}${#製造地#}${#起源物質#}${#原材料#}${#アレルゲン物質#}${#アレルゲン表示#}${#GMO#}

示例步骤

  1. 创建 Excel 文件:

    • 打开 Excel 并创建一个新的工作簿。

  2. 设置表头:

    • 在第一行添加列标题（例如：明細番号、成分、比率等）。

  3. 添加占位符:

    • 在第二行及以下的单元格中使用 ${#占位符#} 格式来标识需要替换的数据。

  4. 调整列宽和行高:

    • 根据数据的实际大小调整列宽和行高，以确保数据能够正确显示。

  5. 保存模板:

    • 将文件保存为 template.xlsx，并确保保存到你打算在代码中使用的路径。
avatar
文章
阅读
粉丝