使用 NPOI 、aspose实现execl模板公式计算

 　　最近接到一个需求，公司财务有个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...这些坐标做转换。

 /// <summary>
        /// 将Excel坐标转换成10进制坐标, Item1 Row ,Item2 Column
        /// </summary>
        /// <param name="strCoordinate"></param>
        /// <returns></returns>
        private Tuple<int, int> 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<int, int>(rowNumber - 1, columnNumber - 1); ;
        }

        /// <summary>
        /// 获得Excel 列坐标
        /// </summary>
        /// <param name="strCol"></param>
        /// <returns></returns>
        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)
  {
     　　 Tuple<int, int> ElementCoordinate = 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;
                   
                }