C#

2023-07-06 21:37| 来源: 网络整理| 查看: 265

详细教程：

NPOI使用手册——c# - vv彭 - 博客园 (cnblogs.com)

1，NPOI读取Excel高低版本兼容性问题

报错1：NPOI.POIFS.FileSystem.OfficeXmlFileException HResult=0x80070057 Message=The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process

报错2：NPOI.POIFS.FileSystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)

原因：这是因为NPOI读取2003时使用的是HSSFWorkbook，更高版本读取需要使用XSSFWorkbook

解决方式：

将代码中HSSFWorkbook修改为XSSFWorkbook，最好是导入文件时选择版本，这样处理更好

IWorkbook workbook = new XSSFWorkbook(file);

2，NPOI读取Excel带计算公式的值 /// /// 根据填充后的综合判定表来综合判定是否OK（获取带公式列的值） /// /// 填充后的综合判定表打印版的文件路径 public static void Step4(FilePath filePath) { XSSFWorkbook workbook; XSSFFormulaEvaluator evalor = null; using (FileStream fs = File.Open(filePath.PrintMatchFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { //把xls文件读入workbook变量里，之后就可以关闭了 workbook = new XSSFWorkbook(fs); //【带公式取值步骤1】创建当前sheet 的动态公式计算器 evalor = new XSSFFormulaEvaluator(workbook); fs.Close(); } ISheet sheet = workbook.GetSheetAt(0); int rowCount = sheet.LastRowNum; int StandardTotalCount = 0; int OKTotalCount = 0; ICell cellObj = sheet.GetRow(21).GetCell(3); var cellType = cellObj.CellType; switch (cellType) { case CellType.Formula: //【带公式取值步骤2】针对公式列进行动态计算;注意：公式暂时只支持数值字符串类型 var formulaValue = evalor.Evaluate(cellObj); if (formulaValue.CellType == CellType.Numeric) { double b = formulaValue.NumberValue; StandardTotalCount = Convert.ToInt32(b); } else if (formulaValue.CellType == CellType.String) { string s = formulaValue.StringValue; } break; default: break; } ICell cellObj2 = sheet.GetRow(21).GetCell(4); var cellType2 = cellObj2.CellType; switch (cellType2) { case CellType.Formula: //针对公式列进行动态计算;注意：公式暂时只支持数值字符串类型 var formulaValue = evalor.Evaluate(cellObj2); if (formulaValue.CellType == CellType.Numeric) { double b = formulaValue.NumberValue; OKTotalCount = Convert.ToInt32(b); } else if (formulaValue.CellType == CellType.String) { string s = formulaValue.StringValue; } break; default: break; } if (StandardTotalCount == OKTotalCount) { sheet.GetRow(21).GetCell(6).SetCellValue("OK"); } else { sheet.GetRow(21).GetCell(6).SetCellValue("NG"); } //转为字节数组 MemoryStream stream = new MemoryStream(); workbook.Write(stream); var buf = stream.ToArray(); //保存为Excel文件这种方式能保存.xls和.xlsx文件 using (FileStream fs = new FileStream(filePath.PrintMatchFilePath, FileMode.Create, FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); } }

3，元格带有公式的值不能自动更新 ISheet sheet = workbook.GetSheetAt(0); //解决单元格带有公式的值不能自动更新 //解决办法：在程序最后添加一句（关闭文件前），使得sheetdata自动重算【我测试过不用放在关闭文件前也可以的】 sheet.ForceFormulaRecalculation = true;

4，设置打印页面缩放和自适应列宽 /// /// 自适应列宽和打印页缩放 /// /// excel文件路径 /// 缩放比例(77,80,100等） public static void AutoColumnWidth(string filePath,short scale=80) { //【1】打开excel文件的第几张表，第几行的数据，返回一个字典{列名：列的索引} //字典的作用：可以根据列名快速找到对应的列索引 Dictionary dicData = ExcelHelper.GetDataDictionary(filePath, 1, 1); bool isCompatible = ExcelHelper.GetIsCompatible(filePath); IWorkbook workbook = null; using (FileStream fs = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { //把xls文件读入workbook变量里，之后就可以关闭了 workbook = ExcelHelper.CreateWorkbook(isCompatible, fs); fs.Close(); } ISheet sheet = workbook.GetSheetAt(0); int rowCount = sheet.LastRowNum; for (int col = 0; col < sheet.GetRow(0).LastCellNum; col++) { //自适应列宽 sheet.AutoSizeColumn(col); } //sheet.PrintSetup.FitWidth = 1; //sheet.PrintSetup.FitHeight = 0; //设置打印页面缩放比例 sheet.PrintSetup.Scale = scale; #region 非常的耗时，不推荐使用 ////开始遍历【遍历行操作】 //for (int r = 1; r

【本文地址】

C#

C#

今日新闻

推荐新闻