winfrom_导入Excel文件加载数据到datagridview并将数据保存到数据库

private void btn_excel_Click(object sender, EventArgs e) 2 { 3 try 4 { 5 //获取Excel文件路径和名称   6 OpenFileDialog odXls = new OpenFileDialog(); 7 //指定相应的打开文档的目录  AppDomain.CurrentDomain.BaseDirectory定位到Debug目录，再根据实际情况进行目录调整   8 string folderPath = AppDomain.CurrentDomain.BaseDirectory + @"databackup\"; 9 odXls.InitialDirectory = folderPath; 10 // 设置文件格式     11 odXls.Filter = "Excel files office2003(*.xls)|*.xls|Excel office2010(*.xlsx)|*.xlsx"; 12 //openFileDialog1.Filter = "图片文件(*.jpg)|*.jpg|(*.JPEG)|*.jpeg|(*.PNG)|*.png";   13 odXls.FilterIndex = 1; 14 odXls.RestoreDirectory = true; 15 if (odXls.ShowDialog() == DialogResult.OK) 16 { 17 this.txtFilePath.Text = odXls.FileName; 18 this.txtFilePath.ReadOnly = true; 19 20 string sConnString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=Yes;\"", odXls.FileName); 21 if ((System.IO.Path.GetExtension(txtFilePath.Text.Trim())).ToLower() == ".xls") 22 { 23 sConnString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\"", odXls.FileName); 24 } 25 using (OleDbConnection oleDbConn = new OleDbConnection(sConnString)) 26 { 27 oleDbConn.Open(); 28 DataTable dt = oleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); 29 //判断是否cmb中已有数据，有则清空   30 if (cmbtablename.Items.Count > 0) 31 { 32 cmbtablename.DataSource = null; 33 cmbtablename.Items.Clear(); 34 } 35 //遍历dt的rows得到所有的TABLE_NAME，并Add到cmb中   36 foreach (DataRow dr in dt.Rows) 37 { 38 cmbtablename.Items.Add((String)dr["TABLE_NAME"]); 39 } 40 if (cmbtablename.Items.Count > 0) 41 { 42 cmbtablename.SelectedIndex = 0; 43 } 44 //加载Excel文件数据按钮   45 this.btnSearch.Enabled = true; 46 } 47 } 48 } 49 catch (Exception ex) 50 { 51 MessageBox.Show(ex.Message); 52 } 53 }

注：可能会报错 ‘未在本地计算机上注册“Microsoft.ACE.OLEDB.12.0”提供程序’，需要下载AccessDatabaseEngine.exe并安装；

     3.点击 ‘加载Excel’ 按钮事件

 1 private void btnSearch_Click(object sender, EventArgs e)
 2         {
 3             if (string.IsNullOrEmpty(txtFilePath.Text))
 4             {
 5                 MessageBox.Show("请选择Excel!");
 6             }
 7             else
 8             {//读取相应的表名的Excel文件中数据到当前DataGridview中显示 
 9                 string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=Yes;\"", txtFilePath.Text.Trim());
10                 if ((System.IO.Path.GetExtension(txtFilePath.Text.Trim())).ToLower() == ".xls" && (System.IO.Path.GetExtension(txtFilePath.Text.Trim())).ToLower() == ".xlsx")
11                 {
12                     strConn = string.Format("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = {0}; Extended Properties =\"Excel 8.0;HDR=Yes;IMEX=1;\"", txtFilePath.Text.Trim());
13                 }
14                 string sTableName = cmbtablename.Text.Trim();
15                 try
16                 {
17                     OleDbDataAdapter da = new OleDbDataAdapter("select *from [" + sTableName + "]", strConn);
18                     DataSet oldDS = new DataSet();
19                     da.Fill(oldDS);
20                     this.dgv_students.DataSource = oldDS.Tables[0];
21                 }
22                 catch (Exception ex)
23                 {
24                     MessageBox.Show(ex.Message);
25                 }
26             }
27         }      

4.点击‘确定导入’按钮事件

 1 private void btnSub_Click(object sender, EventArgs e)
 2         {
 3             if (txtFilePath.Text.Trim() != "" && cmbtablename.Text.Trim() != "")
 4             {
 5                 int nums = dgv_students.Rows.Count;
 6                 if (nums < 0) return;
 7 
 8                 string studentID, name, sex, phone, idNumber, address, deposit, tuitionPayable, seatNumber;
 9                 for (int i = 0; i < dgv_students.Rows.Count; i++)
10                 {
11                     studentID = this.dgv_students.Rows[i].Cells[0].Value.ToString();
12                     name = this.dgv_students.Rows[i].Cells[1].Value.ToString();
13                     sex = this.dgv_students.Rows[i].Cells[2].Value.ToString();
14                     phone = this.dgv_students.Rows[i].Cells[3].Value.ToString();
15                     idNumber = this.dgv_students.Rows[i].Cells[4].Value.ToString();
16                     address = this.dgv_students.Rows[i].Cells[5].Value.ToString();
17                     deposit = this.dgv_students.Rows[i].Cells[6].Value.ToString();
18                     tuitionPayable = this.dgv_students.Rows[i].Cells[7].Value.ToString();
19                     seatNumber = this.dgv_students.Rows[i].Cells[8].Value.ToString();        
20                     int result = dal.AddtoImport(studentID, name, sex, phone, idNumber, address, deposit, tuitionPayable, seatNumber);
21                     if (result == 0)
22                     {
23                         MessageBox.Show("数据导入成功！\n");
24                     }
25                     else
26                     {
27                         MessageBox.Show("数据导入失败！ Error:" + result + "\n");
28                     }
29                 }
30             }
31             else
32             {
33                 MessageBox.Show("请加载表格！");
34             }
35         }

 

winfrom_导入Excel文件加载数据到datagridview并将数据保存到数据库

标签：ssd   UI   div   director   导入excel   tostring   str   gui   dir