form1.cs

可以把EXCEL数据导入SQL Server

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace ExcelToSqlServer
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            this.openFileDialog1.Filter = "数据文件|*.xls";
            openFileDialog1.ShowDialog();
            textBox1.Text = openFileDialog1.FileName;
        }

        private void chakan_Click(object sender, EventArgs e)
        {
            string filename = textBox1.Text;
            string conn = " Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" + filename + ";Extended Properties=Excel 8.0";
            OleDbConnection objcon = new OleDbConnection(conn);
            objcon.Open();
            string Sql = "select * from [Sheet1$]";
            OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, objcon);
            DataSet ds1 = new DataSet();
            mycommand.Fill(ds1);
            dataGridView1.DataSource = ds1.Tables[0];
            objcon.Close();

        }

        private void daoru_Click(object sender, EventArgs e)
        {
            string filename = textBox1.Text;
            string conn = " Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" + filename + ";Extended Properties=Excel 8.0";
            OleDbConnection objcon = new OleDbConnection(conn);
            objcon.Open();
            string Sql = "select * from [Sheet1$]";
            OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, objcon);
            DataSet ds = new DataSet();
            mycommand.Fill(ds);
            objcon.Close();

            String strcon = "Data Source=LIC-LIC\\QIANL;Initial Catalog=Test;"
                                  + "User ID=sa;Password=123";
            SqlConnection objcon1 = new SqlConnection(strcon);
            objcon1.Open();
            int count = ds.Tables[0].Rows.Count;

            for (int i = 0; i < count; i++)
            {
                string strinsert = "insert into ZXQYT(企业名称,工商注册号,注册地址," +
                                  "注册类型,注册资金,设立日期,组织机构代码,海关注册号,国税登记号,地税登记号,从业人数,联系方式,法人姓名,法人性别," +
                                  "联系电话,职务职称,文化程度,婚姻状况,身份证号,出生年月,法人履历,经营范围,主营业务,技术质量状况,经营状况,营业收入,发展沿革," +
                                  "资产总额,不动产,净资产,净利润,银行往来,物业产权,债权债务,担保事项,诉讼记录,良好记录,警示记录,投资联营情况,分支机构情况,所在地中小企业局,录入人姓名,提交日期)" +
                                  " values(ltrim(rtrim('" + ds.Tables[0].Rows[i][0].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][1].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][2].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][3].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][4].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][5].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][6].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][7].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][8].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][9].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][10].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][11].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][12].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][13].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][14].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][15].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][16].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][17].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][18].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][19].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][20].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][21].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][22].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][23].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][24].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][25].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][26].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][27].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][28].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][29].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][30].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][31].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][32].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][33].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][34].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][35].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][36].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][37].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][38].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][39].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][40].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][41].ToString() + "')),ltrim(rtrim('"
                                       + ds.Tables[0].Rows[i][42].ToString() + "')))";

                SqlCommand objcom = new SqlCommand(strinsert, objcon1);
                objcom.ExecuteNonQuery();
            }
            objcon1.Close();

            MessageBox.Show("成功导入" + ds.Tables[0].Rows.Count.ToString() + "条数据！");
        }
    }
}