c# NPOI通过单元格里的公式，计算数值

业务场景

公司B是母公司A的子公司，每个月都需要将耗材销售情况统计向总公司报账。

其中计算的内容如下：

       1、该时间段客户a、b、c 。。。z的分别购买耗材金额，即该客户端销售额

　　2、对于a、b、c 。。。z公司，每销售一个单位数量的耗材都有居间费(抽成)

 

 

 以上Excel中A-P列是从系统导出来的，其中Q（佣金总额）、P（业绩）列是公式拖拽生成的。

Q列的公式 H2 * M2（M列如果没有，默认为0）

R列的公式 L2 - Q2

以上公式，如果在任何一个Excel中，经过拖拽都很容易实现。

笔者以下使用C#中NPOI实现，将计算获得的数据存入DataTable

关键代码DataField.cs

public class DataField
{
    private string _label;
    private string _name;
    private Type _type;
    private int _columnWidth = 10 /* default 10 */;
    private string _formula;     
    private bool _isRequireCreateColumn;
 
    public DataField()
    { }

    public DataField(string label, string name, Type type)
    {
        this._label = label;
        this._name = name;
        this._type = type;
    }

    public DataField(string label, string name, Type type, int columnWidth):this(label,name,type)
    {
        this.ColumnWidth = columnWidth;
    }

    public DataField(string label, string name, Type type, string formula)
   : this(label, name, type)
    {
        this._formula = formula;            
    }

    
    public DataField(string label, string name, Type type, int columnWidth, string formula) 
        : this(label, name, type,columnWidth)
    {
        this._formula = formula;            
    }


    public DataField(string label, string name, Type type, string formula, bool isRequireCreateColumn)
        : this(label, name, type,formula)
    {
        this._isRequireCreateColumn = isRequireCreateColumn;
    }

    public DataField(string label, string name, Type type, int columnWidth, string formula, bool isRequireCreateColumn)
        : this(label, name, type,formula,isRequireCreateColumn)
    {
        this._columnWidth = columnWidth;
    }


    /// <summary>
    /// 字段显示名称
    /// </summary>
    public string Label { get => _label; set => _label = value; }

    /// <summary>
    /// 字段属性名
    /// </summary>
    public string Name { get => _name; set => _name = value; }

    /// <summary>
    /// 字段数据类型
    /// </summary>
    public Type Type { get => _type; set => _type = value; }

    /// <summary>
    /// 字段宽度(n *256)
    /// </summary>
    public int ColumnWidth { get => _columnWidth; set => _columnWidth = value; }

    /// <summary>
    /// 公式（该字段必须是计算生成的类型）
    /// </summary>
    public string Formula { get => _formula; set => _formula = value; }
   
    /// <summary>
    /// 该字段通过创建插入表格中
    /// </summary>
    public bool IsRequireCreateColumn { get => _isRequireCreateColumn; set => _isRequireCreateColumn = value; }
    
}

关键业务代码

private static DataField[] fields = new DataField[] {
        new DataField("序号","id",typeof(short)),
        new DataField("选择","checked",typeof(string)),
        //delivery time交货时间
        //发货时间 
        new DataField("发货日期","shippingTime",typeof(DateTime)),
        new DataField("客户简称","customerAbbreviation",typeof(string)),
        new DataField("销售部门","department",typeof(string)),
        new DataField("业 务 员","seller",typeof(string)),
        new DataField("存货名称","productName",typeof(string),25),
        new DataField("数量","quantity",typeof(int)),
        new DataField("销售单位","unit",typeof(string)),
        new DataField("无税单价","price",typeof(float)),
        new DataField("无税金额","amount1",typeof(float)),
        new DataField("价税合计","amount2",typeof(float)),
        new DataField("居间费","commission",typeof(float)),
        new DataField("发票总金额","",typeof(string)),
        new DataField("制单人","",typeof(string)),
        new DataField("审核人","",typeof(string)),
        new DataField("佣金总额","totalCommission",typeof(float),"H{0} * M{0}",true),
        new DataField("业绩","performance",typeof(float),"L{0} - Q{0}",true)
    };


for (int lineNum = 0; lineNum < rowCount; ++lineNum)
{
    DataRow NewRow = myTable.NewRow();
    for (int index = 0; index < fields.Length; index++)
    {
        var field = fields[index];
        string formula = field.Formula;
        string label = field.Label;
        string name = field.Name;
        Type type = field.Type;
        bool isRequireCreateColumn = field.IsRequireCreateColumn;

        ICell cell;
        object value = null;
        //如果公式的值不为null/空字符等，则认为该列是需要计算生成的
        if (isRequireCreateColumn || !string.IsNullOrWhiteSpace(formula))
        {
            //新建列并设置数据类型为公式
            cell = defaultSheet.GetRow(lineNum + 1).CreateCell(index);            
            cell.SetCellType(CellType.Formula);
            cell.SetCellFormula(string.Format(formula, lineNum + 2));
            //评估表达式的值
            workbook.GetCreationHelper().CreateFormulaEvaluator().EvaluateFormulaCell(cell);
            value = cell.NumericCellValue;
        }
    }
}

笔者为每个列额外设置了数据类型（即DataField的Type属性），是为了方便在DataTable及后续里面进行数值计算甚至精确度裁剪。

经过以上设置，就可以计算出数值了

 

 最终效果

 

 

 

其他参考：

https://stackoverflow.com/questions/24088737/how-to-re-calculate-a-cells-formula

https://www.cr173.com/html/18143_all.html

https://www.cnblogs.com/shiyh/p/7478222.html

https://stackoverflow.com/questions/25077009/npoi-setcellformula-custom-formula-in-vba

https://stackoverflow.com/questions/17475359/how-to-use-npoi-to-read-excel-spreadsheet-that-contains-empty-cells