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 代码
代码语言：javascript复制''注意文件格式要改成带宏.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 加密和锁定
代码语言：javascript复制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 数据验证
代码语言：javascript复制Dim v As Object = xlsht.DataValidations.AddListValidation(xlsht.Cells("AA1:AA2").Address) '设置下拉框显示的数据区域
v.Formula.ExcelFormula = "=parameter" '数据区域的名称
v.Prompt = "下拉选择参数" '下拉提示
v.ShowInputMessage = True
进微信群,请添加上方微信号!!!
EPPLUS动态链接库DLL下载
代码语言：javascript复制链接：https://pan.baidu.com/s/1OeNdzJckbdhORYl4CBJoBQ
提取码：vbee
--来自百度网盘超级会员V5的分享
