390 lines
16 KiB
C#
390 lines
16 KiB
C#
|
||
using Irony.Parsing;
|
||
using System;
|
||
using System.Collections.Generic;
|
||
using System.Linq;
|
||
using System.Security.Cryptography;
|
||
using System.Text;
|
||
using System.Threading.Tasks;
|
||
using XLParser;
|
||
|
||
namespace WCS.BLL
|
||
{
|
||
public static class PnHelp
|
||
{
|
||
public static string Jx(string text, string th_A1 = null)
|
||
{
|
||
var aaa = new Dictionary<string, string>();
|
||
if (!string.IsNullOrWhiteSpace(th_A1))
|
||
aaa.Add("A1", th_A1);
|
||
|
||
return Jx(text, aaa);
|
||
}
|
||
|
||
public static string Jx(string text, Dictionary<string, string> th = null)
|
||
{
|
||
if (!text.StartsWith("="))
|
||
return text;
|
||
|
||
var par = ExcelFormulaParser.Parse(text.Substring(1));
|
||
var aaa1 = Jx(par, th);
|
||
return aaa1.AllNodes().LastOrDefault()?.Token.ValueString ?? "";
|
||
}
|
||
|
||
public static ParseTreeNode Jx(ParseTreeNode treeNode, Dictionary<string, string> th = null)
|
||
{
|
||
var nodes = treeNode.ChildNodes;
|
||
foreach (var node in nodes)
|
||
{
|
||
//var p1 = node.Print();
|
||
if (node.IsFunction())
|
||
{
|
||
//函数
|
||
var hs = node.GetFunction();
|
||
//参数
|
||
var csList = node.GetFunctionArguments();
|
||
//参数
|
||
List<string> csa = new List<string>();
|
||
foreach (var cs in csList)
|
||
{
|
||
var p12 = cs.Print();
|
||
var cs4 = cs.AllNodes().ElementAtOrDefault(3);
|
||
if (cs4 != null && (cs4.Is(GrammarNames.TokenText) || cs4.Is(GrammarNames.TokenNumber) || cs4.Is(GrammarNames.TokenBool)))
|
||
{
|
||
csa.Add(cs4.Token.ValueString);
|
||
}
|
||
else if (cs4 != null && cs4.Is(GrammarNames.TokenCell))
|
||
{
|
||
if (th != null && th.ContainsKey(cs4.Token.ValueString))
|
||
csa.Add(th[cs4.Token.ValueString]);
|
||
else
|
||
csa.Add(cs4.Token.ValueString);
|
||
}
|
||
else
|
||
{
|
||
var csjg = Jx(cs, th);
|
||
var aaaaaa = csjg.AllNodes().LastOrDefault();
|
||
csa.Add(aaaaaa.Token.ValueString);
|
||
}
|
||
}
|
||
|
||
//执行
|
||
var val = keyValuePairs[hs].Item1.Invoke(csa);
|
||
var newval = ExcelFormulaParser.Parse("\"" + val + "\"");
|
||
|
||
treeNode.ChildNodes.Clear();
|
||
treeNode.ChildNodes.Add(newval);
|
||
return newval;
|
||
}
|
||
else if (node.Is(GrammarNames.Reference))
|
||
{
|
||
return Jx(node, th);
|
||
}
|
||
else if (node.Is(GrammarNames.Formula))
|
||
{
|
||
return Jx(node, th);
|
||
}
|
||
//else if (node.Is(GrammarNames.Cell))
|
||
//{
|
||
// return Jx(node, th);
|
||
//}
|
||
}
|
||
return treeNode;
|
||
}
|
||
|
||
public static Dictionary<string, (Func<List<string>, string>, string)> keyValuePairs = new Dictionary<string, (Func<List<string>, string>, string)>()
|
||
{
|
||
//excel自带
|
||
{ "+",(Jia,"加号")},
|
||
{ "-",(Jian,"减号、负数")},
|
||
{ "*",(Cen,"乘号")},
|
||
{ "/",(Chu,"除号")},
|
||
{ "%",(BaiFenBi,"百分号")},
|
||
{ "^",(CenMu,"乘幂")},
|
||
{ "&",(HeBin,"和号，用于连接两个或更多的文本字符串，或者是文本与数值之间的连接。")},
|
||
{ "=",(DengYu,"等于")},
|
||
{ "<>",(BuDengYu,"不等于")},
|
||
{ ">",(DaYu,"大于")},
|
||
{ "<",(XiaoYu,"小于")},
|
||
{ ">=",(DaYuDengYu,"大于等于")},
|
||
{ "<=",(XiaoYuDengYu,"小于等于")},
|
||
{ "IF",(IF,"条件判断。IF(TRUE|FALSE,值_true,值_false)")},
|
||
{ "IFS",(IFS,"多条件判断并执行。IFS(TRUE|FALSE,值1,TRUE|FALSE,值2,...)")},
|
||
{ "AND",(AND,"多个条件判断，需要所有满足。AND(TRUE|FALSE,TRUE|FALSE,...)")},
|
||
{ "OR",(OR,"多个条件判断,需要一个满足。OR(TRUE|FALSE,TRUE|FALSE,...)")},
|
||
{ "ABS",(ABS,"绝对值。ABS(值)")},
|
||
{ "LOG",(LOG,"对数。LOG(值,[底,默认10])")},
|
||
{ "REPLACE",(REPLACE,"替换字符串。REPLACE(值,开始位置,个数,新值)")},
|
||
{ "SUBSTITUTE",(SUBSTITUTE,"替换字符串。SUBSTITUTE(值,原值,新值,[替换序号|未实现])")},
|
||
{ "MID",(MID,"截取字符串。MID(值,开始位置|负数倒取,字符个数)")},
|
||
{ "SEARCH",(SEARCH,"查找字符串，没有找到为0。SEARCH(查找值,值,[开始位置])")},
|
||
{ "TRIM",(TRIM,"清除前后空格。TRIM(值)")},
|
||
{ "LEN",(LEN,"获取长度。LEN(值)")},
|
||
//自定义函数
|
||
{ "REVERSE",(REVERSE,"反转字符串。REVERSE(值)")},
|
||
{ "REPLACES",(REPLACES,"替换多个字符串。REPLACES(值,新值,旧值1,旧值2,...)")},
|
||
{ "CONTAINS",(CONTAINS,"字符串中是否存在。CONTAINS(值,值1,值2,...)")},
|
||
{ "WHERENUM",(WHERENUM,"只保留数字。WHERENUM(值)")},
|
||
{ "WHEREAZ",(WHEREAZ,"只保留字母。WHEREAZ(值)")},
|
||
{ "WHERENUMAZ",(WHERENUMAZ,"只保留数字和字母。WHERENUMAZ(值)")},
|
||
{ "SPLIT",(SPLIT,"分隔字符串。SPLIT(值,分隔值,取的位置|负数倒取)")},
|
||
{ "PADLEFT",(PADLEFT,"字符串左侧填充字符,实现右对齐。PADLEFT(值,长度,对齐字符)")},
|
||
{ "PADRIGHT",(PADRIGHT,"字符串右侧填充字符,实现左对齐。PADRIGHT(值,长度,对齐字符)")},
|
||
{ "MIDLOG10",(MIDLOG10,"截取后计算10底的对数(如:1000=102)。MIDLOG10(值,截取长度,1输出截取|2不输出截取)")},
|
||
{ "MIDLOG10R",(MIDLOG10R,"尾位当做10底的对数转为整数(如:102=1000)。MIDLOG10R(值,[尾位长度])")},
|
||
{ "KILO4",(KILO4,"千值转为4长度字符串(如:1000=001K)。KILO4(值,从小到大的3个长度的字符单位)")},
|
||
{ "KILO4R",(KILO4R,"4长度字符串转为千值(如:001K=1000)。KILO4R(值,从小到大的3个长度的字符单位)")},
|
||
};
|
||
|
||
static string Jia(List<string> data)
|
||
{
|
||
return (Convert.ToDecimal(data[0]) + Convert.ToDecimal(data[1])).ToString();
|
||
}
|
||
static string Jian(List<string> data)
|
||
{
|
||
if (data.Count() == 1)
|
||
return (0m - Convert.ToDecimal(data[0])).ToString();
|
||
else
|
||
return (Convert.ToDecimal(data[0]) - Convert.ToDecimal(data[1])).ToString();
|
||
}
|
||
static string Cen(List<string> data)
|
||
{
|
||
return (Convert.ToDecimal(data[0]) * Convert.ToDecimal(data[1])).ToString();
|
||
}
|
||
static string Chu(List<string> data)
|
||
{
|
||
return (Convert.ToDecimal(data[0]) / Convert.ToDecimal(data[1])).ToString();
|
||
}
|
||
static string BaiFenBi(List<string> data)
|
||
{
|
||
return (Convert.ToDecimal(data[0]) / 100m).ToString();
|
||
}
|
||
static string CenMu(List<string> data)
|
||
{
|
||
return (Math.Pow(Convert.ToDouble(data[0]), Convert.ToDouble(data[1]))).ToString();
|
||
}
|
||
static string HeBin(List<string> data)
|
||
{
|
||
return string.Join("", data);
|
||
}
|
||
static string DengYu(List<string> data)
|
||
{
|
||
return (data[0] == data[1]).ToString().ToUpper();
|
||
}
|
||
static string BuDengYu(List<string> data)
|
||
{
|
||
return (data[0] != data[1]).ToString().ToUpper();
|
||
}
|
||
static string DaYu(List<string> data)
|
||
{
|
||
return (Convert.ToDecimal(data[0]) > Convert.ToDecimal(data[1])).ToString().ToUpper();
|
||
}
|
||
static string XiaoYu(List<string> data)
|
||
{
|
||
return (Convert.ToDecimal(data[0]) < Convert.ToDecimal(data[1])).ToString().ToUpper();
|
||
}
|
||
static string DaYuDengYu(List<string> data)
|
||
{
|
||
return (Convert.ToDecimal(data[0]) >= Convert.ToDecimal(data[1])).ToString().ToUpper();
|
||
}
|
||
static string XiaoYuDengYu(List<string> data)
|
||
{
|
||
return (Convert.ToDecimal(data[0]) <= Convert.ToDecimal(data[1])).ToString().ToUpper();
|
||
}
|
||
static string IF(List<string> data)
|
||
{
|
||
return (data[0] == "TRUE" ? data[1] : data[2]).ToString();
|
||
}
|
||
static string IFS(List<string> data)
|
||
{
|
||
int i = 0;
|
||
while (true)
|
||
{
|
||
if (data[i * 2] == "TRUE")
|
||
return data[(i * 2) + 1];
|
||
i++;
|
||
}
|
||
}
|
||
static string AND(List<string> data)
|
||
{
|
||
return (data.All(o => o == "TRUE")).ToString().ToUpper();
|
||
}
|
||
static string OR(List<string> data)
|
||
{
|
||
return (data.Any(o => o == "TRUE")).ToString().ToUpper();
|
||
}
|
||
static string ABS(List<string> data)
|
||
{
|
||
return Math.Abs(Convert.ToDecimal(data[0])).ToString();
|
||
}
|
||
static string LOG(List<string> data)
|
||
{
|
||
return Math.Log(Convert.ToDouble(data[0]), data.Count() == 1 ? 10 : Convert.ToDouble(data[1])).ToString();
|
||
}
|
||
static string REPLACE(List<string> data)
|
||
{
|
||
return $"{data[0].Substring(0, Convert.ToInt32(data[1]) - 1)}{data[3]}{data[0].Substring(Convert.ToInt32(data[1]) + Convert.ToInt32(data[2]) - 1)}";
|
||
}
|
||
static string SUBSTITUTE(List<string> data)
|
||
{
|
||
return data.Count == 3 ? data[0].Replace(data[1], data[2]) : data[0].Replace(data[1], data[2]);
|
||
}
|
||
static string MID(List<string> data)
|
||
{
|
||
return Convert.ToInt32(data[1]) > 0 ? new string(data[0].Skip(Convert.ToInt32(data[1]) - 1).Take(Convert.ToInt32(data[2])).ToArray()) : new string(data[0].Reverse().Skip(Math.Abs(Convert.ToInt32(data[1])) - 1).Take(Convert.ToInt32(data[2])).Reverse().ToArray());
|
||
}
|
||
static string SEARCH(List<string> data)
|
||
{
|
||
var v1 = (data.Count() == 2 ? data[1].IndexOf(data[0]) + 1 : data[1].Substring(Convert.ToInt32(data[2])).IndexOf(data[0]) + 1);
|
||
return (v1 == 0 ? 0 : data.Count() == 2 ? v1 : v1 + Convert.ToInt32(data[2])).ToString();
|
||
}
|
||
static string TRIM(List<string> data)
|
||
{
|
||
return data[0].Trim();
|
||
}
|
||
static string LEN(List<string> data)
|
||
{
|
||
return data[0].Length.ToString();
|
||
}
|
||
static string REVERSE(List<string> data)
|
||
{
|
||
return new string(data[0].Reverse().ToArray());
|
||
}
|
||
static string REPLACES(List<string> data)
|
||
{
|
||
var va = data[0];
|
||
foreach (var v in data.Skip(2))
|
||
{
|
||
va = va.Replace(v, data[1]);
|
||
}
|
||
return va;
|
||
}
|
||
static string CONTAINS(List<string> data)
|
||
{
|
||
foreach (var item in data.Skip(1))
|
||
{
|
||
if (data[0].Contains(item))
|
||
return "TRUE";
|
||
}
|
||
return "FALSE";
|
||
}
|
||
static string WHERENUM(List<string> data)
|
||
{
|
||
var sb = new StringBuilder();
|
||
foreach (var item in data[0])
|
||
if (Char.IsDigit(item))
|
||
sb.Append(item);
|
||
|
||
return sb.ToString();
|
||
}
|
||
static string WHEREAZ(List<string> data)
|
||
{
|
||
var sb = new StringBuilder();
|
||
foreach (var item in data[0])
|
||
if (Char.IsLetter(item))
|
||
sb.Append(item);
|
||
|
||
return sb.ToString();
|
||
}
|
||
static string WHERENUMAZ(List<string> data)
|
||
{
|
||
var sb = new StringBuilder();
|
||
foreach (var item in data[0])
|
||
if (Char.IsLetterOrDigit(item))
|
||
sb.Append(item);
|
||
|
||
return sb.ToString();
|
||
}
|
||
static string SPLIT(List<string> data)
|
||
{
|
||
return Convert.ToInt32(data[2]) > 0 ? data[0].Split(new string[] { data[1] }, StringSplitOptions.None).ElementAtOrDefault(Convert.ToInt32(data[2]) - 1) : data[0].Split(new string[] { data[1] }, StringSplitOptions.None).Reverse().ElementAtOrDefault(Math.Abs(Convert.ToInt32(data[2])) - 1);
|
||
}
|
||
static string PADLEFT(List<string> data)
|
||
{
|
||
return data[0].PadLeft(Convert.ToInt32(data[1]), data[2].First());
|
||
}
|
||
static string PADRIGHT(List<string> data)
|
||
{
|
||
return data[0].PadRight(Convert.ToInt32(data[1]), data[2].First());
|
||
}
|
||
static string MIDLOG10R(List<string> data)
|
||
{
|
||
var cc = data[0].Length - (data.Count > 1 ? Convert.ToInt32(data[1]) : 1);
|
||
var v1 = Convert.ToInt32(data[0].Substring(0, cc));
|
||
var v2 = Convert.ToInt32(data[0].Substring(cc));
|
||
return (v1 * Math.Pow(10, v2)).ToString();
|
||
}
|
||
static string MIDLOG10(List<string> data)
|
||
{
|
||
var d1 = (Int32)Convert.ToDecimal(data[0]);
|
||
var d2 = Convert.ToInt32(data[1]);
|
||
var d3 = Convert.ToInt32(data[2]);
|
||
var ws = Convert.ToInt32(new string(d1.ToString().Take(d2).ToArray()));
|
||
var log = ws == 0 ? 0 : Math.Log10(d1 / ws);
|
||
if (double.IsNaN(log) || double.IsInfinity(log))
|
||
log = 0;
|
||
|
||
return d3 == 1 ? (ws.ToString() + log).PadLeft(d2, '0') : log.ToString();
|
||
}
|
||
static string KILO4(List<string> data)
|
||
{
|
||
var d1 = Convert.ToDecimal(data[0]);
|
||
var d2 = data[1].Skip(0).First();
|
||
var d3 = data[1].Skip(1).First();
|
||
var d4 = data[1].Skip(2).First();
|
||
if (d1 <= 0)
|
||
{
|
||
return "000" + d2;
|
||
}
|
||
else if (d1 < 1)
|
||
{
|
||
return d1.ToString().Replace('.', d2).Trim('0').PadLeft(4, '0').Substring(0, 4);
|
||
}
|
||
else if (d1 < 1000)
|
||
{
|
||
var aa = d1.ToString().Replace('.', d2);
|
||
return (aa.IndexOf(d2) == -1 ? (aa + d2) : aa).PadLeft(4, '0').Substring(0, 4);
|
||
}
|
||
else if (d1 < 1000 * 1000)
|
||
{
|
||
d1 = d1 / 1000m;
|
||
var aa = d1.ToString().Replace('.', d3);
|
||
return (aa.IndexOf(d3) == -1 ? (aa + d3) : aa).PadLeft(4, '0').Substring(0, 4);
|
||
}
|
||
else if (d1 < 1000 * 1000 * 1000)
|
||
{
|
||
d1 = d1 / (1000m * 1000m);
|
||
var aa = d1.ToString().Replace('.', d4);
|
||
return (aa.IndexOf(d4) == -1 ? (aa + d4) : aa).PadLeft(4, '0').Substring(0, 4);
|
||
}
|
||
else
|
||
{
|
||
return "999" + d4;
|
||
}
|
||
}
|
||
static string KILO4R(List<string> data)
|
||
{
|
||
var d2 = data[1].Skip(0).First();
|
||
var d3 = data[1].Skip(1).First();
|
||
var d4 = data[1].Skip(2).First();
|
||
|
||
if (data[0].Contains(d2))
|
||
{
|
||
return Convert.ToDecimal(data[0].Replace(d2, '.')).ToString();
|
||
}
|
||
else if (data[0].Contains(d3))
|
||
{
|
||
return (Convert.ToDecimal(data[0].Replace(d3, '.')) * 1000m).ToString();
|
||
}
|
||
else if (data[0].Contains(d4))
|
||
{
|
||
return (Convert.ToDecimal(data[0].Replace(d4, '.')) * 1000m * 1000m).ToString();
|
||
}
|
||
else
|
||
{
|
||
return Convert.ToDecimal(data[0]).ToString();
|
||
}
|
||
}
|
||
}
|
||
}
|