NPOI 生成Excel

记录使用NPOI生成Excel的通用方法

NPOI安装，使用Nuget：

Install-Package NPOI

使用泛型方法，反射两种方式实现生成Excel

第一种方法

有指定Header类型的方式：

 /// <summary>
        /// 指定表头,根据List生成Excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="filePath"></param>
        /// <param name="excelHeaders"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        public static bool CreateExcel<T>(string filePath, List<ExcelHeader> excelHeaders, List<T> data) where T : class
        {
            try
            {
            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet sheet1 = workbook.CreateSheet("Sheet1");
            //新增一个Row
            IRow headerRow = sheet1.CreateRow(0);
                for (int i = 0; i < excelHeaders.Count; i++)
                {
                    //新增单元格
                    headerRow.CreateCell(i).SetCellValue(excelHeaders[i].HeaderName);
                }

            int count = 1;
            foreach (T item in data)
            {
                //循环新增
                IRow row = sheet1.CreateRow(count);
                for (int i = 0; i < excelHeaders.Count; i++)
                {
                    //反射处理
                    Type t = item.GetType();
                    PropertyInfo propertyInfo = t.GetProperties().FirstOrDefault(w => w.Name == excelHeaders[i].ColumnName);
                    if (propertyInfo != null)
                    {
                        row.CreateCell(i).SetCellValue(propertyInfo.GetValue(item, null).ToString());
                    }
                }
                count++;
            }
                //保存文件
                using (var f = File.Create(filePath))
                {
                    workbook.Write(f);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

ExcelHeader类：

    public class ExcelHeader
    {
        public string HeaderName { get; set; }
        public string ColumnName { get; set; }
    }

第二种方法

这个减少去手动指定需要生成Excel内容的字段信息。会根据List<T>全部生成Excel，逻辑都是一样的：

 /// <summary>
        /// T 实体类必须指定DisplayName，根据DisplayName 生成列名
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="filePath"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        public static bool CreateExcel<T>(string filePath, List<T> data) where T:class
        {
            try
            {
                XSSFWorkbook workbook = new XSSFWorkbook();
                ISheet sheet1 = workbook.CreateSheet("Sheet1");

                IRow headerRow = sheet1.CreateRow(0);
                //列名
                 PropertyInfo[] columnsPropertyInfo = data.FirstOrDefault().GetType().GetProperties();
                int cellCount = 0;
                foreach (var column in columnsPropertyInfo)
                {
                    //根据实体类中DisplayName 生成列名
                    headerRow.CreateCell(cellCount).SetCellValue(column.GetCustomAttribute<DisplayNameAttribute>().DisplayName);
                    cellCount++;
                }
                //item
                int rowCount = 1;
                foreach (T item in data)
                {
                    IRow row = sheet1.CreateRow(rowCount);
                    int count = 0;
                    foreach (PropertyInfo propertyInfo in item.GetType().GetProperties())
                    {
                        row.CreateCell(count).SetCellValue(propertyInfo.GetValue(item, null).ToString());
                        count++;
                    }
                    rowCount++;
                }

                using (var f = File.Create(filePath))
                {
                    workbook.Write(f);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

这里展示在MVC项目中的使用方法：

             public ActionResult DownLoad()
        {
            //获取需要查询的数据集
            var data = GetAllData();
            //根据查询出数据实体类，指定对应需要生成Excel类容的字段名信息
            List<ExcelHeader> headers = new List<ExcelHeader>();
            headers.Add(new ExcelHeader() { ColumnName = "AcquittanceId", HeaderName = "收据号" });
            headers.Add(new ExcelHeader() { ColumnName = "Company", HeaderName = "公司" });
            headers.Add(new ExcelHeader() { ColumnName = "TaxId", HeaderName = "税号" });
            headers.Add(new ExcelHeader() { ColumnName = "BackName", HeaderName = "开户行" });
            headers.Add(new ExcelHeader() { ColumnName = "BankAccount", HeaderName = "银行账号" });
            headers.Add(new ExcelHeader() { ColumnName = "UserName", HeaderName = "姓名" });
            headers.Add(new ExcelHeader() { ColumnName = "PhoneNo", HeaderName = "联系方式" });
            headers.Add(new ExcelHeader() { ColumnName = "Email", HeaderName = "地址" });
            headers.Add(new ExcelHeader() { ColumnName = "CreateTime", HeaderName = "提交时间" });
            //生成物理路径
            string fileName = Server.MapPath(string.Format("~/Download/{0}.xlsx",DateTime.Now.ToString("yyyyMMddhhmmss")));
            //调用生成 第一种方式
            //if (ExcelHelper.CreateExcel<SurverTemp>(fileName, headers, data))
            //{
            //下载
            //     DownloadHelper.Download(fileName);
            //}
            //调用生成 第二种方式
            if (ExcelHelper.CreateExcel<SurverTemp>(fileName,data))
            {
                //下载
                DownloadHelper.Download(fileName);
            }
            return null;
        }