文章目录
一、NPOI概述
NPOI是用于读写Excel和Word的插件包。
它是Apache POI的.NET版。
总之,Apache POI是一个Java的强大的、开源的Office文档处理包,而NPOI是它的.NET版本。所以在.NET平台下用NPOI来读写Office文档应该是优先级比较高的,一是稳定,二是强大,三是背后支持力量庞大。
在网上介绍时有一点非常突出,
使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。
我觉得很多人在.NET平台读写Office文档时,应该都会优先考虑使用Office自带的库(Microsoft.Office.Interop.Excel之类),但是这类库兼容性极差,而且由于我电脑上又装了WPS,与Office会冲突,这类的库的使用就更难了(虽然最后通过全部卸载,重装Office勉强解决)。说了这么多,就想表达,它不需要Office库的这点很重要,一是冲突问题,二是你总不能要求客户机都装了兼容的Office吧。
注意:
本文只介绍Excel的基本使用。且例子均使用.xlsx格式的excel,因为现在已经2022年了,应该主流是.xlsx了,而且两者使用相差不大。
二、使用过程
1. 获取安装
NuGet下搜索NPOI,找到合适的版本安装即可。
安装后,多了四个库,每个库的用处从命名中可以看出一二,
2. 基本概念
在正式用之前,需要知道里面常用类的含义和一些基本概念。
-
文件类型
- 1️⃣HSSF,提供读写MicroSoft Excel XLS格式文件的功能(.xls结尾的excel,是excel2003及以前的版本生成的文件格式)。
- 2️⃣XSSF,提供读写MicroSoft Excel OOXML XLSX格式文件的功能(.xlsx结尾的excel,是excel2007及以后版本生成的文件格式,向下兼容xls)。 常用类
-
1️⃣Workbook,工作簿,相当于整个Excel文件。
为啥叫工作簿?
我觉得可以把一个Excel文件理解为现实中记账本的抽象,
在有电脑办公软件之前,人们各种账都记在一本本本子上,本子的内容往往是各种表(后面的Sheet概念)。所以我新建一个工作簿文件(.xls或.xlsx),就相当于现实中拿到一本记账本。就像下图一样,
-
2️⃣Sheet,工作表,相当于Excel中的一个sheet,
你打开一个Excel文件,呈现在眼前的密密麻麻的方格子页面就是一个Sheet,如下图,在下方你可以切换Sheet,
-
3️⃣Row,工作表的行,
没啥好说的,就是一行。
-
4️⃣Cell,行的单元格,
就是行中的一个小方格子,
-
5️⃣CellStyle,单元格样式,
就是每个格子的边框,填充,居中那些。
3. 基本操作
3.1. 创建一个excel文件
using NPOI.XSSF.UserModel;
...
// 新建工作簿对象
XSSFWorkbook workBook = new XSSFWorkbook();
// 写入文件
workBook.Write(new FileStream(@"test.xlsx", FileMode.Create));
之后,你在相应的目录下就能看到test.xlsx文件了,但此时若用excel打开,会报错“文件可能损坏”;原因是你的文件中只有一个工作簿,没有为工作簿创建工作表。若你用“桌面右键>新建MicroSoft Excel工作表”的方式建一个.xlsx的方式,则可以正常打开。两者区别在于桌面右键创建,会往里面加表。解决方法很简单,你只需要在创建工作簿之后,为工作簿创建一个工作表:
XSSFSheet newSheet = (XSSFSheet)workBook.CreateSheet("mySheet");
回到上节工作簿的例子,工作表相当于本子中的内容,如果一本本子只有一张皮,而没有内容,那又有什么意义呢,对吧?
3.2. 往单元格写值
一个excel文件创建好之后,那心急的人肯定就想往里面写东西了。代码如下:
XSSFWorkbook workBook = new XSSFWorkbook();
ISheet sheet = workBook.CreateSheet("mySheet");
// 修改单元格的值
sheet.GetRow(0).GetCell(0).SetCellValue("一个值");
workBook.Write(new FileStream(@"D:/aa.xlsx", FileMode.OpenOrCreate, FileAccess.ReadWrite));
程序运行起来同样会报错,因为你还没有创建单元格,就往单元格中写东西了。把上面修改单元格值的语句换成下面即可,
sheet.CreateRow(0).CreateCell(0).SetCellValue("一个值");
通常来讲,你需要先创建工作簿,再创建工作表,再为工作表创建行,然后再为指定行创建单元格,再去修改单元格的值。
犯上面两个错误,主要是平时可视化编辑excel文件习惯了,右键创建好excel文件,在excel文件里直接修改单元格的值,一切都是那么自然。其实在可视化操作时,Office工具为我们做了很多事情了。
3.3. 文件保存
上面两个代码示例中,已经出现了保存的影子,工作簿调用Write方法,写入文件流即可:
workBook.Write(new FileStream(...));
⭐3.4. 一般用法
这边再介绍下,.NET中读写Excel的一般写法,
首先是读Excel,读文件往往没有什么问题,
using(FileStream fs = new FileStream(@"D:/a3.xlsx", FileMode.Open, FileAccess.Read))
{
XSSFWorkbook workBook = new XSSFWorkbook(fs);
ISheet sheet = workBook.GetSheetAt(0);
for(int r = 0; r < 10; r++)
{
for(int c = 0; c < 10; c++)
{
// 读取单元格内容(前提是单元格存在)
sheet.GetRow(r).GetCell(c);
}
}
}
然后是修改已有Excel,
IWorkbook workBook = null;
using(FileStream fs = new FileStream(@"D:/a3.xlsx", FileMode.Open, FileAccess.Read))
{
workBook = new XSSFWorkbook(fs);
ISheet sheet = workBook.GetSheetAt(0);
sheet.GetRow(3).GetCell(3).SetCellValue(33);
sheet.GetRow(4).GetCell(3).SetCellValue(22);
sheet.GetRow(5).GetCell(3).SetCellValue(22);
sheet.GetRow(6).GetCell(3).SetCellValue(33);
}
using(FileStream fs = new FileStream(@"D:/a3.xlsx", FileMode.Create, FileAccess.Write))
{
workBook.Write(fs);
}
修改内容的示例中,在第二次打开文件流,FileMode枚举使用的是Create而不是Open,关于这点,网上的说法是,Open会在文件末尾写入内容,Create则是覆盖内容(在文件已存在的情况下),所以使用Open时,会在已存在的xlsx文件末尾写入workbook内容导致文件损坏,
详情看原文链接
而对FileMode.Open与FileMode.Create具体的底层区别,官方文档并没有明确说明。
当然,这种说法我不是很赞同。
FileStream类中有两个属性Length和Position,含义是流的长度与流中的位置,
如果用FileMode.Open和FileMode.Create打开同一个文件后,观察这两个属性的值,你会发现,
FileMode.Open下,Length的值即文件原本的长度,Position是0;而Create,Length与Position都是0。
官方文档中有以下说明,Create模式下,若文件已存在,则会截断文件,
啥叫截断,从描述来看,就是文件大小视为0(估计底层就是偏移一下文件结束指针的操作)。
于是我又做了一个实验,观察两种模式写入之后,Excel文件的大小发生的变化,结果如下,
首先是原文件的长度:
然后是,Create模式下的长度:
最后是Open模式下的长度:
结论呼之欲出(有兴趣想深入探究的可以看.NET源码看底层实现),Open模式下,原文件的内容保留了,而写入流从头开始写入,覆盖了前面一部分,导致文件内容错乱,后面部分解析出问题。(事后我用文本内容对比工具对比了,两种模式下文件开头部分内容相同,但Open模式残留了原文件的末尾部分)
所以在使用上,写入Excel时,得使用FileMode.Create模式。
3.5. 常用操作汇总
FileStream fs = new FileStream(filepath, FileMode.Open, FileAccess.ReadWrite);
// 1. 获取工作簿对象
IWorkbook workbook = new XSSFWorkbook(fs); // 2007
// IWorkbook workbook = new HSSFWorkbook(fs); // 2003
// 2. 获取工作表对象(第一个表,序号从0开始)
ISheet sheet = workbook.GetSheetAt(0);
// 3. 获取工作表的行(第一行)
IRow row = sheet.GetRow(0);
// 4. 获取指定行的单元格
ICell cell = row.GetCell(0);
// 5. 获取单元格样式
ICellStyle cellStyle = cell.CellStyle;
// 6. 创建工作簿对象
XSSFWorkbook workBook= new XSSFWorkbook();
// 7. 创建工作表对象
XSSFSheet newSheet = (XSSFSheet)workBook.CreateSheet("new sheet");
// 8. 创建工作表的行
XSSFRow newRow = (XSSFRow)newSheet.CreateRow(0);
// 9. 创建单元格
XSSFCell newCell = (XSSFCell)newRow.CreateCell(0);
// 10. 单元格写值
newCell.SetCellValue(1);
// 11. 设置Sheet名称
workBook.SetSheetName(0, "第一张表");
// 12. 设置单元格内容
newCell.SetCellValue(11);
// 13. 得到工作簿中Sheet数量
workBook.NumberOfSheets
// 14. 保存excel文件
workBook.Write(new FileStream(@"pathName", FileMode.Create, FileAccess.ReadWrite));
三、使用注意项
- 表更新问题,报表的时候经常会有这样的用法,Excel模板已经给你做好了,你只需要往里面指定单元格写数据,然后模板根据数据变化自动改变。但是你按上述方式写入时,会发现数据变了,公式单元格并没自动改变。你需要在改变完后,加上下面这句代码。
sheet.ForceFormulaRecalculation = true;