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;

namespace 将数据从excel中导入到access中

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        private void button1_Click(object sender, EventArgs e)

        {

            string mystr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\\新建 Microsoft Excel 工作表.xls;Extended Properties=Excel 8.0;";

            OleDbConnection mycon = new OleDbConnection(mystr);

            OleDbDataAdapter mydata = new OleDbDataAdapter("select * from   [Sheet1$]", mycon);

            DataSet ds = new DataSet();

            mydata.Fill(ds);

            this.dataGridView1.DataSource = ds.Tables[0].DefaultView;

            string mystring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\\biancheng\\3sdnMap\\新地图\\甘肃省.mdb";

            string sql = "CREATE TABLE "+this.textBox2 .Text +" (";

            for (int i = 0; i < ds.Tables[0].Columns.Count; i++)

            {

                if (i == 0)

                {

                    sql += ds.Tables[0].Columns[i].ColumnName;

                    sql += "\t" + "varchar(50)";

                }

                else

                {

                    sql += "," + ds.Tables[0].Columns[i].ColumnName;

                    sql += "\t" + "varchar(50)";

                }

            }

            sql += ")";

          

            OleDbConnection mycreate = new OleDbConnection(mystring);

            OleDbCommand mycreateop = new OleDbCommand(sql, mycreate);

            mycreate.Open();

            mycreateop.ExecuteNonQuery();

            mycreate.Close();

            string mystring1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\\biancheng\\3sdnMap\\新地图\\甘肃省.mdb";

            string sql1;

            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)

            {

                sql1 = "insert into " + this.textBox2.Text + "( ";

                for (int k = 0; k < ds.Tables[0].Columns.Count; k++)

                {

                    if (k == 0)

                    {

                        sql1 += ds.Tables[0].Columns[k].ColumnName;

                    }

                    else

                    {

                        sql1 += "," + ds.Tables[0].Columns[k].ColumnName;

                    }

                }

                sql1 += ")values(";

                for (int j = 0; j < ds.Tables[0].Columns.Count; j++)

                {

                    if (j == 0)

                    {

                        sql1 += ds.Tables[0].Rows[i][j];

                    }

                    else

                    {

                        sql1 += "," + ""+ds.Tables[0].Rows[i][j]+"";

                    }

                }

                sql1 += ")";

                OleDbConnection con = new OleDbConnection(mystring1);

                OleDbCommand com = new OleDbCommand(sql1, con);

                con.Open();

                com.ExecuteNonQuery();

                con.Close();

               

            }

           

        }

    }

}