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);
}
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);
}
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);
}
}
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);
}
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);
}
}
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();
}
