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