// 导入Excel
private void barBtnImport_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
Dictionary <string, string> error = new Dictionary <string, string>();
string msg = string.Empty;
try
{
SplashScreenManager.ShowForm(typeof(DevWaitForm));
// STEP1：导入系统，验证单元格格式
var ds = ImportExcel.ReadExcelToDataSet(openFileDialog1.FileName);
for (int i = 0; i < ds.Tables["TEMPLATE"].Columns.Count; i++)
{
if (ds.Tables[0].Columns[i].ColumnName.Equals("车辆制造/进口日期"))
{
for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
{
if (ds.Tables[0].Rows[j][i].GetType() != typeof(System.DBNull))
{
if (ds.Tables[0].Rows[j][i].GetType() != typeof(System.DateTime))
{
MessageBox.Show(String.Format("【{1}】列中第【{0}】行的单元格格式不正确，应为日期格式!", j + 2, ds.Tables[0].Columns[i].ColumnName), "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
}
}
else
{
for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
{
if (ds.Tables[0].Rows[j][i].GetType() != typeof(System.DBNull))
{
if (ds.Tables[0].Rows[j][i].GetType() != typeof(System.String))
{
MessageBox.Show(String.Format("【{1}】列中第【{0}】行的单元格格式不正确，应为文本格式!", j + 2, ds.Tables[0].Columns[i].ColumnName), "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
}
}
}
// STEP2：替换列名，验证参数的数值
var dt = ImportExcel.SwitchCLJBXXColumnName(ds);
if (dt != null)
{
error = DataVerifyHelper.VerifyCLJBXXData(dt);
if (error.Count == 0)
{
using (OracleConnection conn = new OracleConnection(OracleHelper.conn))
{
conn.Open();
using (OracleTransaction trans = conn.BeginTransaction())
{
// STEP3：验证无误，导入系统数据库
foreach (DataRow dr in dt.Rows)
{
try
{
string exist = OracleHelper.ExecuteScalar(trans, string.Format("SELECT COUNT(*) FROM OCN_CLJBXX WHERE OPERATION!='4' AND SC_OCN='{0}'", dr["SC_OCN"])).ToString();
int existNum = string.IsNullOrEmpty(exist) ? 0 : Convert.ToInt32(exist);
if (existNum > 0)
{
error.Add(dr["SC_OCN"].ToString().Trim(), "系统已经存在改生产OCN的整车基础数据！");
continue;
}
OracleParameter[] parameters =
{
new OracleParameter("SC_OCN", OracleDbType.NVarchar2, 255),
new OracleParameter("XT_OCN", OracleDbType.NVarchar2, 255),
new OracleParameter("MI_XT_OCN", OracleDbType.NVarchar2, 255),
new OracleParameter("TYMC", OracleDbType.NVarchar2, 255),
new OracleParameter("CLXH", OracleDbType.NVarchar2, 255),
new OracleParameter("PFBZ", OracleDbType.NVarchar2, 255),
new OracleParameter("SFJKQC", OracleDbType.NVarchar2, 255),
new OracleParameter("QCSCQY", OracleDbType.NVarchar2, 255),
new OracleParameter("JKQCZJXS", OracleDbType.NVarchar2, 255),
new OracleParameter("JCJGMC", OracleDbType.NVarchar2, 255),
new OracleParameter("BGBH", OracleDbType.NVarchar2, 255),
new OracleParameter("BAH", OracleDbType.NVarchar2, 255),
new OracleParameter("CLZZRQ", OracleDbType.Date),
new OracleParameter("CLZL", OracleDbType.NVarchar2, 255),
new OracleParameter("YYC", OracleDbType.NVarchar2, 255),
new OracleParameter("QDXS", OracleDbType.NVarchar2, 255),
new OracleParameter("ZWPS", OracleDbType.NVarchar2, 255),
new OracleParameter("ZGCS", OracleDbType.NVarchar2, 255),
new OracleParameter("EDZK", OracleDbType.NVarchar2, 255),
new OracleParameter("LTGG", OracleDbType.NVarchar2, 255),
new OracleParameter("LJ", OracleDbType.NVarchar2, 255),
new OracleParameter("ZJ", OracleDbType.NVarchar2, 255),
new OracleParameter("RLLX", OracleDbType.NVarchar2, 255),
new OracleParameter("YHDYBAH", OracleDbType.NVarchar2, 255),
new OracleParameter("ZCZBZL", OracleDbType.NVarchar2, 255),
new OracleParameter("ZDSJZZL", OracleDbType.NVarchar2, 255),
new OracleParameter("ZHGKRLXHL", OracleDbType.NVarchar2, 255),
new OracleParameter("RLXHLMBZ", OracleDbType.NVarchar2, 255),
new OracleParameter("JDBZMBZ4", OracleDbType.NVarchar2, 255),
new OracleParameter("BSQXS", OracleDbType.NVarchar2, 255),
new OracleParameter("PL", OracleDbType.NVarchar2, 255),
new OracleParameter("CDDQDMSZHGKXHLC", OracleDbType.NVarchar2, 255),
new OracleParameter("OPERATION", OracleDbType.NVarchar2, 255),
new OracleParameter("CREATE_TIME", OracleDbType.Date),
new OracleParameter("CREATE_ROLE", OracleDbType.NVarchar2, 255),
new OracleParameter("UPDATE_TIME", OracleDbType.Date),
new OracleParameter("UPDATE_ROLE", OracleDbType.NVarchar2, 255),
new OracleParameter("VERSION", OracleDbType.Int32),
};
parameters[0].Value = dr["SC_OCN"].ToString().Trim();
parameters[1].Value = dr["XT_OCN"].ToString().Trim();
parameters[2].Value = dr["MI_XT_OCN"].ToString().Trim();
parameters[3].Value = dr["TYMC"].ToString().Trim();
parameters[4].Value = dr["CLXH"].ToString().Trim();
parameters[5].Value = dr["PFBZ"].ToString().Trim();
parameters[6].Value = dr["SFJKQC"].ToString().Trim();
parameters[7].Value = dr["QCSCQY"].ToString().Trim();
parameters[8].Value = dr["JKQCZJXS"].ToString().Trim();
parameters[9].Value = dr["JCJGMC"].ToString().Trim();
parameters[10].Value = dr["BGBH"].ToString().Trim();
parameters[11].Value = dr["BAH"].ToString().Trim();
parameters[12].Value = dr["CLZZRQ"];
parameters[13].Value = dr["CLZL"].ToString().Trim();
parameters[14].Value = dr["YYC"].ToString().Trim();
parameters[15].Value = dr["QDXS"].ToString().Trim();
parameters[16].Value = dr["ZWPS"].ToString().Trim();
parameters[17].Value = dr["ZGCS"].ToString().Trim();
parameters[18].Value = dr["EDZK"].ToString().Trim();
parameters[19].Value = dr["LTGG"].ToString().Trim();
parameters[20].Value = dr["LJ"].ToString().Trim();
parameters[21].Value = dr["ZJ"].ToString().Trim();
parameters[22].Value = dr["RLLX"].ToString().Trim();
parameters[23].Value = dr["YHDYBAH"].ToString().Trim();
parameters[24].Value = dr["ZCZBZL"].ToString().Trim();
parameters[25].Value = dr["ZDSJZZL"].ToString().Trim();
parameters[26].Value = dr["ZHGKRLXHL"].ToString().Trim();
parameters[27].Value = dr["RLXHLMBZ"].ToString().Trim();
parameters[28].Value = dr["JDBZMBZ4"].ToString().Trim();
parameters[29].Value = dr["BSQXS"].ToString().Trim();
parameters[30].Value = dr["PL"].ToString().Trim();
parameters[31].Value = dr["CDDQDMSZHGKXHLC"].ToString().Trim();
parameters[32].Value = "0";
parameters[33].Value = System.DateTime.Today;
parameters[34].Value = Utils.localUserId;
parameters[35].Value = System.DateTime.Today;
parameters[36].Value = Utils.localUserId;
parameters[37].Value = 0;
OracleHelper.ExecuteNonQuery(trans, "Insert into OCN_CLJBXX (SC_OCN,XT_OCN,MI_XT_OCN,TYMC,CLXH,PFBZ,SFJKQC,QCSCQY,JKQCZJXS,JCJGMC,BGBH,BAH,CLZZRQ,CLZL,YYC,QDXS,ZWPS,ZGCS,EDZK,LTGG,LJ,ZJ,RLLX,YHDYBAH,ZCZBZL,ZDSJZZL,ZHGKRLXHL,RLXHLMBZ,JDBZMBZ4,BSQXS,PL,CDDQDMSZHGKXHLC,OPERATION,CREATE_TIME,CREATE_ROLE,UPDATE_TIME,UPDATE_ROLE,VERSION) values (:SC_OCN,:XT_OCN,:MI_XT_OCN,:TYMC,:CLXH,:PFBZ,:SFJKQC,:QCSCQY,:JKQCZJXS,:JCJGMC,:BGBH,:BAH,:CLZZRQ,:CLZL,:YYC,:QDXS,:ZWPS,:ZGCS,:EDZK,:LTGG,:LJ,:ZJ,:RLLX,:YHDYBAH,:ZCZBZL,:ZDSJZZL,:ZHGKRLXHL,:RLXHLMBZ,:JDBZMBZ4,:BSQXS,:PL,:CDDQDMSZHGKXHLC,:OPERATION,:CREATE_TIME,:CREATE_ROLE,:UPDATE_TIME,:UPDATE_ROLE,:VERSION)", parameters);
}
catch (Exception ex)
{
error.Add(dr["SC_OCN"].ToString().Trim(), ex.Message);
continue;
}
}
if (trans.Connection != null)
{
trans.Commit();
}
}
}
// STEP4：处理无误，处理完成的文件
if (error.Count == 0)
{
var destFolder = Path.Combine(Path.GetDirectoryName(openFileDialog1.FileName), DateTime.Today.ToLongDateString() + "-整车基础数据-Imported");
Directory.CreateDirectory(destFolder);
try
{
File.Move(openFileDialog1.FileName, Path.Combine(destFolder, String.Format("Imported-{0}{1}", Path.GetFileNameWithoutExtension(openFileDialog1.FileName), Path.GetExtension(openFileDialog1.FileName))));
}
catch (Exception ex)
{
MessageBox.Show(String.Format("Excel处理操作异常：导入完成，{0}", ex.Message), "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
}
foreach (KeyValuePair <string, string> kvp in error)
{
msg += String.Format("{0}\r\n{1}\r\n", kvp.Key, kvp.Value);
}
}
}
catch (Exception ex)
{
MessageBox.Show("Excel导入操作异常：" + ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
finally
{
SplashScreenManager.CloseForm();
}
MessageForm msgForm = new MessageForm(msg + String.Format("\r\n{0}Excel导入操作完成", Path.GetFileNameWithoutExtension(openFileDialog1.FileName)))
{
Text = "整车基础数据导入信息"
};
msgForm.Show();
SearchLocal(1);
}
}
