C#读取EXCEL文件内容写入数据库的代码

发布时间:2020-02-24编辑:脚本学堂
C#实现读取excel文件的内容并写入数据库的代码,供大家学习参考。

代码如下:

//读取excel文件内容写入数据库
//http://www.jb200.com
if (this.textBox2.Text.Trim() == "")
 {
     MessageBox.Show("请输入EXCEL文件路径!");
 }
 else
 {
     string fileName = "告警手册-脚本学堂_www.jb200.com.xls";
     string currPath = this.textBox2.Text.Trim();
     if (!currPath.Substring(currPath.Length - 1).Contains(""))
     {
         currPath = currPath + "";
     }
     fileName = currPath + fileName;
}
 try
 {
     string strSheetName = "脚本学堂-RNC";
     string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
     string strExcel = "select * from  [" + strSheetName + "$] ";
     DataSet ds = new DataSet();
     OleDbConnection conn = new OleDbConnection(strConn);
     conn.Open();
     OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, conn);
     adapter.Fill(ds, "data");
     conn.Close();
     DataTable excelDT = ds.Tables["data"];//EXCEL表
     OracleConnection ocon = new System.Data.OracleClient.OracleConnection("data source=cqwcdma;user id=ifms_test;pwd=ifms;");
     foreach (DataRow dr in excelDT.Rows)
     {
         string alarmCode = dr["告警码"].ToString();
         string experience = dr["处理建议"].ToString();
         ocon.Open();
         OracleCommand ocmd = new OracleCommand("select count(*) from alarmcause t where vendor='ZY0804' and (experience is null or dbms_lob.compare(experience, empty_clob()) = 0) and alarmcause like '%" + alarmCode + "%' or  probablecause like '%" + alarmCode + "%'", ocon);
         int count = int.Parse(ocmd.ExecuteScalar().ToString());
         ocon.Close();
         if (count > 0)
         {
  ocon.Open();
  ocmd = new OracleCommand("update alarmcause t set experience='" + experience + "' where vendor='ZY0804' and alarmcause like '%" + alarmCode + "%' or  probablecause like '%" + alarmCode + "%'", ocon);
  ocmd.ExecuteNonQuery();
  ocon.Close();
         }
     }
     MessageBox.Show("操作成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
 }
 catch (Exception se)
 {
     MessageBox.Show("操作失败:"+se.Message);
 }