datatable.compute计算表达式实现运输费用公式计算

运输费用公式定义：

 

支持iif()函数，向上取整函数

提供对外计算方法

用到，winform控件，datatable.compute,string字符串操作。

界面如下

using Genersoft.Drp.Common;
using Genersoft.Drp.LS.Control;
using Genersoft.Drp.TP.Client;

using Genersoft.GS.Public.Biz.Client;
using Genersoft.GS.Public.Biz.Control;
using Genersoft.GS.Public.Focus.GuiControl;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows.Forms;

namespace Genersoft.Drp.TP.Control.BaseData
{
    public partial class TspExpenseformulaForm : Form
    {
        //计算表达式参数名称显示
        public string vsCostsFormulaName = "";
        //计算表达式参数编号显示
        public string vsCostsFormula = "";
        public TspExpenseformulaForm()
        {
            PubFunctionControlLS.InitForm(this, InitializeComponent);
            PubFunctionControlLS.AdjustSkin(this, true);
        }

        private Dictionary<string, string> dictParams = new Dictionary<string, string>();
        private void TspExpenseformulaForm_Load(object sender, EventArgs e)
        {

            this.txtFormula.Text = vsCostsFormulaName;
            //设置行变色
            this.dgvParams.RowsDefaultCellStyle.BackColor = Color.Bisque;
            this.dgvParams.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige;

            this.dgvFunc.RowsDefaultCellStyle.BackColor = Color.Bisque;
            this.dgvFunc.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige;

            this.dgvFunc.AllowUserToAddRows = false;

            this.FormBorderStyle = FormBorderStyle.FixedDialog;
            //this.dgvParams.AllowUserToAddRows = false;去掉最后一行空白行
            //this.dgvParams.AutoSizeColumnsMode = System.Windows.Forms.DataGridViewAutoSizeColumnsMode.AllCells;设置自适应列宽
            //加载函数
            DgvFuncLoad();
            //加载参数
            DgvParamsLoad();

        }

        private void DgvParamsLoad()
        {
            //取自定义项
            //DataSet dsDisplay = TspCostsFormulaClient.Current.GetTspTaskBillsCustomFields("TspTaskBills", "TspTaskBills");//统计依据表头表体自定义项部分

            DataTable dtcus = PubFunctionClient.GetService(null).GetCustomFieldInfo("TspTaskBills", "TspTaskBills", " IsDeleted='0' ");
            this.dgvParams.Columns.Add("ParamCode", "字段语义化");
            this.dgvParams.Columns.Add("ParamName","参数名称");
            this.dgvParams.Columns["ParamCode"].Visible = false;
            this.dgvParams.Rows.Add("TspTaskBills.BaseTspCost", "基本运费");
            this.dgvParams.Rows.Add("TspTaskBills.BaseTspPrice", "基本单价");
            this.dgvParams.Rows.Add("TspTaskBills.TotalQuantity", "运输任务总数量");
            this.dgvParams.Rows.Add("TspTaskBills.TotalTransGrWeight", "运输任务总重量");
            this.dgvParams.Rows.Add("TspTaskBills.TotalTransVolume", "运输任务总体积");
            dictParams.Add("基本运费", "TspTaskBills.BaseTspCost");
            dictParams.Add("基本单价", "TspTaskBills.BaseTspPrice");
            dictParams.Add("运输任务总数量", "TspTaskBills.TotalQuantity");
            dictParams.Add("运输任务总重量", "TspTaskBills.TotalTransGrWeight");
            dictParams.Add("运输任务总体积", "TspTaskBills.TotalTransVolume");

            foreach (DataRow dr in dtcus.Rows)
            {
                this.dgvParams.Rows.Add(string.Format("TspTaskBills.{0}", dr["FieldName"]), string.Format(@"运输任务{0}", dr["DisplayName"]));

                dictParams.Add(string.Format(@"运输任务{0}", dr["DisplayName"]), string.Format("TspTaskBills.{0}", dr["FieldName"]));
            }





        }

