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 (int colIndex = 0; colIndex < tb.Columns.Count; colIndex++)
                {
                    //第一列汇总
                    ICell cell = rowSummary.CreateCell(colIndex);
                    ICellStyle textCellStyle = wb.CreateCellStyle();
                    if (colIndex == 0)
                    {
                        textCellStyle.DataFormat = wb.CreateDataFormat().GetFormat("@");                       
                        cell.SetCellValue("合计");
                    }
                    else if (cell.ColumnIndex > 2)
                    {
                        textCellStyle.DataFormat = wb.CreateDataFormat().GetFormat("#,##0.00");
                        cell.CellFormula = string.Format("SUM({0}{1}:{0}{2})",excelDic[colIndex], 1, tb.Rows.Count);
                    }
                }

                //写入Excel
                using (FileStream fs = File.OpenWrite(filePath))
                {
                    wb.Write(fs);
                }
            }
            finally
            {
                if (wb != null)
                {
                    wb.Close();
                }
                //清理中间临时xml文件
                if (File.Exists(tempXmlFile))
                {
                    File.Delete(tempXmlFile);
                }

            }
            return fileUrl;
        }

        public override void OnInitialize(InitializeEventArgs e)
        {
            excelDic.Add(3, "D");
            excelDic.Add(4, "E");
            excelDic.Add(5, "F");
        }

        private Dictionary<int,string> excelDic = new Dictionary<int, string>()
        {
           
        };

        private DataTable BuildData()
        {
            var sheetName = "NPOI Demo";

            var tb = new DataTable(sheetName);
            tb.Columns.Add("fbillno");
            tb.Columns.Add("fdate");
            tb.Columns.Add("FDOCUMENTSTATUS");
            tb.Columns.Add("FQTY");
            tb.Columns.Add("FPRICE");
            tb.Columns.Add("FAMOUNT");

            //构造表头
            var newRow = tb.NewRow();
            newRow["fbillno"] = "单据编号";
            newRow["FDate"] = "日期";
            newRow["FDOCUMENTSTATUS"] = "状态";
            newRow["FQTY"] = "数量";
            newRow["FPRICE"] = "单价";
            newRow["FAMOUNT"] = "金额-公式计算";
            tb.Rows.Add(newRow);

            for (int i = 0; i <= 100; i++)
            {
                var row = tb.NewRow();
                row["fbillno"] = string.Format("BillNo-{0}", i.ToString("000"));
                row["FDate"] = DateTime.Now.ToLongDateString();
                row["FDOCUMENTSTATUS"] = "C";
                row["FQTY"] = i + 1;
                row["FPRICE"] = i * 10 + 5;
                row["FAMOUNT"] = 0;
                tb.Rows.Add(row);
            }

            return tb;
        }
    }
}