c# excel批量上传数据

         下面就是excel批量上传数据到数据库

一：UI层

1.前台代码：

<a>上传文件(Excel)：<asp:FileUpload ID="FileUpload1" runat="server" Width="237px" /></a>
            <a>
                <asp:Button ID="btnUp" runat="server" Text="导入数据库" Width="75px"
                οnclick="btnUp_Click" /></a>

2.后台代码

 protected void btnUp_Click(object sender, EventArgs e)
    {
        //BLL创建实例化
        ScoreManagerBLL.ImportScoreManager ImportSocre = new ImportScoreManager();
        string path;//定义路径变量
      
        //HasFile用来检查FileUpload是否有指定文件
        if (this.FileUpload1.PostedFile == null || this.FileUpload1.PostedFile.FileName == "")
        {
            Response.Write("<script>alert('请您选择Excel文件')</script> ");
            return;
        }
        //System.IO.Path.GetExtension获得文件的扩展名
        string fileExt = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
        if (fileExt == ".xls" || fileExt == ".xlsx")
        {
            int len = this.FileUpload1.FileName.ToString().Trim().Length;
            //获取上传的虚拟路径“..”是上一目录
            path = "../TempPlace/" + this.FileUpload1.FileName.ToString().Trim();
            path = Server.MapPath(path);
            this.FileUpload1.SaveAs(path);
            //调用bll层的方法
            ImportSocre.ImaportOptional(path);
        }
        else
        {
            Response.Write("<script>alert('只可以选择Excel文件')</script>");
            return;//当选择的不是Excel文件时,返回       
        }
        //上传完毕后删除此Excel
        if (System.IO.File.Exists(path))
        {
            System.IO.File.Delete(path);
        }
        else
        {
            Response.Write("该Excel不存在！ ");
        }

    }

二：Bll层（这层采用将具体的做法封装，可以多次调用）

1.BLL外部开放的操作：

 public string ImaportOptional(string strPath)
        {
            //创建dal层的实例化
            OptionalCourseScoreDAO OptionalSDAO = new OptionalCourseScoreDAO();
            DataTable DT = new DataTable();
            //调用具体操作类中的方法，返回一个Datatable
            DT = ISBLL.WriteExcelToDataBase(strPath);
            //调用dll层的方法
            OptionalSDAO.ExcelToDataBase(DT);
            return null;
        }
        2.具体操作（封装到一个小类里面）

  public DataTable WriteExcelToDataBase(string strPath)
        {
            DataTable DT = new DataTable();
            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();

//取得sheet1中的数据
            try
            {
                DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                string sheetName = string.Empty;
                //只让次循环一次，取得的仅是sheet1中的表
                for (int j = 0; j < 1; j++)
                {
                    sheetName = schemaTable.Rows[j][2].ToString().Trim();//获取 Excel 的表名，默认值是sheet1
                    DT = ExcelToDataTable(strPath, sheetName, true);

               }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                objConn.Close();//关闭连接
                objConn.Dispose();//释放连接
            }
            return DT;

        }

