SqlServer----导入Excel数据(程序导入)

   业务中经常会遇到批量导入数据库的操作,今天总结一下方法.

          本文利用三层的架构的思想。将整个业务分成三部分，分别是界面，业务和数据处理。

          先介绍第一种方法:

          即先将Excel表格的内容转化到DataTable中，然后再将DataTable导入到SqlServer数据库.         

          下面看一个小demo

          界面上用到的控件是FileUpload和Button

 

<span style="font-size: 18px;">protected void btnImport_Click(object sender, EventArgs e)
    {

        string path;//定义文件保存的路径

        // 检查FileUpload是否有指定文件
        if (fupImport.PostedFile == null || fupImport.PostedFile.FileName == "")
        {
            Response.Write("<script>alert('请您选择Excel文件')</script> ");
            return;
        }
        //System.IO.Path.GetExtension获得文件的扩展名
        string fileExt = System.IO.Path.GetExtension(fupImport.FileName).ToString().ToLower();
        if (fileExt == ".xls" || fileExt == ".xlsx")
        {
            //给文件设置路径
            path = "TempPlace/" + this.fupImport.FileName.ToString().Trim();
            //将虚拟路径转化为物理路径
            path = Server.MapPath(path);
            fupImport.SaveAs(path);
            //调用b层的方法把Excel写入到数据库
            if (new BLL().ExcelToDataTable(path))
            {
                //成功导入到sql
                Response.Write("<script>alert('导入成功')</script>");
            }
        }
        else
        {
            //当选择的不是Excel文件时,返回
            Response.Write("<script>alert('只可以选择Excel文件')</script>");
            return;
        }


        if (System.IO.File.Exists(path))
        {
            System.IO.File.Delete(path);
        }
        else
        {
            Response.Write("该Excel不存在");
        }

    }</span>

protected void btnImport_Click(object sender, EventArgs e)
    {

        string path;//定义文件保存的路径

        // 检查FileUpload是否有指定文件
        if (fupImport.PostedFile == null || fupImport.PostedFile.FileName == "")
        {
            Response.Write("<script>alert('请您选择Excel文件')</script> ");
            return;
        }
        //System.IO.Path.GetExtension获得文件的扩展名
        string fileExt = System.IO.Path.GetExtension(fupImport.FileName).ToString().ToLower();
        if (fileExt == ".xls" || fileExt == ".xlsx")
        {
            //给文件设置路径
            path = "TempPlace/" + this.fupImport.FileName.ToString().Trim();
            //将虚拟路径转化为物理路径
            path = Server.MapPath(path);
            fupImport.SaveAs(path);
            //调用b层的方法把Excel写入到数据库
            if (new BLL().ExcelToDataTable(path))
            {
                //成功导入到sql
                Response.Write("<script>alert('导入成功')</script>");
            }
        }
        else
        {
            //当选择的不是Excel文件时,返回
            Response.Write("<script>alert('只可以选择Excel文件')</script>");
            return;
        }


        if (System.IO.File.Exists(path))
        {
            System.IO.File.Delete(path);
        }
        else
        {
            Response.Write("该Excel不存在");
        }

    }

        

 

 

        逻辑层的主要任务是将界面传过来的Excel转化到DataTable中,并将DataTable传给Dal层处理.因为读取Excel的操作不是针对的Sql,而是用的OLEDB,所以放到了逻辑层.        

<span style="font-size: 18px;"> /// <summary>
    /// 将坐在路径的Excel中的内容写入到DataTable中
    /// </summary>
    /// <param name="strPath">需要导入的Excel的路径</param>
    /// <returns>返回true 或false</returns>
    public bool ExcelToDataTable(string strPath)
    {
        DataTable DT = new DataTable();
        //设置数据连接
          // HDR = "YES",第一行是标题 ,则第一行不写如数据库;
        //HDR = "NO" 第一行是数据  ,则第一行写入数据库;    这个选择可以由界面当作参数传下来.
        OleDbConnection objConn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data source=" + strPath + ";Extended Properties='Excel 12.0 Xml;HDR=YES'");
        //打开连接
        objConn.Open();
        try
        {
            //获取 Excel 的表名，默认值是sheet1
            DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            string sheetName = string.Empty;
            sheetName = schemaTable.Rows[0][2].ToString().Trim();
            //Sql语句
               //string strExcel = string.Format("select * from [{0}$]", strSheetName); 这是一种方法
               //string strExcel = "select * from   [" + sheetName + "]";
            string strExcel = "select * from [Sheet1$]";

            //定义存放的数据表
               DataSet ds = new DataSet();
            //连接数据源
               OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, objConn);
            //将Excel表中内容填入ds中
               adapter.Fill(ds, sheetName);
            //调用d层方法
               return new DAL().BatchInsertData("user_Info", ds.Tables[sheetName]);
        }
        catch (SqlException ex)
        {
            throw ex;
        }
        finally
        {
            //关闭连接
            objConn.Close();
            objConn.Dispose();
        }
    }</span>

 /// <summary>
    /// 将坐在路径的Excel中的内容写入到DataTable中
    /// </summary>
    /// <param name="strPath">需要导入的Excel的路径</param>
    /// <returns>返回true 或false</returns>
    public bool ExcelToDataTable(string strPath)
    {
        DataTable DT = new DataTable();
        //设置数据连接
          // HDR = "YES",第一行是标题 ,则第一行不写如数据库;
        //HDR = "NO" 第一行是数据  ,则第一行写入数据库;    这个选择可以由界面当作参数传下来.     
        OleDbConnection objConn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;data source=" + strPath + ";Extended Properties='Excel 12.0 Xml;HDR=YES'");
        //打开连接
        objConn.Open();
        try
        {
            //获取 Excel 的表名，默认值是sheet1 
            DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            string sheetName = string.Empty;
            sheetName = schemaTable.Rows[0][2].ToString().Trim();
            //Sql语句
               //string strExcel = string.Format("select * from [{0}$]", strSheetName); 这是一种方法
               //string strExcel = "select * from   [" + sheetName + "]";
            string strExcel = "select * from [Sheet1$]";

            //定义存放的数据表
               DataSet ds = new DataSet();
            //连接数据源 
               OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, objConn);
            //将Excel表中内容填入ds中
               adapter.Fill(ds, sheetName);
            //调用d层方法
               return new DAL().BatchInsertData("user_Info", ds.Tables[sheetName]);
        }
        catch (SqlException ex)
        {
            throw ex;
        }
        finally
        {
            //关闭连接
            objConn.Close();
            objConn.Dispose();
        }
    }

         

 

           D层,具体处理,将得到的DataTable批量导入到Sql中 

