public bool ImportFrom(ExcelImportBase import) { string columNames = import.ColumnNames.Aggregate(new StringBuilder(), (current, next) => current.Append(current.Length == 0 ? "" : ",").Append(next)).ToString(); using (var trx = conn.BeginTransaction()) { int rowCount = 0; try { var truncateCmd = conn.CreateCommand(); truncateCmd.CommandText = $"DELETE FROM {import.TableName}"; truncateCmd.ExecuteNonQuery(); var query = $"COPY {import.TableName} ({columNames}) FROM STDIN (FORMAT BINARY)"; using (var writer = conn.BeginBinaryImport($"COPY {import.TableName} ({columNames}) FROM STDIN (FORMAT BINARY)")) { rowCount = 1; import.ResetId(); foreach (var r in import.GetRows()) { rowCount++; var result = import.GetValues(r); if (result == null) { continue; } writer.WriteRow(result); } writer.Complete(); } } catch (PostgresException e) { trx.Rollback(); Console.WriteLine($"Excel Name: {import.eName}"); Console.WriteLine($"Row: {rowCount}"); Console.WriteLine($"Msg: {e.Message}"); Console.WriteLine($"Detail: {e.Detail}"); return(false); } catch (Exception e) { trx.Rollback(); Console.WriteLine($"Excel Name: {import.eName}"); Console.WriteLine($"Row: {rowCount}"); Console.WriteLine($"Msg: {e.Message}"); return(false); } trx.Commit(); } Console.WriteLine($"{import.TableName} 완료"); return(true); }
/// <summary> /// 删除行 /// </summary> /// <param name="filePath"></param> /// <param name="className"></param> /// <param name="acctId"></param> /// <returns></returns> public ActionResult DeleteRow(string filePath, string className, int rowNo, string keyValue) { ExcelImportBase importApp = CreateInstance(className); if (importApp == null) { return(Error("创建反射实例失败!")); } importApp.Delete(filePath, rowNo, keyValue); return(Success("删除成功!")); }
public static bool TryCopyToDB(PostgreSql db, ExcelImportBase import) { var dbRows = new List <Dictionary <String, object> >(); if (!db.ExecuteQuery($"select * from {import.TableName};", ref dbRows)) { Console.WriteLine($"DB query failed, {import.TableName}"); return(false); } var dbValues = new List <object[]>(); if (!GetAllValues(dbRows, import.ColumnNames, ref dbValues)) { Console.WriteLine($"Get db query failed, {import.TableName}"); return(false); } var tableValues = new List <object[]>(); import.ResetId(); foreach (var row in import.GetRows()) { try { var tableValue = import.GetValues(row); tableValues.Add(tableValue); } catch (Exception e) { errorMsgQ.Enqueue($"{import.FileName} 테이블에서 예외가 발생하였습니다."); return(false); } } var addedTable = new List <object[]>(); var missingTable = new List <object[]>(); if (!GetAllChanges(ref tableValues, ref dbValues, ref addedTable, ref missingTable)) { Console.WriteLine($"{import.TableName} 테이블 데이터의 바뀐 내용이 없습니다."); return(true); } else { var modifiedTable = new List <object[]>(); GetModifiedTable(ref addedTable, ref modifiedTable, ref missingTable, import.FileName); var changes = new Tuple <ExcelImportBase, List <object[]>, List <object[]>, List <object[]> >(import, addedTable, modifiedTable, missingTable); changesQ.Enqueue(changes); Console.WriteLine($"{import.TableName} 테이블의 수정 사항이 생겼습니다."); return(true); } }
public ActionResult GetErrorItemCollection(string filePath, string className, string[] keyValue) { ExcelImportBase importApp = CreateInstance(className); if (importApp == null) { throw new Exception("创建反射实例失败!"); } var data = importApp.Scan(filePath, keyValue); return(Content(data.ToJson())); }
/// <summary> /// 导入数据 /// </summary> /// <param name="filePath"></param> /// <param name="className"></param> /// <param name="acctId"></param> /// <returns></returns> public ActionResult Import(string filePath, string className, string[] keyValue) { ExcelImportBase importApp = CreateInstance(className); var data = importApp.Scan(filePath, keyValue); if (data.Count > 0) { return(Error("导入数据存在未处理的错误!")); } else { importApp.Import(filePath, keyValue); return(Success("数据导入成功!")); } }
public static bool LoadExcelSheet <T>(string dirPath, string sheetName, out ExcelImportBase import) { import = Activator.CreateInstance(typeof(T)) as ExcelImportBase; import.LoadSheetLoader(dirPath, sheetName); if (!import.Import()) { Console.WriteLine($"Excel import failed, {import.TableName}"); return(false); } else { Console.WriteLine("{0}.xlsx 파일을 성공적으로 로딩했습니다.", sheetName); } return(true); }