static string specialFormulaCode()
{
if (processingformula.StartsWith("SUMPRODUCT("))
{
string[] arr = processingformula.Substring(11, processingformula.Length - 12).Split('*');
if (arr.Length != 3)
{
throw new Exception("SUMPRODUCT 只支持数据检索,限定同一张表内使用第二行和第一列匹配," + processingsheet.SheetName + "," + processingcoord);
}
string tabname = string.Empty;
string proname = string.Empty;
CellCoord coord = new CellCoord(-1, -1);
foreach (string str in arr)
{
string[] tmp1 = str.Trim(' ', ')', '(').Split('!');
string tname = tmp1[0].Contains(']') ? tmp1[0].Split(']')[1] : tmp1[0];
if (string.IsNullOrEmpty(tabname))
{
tabname = tname;
}
else if (tabname != tname)
{
throw new Exception("SUMPRODUCT 只支持数据检索,限定【同一张表内】使用第二行和第一列匹配," + processingsheet.SheetName + "," + processingcoord);
}
string[] tmp2 = tmp1[1].Split('=');
// 数据矩阵忽略
if (tmp2.Length == 1)
{
continue;
}
if (tmp2[1].StartsWith("\""))
{
proname = tmp2[1].Trim('\"');
}
else
{
int row, col;
GetCoordinate(tmp2[1], out row, out col);
ICell cell = processingsheet.GetRow(row - 1).GetCell(col - 1);
if (cell.CellType == CellType.String)
{
proname = cell.StringCellValue;
}
else
{
coord = new CellCoord(row, col);
}
}
}
if (coord.col < 0 || string.IsNullOrEmpty(proname))
{
throw new Exception("SUMPRODUCT 只支持数据检索,限定同一张表内使用第二行和第一列匹配," + processingsheet.SheetName + "," + processingcoord);
}
return(CodeTemplate.Get("func_code_SUMPRODUCTSEARCH")
.Replace("ARGS[0]", CodeTemplate.Get("template_cell").Replace("[ROW]", coord.row.ToString()).Replace("[COL]", coord.col.ToString()))
.Replace("[PARAM1]", CodeTemplate.curlang == CodeTemplate.Langue.Go ? (tabname.Substring(0, 1).ToUpper() + tabname.Substring(1)) : tabname)
.Replace("[PARAM2]", CodeTemplate.curlang == CodeTemplate.Langue.Go ? (proname.Substring(0, 1).ToUpper() + proname.Substring(1)) : proname));
}
return(string.Empty);
}
/// <summary>
/// 处理lua表格的formula
/// </summary>
public static string DealWithFormulaSheetLua(ISheet sheet)
{
CodeTemplate.curlang = CodeTemplate.Langue.Lua;
StringBuilder sb = new StringBuilder();
Dictionary <CellCoord, List <CellCoord> > abouts = new Dictionary <CellCoord, List <CellCoord> >();
// 开头
sb.AppendLine(CodeTemplate.Get("template_title")
.Replace("[SHEET_NAME]", sheet.SheetName.Substring(3))
.Replace("[USEFUL_ROW_COUNT]", (sheet.LastRowNum + 1).ToString())
);
// 声明
sb.AppendLine("\n-- declares");
sb.AppendLine(CodeTemplate.Get("template_input").Replace("[CONTENT]", GetDeclaras(sheet, 0)));
sb.AppendLine(CodeTemplate.Get("template_output").Replace("[CONTENT]", GetDeclaras(sheet, 3)));
// 抓取所有数据
sb.AppendLine("\n-- all datas");
for (int rownum = 0; rownum <= sheet.LastRowNum; rownum++)
{
IRow row = sheet.GetRow(rownum);
if (row == null)
{
continue;
}
for (int i = 0; i < row.Cells.Count; i++)
{
ICell cell = row.Cells[i];
int colnum = cell.ColumnIndex;
// in、out声明忽略
if (colnum == 0 || colnum == 1 || colnum == 3 || colnum == 4)
{
continue;
}
if (cell.CellType == CellType.Boolean || cell.CellType == CellType.Numeric)
{
sb.AppendLine(CodeTemplate.Get("template_value")
.Replace("[ROW]", (rownum + 1).ToString())
.Replace("[COL]", (colnum + 1).ToString())
.Replace("[VALUE]", cell.CellType == CellType.Boolean ? cell.BooleanCellValue.ToString().ToLower() : cell.NumericCellValue.ToString())
);
}
else if (cell.CellType == CellType.Formula)
{
List <CellCoord> about;
sb.AppendLine(CodeTemplate.Get("template_func")
.Replace("[ROW]", (rownum + 1).ToString())
.Replace("[COL]", (colnum + 1).ToString())
.Replace("[CONTENT]", Formula2Code.Translate(sheet, cell.CellFormula, cell.ToString(), out about))
);
CellCoord cur = new CellCoord(rownum + 1, colnum + 1);
foreach (CellCoord cc in about)
{
if (!abouts.ContainsKey(cc))
{
abouts.Add(cc, new List <CellCoord>());
}
if (!abouts[cc].Contains(cur))
{
abouts[cc].Add(cur);
}
}
}
}
}
// 数据影响关联递归统计
bool change;
do
{
change = false;
foreach (var item in abouts)
{
for (int i = 0; i < item.Value.Count; i++)
{
if (abouts.ContainsKey(item.Value[i]))
{
foreach (var c in abouts[item.Value[i]])
{
if (!item.Value.Contains(c))
{
item.Value.Add(c);
change = true;
}
}
}
}
}
} while (change);
// 数据影响关联
sb.AppendLine("\n-- cell data relation");
foreach (var item in abouts)
{
sb.AppendLine(CodeTemplate.Get("template_about")
.Replace("[ROW]", item.Key.row.ToString())
.Replace("[COL]", item.Key.col.ToString())
.Replace("[CONTENT]", string.Join(",", item.Value.Select(CellCoord.ToString)))
);
}
// 枚举器
sb.AppendLine("\n-- enumerator");
sb.AppendLine("sheet.enumerator = {}");
foreach (var item in FormulaEnumerator.GetList(sheet))
{
sb.AppendLine("sheet.enumerator." + item.name + " = {}");
sb.AppendLine("sheet.enumerator." + item.name + ".start = " + (item.start + 1));
sb.AppendLine("sheet.enumerator." + item.name + ".over = " + (item.end + 1));
sb.AppendLine("sheet.enumerator." + item.name + ".div = " + item.div);
sb.AppendLine("sheet.enumerator." + item.name + ".key = " + (item.key - 1));
sb.AppendLine("sheet.enumerator." + item.name + ".propertys = {");
for (int i = 0; i < item.propertys.Count; i++)
{
sb.AppendLine("\"" + item.propertys[i] + "\", --" + item.notes[i]);
}
sb.AppendLine("}");
}
// 结果
formulaContents.Add(sheet.SheetName.Substring(3), sb.ToString());
return(string.Empty);
}
static string ToCode(ParseTreeNode node)
{
switch (node.ChildNodes.Count)
{
case 0: // 叶节点 函数、操作符、引用、固定数据
{
switch (node.Term.Name)
{
case GrammarNames.TokenExcelConditionalRefFunction:
case GrammarNames.TokenExcelRefFunction:
case GrammarNames.ExcelFunction:
{
string key = "func_code_" + node.Token.Text;
key = key.Substring(0, key.Length - 1);
return(CodeTemplate.Get(key));
}
case GrammarNames.TokenNumber:
return(node.Token.ValueString);
case GrammarNames.TokenBool:
return(node.Token.ValueString.ToLower());
case GrammarNames.TokenCell:
int row, col;
GetCoordinate(node.Token.ValueString, out row, out col);
aboutCells.Add(new CellCoord(row, col));
return(CodeTemplate.Get("template_cell")
.Replace("[ROW]", row.ToString()).Replace("[COL]", col.ToString()));
case GrammarNames.TokenSheet:
return(node.Token.ValueString.Substring(0, node.Token.ValueString.Length - 1));
case GrammarNames.TokenVRange:
if (!node.Token.ValueString.StartsWith("$A"))
{
throw new Exception("只支持搜索第一列" + "\nformula = " + processingformula + "\ncoord = " + processingcoord);
}
return("");
case GrammarNames.TokenFileNameNumeric: // 文件名不重要，忽略
return("");
default:
if (node.Term.Flags.HasFlag(TermFlags.IsOperator))
{
return(CodeTemplate.Get("operator_code_" + node.Token.ValueString));
}
else
{
throw new Exception("unkonw flag : " + node.Term.Name + "\nformula = " + processingformula + "\ncoord = " + processingcoord);
}
}
}
case 1: // 单一节点直接向下找
return(ToCode(node.ChildNodes[0]));
case 2:
{
switch (node.Term.Name)
{
case GrammarNames.ReferenceFunctionCall:
case GrammarNames.FunctionCall:
// 函数调用，找出函数和参数组合代码
string funcformat = ToCode(node.ChildNodes[0]);
List <string> args = new List <string>();
for (int i = 0; i < node.ChildNodes[1].ChildNodes.Count; i++)
{
args.Add(ToCode(node.ChildNodes[1].ChildNodes[i]));
}
// 处理负数情况
if (funcformat == CodeTemplate.Get("operator_code_-") && args.Count == 1)
{
args.Insert(0, "0");
}
return(FormatScript(funcformat, args));
case GrammarNames.Reference:
return(ToCode(node.ChildNodes[0]) + ToCode(node.ChildNodes[1]));
case GrammarNames.Prefix:
return(string.Join("", node.ChildNodes.Select(ToCode)));
default:
throw new Exception("unknow node term " + node.Term.Name + "\nformula =" + processingformula + "\ncoord = " + processingcoord);
}
}
case 3: // 操作符，类似函数调用
{
string funcformat = ToCode(node.ChildNodes[1]);
List <string> args = new List <string>();
args.Add(ToCode(node.ChildNodes[0]));
args.Add(ToCode(node.ChildNodes[2]));
return(FormatScript(funcformat, args));
}
default:
throw new Exception("WTF! " + processingformula + "\ncoord = " + processingcoord);
}
}
public static string ExportLua(string exportDir)
{
// 公式写入文件
string formulaDir = exportDir + "formula" + Path.DirectorySeparatorChar;
if (!Cache.enable)
{
if (Directory.Exists(formulaDir))
{
Directory.Delete(formulaDir, true);
}
Directory.CreateDirectory(formulaDir);
}
foreach (var formula in formulaContents)
{
File.WriteAllText(formulaDir + formula.Key.ToLower() + ".lua", formula.Value, new UTF8Encoding(false));
}
// 数据写入文件
string dataDir = exportDir + "data" + Path.DirectorySeparatorChar + Cache.dfolder + Path.DirectorySeparatorChar;
if (!Cache.enable)
{
if (Directory.Exists(dataDir))
{
Directory.Delete(dataDir, true);
}
Directory.CreateDirectory(dataDir);
}
List <string> results = new List <string>();
foreach (var data in datas.Values)
{
Cache.MarkTableAbout(data.name, data.files);
ThreadPool.QueueUserWorkItem(ooo =>
{
List <string> groupDeclaras = new List <string>();
Dictionary <string, List <string> > groupids = new Dictionary <string, List <string> >();
data.dataContent.Sort((a, b) => { return((int)a[0] - (int)b[0]); });
foreach (var values in data.dataContent)
{
Dictionary <string, string[]> .Enumerator enumerator = data.groups.GetEnumerator();
while (enumerator.MoveNext())
{
string groupDeclara = "data.groups[\"" + enumerator.Current.Key + "\"]";
if (!groupDeclaras.Contains(groupDeclara))
{
groupDeclaras.Add(groupDeclara);
}
for (int j = 0; j < enumerator.Current.Value.Length; j++)
{
object cv = values[data.groupindexs[enumerator.Current.Key][j]];
groupDeclara += "[" + (cv is string? "\"" + cv + "\"" : cv) + "]";
if (!groupDeclaras.Contains(groupDeclara))
{
groupDeclaras.Add(groupDeclara);
}
}
if (!groupids.ContainsKey(groupDeclara))
{
groupids.Add(groupDeclara, new List <string>());
}
groupids[groupDeclara].Add(values[0].ToString());
}
}
Dictionary <string, List <string> > groupsItemCount = new Dictionary <string, List <string> >();
foreach (var key in groupids.Keys)
{
string[] arr = key.Split(']');
string title = "";
for (int i = 0; i < arr.Length - 2; i++)
{
title += arr[i] + "]";
if (!groupsItemCount.ContainsKey(title))
{
groupsItemCount[title] = new List <string>();
}
if (!groupsItemCount[title].Contains(title + arr[i + 1] + "]"))
{
groupsItemCount[title].Add(title + arr[i + 1] + "]");
}
}
}
data.ids.Sort();
data.files.Sort();
File.WriteAllText(dataDir + data.name.ToLower() + ".lua",
CodeTemplate.Get("template_data")
.Replace("[EXCEL_FILES]", string.Join(",", data.files))
.Replace("[DATA_TABLE_NAME]", data.name)
.Replace("[DATA_KEYS]", string.Join(",\n", data.keys.Select(engname => { return("\"" + engname + "\"--[[" + data.keyNames[data.keys.IndexOf(engname)] + "]]"); })))
.Replace("[DATA_COLS]", string.Join("\n", data.cols.Select(i => { return("data.cols[" + (i + 1) + "] = " + (data.cols.IndexOf(i) + 1)); })))
.Replace("[DATA_IDS]", string.Join(",", data.ids))
.Replace("[DECLARA_GROUPS]", string.Join("\n", groupDeclaras.Select(gd => { return(gd + " = {}"); })))
.Replace("[DATA_GROUPS]", string.Join("\n", groupids.Select(o => { return(o.Key + " = {" + string.Join(",", o.Value) + "}"); })))
.Replace("[DATA_GROUPS_COUNT]", string.Join("\n", groupsItemCount.Select(o =>
{
string nkey = o.Key.Replace("data.groups[", "data.groupscount[");
return(nkey + " = {}\n" + nkey + ".count = " + o.Value.Count);
})))
.Replace("[DATA_LINES]", string.Join("\n", data.dataContent.Select(values =>
{
return("data.lines[" + values[0] + "] = {" + string.Join(",", values.Select(v =>
{
if (v is string)
{
return ModifyLuaString(v as string);
}
if (v is string[])
{
return "{" + string.Join(",", (v as string[]).Select(ModifyLuaString)) + "}";
}
if (v is int[])
{
return "{" + string.Join(",", v as int[]) + "}";
}
if (v is double[])
{
return "{" + string.Join(",", v as double[]) + "}";
}
return v;
})) + "}");
})))
, new UTF8Encoding(false));
lock (results)
results.Add(string.Empty);
});
}
while (results.Count < datas.Values.Count)
{
Thread.Sleep(TimeSpan.FromSeconds(0.01));
}
return(string.Empty);
}
