C#将excel数据导入mdb的三种方法（针对5万条数据）

问题描述：将存有5.6万条的excel表数据导入mdb中

解决办法：

1.DAO。优点：速度快，5.6万条数据，也就不到一分钟，缺点：对于我来说，本机编译好的exe不能在其他电脑运行，最后查出来是DAO的问题。

2.用事务。优点：不依赖于DAO，直接靠using System.Data.OleDb;就能解决，速度为5.6万条数据455秒，缺点：速度慢，在所有行插入完之前，mdb中看不到进度情况。

3.用adapter.Update()。优点：不依赖于DAO,直接靠using System.Data.OleDb,DataTable改变，mdb中的表就能跟着改变，速度为5.6万条数据578秒，能在插入过程中看到进度情况，缺点：速度慢，DataTable的改变也是不断插入行才行，曾试图采用DataTable.Merge（datatable），结果mdb不更新。

详细代码：

1.DAO

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;
using OSGeo.GDAL;
using OSGeo.OGR;
using DAO = Microsoft.Office.Interop.Access.Dao;
using System.Collections;
using System.Runtime.InteropServices;
using System.Diagnostics;


//定义
DAO.Database db;

            db = dbEngine.OpenDatabase(filepath);
            string tablename_encoding = "行政编码表";
//dt_excel获取方法与后面两个例子一致
            Recordset rs1 = db.OpenRecordset(tablename_encoding);
            Field[] myfields_1 = new Field[dt_excel.Columns.Count];
            for (int i = 0; i < dt_excel.Columns.Count; i++)
            {
                myfields_1[i] = rs1.Fields[dt_excel.Columns[i].ColumnName];
            }
            for (int i = 0; i < dt_excel.Rows.Count; i++)
            {
                rs1.AddNew();
                for (int j = 0; j < myfields_1.Length; j++)
                {
                    myfields_1[j].Value = dt_excel.Rows[i][j];
                }
                rs1.Update();
            }
            rs1.Close();

2.事务

using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace 将excel批量插入mdb
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
//设置要导入到mdb的excel路径
            string path_excel = @"C:\Users\Administrator\Desktop\新建文件夹\TestData\附件三：河南省行政编码表.xlsx";
//设置要导入的mdb文件路径
            string path_mdb = @"C:\Users\Administrator\Desktop\新建文件夹\TestData\000012中华财险保险公司承保数据20200810.mdb";
//读取excel内容到DataTable
            DataTable dt_excel = GetExcelTable(path_excel, "行政编码表");
//建立mdb数据库连接
            OleDbConnection ole_mdb = GetMdb(path_mdb);
            //1.根据dt_excel 在mdb中创建表结构
            string sql = "create table " + "行政编码表" + "(";
            for (int i = 0; i < dt_excel.Columns.Count; i = i + 1)
            {
                sql += dt_excel.Columns[i].ColumnName + " varchar(40),";
            }
            sql = sql.Substring(0, sql.Length - 1);
            sql += ")";
            OleDbCommand cmd = new OleDbCommand(sql, ole_mdb);
            cmd.ExecuteNonQuery();
//计时工具，启动
            int startTime = System.Environment.TickCount;
            //2.批量插入数据
//对于dt_excel中的每一行，写插入语句，并存放在ArrayList 中
            ArrayList sqllist = new ArrayList();
            for (int i=0;i<dt_excel.Rows.Count;i++) {
                sql = "INSERT INTO 行政编码表 (县名称, 管辖区名称, 乡镇名称, 村名称,行政村编码 ) " +"VALUES (\"" + dt_excel.Rows[i][0].ToString() + "\"" +

                              ",\"" + dt_excel.Rows[i][1].ToString() + "\"" +

                              ",\"" + dt_excel.Rows[i][2].ToString() + "\"" +

                              ",\"" + dt_excel.Rows[i][3].ToString() + "\"" +

                              ",\"" + dt_excel.Rows[i][4].ToString() + "\")";
                sqllist.Add(sql);
            }
//用事务挨个插入sqllist中的数据并提交
            insertToStockDataByBatch(sqllist,ole_mdb);
//计时工具，停止
            int endTime = System.Environment.TickCount;
            int runTime = (endTime - startTime) / 1000;
            MessageBox.Show("用时：" + runTime + "秒");
        }
//方法：将excel转为datatable
        public DataTable GetExcelTable(string excelFilename, string tableName)
        {
            //建立与指定Excel文件的连接
            string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Jet OLEDB:Engine Type=35;Extended Properties=Excel 8.0;Persist Security Info=False", excelFilename);
            DataSet ds = new DataSet();  //数据集
            //using中声明的对象connection，在using语句块结束后会自动释放
            using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionString))
            {
                connection.Open();
                //获取数据库架构信息，包括列、主键、表等信息
                DataTable table = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);

                string strExcel = "select * from " + "[" + tableName + "$]";
                //打开数据链接，得到一个数据集
                OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, connectionString);
                //在数据集中查询表名为tableName的表，然后把其数据在装入数据集ds中
                adapter.Fill(ds, tableName);

                connection.Close();
            }
            return ds.Tables[tableName];//以datatable类型返回数据集ds中表名为tableName的表
        }
