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"
                onclick="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大量匯入，寫的有點急（注釋不夠好），假如有不明白的可以留言，我們共同探討…….