最近接到一个需求,公司财务有个execl里面已经写满了各种计算公式多个sheet公式嵌套等,bd直接往execl中指定的单元格填充数据,execl中的公式可以算出所有的测算结果。现在这个需要做到系统中去,由于公式过于复杂而且多变(财务会已经调整了好几个版本了),于是不将计算逻辑放在系统中实现。在模块的页面中填数据,并返回计算结果到页面。
开始做使用npoi-2.21 时发现一个问题,在某一个单元格中计算出来数据有误差,于是做了一个实验读取execl模板转成IO流输出从页面下载出来,
对两个模板填一样的数据,发现经过 workbook = new XSSFWorkbook(execlStream);下载出的模板数据出现误差,于是升级npoi到2.3.0。重复实验这回模板正确了。
Execl中需要填写的位置坐标是已知的,于是将这些坐标做成一张配置表并存入。
获取到workbook 并指定 将要操作的sheet;
IWorkbook workbook= new XSSFWorkbook(execlStream); ISheet sheet = workbook.GetSheet("指定sheet")
接下来设置指定单元格的值
sheet.GetRow(Y).GetCell(X).SetCellValue(value);
我们从execl中看到的坐标一般的都是 A1、B2、C3 然而GetRow()于GetCell()的参数设定为int类型,于是需要一个转换坐标将我们的A1...这些坐标做转换。
////// 将Excel坐标转换成10进制坐标, Item1 Row ,Item2 Column /// /// ///private Tuple TransferCoordinate(string strCoordinate) { int rowNumber = 0, columnNumber = 0; string strRowNum, strColumnNum; //获得换行数 MatchCollection collection = Regex.Matches(strCoordinate, @"\d+"); strRowNum = collection[0].ToString(); rowNumber = int.Parse(strRowNum); //获得列数 collection = Regex.Matches(strCoordinate, @"[A-Z]{1,3}"); strColumnNum = collection[0].ToString(); columnNumber = CalColumnNum(strColumnNum); return new Tuple (rowNumber - 1, columnNumber - 1); ; } /// /// 获得Excel 列坐标 /// /// ///private int CalColumnNum(string strCol) { int num = 0, charNum; var charArr = strCol.ToUpper().Trim().ToCharArray(); for (int i = charArr.Length - 1, j = 0; i >= 0; i--, j++) { charNum = (int)charArr[i] - 64; num += (int)Math.Pow(26, j) * charNum; } return num; }
通过转换得到 Tuple<int, int> 得到单元格的坐标赋值
sheet.GetRow(coordinate.Item1).GetCell(coordinate.Item2).SetCellValue(number);
设置 sheet.ForceFormulaRecalculation = true; npoi提供用于重新计算公式
cell.SetCellFormula() 可以在后台代码中设置单元格公式
到此处,对模板的写入已经公式计算已经完成。可以直接做流输出下载。。。
我们的需求是将这计算的结果返回到页面中去显示,已经在内存中做了写入于公式计算,继续使用当前的workbook做值读取
IFormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook); foreach (var item in daseInfo) { TupleElementCoordinate = TransferCoordinate(item.PostionCode);//坐标转换 ICell cell = sheet.GetRow(ElementCoordinate.Item1).GetCell(ElementCoordinate.Item1);
cell = evaluator.EvaluateInCell(cell); string value =cell.NumericCellValue
}
本人在这个地方再次出现了读取出来的值存在误差的BUG,大概一百个单元格的读取,全都使用公式,有两个单元格值存在误差。使用上面的验证,模板转换流,流转回模板都没问题,于是发了封反馈邮件,没有出现此问题的朋友可以使用以上方式。
确认为npoi读取bug,确定使用aspose 16.12版,由于没有授权,在使用过程中发现aspose对于单元格写入做了限制,写入无效,但是读取execl是可以的,而且读写更方便,对于公式支持也足够强大,问题是要钱....钱....钱...公司不出钱,低版本的破解版对于公式的支持不足,于是将两个控件混合使用,NOPI做写入,aspose做读取。
//将workbook转化成流 MemoryStream stream = new MemoryStream(); workbook.Write(stream); //aspose读取流做读取 Aspose.Cells.Workbook work = new Aspose.Cells.Workbook(stream); Aspose.Cells.Cells c = work.Worksheets["填写的sheet"].Cells; work.CalculateFormula();////获取经过公式计算的数据------ //循环坐标 aspose支持string格式的坐标参数也就是 A1 A2 foreach (var item in daseInfo) { var value = c[item.PostionCode].StringValue; }