EXCEL导入sql示例2

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Data.OleDb;

public partial class Department_HumanResources_Attendance_test : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Button1_Click1(object sender, EventArgs e)
    {
        //测试，将excel中的sheet1导入到sqlserver中
        string connString = ConfigurationManager.ConnectionStrings["ERPConnectionString"].ToString();
        //System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();
        //if (fd.ShowDialog() == DialogResult.OK)
        //{
        //    TransferData(fd.FileName, "sheet1", connString);
        //}
        //Response.Write(Server.MapPath("~/Department/HumanResources/Attendance/1.xls"));
        TransferData(Server.MapPath("~/Department/HumanResources/Attendance/6.xls"), "销售4月打卡", connString);

    }

    public void TransferData(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);

            using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))
            {
               
                if (sqlconn.State == ConnectionState.Closed)
                {
                    sqlconn.Open();
                }
/*******************调用SqlBulkCopy实现DataTable到SQL的批量拷贝******************/
                //定义SQL事务并嵌入到批量拷贝的工作中
                SqlTransaction objSqlTran = sqlconn.BeginTransaction();

                //定义SqlBulkCopy:SqlConn为SqlConnection,SqlBulkCopyOptions枚举类型,objSqlTran为调用的事务
                SqlBulkCopy objSqlCopy = new SqlBulkCopy(sqlconn, SqlBulkCopyOptions.KeepIdentity, objSqlTran);

                //分批次拷贝的行数
                objSqlCopy.BatchSize = 100;
                objSqlCopy.BulkCopyTimeout = 240;
                //目标表名
                objSqlCopy.DestinationTableName = "test";

                //源表对目标表字段的映射,因为默认是按顺序以及名字匹配,所以这步很重要
                objSqlCopy.ColumnMappings.Add("人员编号", "RYBH");

                objSqlCopy.ColumnMappings.Add("姓名", "Name");
                objSqlCopy.ColumnMappings.Add("考勤日期", "KQDate");
                objSqlCopy.ColumnMappings.Add("班次", "BC");

                try
                {
                    //将DataTabel类型的objDT作为源拷贝到目标表
                    objSqlCopy.WriteToServer(ds.Tables[0]);
                    objSqlTran.Commit();
                }
                catch
                {
                    objSqlTran.Rollback();
                }
                finally
                {
                    objSqlCopy.Close();
                    sqlconn.Close();
                }

               // return objDT;

            }

        }
        catch (Exception ex)
        {
            System.Windows.Forms.MessageBox.Show(ex.Message);
        }

    }

  

}