C#中基于XML的Excel中的数据导入Oracle数据库，通过调用封装好的Delphi来解析Xml的思路和代码

发布网友 发布时间：2022-04-10 15:37

我来回答

共1个回答

热心网友 时间：2022-04-10 17:07

string strSqlTmp = "";
if (dlr == DialogResult.Yes)
{
string filePath = "";
OpenFileDialog openFiledialog1 = new OpenFileDialog();
openFiledialog1.Filter = "Excel文件(*.xls)|*.xls";
DialogResult result = openFiledialog1.ShowDialog();
if (DialogResult.OK == result)
{
filePath = openFiledialog1.FileName;

string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";
string sql_excel = "select * from [Sheet1$]";
try
{
OleDbConnection thisconnection = new OleDbConnection(conn);
thisconnection.Open();
OleDbDataAdapter mycommand = new OleDbDataAdapter(sql_excel, thisconnection);

mycommand.Fill(dst, "[Sheet1$]");
}
catch (Exception)
{

MessageBox.Show("打开Excel文件失败,文件格式不正确,可能由于Excel表中表名不是[Sheet1]引起的!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}

//universalDAL.ConnectionString = DBInfos.ConnectionString.ToString();
int insertNumb = 0; //记录插入条数
int updateNumb = 0; //记录更新条数
int compareEqualIndex = -1; //判断是更新还是插入操作
if (dst.Tables[0].Columns.Count == ds.Tables[0].Columns.Count)
{
for (int i = 0; i < dst.Tables[0].Rows.Count; i++)
{
for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
{
if (string.Compare(dst.Tables[0].Rows[i][0].ToString(), ds.Tables[0].Rows[j][0].ToString()) == 0)
{
compareEqualIndex = i;
}
}
if (compareEqualIndex != -1)
{
strSqlTmp = "UPDATE PAY SET name = '" + dst.Tables[0].Rows[i][1].ToString() + "', " +
"sex = '" + dst.Tables[0].Rows[i][2].ToString() + "', " +
"daynum = '" + dst.Tables[0].Rows[i][3].ToString() + "' " +
"wages = '" + dst.Tables[0].Rows[i][4].ToString() + "' " +
"subsidy = '" + dst.Tables[0].Rows[i][5].ToString() + "' " +
"comm = '" + dst.Tables[0].Rows[i][6].ToString() + "' " +
"JBF = '" + dst.Tables[0].Rows[i][7].ToString() + "' " +
"OTHER1 = '" + dst.Tables[0].Rows[i][8].ToString() + "' " +
"DELIVER = '" + dst.Tables[0].Rows[i][9].ToString() + "' " +
"CHECK_ON = '" + dst.Tables[0].Rows[i][10].ToString() + "' " +
"MALINS = '" + dst.Tables[0].Rows[i][11].ToString() + "' " +
"OLDAGE = '" + dst.Tables[0].Rows[i][12].ToString() + "' " +
"OTHER2 = '" + dst.Tables[0].Rows[i][13].ToString() + "' " +
"DETAIN = '" + dst.Tables[0].Rows[i][14].ToString() + "' " +
"SAL = '" + dst.Tables[0].Rows[i][15].ToString() + "' " +
"AREA = '" + dst.Tables[0].Rows[i][16].ToString() + "' " +
"BRANCH = '" + dst.Tables[0].Rows[i][17].ToString() + "' " +
"JOB = '" + dst.Tables[0].Rows[i][18].ToString() + "' " +
"WHERE NUM = '" + dst.Tables[0].Rows[i][0].ToString() + "'";
//universalDAL.ExecuteNonQuery(strSqlTmp);

updateNumb++;
compareEqualIndex = -1;
}
else
{
strSqlTmp = "INSERT INTO PAY(num,name,sex,daynum,wages,subsidy,comm,JBF,OTHER1,DELIVER,CHECK_ON,MALINS,OLDAGE,OTHER2,DETAIN,SAL,AREA,BRANCH,JOB)" +
"VALUES('" + dst.Tables[0].Rows[i][0].ToString() + "'," +
"'" + dst.Tables[0].Rows[i][1].ToString() + "'," +
"'" + dst.Tables[0].Rows[i][2].ToString() + "'," +
"'" + dst.Tables[0].Rows[i][3].ToString() + "'," +
"'" + dst.Tables[0].Rows[i][4].ToString() + "'," +
"'" + dst.Tables[0].Rows[i][5].ToString() + "'," +
"'" + dst.Tables[0].Rows[i][6].ToString() + "'," +
"'" + dst.Tables[0].Rows[i][7].ToString() + "'," +
"'" + dst.Tables[0].Rows[i][8].ToString() + "'," +
"'" + dst.Tables[0].Rows[i][9].ToString() + "'," +
"'" + dst.Tables[0].Rows[i][10].ToString() + "'," +
"'" + dst.Tables[0].Rows[i][11].ToString() + "'," +
"'" + dst.Tables[0].Rows[i][12].ToString() + "'," +
"'" + dst.Tables[0].Rows[i][13].ToString() + "'," +
"'" + dst.Tables[0].Rows[i][14].ToString() + "'," +
"'" + dst.Tables[0].Rows[i][15].ToString() + "'," +
"'" + dst.Tables[0].Rows[i][16].ToString() + "'," +
"'" + dst.Tables[0].Rows[i][17].ToString() + "'," +
"'" + dst.Tables[0].Rows[i][18].ToString() + "')";
int intinset=DbHelperOra.ExecuteSql(strSqlTmp);
insertNumb++;
}
}
MessageBox.Show("成功更新" + updateNumb + "条记录,插入" + insertNumb + "条记录!", "提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
DataShow(" 1=1");
}
else
{
MessageBox.Show("Excel文件列的格式不符合数据字典信息列的格式", "提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
}
}
}

}