ASP.NET MVC匯出excel

標籤：ons   res   單位   utc   mem   app   bin   erb   horizon   

前端代碼：

1 @Html.ActionLink("匯出Excel", "ExportWarehouseInOutDetailTable", new {warehouseInOutId = Model.Id},new {@class = "btn btn-primary pull-right"})

後端代碼

 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) //返回空File26             //var model = _service.DetailIncludeDetailsAndGoods(warehouseInOutId);27             ////if(model.IsNull() || model.Id <= 0) //返回空File28             //var workBook = _service.ExportNpoiExcelWookBook(model);29             //var ms = new MemoryStream();30             //workBook.Write(ms);31             //ms.Seek(0, SeekOrigin.Begin);32             //方法指定返回FileResult33             //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         }

 

ASP.NET MVC匯出excel