C#中如何将excel内容批量导入到access数据库表中

实现将excel表内容导入到.mdb格式的acess数据库中:

一、界面如图所示:主要是导入用户的相应信息

二、主要导入代码:

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
    {

        //设置数据库路径 AccessDAO类是自己写的读写数据库内容的类库,后文会有备注
        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类可参考AccessDAO博文


相关推荐

  • Directadmin面板导入大数据库SQL文件. 好多用户使用大墨的1G免费空间,但是好多人的数据库都很大,并且服务商post上传大小限制了,造成数据库大sql文件没办法导入,导入超时等等原因。下面介绍一个大神写的一个php程序$file=$_GET['f'];define('DB_NAM
  • Linux下用bash导入mysql数据库脚本 Linux下通过bash导入由mysqldump命令导出的数据库文件--bash.code如下------------------------------------------------------#!/bin/bashmysqlDir
  • 使用PHP把excel(xls)文件导入mysql数据库 第一篇PHP语言的功能是非常强大的,它也是我们经常会使用到的一种计算机高级语言。在这里,我们将会为大家介绍有关PHP将EXCEL导入MYSQL的具体方法。最近因项目需求,要实现将excel文件通过php页面导入mysql数据库中。PHP中文
  • 数据库表中的时间字段是否可以建立索引? 可以建立索引的;至于建立聚集索引或者是非聚集索引,那要看你这个时间字段的具体情况以及使用或变更频繁程度。一般来说,适合建立聚集索引的要求:“既不能绝大多数都相同,又不能只有极少数相同”的规则。先说说一个误区:有人认为:只要建立索引就能显著提
  • Access数据库在线压缩的实现 Access数据库在线压缩的实现:如果在Access数据库中删除数据或对象,或者在Access项目中删除对象,Access数据库或Access项目可能会产生碎片并会降低磁盘空间的使用效率。压缩Access数据库或Access项目实际上是复制
  • Access数据库的存储上限 Access数据库的存储上限:Access数据库的存储上限Access规格MicrosoftAccess数据库规格Access数据库属性最大值MicrosoftAccess数据库(.mdb)文件大小2G字节减去系统对象所需的空间。数据库中的
  • asp数据库查看器 快速破解Access数据库密码 asp数据库查看器快速破解Access数据库密码软件可查看Windows中以***显示的密码窗口中的实际内容、快速破解目前已知的各版本Access数据库密码,支持批量数据库破解。
  • ADO操作ACCESS数据库的一些心得(MFC) MFC中ADO来操作ACCESS数据库还是挺快的,用起来也还算好,只是刚开始对几个智能指针搞的心里雾里,用了一段时间后还是有些体会,写下来,供以后参考:1.使用ADO操作ACCESS首先要连接到数据库,用_ConnectionPtrm_pC
  • Access数据库的压缩与修复 Access数据库长时间使用后容易出现数据库过大、数据库损坏等现像,微软的Office软件中Access是带有修复和压缩功能的,在操作前您本地电脑上需要安装Office软件中的Access软件。建议一到两个星期修复一次。具体操作共分为二步方
  • Access数据库过大问题的几种解决方案 Access数据库过大问题的几种解决方案:问题:1、我的Access数据库里面没存多少数据,为什么体积很大,别人和我存一样的数据为什么只有我的文件体积的1/10。2、为什么我删除了数据,但是数据库体积没有减小?3、为什么我存了几张图片到ol
  • VC++直接通过ADO操作Access数据库 最近在做一个3D的项目,要用到对数据库的增、删、改、查。我选用的是用ADO(无需配置数据源方式的)操作Access数据库。ADO(ActiveDataObject)实际上是一种基于COM(组件对象模型)的自动化接口(IDispatch)技术
  • Python连接Access数据库 Python编程语言的出现,带给开发人员非常大的好处。我们可以利用这样一款功能强大的面向对象开源语言来轻松的实现许多特定功能需求。比如Python操作Access数据库的功能实现等等。在Python操作Access数据库之前,首先,你应安装

你的评论

就没有什么想说的吗?

最新博客

关于我们 移动版

©2017传客网    琼ICP备15003173号-2    

本站部分文章来源于互联网,版权归属于原作者。
本站所有转载文章言论不代表本站观点,如是侵犯了原作者的权利请发邮件联系站长(weishubao@126.com),我们收到后立即删除。
站内所有资源仅供学习与参考,请勿用于商业用途,否则产生的一切后果将由您自己承担!

X