附1EPPlus使用教程.docx

1、附1EPPlus使用教程EPPlus使用教程1基本介绍EPPlus是一个使用Open Office XML(xlsx)文件格式，能读写Excel 2007/2010 文件的开源组件，在导出Excel的时候不需要电脑上安装office，官网为： 。需要使用的Epplus的DLL文件，从官网上下载下来即可。基本上Excel上的各种功能（例如图表、VBA、数据透视表、加密、数据验证等）Epplus都能实现，它的一个缺点就是不支持导出2003版的Excel。网址：版本：4.0.4最后日期：2015-4-24创建Excel首先将epplus的dll文件添加到项目里，然后添加引用即可。创建Excel，所有

2、代码均放在这个using语句里面，在using语句里面我们可以创建多个worksheet， ExcelPackage后面可以传入路径参数：using (ExcelPackage package=new ExcelPackage(new FileInfo(d:test.xlsx) 创建worksheet：using (ExcelPackage package=new ExcelPackage(new FileInfo(d:test.xlsx) ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(test);/创建worksheet

3、保存Excel：using (ExcelPackage package = new ExcelPackage(new FileInfo(d:test.xlsx) ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(test);/创建worksheet package.Save();/保存excel 至此，一个基本的excel已经完工，下面就是填入数据了。添加数据Epplus中给单元格赋值非常简单，两种方法：( 注:Epplus的所有行列数都是以1开始的 )worksheet.Cells1, 1.Value = 名称;/直接指定行

4、列数进行赋值worksheet.CellsA1.Value = 名称;/直接指定单元格进行赋值下面是一个完整的输出一个简单的excel的代码：FileInfo newFile = new FileInfo(d:test.xlsx);if (newFile.Exists) newFile.Delete(); newFile = new FileInfo(d:test.xlsx);using (ExcelPackage package = new ExcelPackage(newFile) ExcelWorksheet worksheet = package.Workbook.Worksheets

5、.Add(test); worksheet.Cells1, 1.Value = 名称; worksheet.Cells1, 2.Value = 价格; worksheet.Cells1, 3.Value = 销量; worksheet.Cells2, 1.Value = 大米; worksheet.Cells2, 2.Value = 56; worksheet.Cells2, 3.Value = 100; worksheet.Cells3, 1.Value = 玉米; worksheet.Cells3, 2.Value = 45; worksheet.Cells3, 3.Value = 150

6、; worksheet.Cells4, 1.Value = 小米; worksheet.Cells4, 2.Value = 38; worksheet.Cells4, 3.Value = 130; worksheet.Cells5, 1.Value = 糯米; worksheet.Cells5, 2.Value = 22; worksheet.Cells5, 3.Value = 200; package.Save();2样式设置公式计算excel中离不开各种各样的公式计算，在Epplus中运用公式有两种方式，你都可以尝试一下：worksheet.CellsD2:D5.Formula = B2*

7、C2;/这是乘法的公式，意思是第二列乘以第三列的值赋值给第四列，这种方法比较简单明了worksheet.Cells6, 2, 6, 4.Formula = string.Format(SUBTOTAL(9,0), new ExcelAddress(2, 2, 5, 2).Address);/这是自动求和的方法，至于subtotal的用法你需要自己去了解了至于别的公式大家可以自己尝试一下。设置单元格格式worksheet.Cells5, 3.Style.Numberformat.Format = #,#0.00;/这是保留两位小数单元格的格式设置还有很多，我就不一一列出来了，基本上excel上能

8、实现的Epplus都能实现，大家可以去Epplus的源码上看。设置字体和单元格样式设置单元格对齐方式 worksheet.Cells1, 1.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;/水平居中worksheet.Cells1, 1.Style.VerticalAlignment = ExcelVerticalAlignment.Center;/垂直居中worksheet.Cells1, 4, 1, 5.Merge = true;/合并单元格worksheet.Cells.Style.WrapText = true;

9、/自动换行设置单元格字体样式worksheet.Cells1, 1.Style.Font.Bold = true;/字体为粗体worksheet.Cells1, 1.Style.Font.Color.SetColor(Color.White);/字体颜色worksheet.Cells1, 1.Style.Font.Name = 微软雅黑;/字体worksheet.Cells1, 1.Style.Font.Size = 12;/字体大小设置单元格背景样式worksheet.Cells1, 1.Style.Fill.PatternType = ExcelFillStyle.Solid;worksh

10、eet.Cells1, 1.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128,128, 128);/设置单元格背景色设置单元格边框，两种方法worksheet.Cells1,1.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191);/设置单元格所有边框worksheet.Cells1, 1.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;/单独设置单元格底部边框样式和颜色（上下左右

11、均可分开设置）worksheet.Cells1, 1.Style.Border.Bottom.Color.SetColor(Color.FromArgb(191, 191, 191);设置单元格的行高和列宽worksheet.Cells.Style.ShrinkToFit = true;/单元格自动适应大小worksheet.Row(1).Height = 15;/设置行高worksheet.Row(1).CustomHeight = true;/自动调整行高worksheet.Column(1).Width = 15;/设置列宽设置sheet背景worksheet.View.ShowGrid

12、Lines = false;/去掉sheet的网格线worksheet.Cells.Style.Fill.PatternType = ExcelFillStyle.Solid;worksheet.Cells.Style.Fill.BackgroundColor.SetColor(Color.LightGray);/设置背景色worksheet.BackgroundImage.Image = Image.FromFile(firstbg.jpg);/设置背景图片插入图片和形状插入图片ExcelPicture picture=worksheet.Drawings.AddPicture(logo,I

13、mage.FromFile(firstbg.jpg);/插入图片picture.SetPosition(100, 100);/设置图片的位置picture.SetSize(100, 100);/设置图片的大小插入形状ExcelShape shape = worksheet.Drawings.AddShape(shape, eShapeStyle.Rect);/插入形状shape.Font.Color = Color.Red;/设置形状的字体颜色shape.Font.Size = 15;/字体大小shape.Font.Bold = true;/字体粗细shape.Fill.Style = eFi

14、llStyle.NoFill;/设置形状的填充样式shape.Border.Fill.Style = eFillStyle.NoFill;/边框样式shape.SetPosition(200, 300);/形状的位置shape.SetSize(80, 30);/形状的大小shape.Text = test;/形状的内容Epplus里面内置了很多形状，大家可以自己试一试。超链接给图片加超链接ExcelPicture picture = worksheet.Drawings.AddPicture( logo, Image.FromFile(firstbg.jpg), new ExcelHyperL

15、ink(http:, UriKind.Relative);给单元格加超链接worksheet.Cells1,1.Hyperlink = new ExcelHyperLink( http:, UriKind.Relative);隐藏sheetworksheet.Hidden = eWorkSheetHidden.Hidden;/隐藏sheetworksheet.Column(1).Hidden = true;/隐藏某一列worksheet.Row(1).Hidden = true;/隐藏某一行3图表设置嵌入VBA代码首先将vba代码保存成txt文本格式，然后用epplus去调用这个txt文本文件

16、即可，非常简单，当然要想vba调用成功，前提是你的vba代码是没问题的喽！worksheet.CodeModule.Name = sheet ;worksheet.CodeModule.Code = File.ReadAllText(VBA-Codevba.txt), Encoding.Default);Excel加密和锁定对于一些不希望别人随便改的excel可以对其进行锁定和加密，这样别人只能看不能改了，除非有密码。worksheet.Protection.IsProtected = true;/设置是否进行锁定worksheet.Protection.SetPassword(yk);/设置

17、密码worksheet.Protection.AllowAutoFilter = false;/下面是一些锁定时权限的设置worksheet.Protection.AllowDeleteColumns = false;worksheet.Protection.AllowDeleteRows = false;worksheet.Protection.AllowEditScenarios = false;worksheet.Protection.AllowEditObject = false;worksheet.Protection.AllowFormatCells = false;workshe

18、et.Protection.AllowFormatColumns = false;worksheet.Protection.AllowFormatRows = false;worksheet.Protection.AllowInsertColumns = false;worksheet.Protection.AllowInsertHyperlinks = false;worksheet.Protection.AllowInsertRows = false;worksheet.Protection.AllowPivotTables = false;worksheet.Protection.All

19、owSelectLockedCells = false;worksheet.Protection.AllowSelectUnlockedCells = false;worksheet.Protection.AllowSort = false;属性设置针对整个Excel本身的一些其他设置epk.Workbook.Properties.Title = inventory;/设置excel的标题epk.Workbook.Properties.Author = mei;/作者epk.Workbook.Properties.Comments = this is a test;/备注epk.Workboo

20、k.Properties.Company = ABC;/公司下拉框设置下拉框时首先需要设置下拉框显示的数据区域块并将其命名。var val = worksheet.DataValidations.AddListValidation(worksheet.Cells7, 8.Address);/设置下拉框显示的数据区域val.Formula.ExcelFormula = =parameter;/数据区域的名称val.Prompt = 下拉选择参数;/下拉提示val.ShowInputMessage = true;/显示提示内容4图表设置Epplus的图表实现是很简单的，它支持的图表类型也很多，基本

21、上能满足我们的需求。创建图表分为三步（以柱状图举例）：创建图表ExcelChart chart = worksheet.Drawings.AddChart(chart, eChartType.ColumnClustered);/eChartType中可以选择图表类型选择数据这一步是很关键的一步，chart.Series.Add()方法所需参数为：chart.Series.Add(Y轴数据区,X轴数据区)ExcelChartSerie serie = chart.Series.Add(worksheet.Cells2, 3, 5, 3, worksheet.Cells2, 1, 5, 1);/设

22、置图表的x轴和y轴serie.HeaderAddress = worksheet.Cells1, 3;/设置图表的图例设置图表样式 chart.SetPosition(150, 10);/设置位置chart.SetSize(500, 300);/设置大小chart.Title.Text = 销量走势;/设置图表的标题chart.Title.Font.Color = Color.FromArgb(89, 89, 89);/设置标题的颜色chart.Title.Font.Size = 15;/标题的大小chart.Title.Font.Bold = true;/标题的粗体chart.Style =

23、 eChartStyle.Style15;/设置图表的样式chart.Legend.Border.LineStyle = eLineStyle.Solid;chart.Legend.Border.Fill.Color = Color.FromArgb(217, 217, 217);/设置图例的样式基本上生成图表就这么些东西了，不过不同的图表属性可能略有差异，得根据具体图表具体分析。下面是例子的全部代码：FileInfo newFile = new FileInfo(d:test.xlsx);if (newFile.Exists) newFile.Delete(); newFile = new

24、FileInfo(d:test.xlsx);using (ExcelPackage package = new ExcelPackage(newFile) ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(test); worksheet.Cells.Style.WrapText = true; worksheet.View.ShowGridLines = false;/去掉sheet的网格线 worksheet.Cells1, 1.Value = 名称; worksheet.Cells1, 2.Value = 价格; wor

25、ksheet.Cells1, 3.Value = 销量; worksheet.Cells2, 1.Value = 大米; worksheet.Cells2, 2.Value = 56; worksheet.Cells2, 3.Value = 100; worksheet.Cells3, 1.Value = 玉米; worksheet.Cells3, 2.Value = 45; worksheet.Cells3, 3.Value = 150; worksheet.Cells4, 1.Value = 小米; worksheet.Cells4, 2.Value = 38; worksheet.Cel

26、ls4, 3.Value = 130; worksheet.Cells5, 1.Value = 糯米; worksheet.Cells5, 2.Value = 22; worksheet.Cells5, 3.Value = 200; using (ExcelRange range = worksheet.Cells1, 1, 5, 3) range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;

27、using (ExcelRange range = worksheet.Cells1, 1, 1, 3) range.Style.Font.Bold = true; range.Style.Font.Color.SetColor(Color.White); range.Style.Font.Name = 微软雅黑; range.Style.Font.Size = 12; range.Style.Fill.PatternType = ExcelFillStyle.Solid; range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128

28、, 128, 128); worksheet.Cells1,1.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191); worksheet.Cells1,2.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191); worksheet.Cells1,3.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 1

29、91, 191); worksheet.Cells2,1.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191); worksheet.Cells2,2.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191); worksheet.Cells2,3.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191,

30、 191); worksheet.Cells3,1.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191); worksheet.Cells3,2.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191); worksheet.Cells3,3.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191); worksheet.Cells4,1.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191,