Exemplo n.º 1
0
        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);
        }
Exemplo n.º 2
0
        /// <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);
        }
Exemplo n.º 3
0
        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);
            }
        }
Exemplo n.º 4
0
        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);
        }