/// <summary>
/// excel导出sqlite
/// 需要主动连接数据库
/// </summary>
/// <param name="filePath"></param>
public static void Excel2SQLite(string filePath, DBType dbType)
{
filePath = IPath.FormatPathOnUnity3d(filePath); //.Replace("\\", "/").ToLower();
//收集所有的类型
CollectTableTypes();
//
var excelHash = FileHelper.GetMurmurHash3(filePath);
//table判断
SqliteHelper.DB.Connection.CreateTable <ImportExcelLog>();
var table = SqliteHelper.DB.GetTable <ImportExcelLog>();
var importLog = table?.Where((ie) => ie.Path == filePath).FirstOrDefault();
if (importLog == null || !importLog.Hash.Equals(excelHash))
{
//开始导表
var excel = new ExcelExchangeTools(filePath);
var json = excel.GetJson(dbType);
try
{
Json2Sqlite(filePath, json);
}
catch (Exception e)
{
Debug.LogError(e);
EditorUtility.ClearProgressBar();
}
//插入新版本数据
if (importLog == null)
{
importLog = new ImportExcelLog();
importLog.Path = filePath;
importLog.Hash = excelHash;
importLog.Date = DateTime.Now.ToString("yyyyMMdd HH:mm:ss");
;
importLog.UnityVersion = Application.unityVersion;
SqliteHelper.DB.Insert(importLog);
}
else
{
importLog.Hash = excelHash;
importLog.Date = DateTime.Now.ToString();
;
importLog.UnityVersion = Application.unityVersion;
SqliteHelper.DB.Connection.Update(importLog);
}
}
else
{
Debug.Log($"<color=green>【Excel2Sql】内容一致,无需导入 {Path.GetFileName(filePath)} - Hash :{excelHash} </color>");
}
}
public async Task <ImportExcelLog> SaveLog(ImportExcelLog obj)
{
return(await WithConnection(async c =>
{
string sql = $@" INSERT INTO import_excel_log VALUES
( 0,@FileName,@Field, @RecordNum,@CreatedTime, @CreatedBy);
";
sql += "SELECT LAST_INSERT_ID() ";
int newid = await c.QueryFirstOrDefaultAsync <int>(sql, obj);
obj.ID = newid;
return obj;
}));
}
public async Task <ApiResult> Import(IFormFile file)
{
ApiResult ret = new ApiResult();
ret.code = Code.ImportError;
try
{
string fileName = Path.GetFileNameWithoutExtension(file.FileName);
ImportExcelConfig oneConfig = await _importExcelConfigRepo.GetByFileName(fileName);
ImportExcelClass oneClass = await _importExcelConfigRepo.GetClassByID(oneConfig.ClassID);
ImportExcelHelper importExcelHelper = new ImportExcelHelper(oneClass.FullName, oneClass.AssemblyName);
string[] config = oneConfig.Config.Split(',');
string[] required = oneConfig.Required.Split(',');
string errMsg = "";
ImportExcelLog log = new ImportExcelLog();
DataTable dt = importExcelHelper.GetData(file, config, required, userID, ref log, ref errMsg);
if (errMsg != "")
{
ret.msg = errMsg;
return(ret);
}
DateTime now = DateTime.Now;
int i = 0;
foreach (DataRow row in dt.Rows)
{
i++;
foreach (DataColumn col in dt.Columns)
{
if (row["online_date"].ToString() == "")
{
errMsg += "第" + i + "行上线时间必填";
}
else if (row["medium_repair"].ToString() == "")
{
errMsg += "第" + i + "行中修频率必填";
}
else if (row["large_repair"].ToString() == "")
{
errMsg += "第" + i + "行大修频率必填";
}
if (errMsg != "")
{
ret.msg = errMsg;
return(ret);
}
else
{
DateTime onLine = Convert.ToDateTime(row["online_date"]);
int mediumRepair = Convert.ToInt32(row["medium_repair"]);
int largeRepair = Convert.ToInt32(row["large_repair"]);
row["next_medium_repair_date"] = onLine.AddDays(mediumRepair);
row["next_large_repair_date"] = onLine.AddDays(largeRepair);
}
//没有导入的非字符串字段需要默认值
if (row[col].ToString() == "")
{
string typeName = col.DataType.Name;
switch (typeName)
{
case "Int32":
case "Double":
row[col] = 0;
break;
case "DateTime":
row[col] = new DateTime(1970, 1, 1);
break;
}
}
}
//系统赋值
row["created_by"] = userID;
row["updated_by"] = userID;
row["created_time"] = now;
row["updated_time"] = now;
row["is_del"] = 0;
}
log.CreatedBy = userID;
log.CreatedTime = now;
dt.TableName = "equipment_copy";
using (TransactionScope scope = new TransactionScope())
{
ret.data = _importExcelConfigRepo.BulkLoad(dt);
await _importExcelConfigRepo.SaveLog(log);
scope.Complete();
}
ret.code = Code.Success;
return(ret);
}
catch (Exception ex)
{
ret.code = Code.Failure;
ret.msg = ex.Message;
return(ret);
}
}
