VB.NET 第三方 EPPLUS EXCEL操作类库应用笔记

微信公众号:VB 小源码 微信:vbee_club QQ:463550067

• 官网地址:https://www.epplussoftware.com/
• DLL 程序包地址:https://www.nuget.org/packages/EPPlus
• 开源地址:https://github.com/EPPlusSoftware/
• 官方 API 文档:https://epplussoftware.com/docs/5.0/api/index.html

一,EPPLUS 介绍(版本 5.8.0.0)

EPPlus 是使用 Open Office XML 格式（xlsx）读写 Excel 2007 / 2010 文件的.net 开发库。

EPPlus 支持:(单元格范围,单元格样式(边框，颜色，填充，字体，数字，对齐),图表,图片,形状,批注,表格,保护,加密,数据透视表,数据验证,条件格式,VBA,公式计算等等)

1.1 ExcelPackage 类

ExcelPackage 是 EPPlus 的入口类，解析一个 Excel 文件，生成 ExcelWorkbook 对象来表示一个 Excel。该类实现了 IDisposable 接口，也就是说可以使用 using 进行对象释放。

不管构造函数中传入的参数是否有效，该类的 Workbook 属性都会自动创建，无须担心空指针问题。

注意：在获取具体的 Sheet 时，索引号从 1 开始.

1.2 ExcelWorkbook 类

ExcelWorkbook 类表示了一个 Excel 文件，其 Worksheets 属性对应着 Excel 的各个 Sheet。Worksheets 属性会自动创建，不用担心空指针异常，但是其 Count 可能为 0。

1.3 ExcelWorksheet 类

ExcelWorksheet 类表示了一个 sheet 表格，其 ExcelRange 属性对应着 Sheet 的各个单元格。

二,EPPLUS 应用

2.1 创建 Excel

• 在执行创建 Excel 之前,要先指定许可方式(新版本)

    ''指定非商业证书
    ExcelPackage.LicenseContext = LicenseContext.NonCommercial

