把Excel中的資料匯入gridView顯示，再匯入資料庫

把Excel中的資料匯入gridView顯示，再匯入資料庫

  using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.Data;using System.Data.OleDb;

//建立一個臨時DataTable，為了把Excel中的資料匯入gridView後再匯入資料庫。        private static DataTable dtTemp;        /// <summary>        /// 把Excel匯入Gridview，首先把檔案上傳到伺服器        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        protected void btnImport_Click(object sender, EventArgs e)        {            //匯入Excel檔案            //檢查檔案是否存在            //HasFile用來檢查上傳檔案控制項FileUpload是否有指定檔案            if (FileUpload1.HasFile == false)            {                Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('請您選擇Excel檔案! ');</script>");                return;//當無檔案時,返回            }            string filename = DateTime.Now.ToString("yyyymmddhhMMss") + FileUpload1.FileName;            //擷取Execle檔案名稱  DateTime日期函數            string savePath = Server.MapPath(("../Doc/") + filename);//Server.MapPath 獲得虛擬伺服器相對路徑            //如果已經存在就清空            ClearFile(Server.MapPath("../Doc/"));            FileUpload1.SaveAs(savePath);                        //SaveAs 將上傳的檔案內容儲存在伺服器上            DataTable dt = createDataSource(savePath);            if (dtTemp == null)            {                dtTemp = new DataTable();                dtTemp = dt;            }            GridView1.DataSource = dt;            GridView1.DataBind();        }        /// <summary>        /// 從gridview匯入資料庫        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        protected void btnSubmit_Click(object sender, EventArgs e)        {            DataTable dt = new DataTable();            dt = dtTemp;            for (int i = 0; i < dt.Rows.Count; i++)            {                //匯入資料庫的代碼省略            }            Response.Write("<script>alert('添加成功！！')</script>");        }             /// <summary>        /// 將路徑下的Excel檔案轉換為DataTable類型的資料來源        /// </summary>        /// <param name="strPath">Excel路徑</param>        /// <returns></returns>        private DataTable createDataSource(string strPath)        {            string strCon;            strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strPath + ";Extended Properties=Excel 8.0";            OleDbConnection con = new OleDbConnection(strCon);            OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", con);            DataTable dt = new DataTable();            da.Fill(dt);            return dt;        }        /// <summary>        /// 將Excel檔案暫存到伺服器端的一個檔案夾中，用這個方法刪除掉        /// </summary>        /// <param name="FilePath">Excel路徑</param>        private void ClearFile(string FilePath)        {            String[] files = System.IO.Directory.GetFiles(FilePath);            if (files.Length > 5)            {                for (int i = 0; i < 5; i++)                {                    try                    {                        System.IO.File.Delete(files[i]);                    }                    catch                    {                    }                }            }        }