c#中点击button按钮导入本地硬盘的excel数据到SQL数据库
c#中点击button按钮导入本地硬盘的excel数据到SQL数据库
c#中点击button按钮导入本地硬盘的excel数据到SQL数据库
怎么实现啊？
求代码

using System;
using System.Collections.Generic;
using using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using namespace ExcelToSqoServer
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //测试，将excel中的sheet1导入sqlserver
            string connString = "Data Source=localhost;Initial Catalog=master;Integrated Security=true ;User id=sa;pwd=123";


            System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();
            if (fd.ShowDialog() == DialogResult.OK)
            {
                TransferDate(fd.FileName,"sheet1",connString);
                //return;
            }
        }
        public void TransferDate(string excelFile,string sheetName,string connectionString) 
        {
            DataSet ds = new DataSet();
            try 
            {
                //获取全部数据
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties =Excel 8.0;";
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                string strExcel = "";
                OleDbDataAdapter myCommand = null;
                strExcel = string.Format("select * from [{0}$]",sheetName);
                myCommand = new OleDbDataAdapter(strExcel,strConn);
                myCommand.Fill(ds,sheetName);

                //如果目标不存在则创建
                string strSql = string.Format("if object_id('{0}') is null create table {0}(",sheetName);
                foreach(System.Data.DataColumn c in ds.Tables[0].Columns)
                {
                    strSql += string.Format("{0} varchar(255),",c.ColumnName);
                }
                strSql = strSql.Trim(',') + ")";
                using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString)) 
                {
                    sqlconn.Open();
                    System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
                    command.Connection = sqlconn;
                     = CommandType.Text;
                     = strSql;
                    command.ExecuteNonQuery();
                    sqlconn.Close();
                }

                using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString)) 
                {
                    bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowCopied);
                    bcp.BatchSize = 100;
                    bcp.NotifyAfter = 100;
                    bcp.DestinationTableName = sheetName;
                    bcp.WriteToServer(ds.Tables[0]);
                }
            }
            catch(Exception e)
            {
                System.Windows.Forms.MessageBox.Show(e.Message);
            }
        }
        void bcp_SqlRowCopied(object sender,System.Data.SqlClient.SqlRowsCopiedEventArgs e)
        {
            this.Text = e.RowsCopied.ToString();
            this.Update();
        }
    }
}

这个网上不少啊

[ 本帖最后由 leonbana 于 2015-4-10 08:46 编辑 ]