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