C# Excel导入Access 

  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     catch
102     {
103         myTrans.Rollback();
104         resultState = false;
105     }
106     finally
107     {
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     try
123     {
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     catch
135     {
136         sqlResultCount = 0;
137     }
138     finally
139     {
140         Connection.Close();
141     }
142     return sqlResultCount;
143 }

 

posted @ 2016-08-30 19:38  涂山吕吕  阅读(1367)  评论(0)    收藏  举报