Exemple #1
0
        bool Export()
        {
            //DateTime start = DateTime.Now;
            //CustomWorkbook.Init(paths[0]);
            //MessageBox.Show("读入所有表 " + (DateTime.Now - start).TotalSeconds);
            //start = DateTime.Now;
            //CheckError(Exporter.ReadDataXlsx());
            //MessageBox.Show("读取xlsx " + (DateTime.Now - start).TotalSeconds);
            //start = DateTime.Now;
            //CheckError(Exporter.ReadFormulaXlsx(Exporter.DealWithFormulaSheetLua));
            //MessageBox.Show("lua公式 " + (DateTime.Now - start).TotalSeconds);
            //start = DateTime.Now;
            //CheckError(Exporter.ExportLua(paths[1]));
            //MessageBox.Show("导出lua文件 " + (DateTime.Now - start).TotalSeconds);
            //start = DateTime.Now;
            //CheckError(Exporter.ReadFormulaXlsx(Exporter.DealWithFormulaSheetGo));
            //MessageBox.Show("go公式 " + (DateTime.Now - start).TotalSeconds);
            //start = DateTime.Now;
            //CheckError(Exporter.ExportGo(paths[2], paths[3]));
            //MessageBox.Show("导出go文件 " + (DateTime.Now - start).TotalSeconds);
            //Cache.SaveCache();
            //return true;

            CustomWorkbook.Init(paths[0]);
            try
            {
                return
                    // 读取xlsx
                    (CheckError(Exporter.ReadDataXlsx())
                     // 读 lua 公式
                     && CheckError(Exporter.ReadFormulaXlsx(Exporter.DealWithFormulaSheetLua))
                     // 导出lua文件
                     && CheckError(Exporter.ExportLua(paths[1]))
                     // 读 go 公式
                     && CheckError(Exporter.ReadFormulaXlsx(Exporter.DealWithFormulaSheetGo))
                     // 导出go文件
                     && CheckError(Exporter.ExportGo(paths[2], paths[3])));
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

            return(false);
        }
Exemple #2
0
        bool Export()
        {
            try
            {
                DateTime      start = DateTime.Now;
                List <string> readfiles;
                CustomWorkbook.Init(paths[0], out readfiles);
                if (readfiles.Count < 10)
                {
                    readfiles.ForEach(Console.WriteLine);
                }
                Console.WriteLine("读入" + readfiles.Count + "张表," + (DateTime.Now - start).TotalSeconds.ToString("0.00") + "秒");

                start = DateTime.Now;
                if (!CheckError(Exporter.ReadDataXlsx()))
                {
                    return(false);
                }
                Console.WriteLine("读取xlsx, " + (DateTime.Now - start).TotalSeconds.ToString("0.00") + "秒");

                // * lua
                if (_isOutLua)
                {
                    start = DateTime.Now;
                    if (!CheckError(Exporter.ReadFormulaXlsx(Exporter.DealWithFormulaSheetLua)))
                    {
                        return(false);
                    }
                    Console.WriteLine("lua公式, " + (DateTime.Now - start).TotalSeconds.ToString("0.00") + "秒");

                    start = DateTime.Now;
                    if (!CheckError(Exporter.ExportLua(paths[1])))
                    {
                        return(false);
                    }
                    Console.WriteLine("导出lua文件," + (DateTime.Now - start).TotalSeconds.ToString("0.00") + "秒");
                }

                // * go
                if (_isOutGo)
                {
                    start = DateTime.Now;
                    if (!CheckError(Exporter.ReadFormulaXlsx(Exporter.DealWithFormulaSheetGo)))
                    {
                        return(false);
                    }
                    Console.WriteLine("go公式," + (DateTime.Now - start).TotalSeconds.ToString("0.00") + "秒");

                    start = DateTime.Now;
                    if (!CheckError(Exporter.ExportGo(paths[2], paths[3])))
                    {
                        return(false);
                    }
                    Console.WriteLine("导出go文件," + (DateTime.Now - start).TotalSeconds.ToString("0.00") + "秒");
                }

                // * c#
                if (_isOutCs)
                {
                    start = DateTime.Now;
                    if (!CheckError(Exporter.ReadFormulaXlsx(Exporter.DealWithFormulaSheetCS)))
                    {
                        return(false);
                    }
                    Console.WriteLine("c#公式," + (DateTime.Now - start).TotalSeconds.ToString("0.00") + "秒");

                    start = DateTime.Now;
                    if (!CheckError(Exporter.ExportCS(paths[4], paths[5])))
                    {
                        return(false);
                    }
                    Console.WriteLine("导出c#文件," + (DateTime.Now - start).TotalSeconds.ToString("0.00") + "秒");
                }

                // * typescript
                if (_isOutTs)
                {
                    start = DateTime.Now;
                    if (!CheckError(Exporter.ReadFormulaXlsx(Exporter.DealWithFormulaSheetTS)))
                    {
                        return(false);
                    }
                    Console.WriteLine("ts公式," + (DateTime.Now - start).TotalSeconds.ToString("0.00") + "秒");

                    start = DateTime.Now;
                    if (!CheckError(Exporter.ExportTS(paths[6], paths[7])))
                    {
                        return(false);
                    }
                    Console.WriteLine("导出ts文件," + (DateTime.Now - start).TotalSeconds.ToString("0.00") + "秒");
                }

                Cache.SaveCache();
                Console.WriteLine("存储缓存");
                return(true);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

            return(false);
        }
Exemple #3
0
        static string DealWithDataSheet(ISheet sheet, CustomWorkbook book)
        {
            string tableName = sheet.SheetName;

            if (tableName.StartsWith("_"))
            {
                return(string.Empty);
            }

            string[] larr = tableName.Split('_');

            // ! 有分隔符并且是大写的时候才是多语言
            if (tableName.Contains("_") && larr[larr.Length - 1] == larr[larr.Length - 1].ToUpper())
            {
                try
                {
                    return(DealWithDataLabelSheet(sheet, book));
                }
                catch (Exception e)
                {
                    return("deal with label sheet error : " + book.fileName + " - " + tableName + "\n" + e.Message + "\n" + e.StackTrace);
                }
            }

            DataStruct data;

            lock (datas)
            {
                if (!datas.TryGetValue(tableName, out data))
                {
                    data = new DataStruct(tableName);
                }
            }

            lock (data)
            {
                data.files.Add(book.fileName);

                //5、sheet第二行,字段英文名,不填写留空的列将被过滤掉,不予导出,第一列不可留空
                //6、sheet第三行,字段中文名
                //7、sheet第四行,字段类型,int整数、string字符串、double浮点数
                try
                {
                    IRow engRow = sheet.GetRow(1);
                    IRow cnRow  = sheet.GetRow(2);
                    IRow tRow   = sheet.GetRow(3);

                    if (engRow == null || engRow.FirstCellNum != 0)
                    {
                        return("第一个字段不可以留空,SheetName = " + tableName + ",FileName = " + book.fileName);
                    }

                    List <string> keys     = new List <string>();
                    List <string> keyNames = new List <string>();
                    List <string> types    = new List <string>();
                    List <int>    cols     = new List <int>();
                    for (int i = 0; i < engRow.LastCellNum; i++)
                    {
                        // 如果列名没有 || 不是字符串 || 空字符串 都不需要导出
                        if (engRow.GetCell(i) == null || engRow.GetCell(i).CellType != CellType.String || string.IsNullOrEmpty(engRow.GetCell(i).StringCellValue))
                        {
                            continue;
                        }

                        // 记录有效的导出列位置
                        cols.Add(i);

                        string key = engRow.GetCell(i).StringCellValue;
                        if (keys.Contains(key))
                        {
                            return("字段名重复 " + key + ",SheetName = " + tableName + ",FileName = " + book.fileName);
                        }
                        keys.Add(key);

                        // 列的中文字段名称
                        keyNames.Add((cnRow == null || cnRow.GetCell(i) == null) ? "" : cnRow.GetCell(i).StringCellValue.Replace("\n", " "));

                        // 列的数值类型
                        string type = (tRow == null || tRow.GetCell(i) == null) ? " " : tRow.GetCell(i).StringCellValue;
                        types.Add(type);

                        if (!dataTypes.Contains(type))
                        {
                            return("未知的数据类型" + type + ",SheetName = " + tableName + ",FileName = " + book.fileName);
                        }
                    }

                    if (data.isnew)
                    {
                        // ! 如果第一列不是id - int的话
                        if (types[0] != "int")
                        {
                            return("表头错误,索引必须为int类型,SheetName = " + tableName + ",FileName = " + book.fileName);
                        }

                        data.keys     = keys;
                        data.keyNames = keyNames;
                        data.types    = types;
                        data.cols     = cols;
                    }
                    else
                    {
                        // ! 如果是老的表的话重新校验一下
                        string error = "表头不一致,SheetName = " + tableName + ",FileNames = " + string.Join(",", data.files);
                        Compare(keys, data.keys, error);
                        Compare(keyNames, data.keyNames, error);
                        Compare(types, data.types, error);
                        Compare(cols, data.cols, error);
                    }
                }
                catch (Exception ex)
                {
                    return("表头错误,SheetName = " + tableName + ",FileName = " + book.fileName + "\n" + ex.ToString() + "\n" + ex.StackTrace);
                }

                // 读取表头
                //4、sheet第一行,填写字段名数据分组,可以进行多字段联合分组("|"分隔),有分组逻辑的数据必须进行分组
                {
                    List <string> groups = new List <string>();
                    IRow          row    = sheet.GetRow(0);
                    if (row != null)
                    {
                        for (int i = 0; i < row.Cells.Count; i++)
                        {
                            ICell cell = row.Cells[i];
                            if (cell.CellType == CellType.String && !string.IsNullOrEmpty(cell.StringCellValue))
                            {
                                groups.Add(cell.StringCellValue);
                            }
                        }
                    }

                    if (data.isnew)
                    {
                        foreach (string g in groups)
                        {
                            List <int> indexs = new List <int>();
                            string[]   arr    = g.Split('|');
                            foreach (string dt in arr)
                            {
                                int index = data.keys.IndexOf(dt);
                                if (index == -1)
                                {
                                    return("找不到数据分组要的字段[" + dt + "],SheetName = " + tableName + ",FileName = " + book.fileName);
                                }
                                indexs.Add(index);
                            }
                            data.groups.Add(g, arr);
                            data.groupindexs.Add(g, indexs.ToArray());
                        }
                    }
                    else
                    {
                        if (groups.Count != data.groups.Count)
                        {
                            return("数据分组声明不一致,SheetName = " + tableName + ",FileNames = " + string.Join(",", data.files));
                        }
                        foreach (string g in groups)
                        {
                            if (!data.groups.ContainsKey(g))
                            {
                                return("数据分组声明不一致,SheetName = " + tableName + ",FileNames = " + string.Join(",", data.files));
                            }
                        }
                    }
                }


                // 8、sheet第五行开始是表的数据,首字段不填写视为无效数据
                List <int>            ids         = data.ids;
                List <List <object> > dataContent = data.dataContent;
                for (int i = 4; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row == null || row.FirstCellNum > 0 || row.GetCell(0) == null || row.GetCell(0).CellType == CellType.Blank)
                    {
                        continue;
                    }

                    List <object> values = new List <object>();
                    for (int j = 0; j < data.cols.Count; j++)
                    {
                        ICell  cell = row.GetCell(data.cols[j]);
                        string err;
                        object codevalue = GetCodeValue(sheet, book, cell, data.types[j], out err);
                        if (!string.IsNullOrEmpty(err))
                        {
                            return(err);
                        }
                        values.Add(codevalue); // 对应的这一行的每一列的数值都添加进来 - 对应的是有效的导出列的位置的数值
                    }

                    int id = (int)values[0];
                    if (id == 0)
                    {
                        continue;
                    }
                    if (id == -1)
                    {
                        string key = book.fileName + " " + tableName + " ";
                        for (int iii = 1; iii < values.Count; iii++)
                        {
                            key += values[iii].ToString();
                        }
                        do
                        {
                            key += " ";
                            id   = 99000000 + Math.Abs(key.GetHashCode()) % 1000000;
                        } while (ids.Contains(id));
                        values[0] = id;
                    }
                    if (ids.Contains(id))
                    {
                        return("索引冲突 [" + values[0] + "],SheetName = " + tableName + ",FileNames = " + string.Join(",", data.files));
                    }

                    bool useful = true;
                    var  idcell = row.GetCell(data.cols[0]);

                    // 先注释掉,不然导不出那些加备注的行
                    //IComment idcom = null;
                    //while (true) { try { idcom = idcell.CellComment; break; } catch { } }
                    //if (idcom != null)
                    //useful = new List<string>(idcom.String.String.Split('\n')).Intersect(Cache.labels).Count() > 0;
                    if (useful)
                    {
                        // 添加id
                        ids.Add((int)values[0]);
                        // 添加数据
                        dataContent.Add(values);
                    }
                }

                data.isnew = false;
                return(string.Empty);
            }
        }
Exemple #4
0
        static string DealWithDataLabelSheet(ISheet sheet, CustomWorkbook book)
        {
            string[] larr       = sheet.SheetName.Split('_');
            string   tableName  = sheet.SheetName.Substring(0, sheet.SheetName.LastIndexOf('_'));
            int      labelindex = Cache.labels.IndexOf(larr[larr.Length - 1]);

            // 不要这个标签的内容
            if (labelindex < 0)
            {
                return(string.Empty);
            }

            // 等待原始数据导出
            DataStruct data;

            while (!datas.TryGetValue(tableName, out data))
            {
                Thread.Sleep(TimeSpan.FromSeconds(0.1));
            }

            lock (data)
            {
                // 标签表头
                IRow          engRow    = sheet.GetRow(1);
                IRow          tRow      = sheet.GetRow(3);
                List <int>    keyindexs = new List <int>();
                List <int>    cols      = new List <int>();
                List <string> types     = new List <string>();
                for (int i = 0; i < engRow.LastCellNum; i++)
                {
                    if (engRow.GetCell(i) == null || engRow.GetCell(i).CellType != CellType.String || string.IsNullOrEmpty(engRow.GetCell(i).StringCellValue))
                    {
                        continue;
                    }
                    string key      = engRow.GetCell(i).StringCellValue;
                    int    keyindex = data.keys.IndexOf(key);
                    if (keyindex < 0)
                    {
                        continue;
                    }
                    keyindexs.Add(keyindex);
                    string type = (tRow == null || tRow.GetCell(i) == null) ? " " : tRow.GetCell(i).StringCellValue;
                    types.Add(type);
                    if (!dataTypes.Contains(type))
                    {
                        return("未知的数据类型" + type + ",SheetName = " + tableName + ",FileName = " + book.fileName);
                    }
                    cols.Add(i);
                }

                for (int i = 4; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row == null || row.FirstCellNum > 0 || row.GetCell(0) == null || row.GetCell(0).CellType == CellType.Blank)
                    {
                        continue;
                    }

                    ICell  cell = row.GetCell(cols[0]);
                    string err;
                    object codevalue = GetCodeValue(sheet, book, cell, types[0], out err);
                    if (!string.IsNullOrEmpty(err))
                    {
                        return(err);
                    }
                    int id = (int)codevalue;
                    if (id == 0)
                    {
                        continue;
                    }
                    if (data.dataLabelModifys[labelindex].ContainsKey(id))
                    {
                        return("id冲突,表名" + sheet.SheetName + ",id=" + id);
                    }
                    data.dataLabelModifys[labelindex].Add(id, new Dictionary <int, object>());

                    for (int j = 1; j < cols.Count; j++)
                    {
                        cell = row.GetCell(cols[j]);
                        if (cell != null && cell.CellType == CellType.String && cell.StringCellValue == "*")
                        {
                            continue;
                        }
                        // 修改内容
                        codevalue = GetCodeValue(sheet, book, cell, types[j], out err);
                        if (!string.IsNullOrEmpty(err))
                        {
                            return(err);
                        }
                        data.dataLabelModifys[labelindex][id].Add(keyindexs[j], codevalue);
                    }
                }
            }
            return(string.Empty);
        }
