BOS运行时-NPOI引出-支持Excel公式

星空引出默认使用NPOI开源组件进行引出，很多高级的excel功能依赖于NOPI的实现，本文演示使用NPOI设置excel公式，这样引出excel后，修改数据，支持公式同步变化。

对应的接口名称

cell.CellFormula = string.Format("D{0}*E{0}", i + 1);

公式效果，A*B公式，如下图

合计效果，使用excel的sum函数，效果如下

参考代码如下：

using Kingdee.BOS;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Core;
using Kingdee.BOS.Core.Bill;
using Kingdee.BOS.Core.Bill.PlugIn;
using Kingdee.BOS.Core.DynamicForm;
using Kingdee.BOS.Core.DynamicForm.PlugIn.Args;
using Kingdee.BOS.Core.DynamicForm.PlugIn.ControlModel;
using Kingdee.BOS.Core.Metadata;
using Kingdee.BOS.Core.Metadata.ControlElement;
using Kingdee.BOS.Core.Metadata.FieldElement;
using Kingdee.BOS.Core.Metadata.FormElement;
using Kingdee.BOS.Excel;
using Kingdee.BOS.Log;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Resource;
using Kingdee.BOS.ServiceHelper;
using Kingdee.BOS.ServiceHelper.Excel;
using Kingdee.BOS.ServiceHelper.FileServer;
using Kingdee.BOS.Util;
using Kingdee.BOS.Web;
using Kingdee.BOS.WebApi.Client;
using Kingdee.BOS.WebApi.FormService;
using NPOI.HSSF.Util;
using NPOI.OpenXmlFormats.Spreadsheet;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.Streaming;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Cloud.BOS.Support.Bill
{
    [HotUpdate]
    public class NpoiExportPlugin : AbstractBillPlugIn
    {
        


        public override void BarItemClick(BarItemClickEventArgs e)
        {
            if (e.BarItemKey.EqualsIgnoreCase("tbExportByNpoi"))
            {
                Export();
            }            
        }

        private void Export()
        {
            var tb = BuildData();

            var showParameter = new DynamicFormShowParameter();
            showParameter.FormId = "BOS_FileDownLoad";
            showParameter.OpenStyle.ShowType = ShowType.Modal;
            showParameter.CustomParams.Add("url", NPOIExport(tb));
            this.View.ShowForm(showParameter);

        }

        private string NPOIExport(DataTable tb)
        {
            var wb = new SXSSFWorkbook();
            SXSSFSheet sheet = (SXSSFSheet)wb.CreateSheet("demo");
            var tempXmlFile = sheet._writer.TemporaryFilePath();

            string fileName = string.Format("NPOI-demo-{0}.xlsx", DateTime.Now.ToString("yyyyMMddhhmmss"));
            var filePath = PathUtils.GetPhysicalPath(KeyConst.TEMPFILEPATH, fileName);
            var fileUrl = PathUtils.GetServerPath(KeyConst.TEMPFILEPATH, PathUtils.UrlEncode(fileName));

            try
            {
                for (int i = 0; i < tb.Rows.Count; i++)
                {

                    IRow row = sheet.CreateRow(i);

                    for (int colIndex = 0; colIndex < tb.Columns.Count; colIndex++)
                    {
                        ICell cell = row.CreateCell(colIndex);
                        ICellStyle textCellStyle = wb.CreateCellStyle();
                        if (cell.ColumnIndex > 2 && i > 0)
                        {
                            textCellStyle.DataFormat = wb.CreateDataFormat().GetFormat("#,##0.00");
                            cell.SetCellValue(double.Parse(tb.Rows[i][colIndex].ToString()));
                        }
                        else
                        {
                            textCellStyle.DataFormat = wb.CreateDataFormat().GetFormat("@");
                            cell.SetCellValue(tb.Rows[i][colIndex].ToString());
                        }

                        cell.CellStyle = textCellStyle;

                        //设计公式
                        if (cell.ColumnIndex == 5 && i > 0)
                        {
                            cell.CellFormula = string.Format("D{0}*E{0}", i + 1);
                        }

                        if (i % 2 == 0)
                        {
                            textCellStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;
                            textCellStyle.FillPattern = FillPattern.SolidForeground;
                        }

                    }
                }

                //增加汇总行
                IRow rowSummary = sheet.CreateRow(tb.Rows.Count);
                for (in