C#讀取Excel顯示到repeater中

標籤：

首先需要一個用來儲存我們需要顯示的內容,防止頁面回傳丟失(添加時使用)

#region 快取檔案        private DataTable excelData;        /// <summary>        /// 緩衝已經讀取過的Datatable        /// </summary>        public DataTable ExcelData        {            get            {                if (ViewState["mydata"] != null)                {                    excelData = (DataTable)ViewState["mydata"];                }                return excelData;            }            set            {                ViewState["mydata"] = value;            }        }        #endregion

 接下來檢查上傳檔案

 /// <summary>        /// 檢查檔案上傳        /// </summary>        /// <param name="fileName"></param>        /// <returns></returns>        public string CheckFile(string fileName)        {            if (string.IsNullOrEmpty(fileName))            {                return "Please ensure that you upload file exists！";            }            if (!fileName.ToLower().EndsWith(".xls") && !fileName.ToLower().EndsWith(".xlsx"))            {                return "You select the file format is not correct, please try again！";            }            if (fileName.IndexOf(".") <= 0)            {                return "Please select a file to upload!";            }            return "";        }

 

 /// <summary>        /// 串連Excel，並讀取資料來源        /// </summary>        /// <param name="filepath">資料來源路徑</param>        /// <returns>Excel檔案的工作薄裡的資料</returns>        public DataSet connExcel(string filepath)        {            if (!string.IsNullOrEmpty(filepath))            {                try                {                    string connstring = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + filepath + ";Extended Properties=‘Excel 12.0; HDR=Yes; IMEX=1‘"; //此串連可以操作.xls與.xlsx檔案 (支援Excel2003 和 Excel2007 的連接字串)                    OleDbConnection objconn = new OleDbConnection(connstring);                    objconn.Open();                    DataTable dt = objconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "table" });                    string sheetname = "Sheet1$";                    sheetname = dt.Rows[0]["TABLE_NAME"].ToString();                    objconn.Close();                    string strsql = "select * from [" + sheetname + "]";                    DataSet ds = new DataSet();                    OleDbDataAdapter adp = new OleDbDataAdapter(strsql, objconn);                    adp.Fill(ds);                    return ds;                }                catch (Exception ex)                {                    throw new Exception("Occurs when a data source connection：" + ex.Message);                }            }            else            {                throw new Exception("File does not exist！");            }        }

 

 /// <summary>        /// 讀取Excel        /// </summary>        /// <param name="path"></param>        /// <returns></returns>        private DataTable readExcel(string path)        {            DataTable dt = new DataTable("myTestTable");            dt.Columns.Add("ID");            dt.Columns.Add("Name");            dt.Columns.Add("Sex");            DataView dv = new DataView(dt);            DataSet ds = connExcel(path);            return ds.Tables[0];        }

 

/// <summary>        /// 上傳檔案的方法        /// </summary>        /// <returns></returns>        [ScriptMethod]        private void PreviewData()        {            //擷取檔案名稱            string fileName = Path.GetFileName(this.fileSave.PostedFile.FileName);            //檢索檔案            string message = CheckFile(fileName);            if (!string.IsNullOrEmpty(message))            {                Page.ClientScript.RegisterStartupScript(this.GetType(), "ok", "alert(‘" + message + "‘)", true);                return;            }            try            {                string path = Server.MapPath("/MyTest/ExcelData/");                //檔案是否存在,如果不存在則建立                if (!Directory.Exists(path))                {                    Directory.CreateDirectory(path);                }                //定義檔案的隨機數                Random rand = new Random();                path += rand.Next(0, 9999999) + DateTime.Now.Hour + DateTime.Now.Minute + fileName;                //上傳檔案                this.fileSave.PostedFile.SaveAs(path);                //將資料內容儲存到緩衝中                ExcelData = this.readExcel(path);                this.rptData.Visible = true;                this.rptData.DataSource = ExcelData;                this.rptData.DataBind();            }            catch (Exception ex)            {                throw ex;                //Page.ClientScript.RegisterStartupScript(this.GetType(), "no", "alert(‘" + ex.Message + "‘)", true);            }        }

 

C#讀取Excel顯示到repeater中