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

在Controller里，我们定义一个FileResult的Action，返回值是一个文件形式被浏览器下载下来。

　　　　[HttpGet]

        public FileResult ExportProductList1(ProductQueryParam param)

        {

            param.PageSize = ;

            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 = ; i < productList.Count; i++)

                {

                    IRow row = sheet.CreateRow(i + );

                    row.CreateCell().SetCellValue(productList[i].CategoryName);

                    row.CreateCell().SetCellValue(productList[i].No);

                    row.CreateCell().SetCellValue(productList[i].Name);

                    row.CreateCell().SetCellValue(productList[i].IniQty.ToString("f2"));///期初数量

                    row.CreateCell().SetCellValue(productList[i].IniPrice.ToString("f2"));///期初单价

                    row.CreateCell().SetCellValue(productList[i].IniTotal.ToString("f2"));///期初总价

                    row.CreateCell().SetCellValue(productList[i].Specification);

                    for (int j = ; j < productList[i].ProductProp.Count; j++)

                    {

                        row.CreateCell( + j).SetCellValue(productList[i].ProductProp[j].Name);///属性

                    }

                    for (int j = productList[i].ProductProp.Count; j < ; j++)

                    {

                        row.CreateCell( + productList[i].ProductProp.Count + j).SetCellValue("");///属性

                    }

                    ///基本单位

                    row.CreateCell().SetCellValue(productList[i].UnitName);

                    row.CreateCell().SetCellValue(productList[i].Barcode);

                    row.CreateCell().SetCellValue(productList[i].RetailPrice.ToString("f2"));

                    row.CreateCell().SetCellValue(productList[i].WholesalePrice.ToString("f2"));

                    row.CreateCell().SetCellValue(productList[i].LowestsalePrice.ToString("f2"));

                    row.CreateCell().SetCellValue(productList[i].PurchasePrice.ToString("f2"));

                    if (productList[i].unitPrice == null | productList[i].unitPrice.Count>)

                    {

                        ///副单位1

                        row.CreateCell().SetCellValue(productList[i].unitPrice[].UnitName);

                        row.CreateCell().SetCellValue(productList[i].unitPrice[].Urate.ToString());

                        row.CreateCell().SetCellValue(productList[i].unitPrice[].Barcode);

                        row.CreateCell().SetCellValue(productList[i].unitPrice[].RetailPrice.ToString("f2"));

                        row.CreateCell().SetCellValue(productList[i].unitPrice[].WholesalePrice.ToString("f2"));

                        row.CreateCell().SetCellValue(productList[i].unitPrice[].LowestsalePrice.ToString("f2"));

                        row.CreateCell().SetCellValue(productList[i].unitPrice[].PurchasePrice.ToString("f2"));

                        if(productList[i].unitPrice.Count>)

                        {

                            row.CreateCell().SetCellValue(productList[i].unitPrice[].UnitName);

                            row.CreateCell().SetCellValue(productList[i].unitPrice[].Urate.ToString());

                            row.CreateCell().SetCellValue(productList[i].unitPrice[].Barcode);

                            row.CreateCell().SetCellValue(productList[i].unitPrice[].RetailPrice.ToString("f2"));

                            row.CreateCell().SetCellValue(productList[i].unitPrice[].WholesalePrice.ToString("f2"));

                            row.CreateCell().SetCellValue(productList[i].unitPrice[].LowestsalePrice.ToString("f2"));

                            row.CreateCell().SetCellValue(productList[i].unitPrice[].PurchasePrice.ToString("f2"));

                        }

                    }

                    ///库存预警

                    row.CreateCell().SetCellValue(productList[i].MinStock.ToString("f2"));

                    row.CreateCell().SetCellValue(productList[i].MaxStock.ToString("f2"));

                    row.CreateCell().SetCellValue(productList[i].Memo);

                    row.CreateCell().SetCellValue(productList[i].IsStop?"停用":"启用");

                }

                sheet.ForceFormulaRecalculation = true;

                hssfworkbook.Write(ms);

                return ms.ToArray();

            }

        }