public ExcelTableLoader(ExcelContext excelContext, string path, string filename)
{
_filepath = path;
_filename = filename;
_excelContext = excelContext;
_excelDataTable = new ExcelDataTable(_filepath, _filename.Remove(_filename.LastIndexOf(".")));
}
/// <summary>
/// 检查关联表的值是否存在。
/// </summary>
/// <param name="columnName"></param>
/// <param name="columnIndex"></param>
/// <param name="associateTable"></param>
/// <param name="associateColumnName"></param>
/// <returns></returns>
private int CheckAssociateTableValues(string columnName, int columnIndex, ExcelDataTable associateTable, string associateColumnName)
{
int associateColumnIndex = associateTable.GetColumnIndex(associateColumnName);
if (associateColumnIndex == -1)
{
return(0);
}
int error = 0;
for (int i = ExcelDataTable.DataColumnStartRow; i < _excelDataTable.DataRowCount; i++)
{
string value = _excelDataTable.GetColumnValue(i, columnIndex);
if ("".Equals(value))
{
continue;
}
if (!associateTable.ExistColumnValue(associateColumnIndex, value))
{
error += 1;
_excelContext.WriteLog(LogLevel.Error, $"表格 {_excelDataTable.DataFileName} {columnName} 列, {i + 1} 行, 字段 {columnName} 关联表 {associateTable.DataFileName} 的值 {value} 不存在!");
}
}
return(error);
}
/// <summary>
/// 导出cs代码。
/// </summary>
/// <param name="excelDataTable"></param>
public void ExportDataTable(ExcelDataTable excelDataTable)
{
WriteLog(LogLevel.Debug, $"开始导出数据表: {excelDataTable.DataFileName}");
string classname = StringUtils.ToCamel(excelDataTable.DataFileName);
string filename = _csharpTableCodeOutDirectory
+ Path.DirectorySeparatorChar
+ _codeGenerateClassPrefix
+ classname + ".cs";
using (FileStream fileStream = new FileStream(filename, FileMode.Create))
using (StreamWriter streamWriter = new StreamWriter(fileStream, Encoding.UTF8))
{
streamWriter.WriteLine("using System;");
streamWriter.WriteLine("using System.IO;");
streamWriter.WriteLine("using System.Linq;");
streamWriter.WriteLine("using System.Collections.Generic;");
streamWriter.WriteLine("");
streamWriter.WriteLine($"namespace {_chsarpCodeNameSpace}");
streamWriter.WriteLine("{");
streamWriter.WriteLine($" public partial class {classname}: DataRow");
streamWriter.WriteLine(" {");
// 字段.
foreach (var columnInfo in excelDataTable.GetColumnInfos(ColumnBelong.Client))
{
IColumnParser parser = _columnParserHelp.GetColumnParser(columnInfo.ColumnType);
streamWriter.WriteLine($" /// <summary> {columnInfo.Title} </summary>");
streamWriter.WriteLine($" public {parser.ToCSharpTypeString()} {columnInfo.Name}");
streamWriter.WriteLine(" {");
streamWriter.WriteLine(" get;");
streamWriter.WriteLine(" private set;");
streamWriter.WriteLine(" }");
}
// 解析函数
streamWriter.WriteLine($" public override void ReadRowData(BinaryReader reader)");
streamWriter.WriteLine(" {");
foreach (var columnInfo in excelDataTable.GetColumnInfos(ColumnBelong.Client))
{
IColumnParser parser = _columnParserHelp.GetColumnParser(columnInfo.ColumnType);
streamWriter.WriteLine($" {columnInfo.Name} = {parser.ReadFromBinaryReaderExpression()} ;");
}
streamWriter.WriteLine(" }");
// 主键函数
ColumnInfo pkInfo = excelDataTable.GetPrimaryColumnInfo();
streamWriter.WriteLine(" public override int GetPk()");
streamWriter.WriteLine(" {");
if (pkInfo == null)
{
streamWriter.WriteLine($" return 0;");
}
else
{
streamWriter.WriteLine($" return {pkInfo.Name};");
}
streamWriter.WriteLine(" }");
streamWriter.WriteLine(" }");
streamWriter.WriteLine("}");
}
}
/// <summary>
/// 导出 json 文件.
/// </summary>
/// <param name="dataFileName"></param>
public void ExportJson(string dataFileName, ColumnBelong belong)
{
if (!_excelDataTables.ContainsKey(dataFileName))
{
WriteLog(LogLevel.Error, $"找不到配置表: {dataFileName}");
return;
}
ExcelDataTable dataTable = _excelDataTables[dataFileName];
List <ColumnInfo> columnInfos = dataTable.GetColumnInfos(belong);
using (FileStream writeFile = new FileStream(GetJsonExportFileName(dataTable), FileMode.Create))
using (TextWriter textWriter = new StreamWriter(writeFile, Encoding.UTF8))
{
Stopwatch stopwatch = Stopwatch.StartNew();
List <Dictionary <string, Object> > list = new List <Dictionary <string, object> >();
for (int i = 0; i < dataTable.DataRowCount; i++)
{
Dictionary <string, object> dictionary = new Dictionary <string, object>();
foreach (var columnInfo in columnInfos)
{
try
{
IColumnParser parser = _columnParserHelp.GetColumnParser(columnInfo.ColumnType);
dictionary.Add(columnInfo.Name, parser.ToObject(columnInfo.GetRowValue(i)));
}
catch (Exception e)
{
WriteLog(LogLevel.Error,
$"表格 {dataTable.DataFileName} {columnInfo.Name} 字段, {i + 5} 行, 值 {columnInfo.GetRowValue(i)}, 导出失败: {e.Message}");
return;
}
}
list.Add(dictionary);
}
textWriter.Write(JsonConvert.SerializeObject(list, Formatting.Indented));
stopwatch.Stop();
WriteLog(LogLevel.Information,
$"线程 {Thread.CurrentThread.ManagedThreadId.ToString()}, 耗时 {stopwatch.ElapsedMilliseconds / 1000} 秒 , 导出 json 文件: {dataFileName}");
}
}
/// <summary>
/// 之前旧版的字节流格式。
/// </summary>
/// <param name="dataFileName"></param>
/// <param name="columnBelong"></param>
public void ExportBytes(string dataFileName, ColumnBelong columnBelong)
{
if (!_excelDataTables.ContainsKey(dataFileName))
{
WriteLog(LogLevel.Error, $"找不到配置表: {dataFileName}");
return;
}
ExcelDataTable dataTable = _excelDataTables[dataFileName];
List <ColumnInfo> columnInfos = dataTable.GetColumnInfos(columnBelong);
try
{
using (FileStream writeFile = new FileStream(GetBinaryExportFileName(dataTable), FileMode.Create))
using (BinaryWriter binaryWriter = new BinaryWriter(writeFile, Encoding.UTF8))
{
Stopwatch stopwatch = Stopwatch.StartNew();
for (int i = 0; i < dataTable.DataRowCount; i++)
{
foreach (var columnInfo in columnInfos)
{
try
{
IColumnParser parser = _columnParserHelp.GetColumnParser(columnInfo.ColumnType);
parser.WriteToBinaryWriter(binaryWriter, columnInfo.GetRowValue(i));
}
catch (Exception e)
{
WriteLog(LogLevel.Error,
$"表格 {dataTable.DataFileName} {columnInfo.Name} 字段, {i + 5} 行, 值 {columnInfo.GetRowValue(i)}, 导出失败: {e.Message}");
throw e;
}
}
}
stopwatch.Stop();
WriteLog(LogLevel.Information,
$"线程 {Thread.CurrentThread.ManagedThreadId.ToString()}, 耗时 {stopwatch.ElapsedMilliseconds / 1000} 秒 , 导出 byte 文件: {dataFileName}");
}
}
catch (Exception e)
{
File.Delete(GetBinaryExportFileName(dataTable));
}
}
/// <summary>
/// 检查表关联约束。
/// </summary>
/// <returns></returns>
public int CheckAssociationConstraint()
{
List <TableConstraint> tableConstraints = GetTableConstraint(ConstraintType.Association);
int error = 0;
foreach (var constraint in tableConstraints)
{
ColumnInfo info = GetColumnInfo(constraint.ColumnName);
if (info == null)
{
error += 1;
_excelContext.WriteLog(LogLevel.Error, $"表格 {_excelDataTable.DataFileName} 枚举约束字段 {constraint.ColumnName} 不存在!");
continue;
}
string[] values = constraint.ConstraintParams.Split('|');
string associateTableName = values[0];
string associateColumn = values[1];
if (!_excelContext.ExcelDataTables.ContainsKey(associateTableName))
{
error += 1;
_excelContext.WriteLog(LogLevel.Error, $"表格 {_excelDataTable.DataFileName} 关联表 {associateTableName} 不存在!");
continue;
}
ExcelDataTable associateTable = _excelContext.ExcelDataTables[associateTableName];
if (!associateTable.HasColumnName(associateColumn))
{
error += 1;
_excelContext.WriteLog(LogLevel.Error, $"表格 {_excelDataTable.DataFileName} 关联表 {associateTableName} 字段 {associateColumn} 不存在!");
continue;
}
error += CheckAssociateTableValues(constraint.ColumnName, info.ColumnIndex, associateTable, associateColumn);
}
return(error);
}
public ExcelDataTableCheck(ExcelContext excelContext, ExcelDataTable excelDataTable)
{
_excelContext = excelContext;
_excelDataTable = excelDataTable;
}
private void AddExcelTable(ExcelDataTable excelDataTable)
{
Monitor.Enter(LoadTableBytes);
_excelDataTables.Add(excelDataTable.DataFileName, excelDataTable);
Monitor.Exit(LoadTableBytes);
}
/// <summary>
/// 导出json的文件名。
/// </summary>
/// <param name="excelDataTable"></param>
/// <returns></returns>
public string GetJsonExportFileName(ExcelDataTable excelDataTable)
{
return(_serverDataOutDirectory + Path.DirectorySeparatorChar + excelDataTable.DataFileName + ".json");
}
/// <summary>
/// 导出二进制字节流。
/// </summary>
/// <param name="excelDataTable"></param>
/// <returns></returns>
public string GetBinaryExportFileName(ExcelDataTable excelDataTable)
{
return(_clientDataOutDirectory + Path.DirectorySeparatorChar + excelDataTable.DataFileName + ".byte");
}