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