使用NPOI导出Excel文件

来源：互联网发布：烯牛数据公司地址编辑：程序博客网时间：2024/09/21 09:04

使用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函数计算订单和商品的总金额；

图 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>

1 0