C#数据导出帮助类

一、创建ExportFieldAttribute特性标识Dto中要导出的属性字段，并指定导出字段顺序

    /// <summary>
    /// 导出字段标识
    /// </summary>
    [AttributeUsage(AttributeTargets.Property)]
    public class ExportFieldAttribute : System.Attribute
    {
        /// <summary>
        /// 导出字段顺序
        /// </summary>
        public int Sort { get; set; }

        /// <summary>
        /// 导出字段名称
        /// </summary>
        public string Name { get; set; }

    }

     例如:

   

 

 

二、Excel文件生成帮助类，注意需引用NPOI

public class ExportExcelHelper
    {
        /// <summary>
        /// 返回生成Excel文件byte流
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static byte[] GenExcelFileStream<T>(List<T> dataList) where T : class
        {
            var workbook = GenExcelWorkbook(dataList);
            var filePath = AppDomain.CurrentDomain.BaseDirectory + Guid.NewGuid() + ".xlsx";
            FileStream stream = new FileStream(filePath, FileMode.CreateNew, FileAccess.ReadWrite);
            workbook.Write(stream);
            workbook.Close();
            stream.Close();

            FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read);
            var bytes=new byte[fileStream.Length];
            fileStream.Read(bytes, 0, bytes.Length);

            fileStream.Close();
            File.Delete(filePath);

            return bytes;

        }

        /// <summary>
        /// 返回生成Excel文件地址
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataList"></param>
        /// <returns></returns>
        public static string GenExcelFile<T>(List<T> dataList) where T : class
        {
            var workbook = GenExcelWorkbook(dataList);
            var filePath = AppDomain.CurrentDomain.BaseDirectory + "FileUpload/" + DateTime.Now.ToString("yyyy-MM-dd") +
                           "/";
            if (!Directory.Exists(filePath))
            {
                Directory.CreateDirectory(filePath);
            }
            var guid = Guid.NewGuid();
            var fileName = filePath + guid + ".xlsx";
            FileStream stream = new FileStream(fileName, FileMode.CreateNew);
            workbook.Write(stream);
            workbook.Close();
            stream.Close();
            return fileName;
        }

        /// <summary>
        /// 生成Workbook
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataList"></param>
        /// <returns></returns>
        private static HSSFWorkbook GenExcelWorkbook<T>(List<T> dataList)
        {
            var type = typeof(T);
            var props = type.GetProperties()
                .Where(t => t.GetCustomAttributes(typeof(ExportFieldAttribute)).Any())
                .ToArray();
            if (!props.Any())
            {
                throw new Exception("导出失败，导出模板中未标记要导出的数据列！");
            }
            var headerList = new List<ExportHeader>();
            foreach (var propertyInfo in props)
            {
                var exportAtt = (ExportFieldAttribute)propertyInfo.GetCustomAttributes(typeof(ExportFieldAttribute)).First();
                ExportHeader header = new ExportHeader()
                {
                    PropertyName = propertyInfo.Name,
                    HeaderName = exportAtt.Name,
                    Sort = exportAtt.Sort
                };
                headerList.Add(header);
            }

            headerList = headerList.OrderBy(t => t.Sort).ToList();

            HSSFWorkbook workbook = new HSSFWorkbook();
            var sheet = workbook.CreateSheet();

            //创建表头
            var rowIndex = 0;
            var sheetHeadRow = sheet.CreateRow(rowIndex);
            for (int i = 0; i < headerList.Count; i++)
            {
                var cell = sheetHeadRow.CreateCell(i);
                cell.SetCellValue(headerList[i].HeaderName);
            }
            rowIndex++;

            //写入数据
            foreach (var dataItem in dataList)
            {
                var dataProps = typeof(T).GetProperties();

                var sheetRow = sheet.CreateRow(rowIndex);
                for (int i = 0; i < headerList.Count; i++)
                {
                    var cell = sheetRow.CreateCell(i);
                    var value = dataProps.First(t => t.Name == headerList[i].PropertyName).GetValue(dataItem)?.ToString();
                    cell.SetCellValue(value);
                }

                rowIndex++;
            }
            return workbook;
        }

        private class ExportHeader
        {
            /// <summary>
            /// 导出字段属性名称
            /// </summary>
            public string PropertyName { get; set; }

            /// <summary>
            /// 导出字段标题名称
            /// </summary>
            public string HeaderName { get; set; }

            /// <summary>
            /// 导出字段显示顺序
            /// </summary>
            public int Sort { get; set; }
        }
    }