/// <summary> /// 导入数据 /// </summary> /// <param name="template"></param> /// <param name="fileFullName"></param> public static void ImportData(string config, string fileFullName) { ImportTemplateStructure its = ImportTemplateStructure.GetFromConfig(config); string filePath = FileService.GetFilePathByFullID(fileFullName); ImportData(its, filePath); }
/// <summary> /// 导入数据 /// </summary> /// <param name="tmplCode">模板编码</param> /// <param name="fileFullName"></param> public static void ImportDataByTemplateCode(string tmplCode, string fileFullName) { SysDataImportTemplate tmplEnt = SysDataImportTemplate.FindFirstByProperties("Code", tmplCode); ImportTemplateStructure its = ImportTemplateStructure.GetFromConfig(tmplEnt.Config); string filePath = FileService.GetFilePathByFullID(fileFullName); ImportData(its, filePath); }
/// <summary> /// 获取EasyDictionary数据 /// </summary> /// <param name="struc">模版结构</param> /// <param name="filePath">文件路径</param> /// <returns></returns> public static DataTable GetDataTable(string config, string fileFullName) { ImportTemplateStructure struc = ImportTemplateStructure.GetFromConfig(config); string filePath = FileService.GetFilePathByFullID(fileFullName); DataTable dt = GetDataTable(struc, filePath); return(dt); }
/// <summary> /// 导入数据 /// </summary> /// <param name="struc"></param> /// <param name="targetTable"></param> /// <param name="primaryKey"></param> /// <param name="filePath"></param> /// <param name="blockSize"></param> public static void ImportData(ImportTemplateStructure struc, string targetTable, SqlConnection sqlConnection, string filePath) { int blockSize = 0; if (struc.DefaultGroup.PropertyNode.BlockSize.HasValue) { blockSize = struc.DefaultGroup.PropertyNode.BlockSize.Value; } ImportData(struc, targetTable, sqlConnection, filePath, blockSize); }
/// <summary> /// 当模版文件变化时操作 /// </summary> /// <param name="ent"></param> public static void DoImportTemplateFileChanged(SysDataImportTemplate ent) { // 当模版文件有变化是,同时更新下载文件与配置模版 string filePath = FileService.GetFilePathByFullID(ent.TemplateFileID.TrimEnd(',')); ImportTemplateParser itp = new ImportTemplateParser(filePath); ImportTemplateStructure its = itp.GetStructure(); ent.Config = its.GetConfig(); ent.DownloadFileID = ent.TemplateFileID; // 这里下载文件与模版文件为同一文件 }
/// <summary> /// 获取EasyDictionary数据 /// </summary> /// <param name="tmplCode">模版编码</param> /// <param name="filePath">文件路径</param> /// <returns></returns> public static DataTable GetDataTableByTemplateCode(string tmplCode, string fileFullName) { SysDataImportTemplate tmplEnt = SysDataImportTemplate.FindFirstByProperties("Code", tmplCode); ImportTemplateStructure struc = ImportTemplateStructure.GetFromConfig(tmplEnt.Config); string filePath = FileService.GetFilePathByFullID(fileFullName); DataTable dt = GetDataTable(struc, filePath); return(dt); }
/// <summary> /// 获取DataTable数据 /// </summary> /// <param name="struc">模版结构</param> /// <param name="filePath">文件路径</param> /// <returns></returns> public static DataTable GetDataTable(ImportTemplateStructure struc, string filePath) { // 获取最大Table IList <DataTable> dts = GetDataTableList(struc, filePath, 0); if (dts.Count > 0) { return(dts[0]); } return(null); }
/// <summary> /// 导入数据 /// </summary> /// <param name="struc">模版结构</param> /// <param name="targetTable">目标表</param> /// <param name="primaryKey">表主键</param> /// <param name="filePath">文件路径</param> /// <param name="blockSize">数据块大小</param> public static void ImportData(ImportTemplateStructure struc, string targetTable, SqlConnection sqlConnection, string filePath, int blockSize) { string primaryKey = String.Empty; // 获取主键 string[] primaryKeys = SysTableStructureRule.GetTablePrimaryKey(targetTable); if (primaryKeys.Length == 1) { primaryKey = primaryKeys[0]; } IIdentifierGenerator idGenerator = struc.DefaultGroup.GetIDGenerator(); IList <DataTable> dts = DataImportService.GetDataTableList(struc, filePath, blockSize); int successAmount = 0; foreach (DataTable tdt in dts) { try { if (!String.IsNullOrEmpty(primaryKey)) { if (!tdt.Columns.Contains(primaryKey)) { tdt.Columns.Add(primaryKey); } foreach (DataRow tdrow in tdt.Rows) { tdrow[primaryKey] = idGenerator.Generate(null, null); } } DataHelper.CopyDataToDatabase(tdt, sqlConnection, targetTable); successAmount += tdt.Rows.Count; } catch (Exception ex) { throw new Exception("导入数据失败,已完成导入“" + successAmount + "”条。" + ex.Message); } } }
/// <summary> /// 获取模版结构 /// </summary> /// <param name="fileId"></param> /// <returns></returns> public ImportTemplateStructure GetStructure() { ImportTemplateStructure struc = new ImportTemplateStructure(); // 解析列节点 foreach (ExcelCell tec in ExcelCellList) { ImportTemplateColumnNodeConfigProcessor tproc = new ImportTemplateColumnNodeConfigProcessor(); ImportTemplateColumnNode tnode = tproc.GetObject(tec.Comment); if (tnode != null) { if (!tnode.ValueColumnIndex.HasValue) { tnode.ValueColumnIndex = tec.ColumnIndex; } if (!tnode.ValueRowIndex.HasValue) { tnode.ValueRowIndex = tec.RowIndex; } if (struc.DefaultGroup.ColumnNodeList.Count(tent => tent.ColumnName == tnode.ColumnName) <= 0) { struc.DefaultGroup.ColumnNodeList.Add(tnode); } else { throw new Exception("不合法模版:模版中存在同名数据列“" + tnode.ColumnName + "”。"); } } } // 解析命令节点 foreach (ExcelCell tec in ExcelCellList) { ImportTemplateCommandNodeConfigProcessor tproc = new ImportTemplateCommandNodeConfigProcessor(); ImportTemplateCommandNode tnode = tproc.GetObject(tec.Comment); if (tnode != null) { tnode.ColumnIndex = tec.ColumnIndex; tnode.RowIndex = tec.RowIndex; struc.DefaultGroup.CommandNodeList.Add(tnode); } } // 解析属性节点 foreach (ExcelCell tec in ExcelCellList) { ImportTemplatePropertyNodeConfigProcessor tproc = new ImportTemplatePropertyNodeConfigProcessor(); ImportTemplatePropertyNode tnode = tproc.GetObject(tec.Comment); if (tnode != null) { struc.DefaultGroup.PropertyNodeList.Add(tnode); } } return(struc); }
/// <summary> /// 导入数据 /// </summary> /// <param name="struc"></param> /// <param name="targetTable"></param> /// <param name="filePath"></param> public static void ImportData(ImportTemplateStructure struc, string targetTable, string filePath) { SqlConnection sqlConnection = DataHelper.GetCurrentDbConnection() as SqlConnection; ImportData(struc, targetTable, sqlConnection, filePath, 0); }
/// <summary> /// 导入数据 /// </summary> /// <param name="struc"></param> /// <param name="filePath"></param> /// <param name="blockSize"></param> public static void ImportData(ImportTemplateStructure struc, string filePath) { SqlConnection sqlConnection = DataHelper.GetCurrentDbConnection() as SqlConnection; ImportData(struc, struc.DefaultGroup.PropertyNode.Target, sqlConnection, filePath, 0); }
/// <summary> /// 获取根据blockSize获取DataTable列表 /// </summary> /// <param name="struc">模版结构</param> /// <param name="filePath">文件路径</param> /// <param name="blockSize">每个DataTable最大大小</param> /// <returns></returns> public static IList <DataTable> GetDataTableList(ImportTemplateStructure struc, string filePath, int blockSize) { if (blockSize <= 0) { blockSize = 1000; // 默认一次性处理1000行 } ImportTemplateCommandNode itcnBegin = struc.DefaultGroup.CommandNodeList.First(tent => tent.CommandCode == ImportTemplateCommandCode.Begin); ImportTemplateCommandNode itcnEnd = struc.DefaultGroup.CommandNodeList.First(tent => tent.CommandCode == ImportTemplateCommandCode.End); IList <DataTable> dtList = new List <DataTable>(); DataSet ds = new DataSet(); using (ExcelProcessor processor = ExcelService.GetProcessor(filePath)) { ds = processor.GetDataSet(); } DataTable dt = ds.Tables[0]; IList <ImportTemplateColumnNode> ccnodes = struc.DefaultGroup.GetCommonColumnNodeList(); // 设置公用数据列默认值 foreach (ImportTemplateColumnNode tnode in ccnodes) { // 行第一个“-1”DataSet从第二行开始,行第二个“-1”,列第一个“-1”数组从0开始 tnode.DefaultValue = dt.Rows[tnode.ValueRowIndex.Value - 2][tnode.ValueColumnIndex.Value - 1]; } IList <ImportTemplateColumnNode> ocnodes = struc.DefaultGroup.GetOrdinaireColumnNodeList(); int startRowIndex = itcnBegin.RowIndex; int endRowIndex = itcnEnd.RowIndex; int startColumnIndex = itcnBegin.ColumnIndex; int endColumnIndex = itcnEnd.ColumnIndex; if (itcnBegin.RowIndex == itcnEnd.RowIndex) { // DataSet从第二行开始这里需要+1 endRowIndex = dt.Rows.Count + 1; } DataTable tdt = null; for (int i = startRowIndex; i <= endRowIndex; i++) { if ((i - startRowIndex) % blockSize == 0) { tdt = struc.DefaultGroup.GetDataTableSchema(); dtList.Add(tdt); } DataRow drow = tdt.NewRow(); bool emptyflag = true; for (int j = startColumnIndex; j <= endColumnIndex; j++) { ImportTemplateColumnNode tnode = ocnodes.First(tent => (tent.ValueColumnIndex) == j); if (tnode != null) { object tval = dt.Rows[(i - 2)][(j - 1)]; if (tval != null && tval.ToString().Trim() != String.Empty) { emptyflag = false; } tval = (tval != null ? tval : tnode.DefaultValue); drow[tnode.ColumnName] = tval; } } // 当前行为空行,这跳过执行 if (emptyflag == true) { continue; } // 设置公共列值 foreach (ImportTemplateColumnNode tnode in ccnodes) { drow[tnode.ColumnName] = tnode.DefaultValue; } tdt.Rows.Add(drow); } return(dtList); }
/// <summary> /// 由配置文件获取模版结构 /// </summary> /// <param name="template"></param> /// <returns></returns> public static ImportTemplateStructure GetFromConfig(string config) { ImportTemplateStructure its = JsonHelper.GetObject <ImportTemplateStructure>(config); return(its); }