/// <summary> /// Excel导入Access /// </summary> public void Excel2Access(string[] filenames) { try { for (int j = 0; j < filenames.Length; j++) { OleDbConnectionStringBuilder connectStringBuilder = new OleDbConnectionStringBuilder(); connectStringBuilder.DataSource = filenames[j]; connectStringBuilder.Provider = "Microsoft.Jet.Oledb.4.0"; connectStringBuilder.Add("Extended Properties", "Excel 8.0"); using (OleDbConnection cn = new OleDbConnection(connectStringBuilder.ConnectionString)) { DataSet ds = new DataSet(); string sql = string.Format("select * from [{0}$]", sheetName); OleDbCommand cmdLiming = new OleDbCommand(sql, cn); cn.Open(); using (OleDbDataReader dr = cmdLiming.ExecuteReader()) { ds.Load(dr, LoadOption.OverwriteChanges, new string[] { sheetName }); DataTable dt = ds.Tables[sheetName]; if (dt.Rows.Count > 0) { for (int i = 4; i < dt.Rows.Count; i++) { //写入数据库数据 if (!(dt.Rows[i][0].ToString() == "" || string.IsNullOrEmpty(dt.Rows[i][0].ToString()))) { string MySql = "insert into TB_Car (PlateNumber,Color,Brand,UseYear,MotorcyleType) values('" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','" + dt.Rows[i][5].ToString() + "')"; AccessHelper.SQLExecute(MySql); } } MessageBox.Show("数据导入成功!"); CarBLL carBLL = new CarBLL(); DataTable carList = carBLL.FindAll_infos(getblacklistsql); dgvCarList.DataSource = carList; } else { MessageBox.Show("请检查你的Excel中是否存在数据"); } } } } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
private void CarForm_Load(object sender, EventArgs e) { DataTable carList = carBLL.FindAll_infos(getblacklistsql); dgvCarList.DataSource = carList; }