NOPI读取模板导出（Excel中追加数据）

在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();
            }
          
        }

 

转载于:https://www.cnblogs.com/huandashao/p/6020340.html