        private void DgvFuncLoad()
        {
            this.dgvFunc.Columns.Add("FuncName", "名称");
            this.dgvFunc.Rows.Add("IIF( , , )");
            this.dgvFunc.Rows.Add("向上取整{ }");
            //this.dgvFunc.Rows.Add("Sum( , )");
            //this.dgvFunc.Rows.Add("Avg( )");
            //this.dgvFunc.Rows.Add("Min( )");
            //this.dgvFunc.Rows.Add("Max( )");


        }
        private void ClearToolStripMenuItem_Click(object sender, EventArgs e)
        {
            
            this.txtFormula.Clear();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void BtnMath_Click(object sender, EventArgs e)
        {
            
            Button btn = (Button)sender;
            txtFormula.SelectedText=btn.Text;
            //txtFormula.Text = this.txtFormula.Text.Insert();

        }

        /// <summary>
        /// 确定
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void SureToolStripMenuItem_Click(object sender, EventArgs e)
        {
          
            ReplaceParams();
            //替换参数
            string checkResult = this.CheckCostFormula();
            if(checkResult=="1")
            {
                DialogResult = DialogResult.OK;
                this.Close();
            }
        }
        private void ReplaceParams()
        {
            this.vsCostsFormula = this.txtFormula.Text;
            this.vsCostsFormulaName = this.txtFormula.Text;
            //string all = @"这是一段测试数据[我们100]这是一段测试数据[你们200]这是一段测试数据[他们100 谁们300]";
            Regex reg = new Regex(@"[(.+?)]");
      
            foreach (Match m in reg.Matches(this.vsCostsFormula))
            {

                if (dictParams.Keys.Contains(m.Groups[1].ToString()))
                {
                    this.vsCostsFormula = this.vsCostsFormula.Replace(m.Groups[1].ToString(), dictParams[m.Groups[1].ToString()]);
                }
            }

            this.vsCostsFormula = this.vsCostsFormula.Replace("编号", "");
            this.vsCostsFormula = this.vsCostsFormula.Replace("内码", "");
            this.vsCostsFormula = this.vsCostsFormula.ToUpper();
            //获取[]中的参数名称



        }


        private void DgvParams_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            
           txtFormula.SelectedText =string.Format(@"[{0}]", Convert.ToString(this.dgvParams.Rows[e.RowIndex].Cells["ParamName"].Value)); 
        }

        private void DgvFunc_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {

            txtFormula.SelectedText =Convert.ToString(this.dgvFunc.Rows[e.RowIndex].Cells["FuncName"].Value);


        }

        private void CheckToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.ReplaceParams();

