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);
            }
        }