将excel导入到数据库

using System;

usingSystem.Collections.Generic;

usingSystem.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

usingSystem.Windows.Forms;

using System.Data.OleDb;

 

 

namespaceWindowsApplication1

{

    public partial class Form1 : Form

    {

        public Form1()

        {

           InitializeComponent();

        }

 

        //绑定数据

        private voidBindData(string sql)

        {

            OleDbConnection conn = newOleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=|DataDirectory|\data.mdb");

           conn.Open();

            OleDbDataAdapter da=newOleDbDataAdapter(sql,conn);

            DataSet ds=new DataSet();

           da.Fill(ds,"pers");

           dataGridView1.DataSource = ds.Tables["pers"];

           ds.Dispose();

           conn.Close();

        }

 

        private voidbutton1_Click(object sender, EventArgs e)

        {

            string sql = "select*from pers where 1=1 ";

            if (textBox1.Text.Trim() != "")

               sql = sql + " and name='" +textBox1.Text.Trim() + "'";

            if (comboBox2.Text != "")

            {

               if(comboBox2.Text=="男D")

                   sql = sql + " and sex=0";

               else

                   sql = sql + " and sex=1";

            }

            if (textBox2.Text.Trim() != "")

               sql = sql + " and address ='"+ textBox2.Text.Trim() + "'";

           BindData(sql);

        }

 

 

        private voidForm1_Load(object sender, EventArgs e)

        {

           BindData("select *from pers");

        }

 

        //把excel数据读入dataset返回数据集

        private DataSetxsldata()

        {

            OpenFileDialog openFile = newOpenFileDialog();//打开文件对话框

           openFile.Filter = ("Excel 文?件t(*.xls)|*.xls");//后缀名

            if (openFile.ShowDialog() == DialogResult.OK)

            {

               string filename = openFile.FileName;

               int index = filename.LastIndexOf("\\");//截取文件的名字

               filename = filename.Substring(index + 1);

               string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=" + openFile.FileName + ";ExtendedProperties='Excel 8.0;IMEX=1'";

               System.Data.OleDb.OleDbConnectionConn = new System.Data.OleDb.OleDbConnection(strCon);

               string strCom = "SELECT* FROM [sheet1$]";

               Conn.Open();

               System.Data.OleDb.OleDbDataAdaptermyCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);

               DataSet ds = newDataSet();

               myCommand.Fill(ds, "[sheet1$]");

               Conn.Close();

               return ds;

            }

            else

               return null;

 

        }

 

        private voidbutton2_Click(object sender, EventArgs e)

        {

            OleDbConnection conn = newOleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=|DataDirectory|\data.mdb");

           conn.Open();

            OleDbCommand cmd = newOleDbCommand();

           cmd.Connection = conn;

            DataSet ds = xsldata();

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

            {

               cmd.CommandText = "insert intopers(name,sex,address)values('" + ds.Tables[0].Rows[i][1] + "'," + ds.Tables[0].Rows[i][2] + ",'" + ds.Tables[0].Rows[i][3] + "')";

               cmd.ExecuteNonQuery();

            }

           conn.Close();

 

        }

 

        private voidForm1_FormClosed(object sender, FormClosedEventArgs e)

        {

           System.Diagnostics.Process.Start("iexplore.exe", "www.cpbcw.com");

        }

    }

}