            this.CheckCostFormula();



        }
       /// <summary>
       /// 校验
       /// </summary>
       /// <returns></returns>
        private string CheckCostFormula()
        {
            string strExpress = "";
            //服务端校验
            //this.GetTspCostsFormulaResult();

            try
            {
                strExpress = Convert.ToString(this.vsCostsFormula);
          


                if (PubFunctionDrp.IsStringEmpty(strExpress))
                {
                    this.txtFormula.Focus();
                    MessageBoxEx.Show("计算表达式不能为空！");
                    return "0";
                }
              
                if (strExpress.Contains("]["))
                {
                    MessageBox.Show("存在相邻的数据列之间没有运算符，请检查！");
                    return "0";
                }
                //先赋值
                List<string> listParams = dictParams.Values.ToList();

                foreach (string strParam in listParams)
                {
                    strExpress = strExpress.Replace(strParam.ToUpper(), "1.33");
                }

                if (strExpress.Contains("向上取整"))
                {
                    //拿出向上取整的整个表达式

                    strExpress= this.CellingFuncCompute(strExpress);


                }            
 
                #region
                //如果表达式中包含聚合函数，则替换为ID和Value列，验证聚合函数是否正确
                //if (strExpress.Contains("SUM") || strExpress.Contains("COUNT") || strExpress.Contains("MIN")
                //        || strExpress.Contains("MAX") || strExpress.Contains("AVG") || strExpress.Contains("ROUND")
                //        || strExpress.Contains("POWER"))
                //    {
                //        dt.Columns.Add("ID", typeof(string));
                //        dt.Columns.Add("Value", typeof(decimal));

                //        strExpress = strExpress.Replace("SUM('1')", "Sum(ID)");
                //        strExpress = strExpress.Replace("COUNT('1')", "Count(ID)");
                //        strExpress = strExpress.Replace("MIN('1')", "Min(ID)");
                //        strExpress = strExpress.Replace("MAX('1')", "Max(ID)");
                //        strExpress = strExpress.Replace("AVG('1')", "Avg(ID)");
                //        strExpress = strExpress.Replace("ROUND('1')", "Round(ID)");

                //        strExpress = strExpress.Replace("SUM(1)", "Sum(Value)");
                //        strExpress = strExpress.Replace("COUNT(1)", "Count(Value)");
                //        strExpress = strExpress.Replace("MIN(1)", "Min(Value)");
                //        strExpress = strExpress.Replace("MAX(1)", "Max(Value)");
                //        strExpress = strExpress.Replace("AVG(1)", "Avg(Value)");
                //        strExpress = strExpress.Replace("ROUND('1')", "Round(Value)");


                //        if (strExpress.Contains("IIF"))
                //        {
                //            strExpress = strExpress.Replace("SUM(ID)", "'1'");
                //            strExpress = strExpress.Replace("COUNT(ID)", "'1'");
                //            strExpress = strExpress.Replace("MIN(ID)", "'1'");
                //            strExpress = strExpress.Replace("MAX(ID)", "'1'");
                //            strExpress = strExpress.Replace("AVG(ID)", "'1'");
                //            strExpress = strExpress.Replace("ROUND(ID)", "'1'");

                //            strExpress = strExpress.Replace("SUM(Value)", "1");
                //            strExpress = strExpress.Replace("COUNT(Value)", "1");
                //            strExpress = strExpress.Replace("MIN(Value)", "1");
                //            strExpress = strExpress.Replace("MAX(Value)", "1");
                //            strExpress = strExpress.Replace("AVG(Value)", "1");
                //            strExpress = strExpress.Replace("ROUND(Value)", "1");
                //        }
                //    }
                //int MaxCount = 0;
                //while (strExpress.Contains("ROUND"))
                //{
                //    var mx = Regex.Matches(strExpress, "ROUND\([0-9]+[.]?[0-9]*,[0-9]+[.]?[0-9]*\)", RegexOptions.IgnoreCase);
                //    if (mx != null && mx.Count > 0)
                //    {
                //        strExpress = strExpress.Replace(mx[0].Value, "1");
                //    }
                //    else
                //    {
                //        MaxCount++;
                //    }
                //    if (MaxCount >= 50)
                //    {
                //        MessageBoxEx.Show("Round函数校验不通过，请检查！");
                //    return;
                //    }
                //}
                //MaxCount = 0;
                //while (strExpress.Contains("POWER"))
                //{
                //    var mx = Regex.Matches(strExpress, "POWER\([0-9]+[.]?[0-9]*,[0-9]+[.]?[0-9]*\)", RegexOptions.IgnoreCase);
                //    if (mx != null && mx.Count > 0)
                //    {
                //        strExpress = strExpress.Replace(mx[0].Value, "1");
                //    }
                //    else
                //    {
                //        MaxCount++;
                //    }
                //    if (MaxCount >= 50)
                //    {
                //        MessageBoxEx.Show("Power函数校验不通过，请检查！");
                //    }
                //}
                #endregion
                //如果抛出异常，则计算表达式验证失败，否则验证成功

                DatableComputeFunc(strExpress);

                MessageBoxEx.Show("校验成功！");
                //MessageBoxEx.Show("校验成功！");

                return "1";

            }
      
            catch (Exception ex)
            {
                MessageBoxEx.Show("校验失败"+ex);
                return "0";
            }
        }

//向上取整函数的嵌套实现
       private string CellingFuncCompute(string strExpress)
        {
            int idxStart = 0;//开始位置索引
            int idxEnd = 0;//结束位置索引
            string strMathCelling = "";//向上取整表达式
            int count = Regex.Matches(strExpress, "向上取整").Count;
            int count1 = 0;
            for (int i = 0; i < count; i++)
            {

                idxStart = strExpress.IndexOf("向上取整");
                if (idxStart == -1)
                {
                    break;
                }

                idxEnd = strExpress.IndexOf("}", idxStart);
                strMathCelling = strExpress.Substring(idxStart, idxEnd - idxStart + 1);
                count1 = Regex.Matches(strMathCelling, "向上取整").Count-1;
                if (count1 > 0)
                {

                    for (int j = count1; j > 0; j--)
                    {
                        idxStart = strMathCelling.IndexOf("{");
                        if (idxStart == -1)
                        {
                            break;
                        }
                        idxStart = strMathCelling.IndexOf("向上取整", idxStart);

                        idxEnd = strMathCelling.IndexOf("}", idxStart);
                        strMathCelling = strMathCelling.Substring(idxStart, idxEnd - idxStart+1);
                        continue;
                    }



                }
                //strExpress = strExpress.Replace(strCelling, "向上取整i");

                string result = DatableComputeFunc(strMathCelling.Replace("向上取整", ""));

                strExpress = strExpress.Replace(strMathCelling, Convert.ToString(Math.Ceiling(Convert.ToDecimal(result))));
                //计算



            }

            return strExpress;

        }

