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