使用NPOI导出Excel文件

阳光穿透心脏的1/2处 2021-09-10 10:02 593阅读 0赞

使用NPOI导出Excel文件，本实例使用了ASP.NET MVC。

**1、使用NPOI导出Excel文件**

实例：导出商品列表。

要求：1、通过NPOI导出导出商品列表信息；

2、使用Excel函数计算商品总金额；

在Controllers控制器目录中创建ExportController.cs控制器

using System.IO;
    using NPOI;
    using NPOI.POIFS;
    using NPOI.HSSF;
    using NPOI.Util;
    using NPOI.HSSF.UserModel;
    using NPOI.HPSF;
    using NPOI.SS.UserModel;
    using NPOI.SS.Util;
    using NPOI.HSSF.Util;

/// <summary>
    /// 导出商品列表
    /// </summary>
    public FileResult ExportProduct()
    {
        //创建一个新的xls文件
        HSSFWorkbook workbook = new HSSFWorkbook();
    
        //创建DocumentSummaryInformation（选填）
        DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
        dsi.Company = "深圳市电子商务有限公司";
        dsi.Category = "报表";
        //创建SummaryInformation（选填）
        SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
        si.Subject = "商品列表";
        si.Title = "商品列表导出";
        si.Author = "Kevin Pan";
        //把创建好的对象赋给Workbook
        workbook.DocumentSummaryInformation = dsi;
        workbook.SummaryInformation = si;
    
        //创建一个Sheet
        ISheet sheet = workbook.CreateSheet("Sheet1");
        sheet.DefaultRowHeight = 300;
        //创建标题
        IRow rowTitle = sheet.CreateRow(0);
        rowTitle.Height = 500;
        ICellStyle styleTitle = workbook.CreateCellStyle();
        styleTitle.Alignment = HorizontalAlignment.Center;
        styleTitle.VerticalAlignment = VerticalAlignment.Center;
        IFont fontTitle = workbook.CreateFont();
        fontTitle.FontName = "宋体";
        fontTitle.FontHeightInPoints = 18;
        styleTitle.SetFont(fontTitle);
        ICell cellTitle = rowTitle.CreateCell(0);
        cellTitle.SetCellValue("商品列表");
        cellTitle.CellStyle = styleTitle;
        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 5));  //合并单元格
        //创建表格样式
        IFont font = workbook.CreateFont();
        font.FontName = "宋体";
        font.FontHeightInPoints = 10;
        ICellStyle style = workbook.CreateCellStyle(); ;
        style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        style.BottomBorderColor = HSSFColor.Black.Index;
        style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
        style.LeftBorderColor = HSSFColor.Black.Index;
        style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
        style.RightBorderColor = HSSFColor.Black.Index;
        style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
        style.TopBorderColor = HSSFColor.Black.Index;
        style.Alignment = HorizontalAlignment.Center;
        style.SetFont(font);
        //创建表头
        IRow rowHead = sheet.CreateRow(1);
        rowHead.CreateCell(0).SetCellValue("序号");
        rowHead.GetCell(0).CellStyle = style;
        sheet.SetColumnWidth(0, 256 * 5);
    
        rowHead.CreateCell(1).SetCellValue("商品名称");
        rowHead.GetCell(1).CellStyle = style;
        sheet.SetColumnWidth(1, 256 * 25);
    
        rowHead.CreateCell(2).SetCellValue("商品品牌");
        rowHead.GetCell(2).CellStyle = style;
        sheet.SetColumnWidth(2, 256 * 20);
    
        rowHead.CreateCell(3).SetCellValue("商品价格");
        rowHead.GetCell(3).CellStyle = style;
        sheet.SetColumnWidth(3, 256 * 15);
    
        rowHead.CreateCell(4).SetCellValue("数量");
        rowHead.GetCell(4).CellStyle = style;
        sheet.SetColumnWidth(3, 256 * 10);
    
        rowHead.CreateCell(5).SetCellValue("总金额");
        rowHead.GetCell(5).CellStyle = style;
        sheet.SetColumnWidth(3, 256 * 15);
    
        //获取商品列表数据
        List<ProductModel> dataList = GetProductList();
        //绑定表内容
        int rowindex = 2;
        int xh = 1;
        foreach (var item in dataList)
        {
            IRow rowContent = sheet.CreateRow(rowindex);
            rowContent.CreateCell(0).SetCellValue(xh);
            rowContent.GetCell(0).CellStyle = style;
    
            rowContent.CreateCell(1).SetCellValue(item.ProductName);
            rowContent.GetCell(1).CellStyle = style;
    
            rowContent.CreateCell(2).SetCellValue(item.ProductBrand);
            rowContent.GetCell(2).CellStyle = style;
    
            rowContent.CreateCell(3).SetCellValue(item.ProductPrice.ToString());
            rowContent.GetCell(3).CellStyle = style;
    
            rowContent.CreateCell(4).SetCellValue(item.Quantity.ToString());
            rowContent.GetCell(4).CellStyle = style;
            //设置函数，计算总金额
            rowContent.CreateCell(5).SetCellFormula(String.Format("$D{0}*$E{0}", rowindex+1));
            rowContent.GetCell(5).CellStyle = style;
    
            rowindex++;
            xh++;
        }
    
        //输出 
        System.IO.MemoryStream ms = new System.IO.MemoryStream();
        workbook.Write(ms);
        ms.Seek(0, SeekOrigin.Begin);
        return File(ms, "application/vnd.ms-excel", "商品列表.xls");
    }

