在Controller里,我们定义一个FileResult的Action,返回值是一个文件形式被浏览器下载下来。
[HttpGet] public FileResult ExportProductList1(ProductQueryParam param) { param.PageSize = 1000; var results = _baseInfoBusiness.ExportProduct(param, Customer.BookId);try { string filePath = Server.MapPath("~/others/tempFiles/商品列表.xls");///文件模板路径 FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read);///读取文件流 var buffer = DataExport.ExportProduct(results.Data.Items, file);///在Excel中追加数据,返回值是二进制数据流 var name = string.Format("{0}_{1:yyyyMMddHHmmss}.xls", "商品列表", DateTime.Now); return File(buffer, "application/vnd.ms-excel", name); } catch (Exception e) { } return null; }
Excel追加数据处理方法
public byte[] ExportProduct(List<ProductInfo> productList, FileStream file) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);///如果带参数是创建一个Excel,带参数就是读取一个Excel ISheet sheet = hssfworkbook.GetSheet("商品资料");///读完Sheet using (System.IO.MemoryStream ms = new System.IO.MemoryStream()) { for (int i = 0; i < productList.Count; i++) { IRow row = sheet.CreateRow(i + 4); row.CreateCell(0).SetCellValue(productList[i].CategoryName); row.CreateCell(1).SetCellValue(productList[i].No); row.CreateCell(2).SetCellValue(productList[i].Name); row.CreateCell(3).SetCellValue(productList[i].IniQty.ToString("f2"));///期初数量 row.CreateCell(4).SetCellValue(productList[i].IniPrice.ToString("f2"));///期初单价 row.CreateCell(5).SetCellValue(productList[i].IniTotal.ToString("f2"));///期初总价 row.CreateCell(6).SetCellValue(productList[i].Specification); for (int j = 0; j < productList[i].ProductProp.Count; j++) { row.CreateCell(7 + j).SetCellValue(productList[i].ProductProp[j].Name);///属性 } for (int j = productList[i].ProductProp.Count; j < 5; j++) { row.CreateCell(7 + productList[i].ProductProp.Count + j).SetCellValue("");///属性 } ///基本单位 row.CreateCell(12).SetCellValue(productList[i].UnitName); row.CreateCell(17).SetCellValue(productList[i].Barcode); row.CreateCell(20).SetCellValue(productList[i].RetailPrice.ToString("f2")); row.CreateCell(23).SetCellValue(productList[i].WholesalePrice.ToString("f2")); row.CreateCell(26).SetCellValue(productList[i].LowestsalePrice.ToString("f2")); row.CreateCell(29).SetCellValue(productList[i].PurchasePrice.ToString("f2")); if (productList[i].unitPrice == null | productList[i].unitPrice.Count>0) { ///副单位1 row.CreateCell(13).SetCellValue(productList[i].unitPrice[0].UnitName); row.CreateCell(14).SetCellValue(productList[i].unitPrice[0].Urate.ToString()); row.CreateCell(18).SetCellValue(productList[i].unitPrice[0].Barcode); row.CreateCell(21).SetCellValue(productList[i].unitPrice[0].RetailPrice.ToString("f2")); row.CreateCell(24).SetCellValue(productList[i].unitPrice[0].WholesalePrice.ToString("f2")); row.CreateCell(27).SetCellValue(productList[i].unitPrice[0].LowestsalePrice.ToString("f2")); row.CreateCell(30).SetCellValue(productList[i].unitPrice[0].PurchasePrice.ToString("f2")); if(productList[i].unitPrice.Count>1) { row.CreateCell(15).SetCellValue(productList[i].unitPrice[1].UnitName); row.CreateCell(16).SetCellValue(productList[i].unitPrice[1].Urate.ToString()); row.CreateCell(19).SetCellValue(productList[i].unitPrice[1].Barcode); row.CreateCell(22).SetCellValue(productList[i].unitPrice[1].RetailPrice.ToString("f2")); row.CreateCell(25).SetCellValue(productList[i].unitPrice[1].WholesalePrice.ToString("f2")); row.CreateCell(28).SetCellValue(productList[i].unitPrice[1].LowestsalePrice.ToString("f2")); row.CreateCell(31).SetCellValue(productList[i].unitPrice[1].PurchasePrice.ToString("f2")); } } ///库存预警 row.CreateCell(32).SetCellValue(productList[i].MinStock.ToString("f2")); row.CreateCell(33).SetCellValue(productList[i].MaxStock.ToString("f2")); row.CreateCell(34).SetCellValue(productList[i].Memo); row.CreateCell(35).SetCellValue(productList[i].IsStop?"停用":"启用"); } sheet.ForceFormulaRecalculation = true; hssfworkbook.Write(ms); return ms.ToArray(); } }