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 == )

            {

                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 > )

                            {

                                for (int i = ; 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 > )

                            {

                                for (int i = ; 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 > )

                            {

                                for (int i = ; 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数据表中的相关教程结束。