ASP.NET MVC导出excel npoi
使用npoi组件
前端代码:
1 @Html.ActionLink("导出Excel", "ExportWarehouseInOutDetailTable", new {warehouseInOutId = Model.Id},new {@class = "btn btn-primary pull-right"})
1 @*ie8低版本以下不支持h5的formaction特性,故改用下一种方法。但这样有个小bug:修改查询条件后点击“导出Excel”只会导出条件未修改前的数据,但“查询”按钮无此问题*@ 2 @*<input type="submit" formaction="" class="btn btn-primary btn-small" value="导出Excel" />*@ 3 <a class="btn btn-primary btn-small" href="@("/WarehouseInOut/ExportTable?" + Request.QueryString.ToString())">导出Excel</a>
后端代码
1 public void ExportWarehouseInOutDetailTable(long warehouseInOutId) 2 { 3 Response.Clear(); 4 if (warehouseInOutId <= 0) 5 { 6 Response.Write("<script>confirm('没有查询到任何数据!')</script>"); 7 return; 8 } 9 var model = _service.DetailIncludeDetailsAndGoods(warehouseInOutId); 10 if (model.IsNull() || model.Id <= 0) 11 { 12 Response.Write("<script>confirm('没有查询到任何数据!')</script>"); 13 return; 14 } 15 16 Response.ContentType = "application/vnd.ms-excel"; 17 string fileName = string.Format("{0}_{1}.xls", model.StorageStatus.GetEnumDesc(), model.SerialId); 18 Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName + ".xls")); 19 Response.Clear(); 20 var workBook = _service.ExportWarehouseInOutDetailTable(model); 21 var ms = new MemoryStream(); 22 workBook.Write(ms); 23 Response.BinaryWrite(ms.GetBuffer()); 24 #region 旧代码返回File,已注释 25 ////if(warehouseInOutId<=0) //返回空File 26 //var model = _service.DetailIncludeDetailsAndGoods(warehouseInOutId); 27 ////if(model.IsNull() || model.Id <= 0) //返回空File 28 //var workBook = _service.ExportNpoiExcelWookBook(model); 29 //var ms = new MemoryStream(); 30 //workBook.Write(ms); 31 //ms.Seek(0, SeekOrigin.Begin); 32 //方法指定返回FileResult 33 //return File(ms, "application/vnd.ms-excel" 34 #endregion, string.Format("{0}_{1}.xls", model.StorageStatus.GetEnumDesc(), model.SerialId)); 35 } 36 37 public HSSFWorkbook ExportWarehouseInOutDetailTable(WarehouseInOutContract model) 38 { 39 HSSFWorkbook workBook = new HSSFWorkbook(); 40 ISheet sheet1 = workBook.CreateSheet("Sheet1"); 41 ICellStyle centerCellstyle = workBook.CreateCellStyle(); 42 centerCellstyle.VerticalAlignment = VerticalAlignment.Center; 43 centerCellstyle.Alignment = HorizontalAlignment.Center; 44 ICellStyle centerBoldCellstyle = centerCellstyle; 45 HSSFFont font = (HSSFFont) workBook.CreateFont(); 46 font.Boldweight = (short)FontBoldWeight.Bold; 47 centerBoldCellstyle.SetFont(font); 48 int rowNumIndex = 0; 49 50 IRow row1 = sheet1.CreateRow(rowNumIndex++); 51 var cellTitle = row1.CreateCell(0); 52 cellTitle.SetCellValue(model.StorageStatus.GetEnumDesc()); 53 cellTitle.CellStyle = centerBoldCellstyle; //样式必须要单独指定到cell元素,直接指定到行无效:row1.RowStyle = centerBoldCellstyle; 54 sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 5)); 55 56 IRow row2 = sheet1.CreateRow(rowNumIndex++); 57 row2.CreateCell(0).SetCellValue("单据编号"); //TODO:待实现 GetDisplayAttrName(model.SerialId) 58 row2.CreateCell(1).SetCellValue(model.SerialId); 59 row2.CreateCell(2).SetCellValue("创建时间"); 60 row2.CreateCell(3).SetCellValue(model.CreatedOn); 61 if (!model.IsOutWarehouse) 62 { 63 row2.CreateCell(4).SetCellValue("本批次总价"); 64 row2.CreateCell(5).SetCellValue(decimal.ToDouble(model.TotalPrice)); 65 } 66 67 IRow row3 = sheet1.CreateRow(rowNumIndex++); 68 row3.CreateCell(0).SetCellValue("仓库管理员姓名"); 69 row3.CreateCell(1).SetCellValue(model.WarehouseHandlerName); 70 row3.CreateCell(2).SetCellValue("采购员或领料员"); 71 row3.CreateCell(3).SetCellValue(model.OutHandlerName); 72 row3.CreateCell(4).SetCellValue("所属仓库名称"); 73 row3.CreateCell(5).SetCellValue(model.WarehouseAreaName); 74 75 IRow row4 = sheet1.CreateRow(rowNumIndex++); 76 row4.CreateCell(0).SetCellValue("物品名称"); 77 row4.CreateCell(1).SetCellValue("单位名称"); 78 row4.CreateCell(2).SetCellValue("类型名称"); 79 row4.CreateCell(3).SetCellValue("总价"); 80 row4.CreateCell(4).SetCellValue("数量"); 81 row4.CreateCell(5).SetCellValue("单价"); 82 row4.CreateCell(6).SetCellValue("备注"); 83 foreach (var det in model.WarehouseInOutDetails) 84 { 85 IRow rowDet = sheet1.CreateRow(rowNumIndex++); 86 rowDet.CreateCell(0).SetCellValue(det.Goods.Name); 87 rowDet.CreateCell(1).SetCellValue(det.Goods.UnitName); 88 rowDet.CreateCell(2).SetCellValue(det.Goods.TypeName); 89 rowDet.CreateCell(3).SetCellValue(decimal.ToDouble(det.TotalPrice)); 90 rowDet.CreateCell(4).SetCellValue(det.Quantity); 91 rowDet.CreateCell(5).SetCellValue(decimal.ToDouble(det.UnitPrice)); 92 rowDet.CreateCell(6).SetCellValue(det.Remark); 93 } 94 return workBook; 95 }
参考网站:
http://www.cnblogs.com/jiekzou/p/4766701.html
https://dotblogs.com.tw/killysss/archive/2010/01/27/13344.aspx
http://www.cnblogs.com/xwgli/archive/2013/05/03/3057824.html
http://www.cnblogs.com/bubugao/p/Excel.html
http://www.cnblogs.com/Lxy-/p/5791721.html