通过SqlBulkCopy对象从Excel中批量导入到SQL中

批量导入，通过列映射进行批量导入到SQLserver中。不需要添加组件。

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

namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public DataSet ds = new DataSet（）;
        public Form1（）
        {
            InitializeComponent（）;
        }

        /// 

        /// 将Excel表格中的数据转换为可编辑的存放在内存当中的DataSet对象
        /// 

        /// 需要导入的Excel表格存放的绝对路径
        /// 需要导入的Excel表格中的Sheet名称
        /// 转换结束的DataSet对象
        public DataSet ExcelDataSource（string filepath string sheetname）
        {
            string strConn;
            strConn = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=“ + filepath + “;Extended Properties=Excel 8.0;“;
            OleDbConnection conn = new OleDbConnection（strConn）;
            OleDbDataAdapter oada = new OleDbDataAdapter（“select * from [“ + sheetname + “$]“ strConn）;
            DataSet ds = new DataSet（）;
            oada.Fill（ds）;
            return ds;
        }

        private void button1_Click（object sender EventArgs e）
        {
            this.openFileDialog1.DefaultExt = “*.xls“;
            this.openFileDialog1.ShowDialog（）;
            this.textBox1.Text = this.openFileDialog1.FileName;
        }

        private void button2_Click（object sender EventArgs e）
        {
            ds = ExcelDataSource（this.textBox1.Text “Sheet1“）;

            this.dataGridView1.DataSource = ds;
            this.dataGridView1.DataMember = “Table“;
        }

        // 该按钮下实现了如何将数据批量添加到数据库的对应表中
        private void button3_Click（object sender EventArgs e）
        {
            //SqlConnection sc = new SqlConnection（“Server=（local）;uid=nan;pwd=sa;database=PDSSC“）;
            string connectionString = “Data Source=（local）;Initial Catalog=pdssc;user id=nan;password=nan“;
            SqlConnection sc = new SqlConnection（connectionString）;
            sc.Open（）;
            //声明 SqlBulkCopy对象
            //没有这个对象，批量导入就没戏
            SqlBulkCopy sbc = new SqlBulkCopy（sc）;
            sbc.DestinationTableName = “Test“;

            //sbc.ColumnMappings.Add（ds.Tables[0].Columns[0].ColumnName 0）;
            //sbc.ColumnMappings.Add（ds.Tables[0].Columns[1].ColumnName 1）;
            //sbc.ColumnMappings.Add（ds.Tables[0].Columns[2].ColumnName 2）;

            // 列映射
            for （int q = 0; q < ds.Tables[0].Columns.Count; q++）
            {
                sbc.ColumnMappings.Add（ds.Tables[0].Columns[q].ColumnNameq）;
            }

            try
            {
                sbc.WriteToServer（ds.Tables[0]）;
                MessageBox.Show（“导入完成。“）;
            }
            catch （Exception ex）
            {
                MessageBox.Show（ex.Message）;
            

 属性            大小     日期    时间   名称
----------- ---------  ---------- -----  ----

     目录          0  2008-12-31 15:26  批量导入数据

     目录          0  2008-11-21 13:06  批量导入数据\WindowsApplication1

     文件        947  2008-11-21 13:24  批量导入数据\WindowsApplication1\WindowsApplication1.sln

    ..A..H.     13312  2008-11-21 13:29  批量导入数据\WindowsApplication1\WindowsApplication1.suo

     目录          0  2008-11-21 13:06  批量导入数据\WindowsApplication1\WindowsApplication1

     文件        478  2008-11-13 12:51  批量导入数据\WindowsApplication1\WindowsApplication1\Program.cs

     文件       3453  2008-11-21 13:24  批量导入数据\WindowsApplication1\WindowsApplication1\WindowsApplication1.csproj

     目录          0  2008-11-21 13:06  批量导入数据\WindowsApplication1\WindowsApplication1\bin

     目录          0  2008-11-21 13:06  批量导入数据\WindowsApplication1\WindowsApplication1\bin\Debug

     文件      14328  2008-12-23 15:51  批量导入数据\WindowsApplication1\WindowsApplication1\bin\Debug\WindowsApplication1.vshost.exe

     文件        490  2007-07-21 01:33  批量导入数据\WindowsApplication1\WindowsApplication1\bin\Debug\WindowsApplication1.vshost.exe.manifest

     文件      11264  2008-12-23 15:50  批量导入数据\WindowsApplication1\WindowsApplication1\bin\Debug\WindowsApplication1.exe

     文件      28160  2008-12-23 15:50  批量导入数据\WindowsApplication1\WindowsApplication1\bin\Debug\WindowsApplication1.pdb

     目录          0  2008-11-21 13:06  批量导入数据\WindowsApplication1\WindowsApplication1\obj

     文件        992  2008-11-13 17:55  批量导入数据\WindowsApplication1\WindowsApplication1\obj\WindowsApplication1.csproj.FileListAbsolute.txt

     目录          0  2008-11-21 13:06  批量导入数据\WindowsApplication1\WindowsApplication1\obj\Debug

     目录          0  2008-11-21 13:06  批量导入数据\WindowsApplication1\WindowsApplication1\obj\Debug\TempPE

     文件       4608  2008-12-23 15:46  批量导入数据\WindowsApplication1\WindowsApplication1\obj\Debug\TempPE\Properties.Resources.Designer.cs.dll

     文件        180  2008-12-23 15:46  批量导入数据\WindowsApplication1\WindowsApplication1\obj\Debug\WindowsApplication1.Form1.resources

     文件        180  2008-11-21 13:24  批量导入数据\WindowsApplication1\WindowsApplication1\obj\Debug\WindowsApplication1.Properties.Resources.resources

     文件      28160  2008-12-23 15:50  批量导入数据\WindowsApplication1\WindowsApplication1\obj\Debug\WindowsApplication1.pdb

     文件      11264  2008-12-23 15:50  批量导入数据\WindowsApplication1\WindowsApplication1\obj\Debug\WindowsApplication1.exe

     文件        976  2008-12-23 15:51  批量导入数据\WindowsApplication1\WindowsApplication1\obj\Debug\WindowsApplication1.csproj.FileListAbsolute.txt

     文件        847  2008-12-23 15:46  批量导入数据\WindowsApplication1\WindowsApplication1\obj\Debug\WindowsApplication1.csproj.GenerateResource.Cache

     目录          0  2008-11-21 13:06  批量导入数据\WindowsApplication1\WindowsApplication1\Properties

     文件       1194  2008-11-13 12:51  批量导入数据\WindowsApplication1\WindowsApplication1\Properties\AssemblyInfo.cs

     文件       2862  2008-11-21 13:24  批量导入数据\WindowsApplication1\WindowsApplication1\Properties\Resources.Designer.cs

     文件       5350  2008-11-13 12:51  批量导入数据\WindowsApplication1\WindowsApplication1\Properties\Resources.resx

     文件       1117  2008-11-21 13:24  批量导入数据\WindowsApplication1\WindowsApplication1\Properties\Settings.Designer.cs

     文件        249  2008-11-13 12:50  批量导入数据\WindowsApplication1\WindowsApplication1\Properties\Settings.settings

............此处省略12个文件信息