网页中NPIO对Excel的操作实例
上一节是在wpf中实现对excel的操作方法，这一节看看网页中如何封装实现对excel的上传导入和下载保存的。
看看效果图：
--------------------------
1、同样封装工具类Tools:
public class Tools { #region 读excel public static DataTable ImportExcelFile(string filepath) { DataTable dt = new DataTable(); //打开excel对话框 if (filepath != null) { HSSFWorkbook hssfworkbook = null; #region//初始化信息 try { using (FileStream file = new FileStream(filepath, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } } catch (Exception e) { throw e; } #endregion var sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) { dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString()); } while (rows.MoveNext()) { HSSFRow row = (HSSFRow)rows.Current; DataRow dr = dt.NewRow(); for (int i = 0; i < row.LastCellNum; i++) { var cell = row.GetCell(i); if (cell == null) { dr[i] = ""; } else { dr[i] = cell.ToString(); } } dt.Rows.Add(dr); } } return dt; } #endregion #region list转datatable public static DataTable ListToDataTable<T>(IEnumerable<T> c) { var props = typeof(T).GetProperties(); var dt = new DataTable(); dt.Columns.AddRange(props.Select(p => new DataColumn(p.Name, p.PropertyType)).ToArray()); if (c.Count() > 0) { for (int i = 0; i < c.Count(); i++) { ArrayList tempList = new ArrayList(); foreach (PropertyInfo item in props) { object obj = item.GetValue(c.ElementAt(i), null); tempList.Add(obj); } dt.LoadDataRow(tempList.ToArray(), true); } } return dt; } #endregion #region 写入excel public static MemoryStream WriteExcel<T>(IList<T> list) { //list转datatable var dt = ListToDataTable<T>(list); if (null != dt && dt.Rows.Count > 0) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1"); NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); for (int i = 0; i < dt.Columns.Count; i++) { row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName); } for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j])); } } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); ; book.Write(ms); ms.Seek(0, SeekOrigin.Begin); book = null; return ms; } return null; } #endregion }
2、网页界面设计：
@model IEnumerable<网页中NPIO对Excel的操作.Models.User> @{ ViewBag.Title = "Index"; } <h2>Index</h2> 1、导入数据： @*enctype= "multipart/form-data"是必需有的，否则action接收不到相应的file*@ @using (Html.BeginForm("Upload", "Home", FormMethod.Post, new { enctype = "multipart/form-data" })) { <br /> <span>上传文件</span> <br /> <input type="file" name="file" id="file1" /> <br /> <br /> <input id="ButtonUpload" type="submit" value="提交" class="btn btn-primary" /> }<br /> <hr /> 2、导出数据： <a href="/Home/Save">保存成excel</a> <p> @Html.ActionLink("Create New", "Create") </p> <table> <tr> <th> @Html.DisplayNameFor(model => model.name) </th> <th> @Html.DisplayNameFor(model => model.pwd) </th> <th></th> </tr> @if (Model != null) { foreach (var item in Model) { <tr> <td> @Html.DisplayFor(modelItem => item.name) </td> <td> @Html.DisplayFor(modelItem => item.pwd) </td> <td> @Html.ActionLink("Edit", "Edit", new { /* id=item.PrimaryKey */ }) | @Html.ActionLink("Details", "Details", new { /* id=item.PrimaryKey */ }) | @Html.ActionLink("Delete", "Delete", new { /* id=item.PrimaryKey */ }) </td> </tr> } } </table>
3、上传导入
public ActionResult Index() { return View(this.TempData["users"]); } [HttpPost] public ActionResult Upload(HttpPostedFileBase file) { if (file == null) { return Content("没有文件！", "text/plain"); } var tempname = DateTime.Now.ToLocalTime() + Path.GetExtension(file.FileName); var filename = tempname.Replace("/", "").Replace(" ", "").Replace(":", ""); var fileName = Path.Combine(Request.MapPath("~/Upload"), Path.GetFileName(filename)); try { file.SaveAs(fileName); } catch { return Content("上传异常 ！", "text/plain"); } var dt = Tools.ImportExcelFile(fileName); if (dt == null) return Content("取消"); List<User> list = new List<User>(); for (int i = 1; i < dt.Rows.Count; i++) { var row = dt.Rows[i]; list.Add(new User() { name = row[0].ToString(), pwd = row[1].ToString() }); } TempData["users"] = list; Session["users"] = list; return RedirectToAction("index"); }
4、保存下载
public ActionResult Save() { var list = Session["users"] as List<User>; var ms = Tools.WriteExcel(list); if (ms == null) return Content("保存失败,数据无效"); return File(ms, "application/vnd.ms-excel", "用户名单.xls"); }
//以下这个方法只作参考，实现保存下载就用上面方法 public ActionResult otherSave() { var list=Session["users"] as List<User>; if (list != null) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1"); // 第一列 NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); row.CreateCell(0).SetCellValue("姓名"); row.CreateCell(1).SetCellValue("密码"); // 第二列后 for (int i = 0; i < list.Count; i++) { User user = list[i] as User; IRow row2 = sheet.CreateRow(i+1); row2.CreateCell(0).SetCellValue(user.name); row2.CreateCell(1).SetCellValue(user.pwd); } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); //下面两句也可以实现导出文件功能 //Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff"))); //Response.BinaryWrite(ms.ToArray()); book = null; ms.Seek(0, SeekOrigin.Begin); return File(ms, "application/vnd.ms-excel", "用户名单.xls"); //ms.Close(); //ms.Dispose(); } else { return Content("无数据"); } //return Content("OK");//使用response时打开此备注 }
