ASP.NET MVC导出excel npoi

使用npoi组件

前端代码：

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

 @*ie8低版本以下不支持h5的formaction特性，故改用下一种方法。但这样有个小bug：修改查询条件后点击“导出Excel”只会导出条件未修改前的数据，但“查询”按钮无此问题*@
@*<input type="submit" formaction="" class="btn btn-primary btn-small" value="导出Excel" />*@
<a class="btn btn-primary btn-small" href="@("/WarehouseInOut/ExportTable?" + Request.QueryString.ToString())">导出Excel</a>

后端代码

public void ExportWarehouseInOutDetailTable(long warehouseInOutId)
        {
            Response.Clear();
            if (warehouseInOutId <= 0)
            {
                Response.Write("<script>confirm('没有查询到任何数据！')</script>");
                return;
            }
            var model = _service.DetailIncludeDetailsAndGoods(warehouseInOutId);
            if (model.IsNull() || model.Id <= 0)
            {
                Response.Write("<script>confirm('没有查询到任何数据！')</script>");
                return;
            }

            Response.ContentType = "application/vnd.ms-excel";
            string fileName = string.Format("{0}_{1}.xls", model.StorageStatus.GetEnumDesc(), model.SerialId);
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName + ".xls"));
            Response.Clear();
            var workBook = _service.ExportWarehouseInOutDetailTable(model);
            var ms = new MemoryStream();
            workBook.Write(ms);
            Response.BinaryWrite(ms.GetBuffer());
            #region 旧代码返回File，已注释
            ////if(warehouseInOutId<=0) //返回空File
            //var model = _service.DetailIncludeDetailsAndGoods(warehouseInOutId);
            ////if(model.IsNull() || model.Id <= 0) //返回空File
            //var workBook = _service.ExportNpoiExcelWookBook(model);
            //var ms = new MemoryStream();
            //workBook.Write(ms);
            //ms.Seek(0, SeekOrigin.Begin);
            //方法指定返回FileResult
            //return File(ms, "application/vnd.ms-excel" 
            #endregion, string.Format("{0}_{1}.xls", model.StorageStatus.GetEnumDesc(), model.SerialId));
        }

public HSSFWorkbook ExportWarehouseInOutDetailTable(WarehouseInOutContract model)
        {
            HSSFWorkbook workBook = new HSSFWorkbook();
            ISheet sheet1 = workBook.CreateSheet("Sheet1");
            ICellStyle centerCellstyle = workBook.CreateCellStyle();
            centerCellstyle.VerticalAlignment = VerticalAlignment.Center;
            centerCellstyle.Alignment = HorizontalAlignment.Center;
            ICellStyle centerBoldCellstyle = centerCellstyle;
            HSSFFont font = (HSSFFont) workBook.CreateFont();
            font.Boldweight = (short)FontBoldWeight.Bold;
            centerBoldCellstyle.SetFont(font);
            int rowNumIndex = 0;

            IRow row1 = sheet1.CreateRow(rowNumIndex++);
            var cellTitle = row1.CreateCell(0);
            cellTitle.SetCellValue(model.StorageStatus.GetEnumDesc());
            cellTitle.CellStyle = centerBoldCellstyle;  //样式必须要单独指定到cell元素，直接指定到行无效：row1.RowStyle = centerBoldCellstyle; 
            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 5));

            IRow row2 = sheet1.CreateRow(rowNumIndex++);
            row2.CreateCell(0).SetCellValue("单据编号");    //TODO:待实现 GetDisplayAttrName(model.SerialId)
            row2.CreateCell(1).SetCellValue(model.SerialId);
            row2.CreateCell(2).SetCellValue("创建时间");
            row2.CreateCell(3).SetCellValue(model.CreatedOn);
            if (!model.IsOutWarehouse)
            {
                row2.CreateCell(4).SetCellValue("本批次总价");
                row2.CreateCell(5).SetCellValue(decimal.ToDouble(model.TotalPrice));
            }

            IRow row3 = sheet1.CreateRow(rowNumIndex++);
            row3.CreateCell(0).SetCellValue("仓库管理员姓名");
            row3.CreateCell(1).SetCellValue(model.WarehouseHandlerName);
            row3.CreateCell(2).SetCellValue("采购员或领料员");
            row3.CreateCell(3).SetCellValue(model.OutHandlerName);
            row3.CreateCell(4).SetCellValue("所属仓库名称");
            row3.CreateCell(5).SetCellValue(model.WarehouseAreaName);

            IRow row4 = sheet1.CreateRow(rowNumIndex++);
            row4.CreateCell(0).SetCellValue("物品名称");
            row4.CreateCell(1).SetCellValue("单位名称");
            row4.CreateCell(2).SetCellValue("类型名称");
            row4.CreateCell(3).SetCellValue("总价");
            row4.CreateCell(4).SetCellValue("数量");
            row4.CreateCell(5).SetCellValue("单价");
            row4.CreateCell(6).SetCellValue("备注");
            foreach (var det in model.WarehouseInOutDetails)
            {
                IRow rowDet = sheet1.CreateRow(rowNumIndex++);
                rowDet.CreateCell(0).SetCellValue(det.Goods.Name);
                rowDet.CreateCell(1).SetCellValue(det.Goods.UnitName);
                rowDet.CreateCell(2).SetCellValue(det.Goods.TypeName);
                rowDet.CreateCell(3).SetCellValue(decimal.ToDouble(det.TotalPrice));
                rowDet.CreateCell(4).SetCellValue(det.Quantity);
                rowDet.CreateCell(5).SetCellValue(decimal.ToDouble(det.UnitPrice));
                rowDet.CreateCell(6).SetCellValue(det.Remark);
            }
            return workBook;
        }

 参考网站：

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