//方法：连接到mdb数据库
        public OleDbConnection GetMdb(string filepath)
        {
            try
            {
                string sqlcon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + "; Persist Security Info =False";
                OleDbConnection ole_mdb = new OleDbConnection(@sqlcon);
                ole_mdb.Open();
                MessageBox.Show(filepath + "--读取成功");
                //db = dbEngine.OpenDatabase(filepath);
                return ole_mdb;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }
//方法：打开mdb连接，启动事务，逐行插入，提交事务
public  void insertToStockDataByBatch(ArrayList sqlArray,OleDbConnection oleDb)
        {
            try
            {
                if (oleDb.State==ConnectionState.Closed) { oleDb.Open(); }
                OleDbTransaction transaction = oleDb.BeginTransaction();
                OleDbCommand aCommand = new OleDbCommand();
                aCommand.Connection = oleDb;
                aCommand.Transaction = transaction;
                for (int i = 0; i < sqlArray.Count; i++)
                {
                    aCommand.CommandText = sqlArray[i].ToString();
                    aCommand.ExecuteNonQuery();
                }
                transaction.Commit();
                oleDb.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }

        }
    }
}
   

 3.adapter.Update()

using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace 将excel批量插入mdb
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string path_excel = @"C:\Users\Administrator\Desktop\新建文件夹\TestData\附件三：河南省行政编码表.xlsx";
            string path_mdb = @"C:\Users\Administrator\Desktop\新建文件夹\TestData\000012中华财险保险公司承保数据20200810.mdb";
            DataTable dt_excel = GetExcelTable(path_excel, "行政编码表");
            OleDbConnection ole_mdb = GetMdb(path_mdb);
            //开始尝试塞进去
            //1.在mdb中创建表结构
            string sql = "create table " + "行政编码表" + "(";
            for (int i = 0; i < dt_excel.Columns.Count; i = i + 1)
            {
                sql += dt_excel.Columns[i].ColumnName + " varchar(40),";
            }
            sql = sql.Substring(0, sql.Length - 1);
            sql += ")";
            OleDbCommand cmd = new OleDbCommand(sql, ole_mdb);
            cmd.ExecuteNonQuery();
            int startTime = System.Environment.TickCount;

            //2.批量插入数据
            CreateCmdsAndUpdate(ole_mdb, "select * from [行政编码表]", dt_excel);
            int endTime = System.Environment.TickCount;
            int runTime = (endTime - startTime) / 1000;
            MessageBox.Show("用时：" + runTime + "秒");
        }
        public DataTable GetExcelTable(string excelFilename, string tableName)
        {
            //建立与指定Excel文件的连接
            string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Jet OLEDB:Engine Type=35;Extended Properties=Excel 8.0;Persist Security Info=False", excelFilename);
            DataSet ds = new DataSet();  //数据集
            //using中声明的对象connection，在using语句块结束后会自动释放
            using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionString))
            {
                connection.Open();
                //获取数据库架构信息，包括列、主键、表等信息
                DataTable table = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);

                string strExcel = "select * from " + "[" + tableName + "$]";
                //打开数据链接，得到一个数据集
                OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, connectionString);
                //在数据集中查询表名为tableName的表，然后把其数据在装入数据集ds中
                adapter.Fill(ds, tableName);

                connection.Close();
            }
            return ds.Tables[tableName];//以datatable类型返回数据集ds中表名为tableName的表
        }
        public OleDbConnection GetMdb(string filepath)
        {
            try
            {
                string sqlcon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + "; Persist Security Info =False";
                OleDbConnection ole_mdb = new OleDbConnection(@sqlcon);
                ole_mdb.Open();
                MessageBox.Show(filepath + "--读取成功");
                //db = dbEngine.OpenDatabase(filepath);
                return ole_mdb;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }

//方法：使用update更新datatable
        public DataTable CreateCmdsAndUpdate(OleDbConnection oleDbConnection, string queryString, DataTable dt)
        {
            using (oleDbConnection)
            {
                OleDbDataAdapter adapter = new OleDbDataAdapter();
                adapter.AcceptChangesDuringFill = false;
                adapter.SelectCommand = new OleDbCommand(queryString, oleDbConnection);
                OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
                if (oleDbConnection.State == ConnectionState.Closed) { oleDbConnection.Open(); }
                DataTable customers = new DataTable();
                adapter.Fill(customers);

                //code to modify data in DataTable here
//dt表示与mdb数据库关联的表，dt_exel表示存储excel数据的表
//尝试用dt.Merge(dt_excel),运行没问题，但mdb中并未插入数据，失败
//尝试用dt=dt_excel，运行没问题，但mdb中并未插入数据，失败
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = customers.NewRow();
                    dr["县名称"] = dt.Rows[i][0].ToString();
                    dr["管辖区名称"] = dt.Rows[i][1].ToString();
                    dr["乡镇名称"] = dt.Rows[i][2].ToString();
                    dr["村名称"] = dt.Rows[i][3].ToString();
                    dr["行政村编码"] = dt.Rows[i][4].ToString();
                    customers.Rows.Add(dr);
                }
                adapter.Update(customers);
                return customers;
            }
        }
    }
}