Exemple #5
0
        static object GetCodeValue(ISheet sheet, CustomWorkbook book, ICell cell, string type, out string error)
        {
            error = string.Empty;
            try
            {
                object codevalue = null;
                if (CustomWorkbook.evaluateSheets.Contains(sheet.SheetName) && cell != null && cell.CellType == CellType.Formula)
                {
                    book.evaluator.DebugEvaluationOutputForNextEval = true;
                    CellValue cellValue = book.evaluator.Evaluate(cell);
                    switch (type)
                    {
                    case "int":
                        codevalue = cellValue.CellType == CellType.Numeric ? Convert.ToInt32(cellValue.NumberValue) :
                                    cellValue.CellType != CellType.String || string.IsNullOrEmpty(cellValue.StringValue) ? 0 : int.Parse(cellValue.StringValue); break;

                    case "long":
                        codevalue = cellValue.CellType == CellType.Numeric ? Convert.ToInt64(cellValue.NumberValue) :
                                    cellValue.CellType != CellType.String || string.IsNullOrEmpty(cellValue.StringValue) ? 0 : long.Parse(cellValue.StringValue); break;

                    case "string":
                        codevalue = cellValue.CellType == CellType.String ? cellValue.StringValue : cellValue.ToString(); break;

                    case "double":
                    case "float":
                    case "float64":
                        codevalue = cellValue.CellType == CellType.Numeric ? cellValue.NumberValue :
                                    cellValue.CellType != CellType.String || string.IsNullOrEmpty(cellValue.StringValue) ? 0 : double.Parse(cellValue.StringValue); break;

                    default:
                        if (type.StartsWith("[]"))
                        {
                            string[] arr = (cellValue.CellType == CellType.Numeric ? cellValue.NumberValue.ToString() : (cellValue.CellType == CellType.String ? cellValue.StringValue : "")).Split('|');
                            if (arr.Length == 1 && string.IsNullOrEmpty(arr[0]))
                            {
                                arr = new string[] { }
                            }
                            ;
                            switch (type.Substring(2))
                            {
                            case "int":
                                int[] v = new int[arr.Length];
                                for (int ii = 0; ii < arr.Length; ii++)
                                {
                                    v[ii] = string.IsNullOrEmpty(arr[ii]) ? 0 : int.Parse(arr[ii]);
                                }
                                codevalue = v;
                                break;

                            case "long":
                                long[] v64 = new long[arr.Length];
                                for (int ii = 0; ii < arr.Length; ii++)
                                {
                                    v64[ii] = string.IsNullOrEmpty(arr[ii]) ? 0 : long.Parse(arr[ii]);
                                }
                                codevalue = v64;
                                break;

                            case "string":
                                codevalue = arr;
                                break;

                            case "double":
                            case "float":
                                double[] vv = new double[arr.Length];
                                for (int ii = 0; ii < arr.Length; ii++)
                                {
                                    vv[ii] = string.IsNullOrEmpty(arr[ii]) ? 0 : double.Parse(arr[ii]);
                                }
                                codevalue = vv;
                                break;
                            }
                        }
                        break;
                    }
                }
                else
                {
                    CellType ct = CellType.Blank;
                    if (cell != null)
                    {
                        if (cell.CellType == CellType.Formula)
                        {
                            ct = cell.CachedFormulaResultType;
                        }
                        else
                        {
                            ct = cell.CellType;
                        }
                    }

                    switch (type)
                    {
                    case "int":
                        codevalue = ct == CellType.Numeric ? Convert.ToInt32(cell.NumericCellValue) :
                                    (ct == CellType.String && !string.IsNullOrEmpty(cell.StringCellValue) ? int.Parse(cell.StringCellValue) : 0);
                        break;

                    case "long":
                        codevalue = ct == CellType.Numeric ? Convert.ToInt64(cell.NumericCellValue) :
                                    (ct == CellType.String && !string.IsNullOrEmpty(cell.StringCellValue)) ? long.Parse(cell.StringCellValue) : 0; break;

                    case "string":
                        codevalue = ct == CellType.Numeric ? cell.NumericCellValue.ToString() :
                                    (ct == CellType.String ? cell.StringCellValue : "");
                        break;

                    case "double":
                    case "float":
                    case "float64":
                        codevalue = ct == CellType.Numeric ? cell.NumericCellValue :
                                    (ct == CellType.String && !string.IsNullOrEmpty(cell.StringCellValue) ? double.Parse(cell.StringCellValue) : 0);
                        break;

                    default:
                        if (type.StartsWith("[]"))
                        {
                            string[] arr = (ct == CellType.Numeric ? cell.NumericCellValue.ToString() : (ct == CellType.String ? cell.StringCellValue : "")).Split('|');
                            if (arr.Length == 1 && string.IsNullOrEmpty(arr[0]))
                            {
                                arr = new string[] { }
                            }
                            ;
                            switch (type.Substring(2))
                            {
                            case "int":
                                int[] v = new int[arr.Length];
                                for (int ii = 0; ii < arr.Length; ii++)
                                {
                                    v[ii] = string.IsNullOrEmpty(arr[ii]) ? 0 : int.Parse(arr[ii]);
                                }
                                codevalue = v;
                                break;

                            case "long":
                                long[] v64 = new long[arr.Length];
                                for (int ii = 0; ii < arr.Length; ii++)
                                {
                                    v64[ii] = string.IsNullOrEmpty(arr[ii]) ? 0 : long.Parse(arr[ii]);
                                }
                                codevalue = v64;
                                break;

                            case "string":
                                codevalue = arr;
                                break;

                            case "double":
                            case "float":
                            case "float64":
                                double[] vv = new double[arr.Length];
                                for (int ii = 0; ii < arr.Length; ii++)
                                {
                                    vv[ii] = string.IsNullOrEmpty(arr[ii]) ? 0 : double.Parse(arr[ii]);
                                }
                                codevalue = vv;
                                break;
                            }
                        }
                        break;
                    }
                }
                return(codevalue);
            }
            catch (Exception ex)
            {
                Console.Write(ex);
                error = "数据格式有误, 第" + (cell.RowIndex + 1) + "行第" + (cell.ColumnIndex + 1) + "列, SheetName = " + sheet.SheetName + ",FileNames = " + book.fileName;
                return(null);
            }
        }
