NPOI 导出Excel

NPOI是一个用于处理Office文档的开源项目。它是用C#编写的，允许开发人员在.NET平台上读取、写入和操作Word、Excel和PowerPoint文件。NPOI提供了一组API，使开发人员能够以编程方式创建、修改和处理Office文档，这对于自动化生成报表、导出数据等任务非常有用。NPOI是一个非营利性的开源项目，旨在为开发人员提供处理Office文档的免费解决方案。

引用NPOI2.5.5

导出工具类库

using System;
using System.Data;
using System.IO;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using Serenity.ComponentModel;
using System.Linq;
using System.Reflection;
using NPOI.XSSF.UserModel;//NPOI2.0
using NPOI.SS.UserModel;
using NPOI.HSSF.Util;

/// <summary>
/// NPOI导出Excel
/// </summary>
namespace NDPS.Code
{
    public class ExcelExportHelp
    {

        XSSFWorkbook hssfworkbook;//2.0
        public ExcelExportHelp()
        {
            hssfworkbook = new XSSFWorkbook();
        }

        #region HSSFWorkbook

        /// <summary>
        /// List 数据写入Excel Sheet
        /// </summary>
        /// <typeparam name="T">数据模型实体</typeparam>
        /// <param name="list">数据列表</param>
        /// <param name="sheetName">Excel表格名称</param>
        public void GenerateData<T>(IEnumerable<T> list, string sheetName)
        {
            DataTable dt = ListToDataTable<T>(list);

            List<int> columnWiths = new List<int>();//列宽
            List<string> headColumnName = new List<string>();//Excel列名
            List<string> dataColumnFieldName = new List<string>();//数据字段名
            var props = typeof(T).GetProperties();
            foreach (var item in props)
            {
                string fieldENName = item.Name;//列英文字段名称
                string fieldCNName = item.Name;//列中文字段名称
                int colWidth = 15;//列宽 默认15
                DescriptionAttribute customAttribute = (DescriptionAttribute)Attribute.GetCustomAttribute(item, typeof(DescriptionAttribute));
                if (customAttribute != null)
                {
                    fieldCNName = customAttribute.Description;
                }
                WidthAttribute widthAttribute = (WidthAttribute)Attribute.GetCustomAttribute(item, typeof(WidthAttribute));
                if (widthAttribute != null)
                {
                    colWidth = widthAttribute.Value;
                }
                headColumnName.Add(fieldCNName);
                dataColumnFieldName.Add(fieldENName);
                columnWiths.Add(colWidth);
            }

            ISheet sheet1 = hssfworkbook.CreateSheet(sheetName);
            AddMergedRegion(sheet1);
            SetHead(sheet1);
            SetColumnWidth(sheet1, columnWiths);
            LoadData(dt, headColumnName, dataColumnFieldName, sheet1, StyleBorderBg(), StyleBorder());
        }

        public MemoryStream WriteToStream()
        {
            //Write the stream data of workbook to the root directory
            MemoryStream msData = new MemoryStream();
            hssfworkbook.Write(msData);
            return msData;
        }

        /// <summary>
        /// 将数据写入Excel文件
        /// </summary>
        /// <param name="hssfworkbook">工作簿</param>
        /// <param name="exportPath">文件导出路径</param>
        public void WirteToFile(string exportPath)
        {
            FileStream file = null;
            try
            {
                file = new FileStream(exportPath, FileMode.Create);
                hssfworkbook.Write(file);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                file.Dispose();
                hssfworkbook.Close();
            }

        }

        private void AddMergedRegion(ISheet sheet1)
        {
            //sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 7));
        }

        private void SetHead(ISheet sheet1)
        {
            //IFont font1 = hssfworkbook.CreateFont();
            //font1.Color = HSSFColor.DarkBlue.Index;
            //font1.FontHeightInPoints = 16;
            //ICellStyle style1 = hssfworkbook.CreateCellStyle();
            //style1.SetFont(font1);
            //style1.Alignment = HorizontalAlignment.Center;
            //IRow row0 = sheet1.CreateRow(0);
            //row0.CreateCell(0).SetCellValue("");
            //row0.Cells[0].CellStyle = style1;
            //InsertPic(sheet1, "listLogo.jpg", 0, 0, 0, 0);
            //row0.Height = 20 * 20;
        }

        private void SetColumnWidth(ISheet sheet1, List<int> columnWiths)
        {
            for (int i = 0; i < columnWiths.Count; i++)
            {
                sheet1.SetColumnWidth(i, columnWiths[i] * 256);
            }
        }