此类必须调用的一个方法是：

 public static DataTable ExcelToDataTable(string serverMapPathExcel, string strSheetName, bool isTitleOrDataOfFirstRow)
        {

            string HDR = string.Empty;//如果第一行是数据而不是标题的话, 应该写: "HDR=No;"
            if (isTitleOrDataOfFirstRow)
            {
                HDR = "YES";//第一行是标题
            }
            else
            {
                HDR = "NO";//第一行是数据
            }
            //源的定义
            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;data source=" + serverMapPathExcel + ";Extended Properties='Excel 12.0 Xml;HDR=YES'";
            //Sql语句
            //string strExcel = string.Format("select * from [{0}$]", strSheetName); 这是一种方法
            string strExcel = "select * from   [" + strSheetName + "]";
            //定义存放的数据表
            DataSet ds = new DataSet();
            //连接数据源
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                try
                {    conn.Open();
                    //适配到数据源
                    OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
                    adapter.Fill(ds, strSheetName);
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return ds.Tables[strSheetName];
        }

三、DAL：

 public bool  ExcelToDataBase(DataTable DT)
        {

//调用sqlhelp中的批量导入方法，成功返回,0，不成功返回1

            if (SqlHelper.BatchInsertData("graduationProject", DT, 0, 1) == 0)
            {
                return true;
            }
            else
            {
                return false;
            }  

        }

四：Sqlhelp：中的方法:

 ''' <summary>
    ''' 批量插入数据
    ''' </summary>
    ''' <param name="sTableName">数据表名称</param>
    ''' <param name="dt">要插入的数据</param>
    ''' <param name="iSoursFieldOffset">源数据列的偏移量</param>
    ''' <param name="sDestinationFieldOffset">数据库中的列偏移量</param>
    ''' <returns></returns>
    ''' <remarks>Created on:2011-8-5 13:50:05 by sxy</remarks>
    Public Overloads Function BatchInsertData(ByVal sTableName As String, ByVal dt As DataTable, ByVal iSoursFieldOffset As Integer, ByVal sDestinationFieldOffset As Integer) As Integer
        Me.InitializeConnection() '初始化连接
        '数据批量导入sqlserver,创建实例
        Dim sqlbulk As System.Data.SqlClient.SqlBulkCopy = New System.Data.SqlClient.SqlBulkCopy(m_objConnection)
        '目标数据库表名
        sqlbulk.DestinationTableName = sTableName
        '数据集字段索引与数据库字段索引映射
        For i = 0 To dt.Columns.Count - 1
            sqlbulk.ColumnMappings.Add(iSoursFieldOffset + i, sDestinationFieldOffset + i)
        Next

        '导入
        sqlbulk.WriteToServer(dt)

        sqlbulk.Close()
        Me.FinalizeConnection() '关闭连接(与维护连接对象的方式有关)

        Return 0

    End Function

此方法为导入批量导入excel，有一个弊端就是只能读取Sheet1中的数据，但是对于方法的复用性还是可以考虑一下的

 

 

第二种方法：避免这个弊端可以将BLL层两个方法合并具体做法：

  public DataTable WriteExcelToDataBase(string strPath)
        {
            DataTable DT = new DataTable();
            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();

//取得sheet1中的数据
            try
            {
                DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                string sheetName = string.Empty;
                //只让其依次循环，将每一个sheet表的数据写入数据库

                for (int j = 0; j < 1; j++)
                {
                    sheetName = schemaTable.Rows[j][2].ToString().Trim();//获取 Excel 的表名，默认值是sheet1
                    DT = ExcelToDataTable(strPath, sheetName, true);

 if (excelDataTable.Columns.Count > 1)
                    {

                       EDAO.ExcelToDataBase(excelDataTable);
                   }

 

               }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                objConn.Close();//关闭连接
                objConn.Dispose();//释放连接
            }
            return DT;

        }

此类必须调用的一个方法是：

 public static DataTable ExcelToDataTable(string serverMapPathExcel, string strSheetName, bool isTitleOrDataOfFirstRow)
        {

            string HDR = string.Empty;//如果第一行是数据而不是标题的话, 应该写: "HDR=No;"
            if (isTitleOrDataOfFirstRow)
            {
                HDR = "YES";//第一行是标题
            }
            else
            {
                HDR = "NO";//第一行是数据
            }
            //源的定义
            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;data source=" + serverMapPathExcel + ";Extended Properties='Excel 12.0 Xml;HDR=YES'";
            //Sql语句
            //string strExcel = string.Format("select * from [{0}$]", strSheetName); 这是一种方法
            string strExcel = "select * from   [" + strSheetName + "]";
            //定义存放的数据表
            DataSet ds = new DataSet();
            //连接数据源
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                try
                {    conn.Open();
                    //适配到数据源
                    OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
                    adapter.Fill(ds, strSheetName);
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return ds.Tables[strSheetName];
        }

这就是我研究的excel批量导入，写的有点急（注释不够好），假如有不明白的可以留言，我们共同探讨…….