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