1 /// <summary>  2 /// 导入  3 /// </summary>  4 private void btn_In_Click(object sender, EventArgs e)  5 {  6     int i = DataTableToDB();  7     MessageBox.Show("成功导入" + i + "条商品信息！");  8 }  9  10  11  12  13 /// <summary> 14 /// 获取后缀名为*.xlsx的文件 15 /// </summary> 16 public void GetFile() 17 { 18      System.IO.DirectoryInfo dir = new DirectoryInfo(VPath); 19      if (dir.Exists)//判读是否存在改文件 20      { 21          fiList = dir.GetFiles("*.xlsx"); //获取后缀名为*.xlsx的文件 22      } 23 } 24  25  26 /// <summary> 27 /// Excel数据转化为DataTable 28 /// </summary> 29 /// <param name="strSheetName"></param> 30 /// <param name="strExcelFileName">文件路径</param> 31 /// <returns>返回DataTable</returns> 32 public DataTable ExcelToDataTable(string strExcelFileName, string strSheetName) 33 { 34     string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=‘Excel 8.0;HDR=NO;IMEX=1;‘", strExcelFileName); 35     string strExcel = string.Format("select * from [{0}$]", strSheetName); 36     DataSet ds = new DataSet(); 37  38     using (OleDbConnection conn = new OleDbConnection(strConn)) 39     { 40         conn.Open(); 41         OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn); 42         adapter.Fill(ds, strSheetName); 43         conn.Close(); 44     } 45  46     return ds.Tables[strSheetName]; 47 } 48  49  50  51 public int DataTableToDB() 52 { 53     GetFile(); 54     int count = 0; 55     string _strExcelFileName = ""; 56     for (int i = 0; i < fiList.Length; i++) 57     { 58         _strExcelFileName = dir + "\\" + fiList[i]; 59  60         DataTable dtExcel = Global.g_objDb.ExcelToDataTable(_strExcelFileName, "Sheet1"); 61         for (int j = 0; j < dtExcel.Rows.Count; j++) 62         { 63             if ((ReturnSqlResultCount("select * from A where a1=‘" + dtExcel.Rows[j][0].ToString() + "‘")) > 0) 64             { 65                 continue; 66             } 67             else 68             { 69                 Global.g_objDb.InsertDataToAccess(dtExcel.Rows[j][0].ToString(), dtExcel.Rows[j][1].ToString(), dtExcel.Rows[j][2].ToString(), dtExcel.Rows[j][3].ToString(), dtExcel.Rows[j][4].ToString(), dtExcel.Rows[j][5].ToString(), dtExcel.Rows[j][6].ToString(), dtExcel.Rows[j][7].ToString()); 70  71                 count++; 72             } 73         } 74     } 75   76     return count; 77 } 78  79 String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Access_DataBase.mdb;Jet OLEDB:Database Password=123456"";  80  81 OleDbConnection Connection = new OleDbConnection(connectionString);   82  83 /// <summary> 84 /// 执行一查询语句语句，同时返回bool值 85 /// </summary> 86 public bool InsertDataToAccess(string col1, string col2, string col3, string col4, string col5, string col6, string col7, string col8) 87 { 88     bool resultState = false; 89  90     Connection.Open(); 91     string strSQL = "insert into spdm(a,b,c,d,e,f,g,h) values(‘" + col1 + "‘,‘" + col1 + "‘,‘" + col1 + "‘,‘" + col1 + "‘,‘" + col1 + "‘,‘" + col1 + "‘,‘" + col1 + "‘,‘" + col1 + "‘)"; 92     OleDbTransaction myTrans = Connection.BeginTransaction(); 93     OleDbCommand command = new OleDbCommand(strSQL, Connection, myTrans); 94  95     try 96     { 97         command.ExecuteNonQuery(); 98         myTrans.Commit(); 99         resultState = true;100     }101     catch102     {103         myTrans.Rollback();104         resultState = false;105     }106     finally107     {108         Connection.Close();109     }110     return resultState;111 }112 113 /// <summary>114 /// 执行一查询语句，同时返回查询结果数目115 /// </summary>116 /// <param name="strSQL"></param>117 /// <returns></returns>118 public int ReturnSqlResultCount(string strSQL)119 {120     int sqlResultCount = 0;121 122     try123     {124         Connection.Open();125         OleDbCommand command = new OleDbCommand(strSQL, Connection);126         OleDbDataReader dataReader = command.ExecuteReader();127 128         while (dataReader.Read())129         {130             sqlResultCount++;131         }132         dataReader.Close();133     }134     catch135     {136         sqlResultCount = 0;137     }138     finally139     {140         Connection.Close();141     }142     return sqlResultCount;143 }