一、開工必備

????1、micorosoft office2007

????2、VS2010、Oracle 11

二、界面

三、內部代碼

?? (1)獲取數據庫連接，定義全局變量???????????

??????? ?private static string connString = System.Configuration.ConfigurationSettings.AppSettings[ "connStr" ];
???????? DataSet dTable;

?? (2)選擇Excel文件導入dataset??

??????????if(openFileDialog1.ShowDialog()==DialogResult.OK)
???????????? {
???????????????? ctlPath.Text = openFileDialog1.FileName;
???????????????? ExceltoDataSet(ctlPath.Text);
???????????? }???

?? (3)加載Excel文件數據

?????????? ? public DataSet ExceltoDataSet(string path)
???????? {
???????????? MessageBox.Show( "正在獲取數據....請稍候" );
???????????? //
???????????? string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+path+";Extended Properties='Excel 12.0;HRD=Yes;IMEX=1';" ;
???????????? OleDbConnection conn = new OleDbConnection(strConn);
???????????? conn.Open();
??????????? System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,null);
????????????
??????????? string tableName = schemaTable.Rows[0][2].ToString().Trim();
????????????
???????????? string strExcel = ?" " ;
???????????? OleDbDataAdapter myCommand = null;
???????????? DataSet ds = null;
???????????? strExcel = "Select * from [" + tableName + "]";
???????????? myCommand = new OleDbDataAdapter(strExcel,strConn);
???????????? ds = new DataSet();
???????????? try
???????????? {
???????????????? myCommand.Fill(ds, tableName);
???????????? }
???????????? catch(Exception ex)
???????????? {
???????????????? MessageBox.Show(ex.Message);
???????????? }
???????????????? dTable = ds;
???????????
???????????? if (ds != null)
???????????? {
???????????????? button2.Visible = true;
???????????????
?????????
???????????? }
???????????? return ds;
?????
????????
???????? }

???(4)顯示導入數據?

???????? ?DataTable dt = dTable.Tables[0]; ???????

????? try {

???????????????? for (int i = 0; i > 0; i++) ?

??????????????? {??

?????????????????? dt.Rows.Remove(dt.Rows[i]); ?

??????????????? }

???????????????? dataGridView1.DataSource = dt; ?????

??????? } catch (Exception ex) ?

??????????? {??

??????????????? ?throw ex; ???

????????? }

?? (5)將數據導入數據庫

???????? ?DataTable dt = dTable.Tables[0];

????????????

???????????? for (int i = 1; i < dt.Rows.Count; i++)

???????????? {

???????????????

???????????????? string sql1 = string.Format( "insert? /*+nologging*/ into TPB_CARCONFIG(LINERNUM,RESID,CARTYPEID,RESFNAME,RESURL,BRAND" +

????????????????? ?" values('" + dTable.Tables[0].Rows[i][0] + "','101','" + dTable.Tables[0].Rows[i][0] + "','汽車之家','www.autohome.com.cn/', " +

??????????????????? ?" '" +dTable.Tables[0].Rows[i][181]+ "','" +dTable.Tables[0].Rows[i][182]+ "','" +dTable.Tables[0].Rows[i][183]+ "','" +dTable.Tables[0].Rows[i][1]+ "')" );

?????????????????? ?OracleConnection oconn = new OracleConnection(connString); ??

??????????????????? ????? oconn.Open(); ??

??????????????? OracleCommand cmd; ?

??????????????? try?{

???????????????????? cmd = new OracleCommand(sql1.ToString(), oconn);

????????????????????? int a = Convert.ToInt32(cmd.ExecuteNonQuery());

???????????????????? if (a > 0)???{

?

???????????????????? } ????

?????????????????????? } catch (Exception ex)? { ????

? 注：這里收集重復信息的編號，保存到txt文本

???????????????? string path = ?"c:錯誤信息.txt" ; ????????

???????????? FileStream fs = new FileStream(path,FileMode.Append); ?????????

??????????? StreamWriter sw = new StreamWriter(fs); ???????????????? ????????????

???????? sw.WriteLine( "Excel編號" ?+ dTable.Tables[0].Rows[i][0]+ "數據已與數據表中重復" ); ??????

?????????????? sw.Close(); ????????????

???????? fs.Close(); ?????????????????

??? fs.Dispose(); ???????????????

????? continue; ????????????

???? } ??????????

?????????????????? oconn.Close(); ?????

??????? }

??? 注：該程序我沒有使用線程，所以在導入大量數據的時候，winform窗體假死，但是程序依然在奮斗，請耐心等待復活。

四、效果圖
????? 1、導入前

??? 2、導入后→點擊顯示數據

五、源碼下載http://pan.baidu.com/s/1sj4U2i5

?

?

?

?

???

轉載于:https://www.cnblogs.com/QQ248604315/p/4000071.html

總結

以上是生活随笔為你收集整理的EXCEL数据导入dataset的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。