首页 > 代码库 > C#中实现excel文件批量导入access数据表中
C#中实现excel文件批量导入access数据表中
一 、界面简单设计如下：
二 、代码如下：
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Data.OleDb;using System.Data.SqlClient;namespace Data1{ public partial class Excel_Info : Form { //设置数据库路径 private string accessFilePath = AccessDAO.Property.accessFilePath; public Excel_Info() { InitializeComponent(); } //调用界面美化工具IirsSkin对界面进行美化 private void Excel_Info_Load(object sender, EventArgs e) { this.skinEngine1.SkinFile = "MSN.ssk"; } //退出 private void bnExit_Click(object sender, EventArgs e) { this.Close(); } //浏览相应excel文件 private void bnBrouse_Click(object sender, EventArgs e) { OpenFileDialog dlg = new OpenFileDialog(); dlg.Filter = "Excel文件(*.xls)|*.xls"; if (dlg.ShowDialog() == DialogResult.OK) { string filePath = dlg.FileName; this.textBox1.Text = filePath; } } //导入access数据库中 private void bnImport_Click(object sender, EventArgs e) { if (textBox1.Text.Length == 0) { MessageBox.Show("请选择导入数据的Execl文件", "南京星星图"); } //转换类别未选择的时候 else if (raSend.Checked == false && raReceive.Checked == false && raRelation.Checked == false) { MessageBox.Show("请选择转换类别","南京星星图"); } #region excel导入到.mdb发包方表中 if (raSend.Checked == true) { try { OleDbConnectionStringBuilder connectStringBuilder = new OleDbConnectionStringBuilder(); connectStringBuilder.DataSource = this.textBox1.Text.Trim(); 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 = "Select * from [fbf$]"; OleDbCommand cmdLiming = new OleDbCommand(sql, cn); cn.Open(); using (OleDbDataReader drLiming = cmdLiming.ExecuteReader()) { ds.Load(drLiming, LoadOption.OverwriteChanges, new string[] { "fbf" }); DataTable dt = ds.Tables["fbf"]; if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { //写入数据库数据 string MySql = "insert into FBF(FBFBM,FBFMC,FBFFZRXM,FZRZJLX,FZRZJHM,LXDH,FBFDZ,YZBM,FBFDCY,FBFDCRQ,FBFDCJS) values(‘" + dt.Rows[i]["发包方编码"].ToString() + "‘,‘" + dt.Rows[i]["发包方名称"].ToString() + "‘,‘" + dt.Rows[i]["发包方负责姓名"].ToString() + "‘,‘" + dt.Rows[i]["负责人证件"].ToString() + "‘,‘" + dt.Rows[i]["负责人证件号码"].ToString() + "‘,‘" + dt.Rows[i]["联系电话"].ToString() + "‘,‘" + dt.Rows[i]["发包方地址"].ToString() + "‘,‘" + dt.Rows[i]["邮政编码"].ToString() + "‘,‘" + dt.Rows[i]["发包方调查员"].ToString() + "‘,#" + dt.Rows[i]["发包方调日期"].ToString() + "#,‘" + dt.Rows[i]["发包方调查记"].ToString() + "‘)"; //MessageBox.Show(MySql); AccessDAO.updateAccessTable(MySql, accessFilePath); progressBar1.Visible = true; progressBar1.PerformStep(); } progressBar1.Hide(); MessageBox.Show("数据导入成功！"); } else { MessageBox.Show("请检查你的Excel中是否存在数据"); } } } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } #endregion #region excel导入到.mdb承包方表中 if (raReceive.Checked == true) { try { OleDbConnectionStringBuilder connectStringBuilder = new OleDbConnectionStringBuilder(); connectStringBuilder.DataSource = this.textBox1.Text.Trim(); 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 = "Select * from [cbf$]"; OleDbCommand cmdLiming = new OleDbCommand(sql, cn); cn.Open(); using (OleDbDataReader drLiming = cmdLiming.ExecuteReader()) { ds.Load(drLiming, LoadOption.OverwriteChanges, new string[] { "cbf" }); DataTable dt = ds.Tables["cbf"]; if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { //写入数据库数据 string MySql = "insert into CBF(CBFBM,CBFLX,CBFMC,CBFZJLX,CBFZJHM,CBFDZ,YZBM,LXDH,CBFCYSL,CBFDCRQ,CBFDCY,CBFDCJS,GSJS,GSSHRQ,GSJSR,GSSHR)values(‘" + dt.Rows[i]["承包方编码"].ToString() + "‘,‘" + dt.Rows[i]["承包方类型"].ToString() + "‘,‘" + dt.Rows[i]["承包方（代表）名称"].ToString() + "‘,‘" + dt.Rows[i]["承包方（代表）证件"].ToString() + "‘,‘"+ dt.Rows[i]["承包方（代表）证件号码"].ToString() + "‘,‘" + dt.Rows[i]["承包方地址"].ToString() + "‘,‘" + dt.Rows[i]["邮政编码"].ToString()+ "‘,‘" + dt.Rows[i]["联系电话"].ToString() + "‘,‘" + dt.Rows[i]["承包方成员数"].ToString() + "‘,#" + dt.Rows[i]["承包方调查日"].ToString()+ "#,‘" + dt.Rows[i]["承包方调员"].ToString() + "‘,‘" + dt.Rows[i]["承包方调记事"].ToString() + "‘,‘" + dt.Rows[i]["公示记事"].ToString() + "‘,#" + dt.Rows[i]["公示审核日期"].ToString() + "#,‘" + dt.Rows[i]["公示记事人"].ToString() + "‘,‘" + dt.Rows[i]["公示审核人"].ToString() + "‘)"; //MessageBox.Show(MySql); AccessDAO.updateAccessTable(MySql, accessFilePath); progressBar1.Visible = true; progressBar1.PerformStep(); } progressBar1.Hide(); MessageBox.Show("数据导入成功！"); } else { MessageBox.Show("请检查你的Excel中是否存在数据"); } } } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } #endregion #region excel导入到.mdb家庭成员表中 if (raRelation.Checked == true) { try { OleDbConnectionStringBuilder connectStringBuilder = new OleDbConnectionStringBuilder(); connectStringBuilder.DataSource = this.textBox1.Text.Trim(); 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 = "Select * from [CBF_JTCY$]"; OleDbCommand cmdLiming = new OleDbCommand(sql, cn); cn.Open(); using (OleDbDataReader drLiming = cmdLiming.ExecuteReader()) { ds.Load(drLiming, LoadOption.OverwriteChanges, new string[] { "CBF_JTCY" }); DataTable dt = ds.Tables["CBF_JTCY"]; if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { //写入数据库数据 string MySql = "insert into CBF_JTCY(CBFBM,CYXM,CYXB,CYZJLX,CYZJHM,YHZGX,SFGYR,CYBZ)values(‘"+ dt.Rows[i]["承包方编码"].ToString() + "‘,‘" + dt.Rows[i]["成员姓名"].ToString() + "‘,‘" + dt.Rows[i]["成员性别"].ToString() + "‘,‘" + dt.Rows[i]["成员证件类型"].ToString() + "‘,‘" + dt.Rows[i]["成员证件号码"].ToString() + "‘,‘" + dt.Rows[i]["与户主关系"].ToString() + "‘,‘" + dt.Rows[i]["是否共有人"].ToString() + "‘,‘" + dt.Rows[i]["成员备注"].ToString() + "‘)"; //MessageBox.Show(MySql); AccessDAO.updateAccessTable(MySql, accessFilePath); progressBar1.Visible = true; progressBar1.PerformStep(); } progressBar1.Hide(); MessageBox.Show("数据导入成功！"); } else { MessageBox.Show("请检查你的Excel中是否存在数据"); } } } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } #endregion } }}
三、用到的自定义类AccessDAO：
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.OleDb;using System.Data.SqlClient;using System.Text.RegularExpressions; //正则表达式引用所需namespace Data1{ //access的数据访问接口 class AccessDAO { public static class Property { public static string accessFilePath = "d:\\nCTDCBJYQ04DataSet.mdb"; //若放入主程序，则可如下设置 //one mainFrm = (one)this.Owner; //string prjName = mainFrm.laPrj.Text; //string prjPath = mainFrm.laFile_Path.Text; // public static string accessFilePath = prjPath + "\\矢量数据\\" + prjName + ".mdb"; } //从access数据库获取数据 //dataFilePath指定access文件的路径 //sql指定数据库的查询语句 //DataSet为查询返回的数据集 public static DataSet getDataSetFromAccessTable(string sql, string dataFilePath) { // 连接数据库 OleDbConnection connct = new OleDbConnection(); string oleDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dataFilePath; connct.ConnectionString = oleDB; //创建命令 OleDbCommand command = new OleDbCommand(sql, connct); //打开数据库 connct.Open(); //执行命令 DataSet dataSet = new DataSet(); OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command); dataAdapter.Fill(dataSet); // 关闭连接 connct.Close(); return dataSet; } //更新或者插入数据到access数据库 //dataFilePath指定access文件的路径 //sql指定数据库的更新或者插入语句 //返回值int表示此次更新影响的行数 public static int updateAccessTable(string sql, string dataFilePath) { // 连接数据库 OleDbConnection connct = new OleDbConnection(); string oleDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dataFilePath; connct.ConnectionString = oleDB; //打开数据库 connct.Open(); //执行命令 OleDbCommand myCommand = new OleDbCommand(sql, connct); int res = myCommand.ExecuteNonQuery(); // 关闭连接 connct.Close(); return res; } //更新或者插入数据到access数据库 //dataFilePath指定access文件的路径 //command指定操作（更新或者插入）数据库的命令 //返回值int表示此次更新影响的行数 public static int updateAccessTable(OleDbCommand command, string dataFilePath) { // 连接数据库 OleDbConnection connct = new OleDbConnection(); string oleDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dataFilePath; connct.ConnectionString = oleDB; //打开数据库 connct.Open(); //执行命令 //OleDbCommand myCommand = new OleDbCommand(sql, connct); command.Connection = connct; int res = command.ExecuteNonQuery(); // 关闭连接 connct.Close(); return res; } public bool ckDigital_Num(string digitalItem, int digitalNum) //正则检查是否为数字,且位数一定 { bool isDigital_Num = false; Regex reGen = new Regex(@"^\d{" + digitalNum.ToString("F0") + "}$"); //正则表达式,n位数字 if (reGen.IsMatch(digitalItem)) isDigital_Num = true; return isDigital_Num; } }}
C#中实现excel文件批量导入access数据表中
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。