1. 关于NPOI
NPOI是一个开源的用于在C#程序中读写Excel、WORD等微软OLE2组件文档的项目,使用NPOI可以在没有安装Office的情况下对Word或Excel文档进行读写操作。
2. 使用NPOI的好处
- C#程序中操作Excel的另外一种方法是使用.NET自带的Excel API,但是这种方法需要运行环境安装微软的Excel,而使用NPOI可以在没有安装Microsoft Office Excel的设备上对Excel进行操作。
- 因为服务器一般不安装office这么庞大的办公软件,所以NPOI适合在服务器端生成数据文件。
- 几乎支持所有的Office97~Office2007的文件格式。
3. NPOI使用方法
NPOI里面的管理层次为:workbook->worksheet->row->cell,类比关系型数据库就是:
NPOI |
说明 |
关系型数据库 |
workbook |
Excel文件 |
database |
worksheet |
工作表 |
table |
row |
行 |
record记录 |
cell |
单元格 |
field字段 |
3.1 获取DLL
方式1:用Nuget命令行安装NPOI引用
Install-Package NPOI -Version 2.4.0
方式2:直接在VS的Nuget包管理器中搜索“NPOI”获取
3.2 将NPOI的DLL导入工程
using NPOI; //基础辅助库 using NPOI.SS.UserModel; using NPOI.HSSF.UserModel; using NPOI.XSSF.UserModel;
其中,NPOI 使用 HSSFWorkbook 类来处理 xls,XSSFWorkbook 类来处理 xlsx,它们都继承接口 IWorkbook,因此可以通过 IWorkbook 来统一处理 xls 和 xlsx 格式的文件。(本文以XLSX为例)
3.3 创建和读写Excel
(1) 创建Excel文件
XSSFWorkbook workbook2007 = new XSSFWorkbook(); //创建xlsx工作簿 HSSFWorkbook workbook2003 = new HSSFWorkbook(); //新建xls工作簿
Excel规定一个Workbook必须至少带1个Sheet,所以创建Excel之后必须同时创建Sheet的代码才能保证生成的文件正常。
ISheet sheet = workbook2007.CreateSheet("sheet1"); //创建一个Sheet
创建行和列的方式:
IRow row = sheet.CreateRow(0); //创建一行 ICell cell = row.CreateCell(0); //创建一列
创建XSSFWorkbook实例之后,要将其写入文件:
FileStreamfile =new FileStream(@"NpoiTest.xlsx", FileMode.Create); workbook2007.Write(file); file.Close(); workbook2007.Close();
之后会在指定目录下生成Excel文件。
(2) 向Excel文件写入数据
写数据要遵循一定的顺序,可以概括为:读取(或新建一个工作簿)->获取工作表->对工作表添加行->对每一行添加单元格->对单元格赋值。
XSSFWorkbook workbook2007 = new XSSFWorkbook(); //创建xlsx工作簿 workbook2007.CreateSheet("Sheet1"); //新建1个Sheet工作表 //ISheet sheet = workbook2007.CreateSheet("Sheet1"); HSSFSheet SheetOne = (HSSFSheet)workbook2007.GetSheet("Sheet1"); //获取名称为Sheet1的工作表 //对工作表先添加行,下标从0开始 for (int i = 0; i < 2; i++) { SheetOne.CreateRow(i); //为SheetOne添加2行 //IRow row = SheetOne.CreateRow(i); } //对每一行创建3个单元格 HSSFRow SheetRow = (HSSFRow)SheetOne.GetRow(0); //获取Sheet1工作表的首行 HSSFCell[] SheetCell = new HSSFCell[3]; for (int i = 0; i < 3; i++) { SheetCell[i] = (HSSFCell)SheetRow.CreateCell(i); //为第一行创建3个单元格 //ICell headcell = SheetRow.CreateCell(i); } //创建之后就可以赋值了 SheetCell[0].SetCellValue("CellValue"); SheetCell[1].SetCellValue("CellValue2"); SheetCell[2].SetCellValue("CellValue3"); FileStream file2007 = new FileStream(@"E:\Excel2007.xls", FileMode.Create); workbook2007.Write(file2007); file2007.Close(); workbook2007.Close(); //转化为字节数组 //MemoryStream ms = new MemoryStream(); //workbook.Write(ms); //ms.Flush(); //ms.Position = 0; //return ms;
(3) 读取Excel文件数据
workbook的写入需要借助于FileStream来打开一个文件流,在创建FileStream的时候,可以传入数据的保存路径和文件名。
HSSFWorkbook类和XSSFWorkbook类都继承自IWorkbook类,所以在不知道所要读取的Excel文件时xls还是xlsx时,可以使用IWorkbook来声明一个通用的工作簿变量,随后根据传入的文件名判断是xls还是xlsx。
IWorkbook workbook = null; //新建IWorkbook对象 string fileName = "E:\\Excel2007.xls"; FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read); if (fileName.IndexOf(".xlsx") > 0) // 2007版本 { workbook = new XSSFWorkbook(fileStream); //xlsx数据读入workbook } else if (fileName.IndexOf(".xls") > 0) // 2003版本 { workbook = new HSSFWorkbook(fileStream); //xls数据读入workbook } ISheet sheet = workbook.GetSheetAt(0); //获取第一个工作表 IRow row;// = sheet.GetRow(0); //新建当前工作表行数据 for (int i = 0; i < sheet.LastRowNum; i++) //对工作表每一行 { row = sheet.GetRow(i); //row读入第i行数据 if (row != null) { for (int j = 0; j < row.LastCellNum; j++) //对工作表每一列 { string cellValue = row.GetCell(j).ToString(); //获取i行j列数据 Console.WriteLine(cellValue); } } } Console.ReadLine(); fileStream.Close(); workbook.Close();
3.4 Excel样式设置
(1) 设置字体
ICellStyle style = workbook.CreateCellStyle();//创建样式对象 IFont font = workbook.CreateFont(); //创建一个字体样式对象 font.FontName = "方正舒体"; //和excel里面的字体对应 font.Color = new HSSFColor.PIN.Index();//颜色参考NPOI的颜色对照表 (对照表见参考网址(样式设置相关)) font.IsItalic = true; //斜体(下划线?) font.FontHeightInPoints = 16;//字体大小 font.Boldweight = short.MaxValue;//字体加粗 style.SetFont(font); //将字体样式赋给样式对象 cell.CellStyle = style; //把样式赋给单元格
(2) 设置单元格前景色
ICellStyle style=workbook.CreateCellStyle(); style.FillForegroundColor = 14; //具体数字代表的颜色看NPOI颜色对照表 style.FillPattern = FillPatternType.SOLID_FOREGROUND; cellStyle.FillForegroundColor = HSSFColor.Blue.Index;//选中后文字背景 cellStyle.FillBackgroundColor = HSSFColor.Red.Index;//选中后单元格背景 cellStyle.FillPattern = FillPattern.AltBars;
(3) 设置单元格宽高
row.Height = 30 * 20; //行高,行高为30 sheet.SetColumnWidth(3, 13 * 256) //列宽,第4列的列宽为13
(4) 合并单元格
单元格合并后,样式以左上角的单元格为准。
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10)); //CellRangeAddress四个参数为:起始行,结束行,起始列,结束列
(5) 单元格水平居中和对齐
style.Alignment = HorizontalAlignment.CENTER; style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;//水平对齐 style.VerticalAlignment = VerticalAlignment.Center;//垂直对齐
(6) 设置公式
cell.CellFormula = "公式"; //公式中不需要写“=”号
(7) 设置边框
分别设置上下左右边框样式
style.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; style.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; style.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; style.BorderRight = NPOI.SS.UserModel.BorderStyle.THICK;
分别设置上下左右边框颜色
style.TopBorderColor = HSSFColor.OliveGreen.Blue.Index; style.BottomBorderColor = HSSFColor.OliveGreen.Blue.Index; style.LeftBorderColor = HSSFColor.OliveGreen.Blue.Index; style.RightBorderColor = HSSFColor.OliveGreen.Blue.Index;
(8) 其它
cellStyle.WrapText = true; //自动换行 cellStyle.Indention = 0;//缩进
3.5 用类CSS的方法设置单元格样式
NPOI.CSS是一个可以在使用NPOI时用类CSS的方法设置单元格样式的NPOI扩展,支持.NET4及以上版本的项目。
(1) 引用NPOI.CSS.dll
using NPOI.CSS;
(2) 设置单元格样式
cell.CSS("color:red;font-weight:bold;font-size:11;font-name:宋体;border-type:thin;");
具体样式参数见参考网址(参考文档3)。
(网上好像很难找到NPOI.CSS.dll的资源,需要的朋友可以直接在Github(参考文档3)找到源码,然后编译生成。)
参考文档:
1.文档读写相关:
https://blog.csdn.net/dcrmg/article/details/52356236
2. 样式设置相关:
https://www.cnblogs.com/nxxshxf/p/6410797.html
https://blog.csdn.net/qq_35957944/article/details/85163316
3. NPOI.CSS属性:
https://github.com/qihangnet/NPOI.CSS
原文地址:https://www.cnblogs.com/imdeveloper/p/10963364.html
转载请注明出处,谢谢!