• 创建一个 Excel 文件

     ''创建一个Excel文件
        Using XlPkg As New ExcelPackage With {
            .File = New IO.FileInfo("F:\Rambo\桌面\EPPlus.xlsx")
            ''----------
            ''在这里写入Excel操作
            ''----------
            ''保存Excel文件
            XlPkg.Save()
        End Using

• 创建一个 Workbook

        ''创建一个Excel文件
        Using XlPkg As New ExcelPackage With {
            .File = New IO.FileInfo("F:\Rambo\桌面\EPPlus.xlsx")
            ''----------
            ''在这里写入Excel操作
            ''----------
            ''定义一个工作簿
            Dim xlbook As ExcelWorkbook = XlPkg.Workbook
            ''----------
            ''保存Excel文件
            XlPkg.Save()
        End Using

• 创建一个工作表(完整的创建了一个 Excel 文件)

        ''指定非商业证书
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial
        ''创建一个Excel文件
        Using XlPkg As New ExcelPackage With {
            .File = New IO.FileInfo("F:\Rambo\桌面\EPPlus.xlsx")
            ''----------
            ''在这里写入Excel操作
            ''----------
            ''定义一个工作簿
            Dim xlbook As ExcelWorkbook = XlPkg.Workbook
            ''添加一个工作表
            Dim xlsht As ExcelWorksheet = xlbook.Worksheets.Add("EEPLUS测试表")
            ''----------
            ''保存Excel文件
            XlPkg.Save()
        End Using

• 附加:设置 Excel 文件工作簿属性

 ''指定非商业证书
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial
        ''创建一个Excel文件
        Using XlPkg As New ExcelPackage With {
            .File = New IO.FileInfo("F:\Rambo\桌面\EPPlus.xlsx")
            ''----------
            ''在这里写入Excel操作
            ''----------
            ''定义一个工作簿
            Dim xlbook As ExcelWorkbook = XlPkg.Workbook
            ''添加一个工作表
            Dim xlsht As ExcelWorksheet = xlbook.Worksheets.Add("EEPLUS测试表")
            ''添加工作部属性
            With xlbook
                .Properties.Category = "类别"
                .Properties.Author = "作者"
                .Properties.Comments = "备注"
                .Properties.Company = "公司"
                .Properties.Keywords = "关键字"
                .Properties.Manager = "管理者"
                .Properties.Status = "状态"
                .Properties.Subject = "主题"
                .Properties.Title = "标题"
                .Properties.LastModifiedBy = "最后一次修改日期"
                .Properties.Application = "应用"
                ''''自己发现更多吧懒得写
            End With
            ''----------
            ''保存Excel文件
            XlPkg.Save()
        End Using

• 附加:插入一段测试数据

 ''指定非商业证书
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial
        ''创建一个Excel文件
        Using XlPkg As New ExcelPackage With {
            .File = New IO.FileInfo("F:\Rambo\桌面\EPPlus.xlsx")
            ''----------
            ''在这里写入Excel操作
            ''----------
            ''定义一个工作簿
            Dim xlbook As ExcelWorkbook = XlPkg.Workbook
            ''添加一个工作表
            Dim xlsht As ExcelWorksheet = xlbook.Worksheets.Add("EEPLUS测试表")
            ''添加工作部属性
            With xlbook
                .Properties.Category = "类别"
                .Properties.Author = "作者"
                .Properties.Comments = "备注"
                .Properties.Company = "公司"
                .Properties.Keywords = "关键字"
                .Properties.Manager = "管理者"
                .Properties.Status = "状态"
                .Properties.Subject = "主题"
                .Properties.Title = "标题"
                .Properties.LastModifiedBy = "最后一次修改日期"
                .Properties.Application = "应用"
                ''''自己发现更多吧懒得写
            End With
            ''----------
            ''插入测试数据
            With xlsht
                '.Cells(1, 1).Value = "我是帅哥001"
                '.Cells(1, 2).Value = "我是帅哥002"
                '.Cells(2, 1).Value = "我是帅哥003"
                '.Cells(2, 2).Value = "我是帅哥004"
                '.Cells(3, 1).Value = "我是帅哥005"
                '.Cells(3, 2).Value = "我是帅哥006"
                ''------
                .Cells("A1").Value = "我是帅哥001"
                .Cells("B1").Value = "我是帅哥002"
                .Cells("A2").Value = "我是帅哥003"
                .Cells("B2").Value = "我是帅哥004"
                .Cells("A3").Value = "我是帅哥005"
                .Cells("B3").Value = "我是帅哥006"
            End With
            ''----------
            ''保存Excel文件
            XlPkg.Save()
        End Using

2.2 单元格样式配置

• 2.2.1 公式计算(下面演示两条求和公式)

xlsht.Cells("C1:D1").Formula = "A1*B1" ''这是乘法的公式，意思是A1单元格的值乘B1的值赋值给C1,然后B1的值乘C1的值后赋值给D1
xlsht.Cells("A3").Formula = $"SUM({New ExcelAddress(1, 1, 2, 2).Address})" ''这是自动求和的方法,求和A1:B2区域

• 2.2.2 设置单元格格式(下面演示两个格式,更多请查阅官方 API)

''设置单元格格式
xlsht.Cells(1, 1).Style.Numberformat.Format = "#,##0.00" ''A1单元格保留两位小数
xlsht.Cells(2, 3).Style.Numberformat.Format = "yyyy-MM-dd HHmmss" ''改变C2日期格式

• 2.2.3 设置单元格对齐方式

xlsht.Cells("A1:D4").Style.HorizontalAlignment = ExcelHorizontalAlignment.Center ''水平居中
xlsht.Cells("A1:D4").Style.VerticalAlignment = ExcelVerticalAlignment.Center ''垂直居中
xlsht.Cells("E1:F4").Merge = True ''合并单元格
xlsht.Cells.Style.WrapText = True ''所有单元格自动换行

• 2.2.4 设置单元格字体样式

xlsht.Cells("A1:D4").Style.Font.Bold = True '字体为粗体
xlsht.Cells("A1:D4").Style.Font.Color.SetColor(Color.Blue) '字体颜色
xlsht.Cells("A1:D4").Style.Font.Name = "微软雅黑" '字体
xlsht.Cells("A1:D4").Style.Font.Size = 12

• 2.2.5 设置单元格背景样式

xlsht.Cells("A1:D4").Style.Fill.PatternType = ExcelFillStyle.Solid
xlsht.Cells("A1:D4").Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128, 128, 128))

• 2.2.6 设置单元格边框

xlsht.Cells("A1:D4").Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Red) ''设置单元格范围内外边框样式
''修改范围内所有单元格边框样式(上下左右)
xlsht.Cells("A1:D4").Style.Border.Bottom.Style = ExcelBorderStyle.Thin
xlsht.Cells("A1:D4").Style.Border.Bottom.Color.SetColor(Color.FromArgb(191, 191, 191))

• 2.2.7 设置单元格的行高和列宽

xlsht.Cells.Style.ShrinkToFit = True '单元格缩小填充
xlsht.Row(1).Height = 15 '设置行高
xlsht.Rows(1, 15).Height = 15 ''同时设置多行
xlsht.Row(1).CustomHeight = True '自动调整行高
xlsht.Column(1).Width = 3
xlsht.Columns(1, 25).Width = 3 ''同时设置多行

• 2.2.8 设置 sheet 背景,网格线

xlsht.View.ShowGridLines = False '去掉sheet的网格线
xlsht.Cells.Style.Fill.PatternType = ExcelFillStyle.Solid
xlsht.Cells.Style.Fill.BackgroundColor.SetColor(Color.LightGray)  '设置背景色
xlsht.BackgroundImage.Image = Image.FromFile("testbg.jpg")

• 2.2.9 插入图片和形状

'插入图片
Dim pic As ExcelPicture = xlsht.Drawings.AddPicture("logo", Image.FromFile("testbg.jpg"))
pic.SetPosition(100, 100) '设置图片的位置
pic.SetSize(100, 100)

''插入形状
Dim shp As ExcelShape = xlsht.Drawings.AddShape("shape", eShapeStyle.Rect)
With shp
    .Font.Fill.Color = Color.Red '设置形状的字体颜色
    .Font.Size = 15 '字体大小
    .Font.Bold = True '字体粗细
    .Fill.Style = eFillStyle.NoFill '设置形状的填充样式
    .Border.Fill.Style = eFillStyle.NoFill '边框样式
    .SetPosition(200, 300) '形状的位置
    .SetSize(80, 30) '形状的大小
    .Text = "test"
End With

• 2.2.10 超链接

''给图片加超链接
Dim pic As ExcelPicture = xlsht.Drawings.AddPicture("logo", Image.FromFile("test.jpg"), New ExcelHyperLink("http:\www.baidu.com", UriKind.Relative))

xlsht.Cells("A1").Hyperlink = New ExcelHyperLink("http:\www.baidu.com", UriKind.Relative)

• 2.2.11 隐藏 sheet

xlsht.Hidden = eWorkSheetHidden.Hidden '隐藏sheet
xlsht.Column(1).Hidden = True '隐藏某一列
xlsht.Row(1).Hidden = True '隐藏某一行

2.3 图表

• 2.3.1 创建图表

 Dim chart As ExcelChart = xlsht.Drawings.AddChart("chart", eChartType.ColumnClustered)

• 2.3.2 选择图表数据

Dim chart As ExcelChart = xlsht.Drawings.AddChart("chart", eChartType.ColumnClustered)
Dim serie As ExcelChartSerie = chart.Series.Add(xlsht.Cells("A1:A2"), xlsht.Cells("B1:B2"))  '设置图表的x轴和y轴
serie.HeaderAddress = xlsht.Cells(1, 3)

• 2.3.3 设置图表样式

chart.SetPosition(150, 10) '设置位置
chart.SetSize(500, 300) '设置大小
chart.Title.Text = "TEST" '设置图表的标题
chart.Title.Font.Fill.Color = Color.FromArgb(89, 89, 89) '设置标题的颜色
chart.Title.Font.Size = 15  '标题的大小
chart.Title.Font.Bold = True '标题的粗体
chart.Style = eChartStyle.Style15 '设置图表的样式
chart.Legend.Border.LineStyle = eLineStyle.Solid
chart.Legend.Border.Fill.Color = Color.FromArgb(217, 217, 217)

2.4 嵌入 VBA 代码

''注意文件格式要改成带宏.xlsm文件
XlPkg.Workbook.CreateVBAProject()
Dim vbamod As VBA.ExcelVBAModule = xlbook.VbaProject.Modules.AddModule("VBXYM")
vbamod.Code = <vbacode>
                sub test()
                    msgbox "VB小源码"
                end sub
             </vbacode>

2.5 Excel 加密和锁定

xlsht.Protection.IsProtected = True '设置是否进行锁定
 xlsht.Protection.SetPassword("123") '设置密码
xlsht.Protection.AllowAutoFilter = False
'下面是一些锁定时权限的设置
xlsht.Protection.AllowDeleteColumns = False
xlsht.Protection.AllowDeleteRows = False
xlsht.Protection.AllowEditScenarios = False
xlsht.Protection.AllowEditObject = False
xlsht.Protection.AllowFormatCells = False
xlsht.Protection.AllowFormatColumns = False
xlsht.Protection.AllowFormatRows = False
xlsht.Protection.AllowInsertColumns = False
xlsht.Protection.AllowInsertHyperlinks = False
xlsht.Protection.AllowInsertRows = False
xlsht.Protection.AllowPivotTables = False
xlsht.Protection.AllowSelectLockedCells = False
xlsht.Protection.AllowSelectUnlockedCells = False
xlsht.Protection.AllowSort = False

2.6 数据验证

Dim v As Object = xlsht.DataValidations.AddListValidation(xlsht.Cells("AA1:AA2").Address) '设置下拉框显示的数据区域
v.Formula.ExcelFormula = "=parameter" '数据区域的名称
v.Prompt = "下拉选择参数" '下拉提示