        private string DatableComputeFunc(string strExpress)
        {
            //如果表达式中包含聚合函数，则判断使用聚合函数的数据列，是否是本表单的
            if (strExpress.Contains("IIF") || strExpress.Contains("Sum") || strExpress.Contains("Count")
                || strExpress.Contains("Min") || strExpress.Contains("Max") || strExpress.Contains("Avg")
                || strExpress.Contains("Power") || strExpress.Contains("Round"))
            {
                strExpress = strExpress.Replace("{", "");
                strExpress = strExpress.Replace("}", "");
                strExpress = strExpress.Replace("@", "");

                string[] computeS = new string[] { "IIF", "Sum", "Count", "Min", "Max", "Avg", "Power", "Round" };

                string strTemp = strExpress;
                for (int i = 0; i < computeS.Length; i++)
                {
                    if (strExpress.Contains(computeS[i].ToString()))
                    {
                        strTemp = strTemp.Replace(computeS[i].ToString(), "#");
                    }
                }

                string[] tempSS = strTemp.Split('#');

                for (int i = 1; i < tempSS.Length; i++)
                {
                    strTemp = tempSS[i];

                    Match match = Regex.Match(strTemp, "\[[^\]]*\]"); //匹配字段
                    string tableName = match.Value.ToString();
                    //校验列

                }
            }







            DataTable dt = new DataTable();
            //如果包含多个''，替换为'。如果包含%'，替换为%。
            if (strExpress.Contains("''"))
            {
                strExpress = strExpress.Replace("''", "'");
            }
            if (strExpress.Contains("%'"))
            {
                strExpress = strExpress.Replace("%'", "%");
            }
            if (strExpress.Contains("{"))
            {
                strExpress = strExpress.Replace("{", "");
            }
            if (strExpress.Contains("}"))
            {
                strExpress = strExpress.Replace("}", "");
            }

            if (strExpress.Contains("["))
            {
                strExpress = strExpress.Replace("[", "");
            }
            if (strExpress.Contains("]"))
            {
                strExpress = strExpress.Replace("]", "");
            }

            strExpress = strExpress.ToUpper();
            object test = dt.Compute(strExpress, "");
            return Convert.ToString(test);
        }


        private void GetTspCostsFormulaResult()
        {
            DataSet dataSet = new DataSet();

            DataTable dataTable = new DataTable();
            dataTable.TableName = "TspTaskBills";
            dataTable.Columns.Add("TspOrgID");
            dataTable.Columns.Add("VendorID");
            dataTable.Columns.Add("TspTypeID");
            dataTable.Columns.Add("CostsID");
            dataTable.Columns.Add("TotalQuantity");
            dataTable.Columns.Add("TotalTransGrWeight");
            dataTable.Columns.Add("TotalTransVolume");
            dataTable.Columns.Add("BaseTspCost");
            dataTable.Columns.Add("VendorCode");
            dataTable.Columns.Add("TspTypeCode");
            dataTable.Columns["TotalTransGrWeight"].DefaultValue = "200";//默认值放前面
            DataRow dataRow = dataTable.NewRow();
            dataRow["TspOrgID"] = "00000000000000000015";
            dataRow["VendorID"] = "0001";
            dataRow["VendorCode"] = "0001";
            dataRow["TspTypeID"] = "1";
            dataRow["TspTypeCode"] = "1";
            dataRow["CostsID"] = "00000000000000008251";
            dataRow["TotalQuantity"] = "3";
            dataRow["TotalTransGrWeight"] = "2";
          
            dataRow["TotalTransVolume"] = "1";
            dataRow["BaseTspCost"] = "7";
            dataTable.Rows.Add(dataRow);
            DataRow dataRow1 = dataTable.NewRow();
            dataRow1["TspOrgID"] = "00000000000000000015";
            dataRow1["VendorID"] = "0001";
            dataRow1["VendorCode"] = "0001";
            dataRow1["TspTypeID"] = "1";
            dataRow1["TspTypeCode"] = "1";
            dataRow1["CostsID"] = "00000000000000008251";
            dataRow1["TotalQuantity"] = "5";
            dataRow1["TotalTransGrWeight"] = "6";

            dataRow1["TotalTransVolume"] = "7";
            dataRow1["BaseTspCost"] = "8";

            dataTable.Rows.Add(dataRow1);

            DataRow dataRow2 = dataTable.NewRow();
            dataRow2["TspOrgID"] = "00000000000000000015";
            dataRow2["VendorID"] = "0000007";
            dataRow2["VendorCode"] = "0000007";
            dataRow2["TspTypeID"] = "1";
            dataRow2["TspTypeCode"] = "1";
            dataRow2["CostsID"] = "00000000000000004615";
            dataRow2["TotalQuantity"] = "5";
            //dataRow2["TotalTransGrWeight"] = "6";

            dataRow2["TotalTransVolume"] = "7";
            dataRow2["BaseTspCost"] = "8";

            dataTable.Rows.Add(dataRow2);




        


            foreach (DataRow dr in dataTable.Rows)
            {
                dr["BaseTspCost"] = 100;
            }

            dataSet.Tables.Add(dataTable);

            DataSet dataSetResult = new DataSet();
            dataSetResult = TspCostsFormulaClient.Current.GetTspCostsFormulaResult(dataSet);









        }

        private void CancelToolStripMenuItem_Click(object sender, EventArgs e)
        {
            DialogResult = DialogResult.Cancel;
            this.Close();
        }

        private void CloseToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}

代码如上

对于对外计算接口，定义一个webservice，传入值datatable包含参数对应值的数据源，计算写法与校验的一样就可以。