<span style="font-size: 18px;">/// <summary>
    /// 将传过来的DataTable导入到Sql
    /// </summary>
    /// <param name="strTableName">要导入的sql数据库表名</param>
    /// <param name="dt">待导入的数据表</param>
    /// <returns></returns>
    public bool BatchInsertData(string strTableName, DataTable dt)
    {
        bool blnResult;
        //初始化连接
        string strConn = "server=192.168.*.*;database=Student;uid=sa;pwd=123";
        SqlConnection objConn = new SqlConnection(strConn);
        objConn.Open();
        //数据批量导入sqlserver,创建实例
        System.Data.SqlClient.SqlBulkCopy sqlbulk = new System.Data.SqlClient.SqlBulkCopy(objConn);
        //目标数据库表名
        sqlbulk.DestinationTableName = strTableName;
        try
        {
          //数据集字段索引与数据库字段索引映射
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                sqlbulk.ColumnMappings.Add(i, i);
            }
          //导入
            sqlbulk.WriteToServer(dt);
          //导入成功
            blnResult = true;
        }
        catch (Exception)
        {
            blnResult = false;
            throw;
        }
        finally {
          //关闭连接
            sqlbulk.Close();
          objConn.Close();
        }
        return blnResult;

    }</span>

/// <summary>
    /// 将传过来的DataTable导入到Sql
    /// </summary>
    /// <param name="strTableName">要导入的sql数据库表名</param>
    /// <param name="dt">待导入的数据表</param>
    /// <returns></returns>
    public bool BatchInsertData(string strTableName, DataTable dt)
    {
        bool blnResult;
        //初始化连接
        string strConn = "server=192.168.*.*;database=Student;uid=sa;pwd=123";
        SqlConnection objConn = new SqlConnection(strConn);
        objConn.Open();
        //数据批量导入sqlserver,创建实例
        System.Data.SqlClient.SqlBulkCopy sqlbulk = new System.Data.SqlClient.SqlBulkCopy(objConn);
        //目标数据库表名
        sqlbulk.DestinationTableName = strTableName;
        try
        {           
          //数据集字段索引与数据库字段索引映射
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                sqlbulk.ColumnMappings.Add(i, i);
            }
          //导入
            sqlbulk.WriteToServer(dt);  
          //导入成功
            blnResult = true;
        }
        catch (Exception)
        {
            blnResult = false;
            throw;
        }
        finally {       
          //关闭连接
            sqlbulk.Close();
          objConn.Close();
        }
        return blnResult;
         
    }

          这样通过两次调用,Excel就可以顺利导入数据库了，这种方法必须是数据库字段>=Excel表的字段,而且导入时是默认从左到右字段一一对应,如果不需要导入和数据库相等的字段,且字段不连续,可以用数组进行字段的控制.

          在数据库进行映射时的代码如下,其他代码相同,不再赘述.         

<span style="font-size: 18px;">            int[] intSqlFeild = { 0, 7 };
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                //建立映射
                sqlbulk.ColumnMappings.Add(i , intSqlFeild[i]);

            }</span>

            int[] intSqlFeild = { 0, 7 };
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                //建立映射
                sqlbulk.ColumnMappings.Add(i , intSqlFeild[i]);
     
            }

           通过这种方式可以将Excel表的数据和数据库灵活绑定.但相对来说代码较多. 

           ps:由于本文着重实现功能,这个demo的复用性可能不好,如果工程中很多表,很多数据库中需要用到,可以进行重构,将关于表和数据库的内容提取出来,写成要传的参数或者配置文件.B层和D层的方法也可以更细的拆分.