VB.Net如何借助模板或自定义格式写入Excel文件？

一、直接为表头设置格式（无需模板） #

推荐使用EPPlus（无需安装本地Office，轻量高效），也可使用Microsoft Office Interop Excel（依赖本地Office环境）。

1. EPPlus实现示例 #

首先通过NuGet安装EPPlus包，5.x及以上版本需指定许可证：

Imports OfficeOpenXml

' 非商用场景设置许可证
ExcelPackage.LicenseContext = LicenseContext.NonCommercial

Using package As New ExcelPackage()
    ' 创建新工作表
    Dim worksheet = package.Workbook.Worksheets.Add("业务数据")
    
    ' 写入表头内容
    worksheet.Cells("A1").Value = "序号"
    worksheet.Cells("B1").Value = "客户名称"
    worksheet.Cells("C1").Value = "订单金额"
    worksheet.Cells("D1").Value = "下单日期"
    
    ' 选中整个表头行
    Dim headerRange = worksheet.Cells("A1:D1")
    
    ' 设置表头格式：加粗、灰色背景、居中、字体放大
    With headerRange.Style
        .Font.Bold = True
        .Font.Size = 12
        .Fill.PatternType = ExcelFillStyle.Solid
        .Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray)
        .HorizontalAlignment = ExcelHorizontalAlignment.Center
    End With
    
    ' 自动适配列宽
    worksheet.Cells.AutoFitColumns()
    
    ' 保存文件到指定路径
    package.SaveAs(New System.IO.FileInfo("C:\Output\直接生成报表.xlsx"))
End Using

2. Microsoft Interop Excel实现示例 #

需引用Microsoft.Office.Interop.Excel组件（通过NuGet或COM引用）：

Imports Microsoft.Office.Interop.Excel

Dim excelApp As New Application()
Dim workbook As Workbook = excelApp.Workbooks.Add()
Dim worksheet As Worksheet = workbook.ActiveSheet

' 写入表头
worksheet.Cells(1, 1).Value = "序号"
worksheet.Cells(1, 2).Value = "客户名称"
worksheet.Cells(1, 3).Value = "订单金额"
worksheet.Cells(1, 4).Value = "下单日期"

' 选中表头行
Dim headerRange As Range = worksheet.Range("A1:D1")

' 配置格式
With headerRange.Font
    .Bold = True
    .Size = 12
End With
headerRange.Interior.Color = System.Drawing.Color.LightGray
headerRange.HorizontalAlignment = XlHAlign.xlHAlignCenter

' 自动调整列宽
worksheet.Columns.AutoFit()

' 保存并清理资源
workbook.SaveAs("C:\Output\Interop生成报表.xlsx")
workbook.Close()
excelApp.Quit()

' 释放COM对象避免内存泄漏
System.Runtime.InteropServices.Marshal.ReleaseComObject(headerRange)
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet)
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook)
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)

二、使用Excel模板生成带格式文件 #

先在Excel中制作好模板（提前设置表头加粗、背景色、单元格样式等），再通过VB.Net加载模板并填充数据：

1. EPPlus加载模板示例 #

Imports OfficeOpenXml

ExcelPackage.LicenseContext = LicenseContext.NonCommercial

' 加载本地模板文件
Dim templatePath = "C:\Templates\报表模板.xlsx"
Using package As New ExcelPackage(New System.IO.FileInfo(templatePath))
    Dim worksheet = package.Workbook.Worksheets(0) ' 获取第一个工作表
    
    ' 从第2行开始填充数据（模板第1行为预设格式的表头）
    worksheet.Cells("A2").Value = 1
    worksheet.Cells("B2").Value = "XX科技"
    worksheet.Cells("C2").Value = 15800
    worksheet.Cells("A3").Value = 2
    worksheet.Cells("B3").Value = "YY商贸"
    worksheet.Cells("C3").Value = 9600
    
    ' 适配数据列宽
    worksheet.Cells.AutoFitColumns()
    
    ' 另存为新文件，不修改原模板
    package.SaveAs(New System.IO.FileInfo("C:\Output\基于模板的报表.xlsx"))
End Using

2. Interop加载模板示例 #

Imports Microsoft.Office.Interop.Excel

Dim excelApp As New Application()
' 打开模板文件
Dim workbook As Workbook = excelApp.Workbooks.Open("C:\Templates\报表模板.xlsx")
Dim worksheet As Worksheet = workbook.ActiveSheet

' 填充业务数据
worksheet.Cells(2, 1).Value = 1
worksheet.Cells(2, 2).Value = "XX科技"
worksheet.Cells(2, 3).Value = 15800
worksheet.Cells(3, 1).Value = 2
worksheet.Cells(3, 2).Value = "YY商贸"
worksheet.Cells(3, 3).Value = 9600

worksheet.Columns.AutoFit()

' 另存为新文件，保留原模板
workbook.SaveAs("C:\Output\基于模板的报表_Interop.xlsx")
workbook.Close()
excelApp.Quit()

' 释放COM资源
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet)
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook)
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)

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