        private void LoadData(DataTable dt, List<string> headColumnName, List<string> dataColumnFieldName, ISheet sheet1, ICellStyle styleBorderBg, ICellStyle styleBorder)
        {
            IRow row1 = sheet1.CreateRow(0);
            for (int i = 0; i < headColumnName.Count; i++)
            {
                row1.CreateCell(i).SetCellValue(headColumnName[i]);
                row1.Cells[i].CellStyle = styleBorderBg;
            }

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow arow = sheet1.CreateRow(i + 1);
                DataRow dataRow = dt.Rows[i];
                for (int j = 0; j < dataColumnFieldName.Count; j++)
                {
                    if (String.IsNullOrEmpty(dataColumnFieldName[j]))
                    {
                        arow.CreateCell(j).SetCellValue("");
                    }
                    else
                    {
                        arow.CreateCell(j).SetCellValue(dataRow[dataColumnFieldName[j]].ToString());
                        //Attments
                       /* if (dataColumnFieldName[j] == "Attments"){
                            XSSFHyperlink link = new XSSFHyperlink(HyperlinkType.Url);//建一个HSSFHyperlink实体，指明链接类型为URL（这里是枚举，可以根据需求自行更改）
                                                                                      //link.Address = "http://blog.csdn.net/shiershilian";//给HSSFHyperlink的地址赋值
                            link.Address = dataRow[dataColumnFieldName[j]].ToString();
                            arow.CreateCell(j).Hyperlink = link;//将链接方式赋值给单元格的Hyperlink即可将链接附加到单元格上
                        }*/
                    }
                    arow.Cells[j].CellStyle = styleBorderBg;
                }
            }
        }

        private ICellStyle StyleBorderBg()
        {
            ICellStyle styleBorderBg = hssfworkbook.CreateCellStyle();
            //styleBorderBg.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
            //styleBorderBg.BottomBorderColor = HSSFColor.BLACK.index;
            //styleBorderBg.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
            //styleBorderBg.LeftBorderColor = HSSFColor.BLACK.index;
            //styleBorderBg.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
            //styleBorderBg.RightBorderColor = HSSFColor.BLACK.index;
            //styleBorderBg.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
            //styleBorderBg.TopBorderColor = HSSFColor.BLACK.index;
            //styleBorderBg.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIME.index;
            //styleBorderBg.FillPattern = FillPatternType.LESS_DOTS;
            //styleBorderBg.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.BLACK.index;
            return styleBorderBg;
        }

        private ICellStyle StyleBorder()
        {
            ICellStyle styleBorder = hssfworkbook.CreateCellStyle();
            styleBorder.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            styleBorder.BottomBorderColor = HSSFColor.Black.Index;
            styleBorder.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            styleBorder.LeftBorderColor = HSSFColor.Black.Index;
            styleBorder.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            styleBorder.RightBorderColor = HSSFColor.Black.Index;
            styleBorder.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            styleBorder.TopBorderColor = HSSFColor.Black.Index;
            return styleBorder;
        }

        public void InitializeWorkbook()
        {
            hssfworkbook = new XSSFWorkbook();//2.0
            //create a entry of DocumentSummaryInformation
            //DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            //dsi.Company = "";
            //hssfworkbook.DocumentSummaryInformation = dsi;

            //create a entry of SummaryInformation
            //SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            //si.Subject = "";
            //hssfworkbook.SummaryInformation = si;
        }

        #endregion

        #region Other Method
        public DataTable ListToDataTable<T>(IEnumerable<T> collection)
        {
            var props = typeof(T).GetProperties();
            var dt = new DataTable();
            foreach (var item in props)
            {
                //解决DataSet不支持System.Nullable<>问题
                Type colType = item.PropertyType;
                if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                {
                    colType = colType.GetGenericArguments()[0];
                }
                //添加列明及对应类型 
                dt.Columns.Add(item.Name, colType);
            }
            if (collection.Count() > 0)
            {
                for (int i = 0; i < collection.Count(); i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in props)
                    {
                        object obj = pi.GetValue(collection.ElementAt(i), null);
                        tempList.Add(obj);
                    }
                    object[] array = tempList.ToArray();
                    dt.LoadDataRow(array, true);
                }
            }
            return dt;
        }

        #endregion
    }
}

实体类ExportEntity

using System.ComponentModel;
using Serenity.ComponentModel;

namespace NDPS.Domain
{
    public class ExportEntity
    {
        [DescriptionAttribute("订单编号"),Width(15)]//列名称和列宽
        public string ordernum { get; set; }       


        [DescriptionAttribute("创建时间")]
        public string addtime { get; set; }

    }
}

调用代码

注意导出Excel的列名是根据实体类的字段来的。

var list = await _service.GetList();//获取业务数据 该行代码需要自行修改

string fileName = "信息列表.xlsx";
string sheetName = "信息表";

ExcelExportHelp exportHP = new ExcelExportHelp();
exportHP.GenerateData<ExportEntity>(list, sheetName);

return File(exportHP.WriteToStream().ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);

总结

以上代码已经在多个生产项目中成功使用，导出稳定，Window和Linux操作系统均可稳定运行。