**2、基于.xls模板导出Excel文件**

实例：基于.xls模板导出订单信息（如图 1）

要求：1、使用基于.xls模板导出订单信息；

2、使用Excel函数计算订单和商品的总金额；

![SouthEast][]

图 1 基于.xls模板导出订单信息

/// <summary>
    /// 导出订单信息
    /// </summary>
    public FileResult ExportOrder()
    { 
        //获取订单信息
        OrderModel order = GetOrderInfo();
        //获取Excel模板
        string fileName = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + @"/Template/order.xls";
        FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read);
        //通过模板创建一个xls文件
        HSSFWorkbook workbook = new HSSFWorkbook(file);
        //获取Sheet
        ISheet sheet = workbook.GetSheet("Sheet1");
        //导出订单信息
        sheet.GetRow(1).CreateCell(1).SetCellValue(order.OrderNo);
        sheet.GetRow(1).CreateCell(3).SetCellValue(order.OrderTime.ToString("yyyy-MM-dd HH:mm:ss"));
        sheet.GetRow(1).CreateCell(5).SetCellFormula("SUM(F6:F100)"); //设置函数，计算订单总金额
        sheet.GetRow(2).CreateCell(1).SetCellValue(order.CustomName);
        sheet.GetRow(2).CreateCell(3).SetCellValue(order.CustomAddress);
        //导出产品列表
        if (order.ProductList != null && order.ProductList.Count > 0)
        {
            //创建表头
            IRow rowHead = sheet.CreateRow(4);
            rowHead.CreateCell(0).SetCellValue("序号");
            sheet.SetColumnWidth(0, 256 * 15);
    
            rowHead.CreateCell(1).SetCellValue("商品名称");
            sheet.SetColumnWidth(1, 256 * 25);
    
            rowHead.CreateCell(2).SetCellValue("商品品牌");
            sheet.SetColumnWidth(2, 256 * 20);
    
            rowHead.CreateCell(3).SetCellValue("商品价格");
            sheet.SetColumnWidth(3, 256 * 15);
    
            rowHead.CreateCell(4).SetCellValue("数量");
            sheet.SetColumnWidth(3, 256 * 10);
    
            rowHead.CreateCell(5).SetCellValue("总金额");
            sheet.SetColumnWidth(3, 256 * 15);
    
            //绑定表内容
            int rowindex = 5;
            int xh = 1;
            foreach (var item in order.ProductList)
            {
                IRow rowContent = sheet.CreateRow(rowindex);
                rowContent.CreateCell(0).SetCellValue(xh);
                rowContent.CreateCell(1).SetCellValue(item.ProductName);
                rowContent.CreateCell(2).SetCellValue(item.ProductBrand);
                rowContent.CreateCell(3).SetCellValue(item.ProductPrice.ToString());
                rowContent.CreateCell(4).SetCellValue(item.Quantity.ToString());
                rowContent.CreateCell(5).SetCellFormula(String.Format("$D{0}*$E{0}", rowindex + 1));  //设置函数，计算总金额
                rowindex++;
                xh++;
            }
        }
    
        //输出 
        System.IO.MemoryStream ms = new System.IO.MemoryStream();
        workbook.Write(ms);
        ms.Seek(0, SeekOrigin.Begin);
        return File(ms, "application/vnd.ms-excel", "订单信息.xls");
    }

