public static (bool success, string errMsg) CheckConfig(csExcelToDB eo) { if (eo == null) { return(false, "配置错误"); } if (eo.DBType != DB.ORACLE && eo.DBType != DB.SQLSERVER) { return(false, "数据库类型错误"); } if (string.IsNullOrWhiteSpace(eo.ConnStr)) { return(false, "连接字符串错误"); } if (eo.Sheets == null || eo.Sheets.Count == 0) { return(false, "Sheet节点存在错误"); } if (eo.File.Contains("{today}")) { eo.File = eo.File.Replace("{today}", $"{DateTime.Now:D}"); } if (!File.Exists(eo.File)) { return(false, $"文件{eo.File}不存在"); } foreach (var sheet in eo.Sheets) { if (string.IsNullOrWhiteSpace(sheet.SheetName)) { return(false, "SheetName不能为空"); } if (sheet.Fileds == null || sheet.Fileds.Count == 0) { return(false, "Filed节点存在错误"); } foreach (var filed in sheet.Fileds) { if (string.IsNullOrEmpty(filed.FldName)) { return(false, "FldName不能为空"); } if (string.IsNullOrEmpty(filed.RowName)) { return(false, "配置错误"); } } } return(true, null); }
private static string ToOracle(csExcelToDB eo, string[] sheetNames, DataSet ds) { var execMsg = string.Empty; try { logger.Info("开始入库数据"); OracleHelper.connStr = eo.ConnStr; foreach (var sheet in eo.Sheets) { var _sheetName = $"{sheet.SheetName}$"; if (sheetNames.Contains(_sheetName)) { DataTable dt = ds.Tables[_sheetName]; string sql = sheet.Sql; int index = 2; foreach (DataRow row in dt.Rows) { try { OracleParameter[] paras = new OracleParameter[sheet.Fileds.Count]; for (int i = 0; i < sheet.Fileds.Count; i++) { var filed = sheet.Fileds[i]; //paras[i] = new OracleParameter(filed.FldName, GetOracleDbType(filed.FldType)); //paras[i].Value = row[filed.RowName] ?? DBNull.Value; paras[i] = new OracleParameter(filed.FldName, row[filed.RowName] ?? DBNull.Value); } OracleHelper.ExecuteNonQuery(sql, paras); execMsg += $"\nSheet【{sheet.SheetName}】第{index}行执行成功"; } catch (Exception ex) { execMsg += $"\nSheet【{sheet.SheetName}】第{index}行执行失败:{ex.Message}"; } index++; } } } } catch (Exception ex) { logger.Error("导入数据失败:" + ex.Message); execMsg = "导入数据失败:" + ex.Message + "\n"; } return(execMsg); }
public static (bool success, string errMsg) ToOracle(csExcelToDB eo) { //get sheetnames var getExcelSheets = GetExcelSheets(eo.File); if (!getExcelSheets.success) { return(false, "获取SheetNames失败"); } //get excel data var getExcelDs = GetExcelDs(eo, getExcelSheets.sheetNames); if (!getExcelDs.success) { return(false, "获取Excel数据失败"); } //into db var execMsg = ToOracle(eo, getExcelSheets.sheetNames, getExcelDs.ds); return(true, execMsg); }
private static (bool success, DataSet ds) GetExcelDs(csExcelToDB eo, string[] sheetNames) { try { logger.Info("开始获取Excel数据"); //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @path + ";" + "Extended Properties=Excel 8.0;"; string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + @eo.File + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'"; //HDR=yes第一行作为列名;IMEX=1将所有读入数据看作字符 OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); OleDbDataAdapter myCommand = null; DataSet ds = new DataSet(); foreach (var sheetName in sheetNames) { DataTable dt = new DataTable(); string where = string.Empty; var sheet = eo.Sheets.Where(e => (e.SheetName + "$") == sheetName).FirstOrDefault(); if (sheet == null) { continue; } sheet.Fileds.ForEach(e => { where += string.IsNullOrEmpty(where) ? $" where [{e.RowName}] is not null" : $" or [{e.RowName}] is not null"; }); string sql = $@"select * from [{sheetName}] {where}"; myCommand = new OleDbDataAdapter(sql, strConn); myCommand.Fill(dt); dt.TableName = sheetName; ds.Tables.Add(dt); } logger.Info("获取Excel数据成功"); return(true, ds); } catch (Exception ex) { logger.Error("获取Excel数据失败"); return(false, null); } }
public static (bool success, string errMsg) ToDB(csExcelToDB eo) { return(eo.DBType == DB.ORACLE ? ToOracle(eo) : ToSqlServer(eo)); }