Exemple #6
0
        public static void Init(string excelpath, out List <string> readfiles)
        {
            evaluatorEnv = new Dictionary <string, IFormulaEvaluator>();
            allBooks     = new List <CustomWorkbook>();
            int totalCount = 0;

            // 启用公式重算的接口
            evaluateSheets = new List <string>();
            string evaConfPath = new FileInfo(Application.ExecutablePath).Directory.FullName + Path.DirectorySeparatorChar + "evaconfig";

            if (File.Exists(evaConfPath))
            {
                evaluateSheets = new List <string>(File.ReadAllLines(evaConfPath));
            }

            // 遍历导出目录
            readfiles = new List <string>();
            foreach (var file in new DirectoryInfo(excelpath).GetFiles())
            {
                if (file.Name.StartsWith(".~$") || file.Name.StartsWith("~$") || (file.Extension != ".xlsx" && file.Extension != ".xls" && file.Extension != ".xlsm"))
                {
                    continue;
                }

                foreach (var fname in Cache.GetNoCacheAbout(file))
                {
                    if (!readfiles.Contains(fname))
                    {
                        readfiles.Add(fname);
                    }
                }
            }
            int count = 1;

            foreach (var fname in readfiles)
            {
                var            file = new FileInfo(excelpath + "/" + fname);
                CustomWorkbook book = new CustomWorkbook(file);
                Console.WriteLine(count + " - 读取了表:" + fname);
                book.type = CustomWorkbookType.Export;
                totalCount++;
            }

            // 引用表
            string refConfPath = new FileInfo(Application.ExecutablePath).Directory.FullName + Path.DirectorySeparatorChar + "refconfig";

            if (File.Exists(refConfPath))
            {
                foreach (string reffile in File.ReadAllLines(refConfPath))
                {
                    var            file = new FileInfo(reffile);
                    CustomWorkbook book = new CustomWorkbook(file);
                    book.type = CustomWorkbookType.Referenced;
                    totalCount++;
                }
            }

            // 等待完成
            while (allBooks.Count < totalCount)
            {
                Thread.Sleep(TimeSpan.FromSeconds(0.01));
            }

            // 设置公式环境
            foreach (var book in allBooks)
            {
                book.evaluator.SetupReferencedWorkbooks(evaluatorEnv);
            }


            Cache.PrepareToExport();
        }