**3、其他代码**

**3.1 实体类**

在Models模型目录中创建ProductModel.cs和OrderModel.cs实体类

/// <summary>
    /// 商品信息实体类
    /// </summary>
    public class ProductModel
    {
        /// <summary>
        /// 商品名称
        /// </summary>
        public string ProductName { get; set; }
    
        /// <summary>
        /// 商品品牌
        /// </summary>
        public string ProductBrand { get; set; }
    
        /// <summary>
        /// 商品价格
        /// </summary>
        public decimal ProductPrice { get; set; }
    
        /// <summary>
        /// 数量
        /// </summary>
        public int Quantity { get; set; }
    }

/// <summary>
    /// 订单信息实体类
    /// </summary>
    public class OrderModel
    {
        /// <summary>
        /// 订单编号
        /// </summary>
        public string OrderNo { get; set; }
    
        /// <summary>
        /// 下单时间
        /// </summary>
        public DateTime OrderTime { get; set; }
    
        /// <summary>
        /// 总金额
        /// </summary>
        public decimal Amount { get; set; }
    
        /// <summary>
        /// 客户名称
        /// </summary>
        public string CustomName { get; set; }
    
        /// <summary>
        /// 客户地址
        /// </summary>
        public string CustomAddress { get; set; }
    
        /// <summary>
        /// 商品列表
        /// </summary>
        public List<ProductModel> ProductList { get; set; }
    }

**3.2 获取商品和订单数据**

/// <summary>
    /// 获取商品列表
    /// </summary>
    public List<ProductModel> GetProductList()
    {
        List<ProductModel> productList = new List<ProductModel>();
        ProductModel product1 = new ProductModel(){
            ProductName = "苹果IPhone6手机",
            ProductBrand = "苹果",
            ProductPrice = 4999,
            Quantity = 4
        };
        ProductModel product2 = new ProductModel()
        {
            ProductName = "三星智能手机",
            ProductBrand = "三星",
            ProductPrice = 3800,
            Quantity = 3
        };
        ProductModel product3 = new ProductModel()
        {
            ProductName = "松下液晶电视机",
            ProductBrand = "松下",
            ProductPrice = 3800,
            Quantity = 2
        };
        productList.Add(product1);
        productList.Add(product2);
        productList.Add(product3);
        return productList;
    }
    
    /// <summary>
    /// 获取订单信息
    /// </summary>
    public OrderModel GetOrderInfo()
    {
        OrderModel order = new OrderModel() { 
            OrderNo = "P20140929001",
            OrderTime = DateTime.Now,
            CustomName = "张三",
            CustomAddress = "广东省深圳市罗湖区",
            ProductList = GetProductList()
        };
        return order;
    }

3.3 视图

在Views视图目录中创建Index.cshtml

<h2>使用NPOI生成Excel文件</h2>
    <a href="@Url.Action("ExportProduct","Export")">导出商品</a>
    <a href="@Url.Action("ExportOrder","Export")">导出订单</a>

[SouthEast]: /images/20210724/944d7d84e4f74e198bd669784ef7071a.png