C# 数据导出成Excel的流

导出的帮助类

public class ExcelHelper
{
        /// <summary>
        /// 将给定的模型列表转换为 Excel 内存流，第一行和第二行是居中对齐加粗的
        /// </summary>
        /// <typeparam name="T">模型类型</typeparam>
        /// <param name="models">模型列表</param>
        /// <param name="firstRow">Excel 第一行数据</param>
        /// <param name="secondRow">Excel 第二行数据</param>
        /// <returns>Excel 内存流</returns>
        public static MemoryStream ExportExcelStream<T>(List<T> models, string firstRow, string secondRow)
        {
            NPOIMemoryStream ms = new NPOIMemoryStream();//使用自定义类
 
            IWorkbook workbook = Parse(models, firstRow, secondRow);
 
            workbook.Write(ms);
            ms.Position = 0;
 
            return ms;
        }
 
 
        /// <summary>
        /// 将数据导出到Excel内存流中
        /// </summary>
        /// <typeparam name="T">类型参数</typeparam>
        /// <param name="models">要导出的数据列表</param>
        /// <param name="firstRow">第一行的信息</param>
        /// <param name="secondRow">第二行的信息</param>
        /// <param name="hashtable">Hashtable类型的参数</param>
        /// <returns>Excel内存流</returns>
        public static MemoryStream ExportExcelStream<T>(List<T> models, string firstRow, string secondRow,Hashtable hashtable)
        {
            NPOIMemoryStream ms = new NPOIMemoryStream();//使用自定义类
 
            IWorkbook workbook = Parse(models, hashtable, firstRow, secondRow);
 
            workbook.Write(ms);
            ms.Position = 0;
 
            return ms;
        }
    
    
        /// <summary>
        ///     内存流保存到Excel文件中
        /// </summary>
        /// <param name="stream"></param>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static string ConvertStreamToExcel(MemoryStream stream, string filePath)
        {
            using (FileStream fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
            {
                byte[] buffer = new byte[stream.Length];
                stream.Read(buffer, 0, buffer.Length);
                fileStream.Write(buffer, 0, buffer.Length);
            }
            return filePath;
        }
    
        /// <summary>
        ///     将数据传输对象列表解析为HSSFWorkbook对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="models"></param>
        /// <param name="firstRow"></param>
        /// <param name="secondRow"></param>
        /// <returns></returns>
        private static HSSFWorkbook Parse<T>(List<T> models, string firstRow = "", string secondRow = "")
        {
            var book = new HSSFWorkbook();
            var sheet = book.CreateSheet();
            var headerStyle = GetHeaderStyle(book);
            headerStyle.Alignment = HorizontalAlignment.Center;
            headerStyle.BorderBottom = BorderStyle.Thin;
            headerStyle.BorderLeft = BorderStyle.Thin;
            headerStyle.BorderRight = BorderStyle.Thin;
            headerStyle.BorderTop = BorderStyle.Thin;
            var itemStyle = GetItemStyle(book);
            itemStyle.Alignment = HorizontalAlignment.Center;
            itemStyle.BorderBottom = BorderStyle.Thin;
            itemStyle.BorderLeft = BorderStyle.Thin;
            itemStyle.BorderRight = BorderStyle.Thin;
            itemStyle.BorderTop = BorderStyle.Thin;
 
 
            var properties = typeof(T).GetProperties();
            var columns = properties.Select(x => new ColumnEntry
            {
                Property = x,
                Header = x.GetCustomAttribute<ExportHeaderAttribute>()
            })
            .Where(x => x.Header != null)
            .OrderBy(x => x.Header.Order)
            .ToList();
 
            //  列数
            var columnCount = columns.Count();
            //  行数
            var rowCount = models.Count();
 
            SetColumnWith(sheet, columnCount);
 
            WriteMergeRow(0, firstRow, 0, columnCount - 1);
            WriteMergeRow(1, secondRow, 0, columnCount - 1);
 
            WriteRow(2, columns.Select(x => x.Header.Name).ToList());
 
            for (var rowIndex = 3; rowIndex <= rowCount + 2; rowIndex++)
            {
                var values = new List<string>();
                var model = models[rowIndex - 3];
 
                foreach (var column in columns)
                {
                    var value = column.Property.GetValue(model)?.ToString();
                    values.Add(value);
                }
 
                WriteRow(rowIndex, values);
            }
 
            return book;
 
            void WriteRow(int rowIndex, List<string> rows)
            {
                var row = sheet.CreateRow(rowIndex);
 
                if (rowIndex == 0)
                {
                    //  设置表头高度
                    row.Height = row.Height;
                }
 
                for (int i = 0; i < columnCount; i++)
                {
                    var cell = row.CreateCell(i);
 
                    if (rowIndex == 2)
                    {
                        //  设置表头单元格样式
                        cell.CellStyle = headerStyle;
                        cell.SetCellValue(rows[i]?.ToString());
                    }
                    else
                    {
                        cell.CellStyle = itemStyle;
                        if (double.TryParse(rows[i], out double tryNumberValue))
                        {
                            cell.SetCellType(CellType.Numeric);
                            //cell.CellStyle.DataFormat = book.CreateDataFormat().GetFormat("#.######");
                            cell.SetCellValue(tryNumberValue);
                        }
                        else
                        {
                            cell.SetCellValue(rows[i]?.ToString());
                        }
 
                    }
                }
            }
 
            void WriteMergeRow(int rowIndex, string rowValue, int fromCell, int toCell)
            {
                var row = sheet.CreateRow(rowIndex);
 
                if (rowIndex == 0)
                {
                    //  设置表头高度
                    row.Height = row.Height;
                }
 
                var cell = row.CreateCell(0);
                cell.CellStyle = headerStyle;
                cell.SetCellValue(rowValue);
 
                var region = new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, columnCount - 1);
                HSSFRegionUtil.SetBorderBottom(BorderStyle.Thin, region, (HSSFSheet)row.Sheet, book);
                HSSFRegionUtil.SetBorderTop(BorderStyle.Thin, region, (HSSFSheet)row.Sheet, book);
                HSSFRegionUtil.SetBorderLeft(BorderStyle.Thin, region, (HSSFSheet)row.Sheet, book);
                HSSFRegionUtil.SetBorderRight(BorderStyle.Thin, region, (HSSFSheet)row.Sheet, book);
                row.Sheet.AddMergedRegion(region);
            }
        }
 
        /// <summary>
        ///     将数据传输对象列表解析为HSSFWorkbook对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="models"></param>
        /// <param name="hashtable"></param>
        /// <param name="firstRow"></param>
        /// <param name="secondRow"></param>
        /// <returns></returns>
        private static HSSFWorkbook Parse<T>(List<T> models,Hashtable hashtable ,string firstRow = "", string secondRow = "")
        {
            var book = new HSSFWorkbook();
            var sheet = book.CreateSheet();
            var headerStyle = GetHeaderStyle(book);
            headerStyle.Alignment = HorizontalAlignment.Center;
            headerStyle.BorderBottom = BorderStyle.Thin;
            headerStyle.BorderLeft = BorderStyle.Thin;
            headerStyle.BorderRight = BorderStyle.Thin;
            headerStyle.BorderTop = BorderStyle.Thin;
            var itemStyle = GetItemStyle(book);
            itemStyle.Alignment = HorizontalAlignment.Center;
            itemStyle.BorderBottom = BorderStyle.Thin;
            itemStyle.BorderLeft = BorderStyle.Thin;
            itemStyle.BorderRight = BorderStyle.Thin;
            itemStyle.BorderTop = BorderStyle.Thin;
 
 
            var properties = typeof(T).GetProperties();
            var columns = properties.Select(x => new ColumnHeaderEntity
            {
                Property = x,
                Header = hashtable[x.Name].ToString()
            })
            .Where(x => x.Header != null)
            .ToList();
 
            //  列数
            var columnCount = columns.Count();
            //  行数
            var rowCount = models.Count();
 
            SetColumnWith(sheet, columnCount);
 
            WriteMergeRow(0, firstRow, 0, columnCount - 1);
            WriteMergeRow(1, secondRow, 0, columnCount - 1);
 
            WriteRow(2, columns.Select(x => x.Header).ToList());
 
            for (var rowIndex = 3; rowIndex <= rowCount + 2; rowIndex++)
            {
                var values = new List<string>();
                var model = models[rowIndex - 3];
 
                foreach (var column in columns)
                {
                    var value = column.Property.GetValue(model)?.ToString();
                    values.Add(value);
                }
 
                WriteRow(rowIndex, values);
            }
 
            return book;
 
            void WriteRow(int rowIndex, List<string> rows)
            {
                var row = sheet.CreateRow(rowIndex);
 
                if (rowIndex == 0)
                {
                    //  设置表头高度
                    row.Height = row.Height;
                }
 
                for (int i = 0; i < columnCount; i++)
                {
                    var cell = row.CreateCell(i);
 
                    if (rowIndex == 2)
                    {
                        //  设置表头单元格样式
                        cell.CellStyle = headerStyle;
                        cell.SetCellValue(rows[i]?.ToString());
                    }
                    else
                    {
                        cell.CellStyle = itemStyle;
                        if (double.TryParse(rows[i], out double tryNumberValue))
                        {
                            cell.SetCellType(CellType.Numeric);
                            //cell.CellStyle.DataFormat = book.CreateDataFormat().GetFormat("#.######");
                            cell.SetCellValue(tryNumberValue);
                        }
                        else
                        {
                            cell.SetCellValue(rows[i]?.ToString());
                        }
 
                    }
                }
            }
 
            void WriteMergeRow(int rowIndex, string rowValue, int fromCell, int toCell)
            {
                var row = sheet.CreateRow(rowIndex);
 
                if (rowIndex == 0)
                {
                    //  设置表头高度
                    row.Height = row.Height;
                }
 
                var cell = row.CreateCell(0);
                cell.CellStyle = headerStyle;
                cell.SetCellValue(rowValue);
 
                var region = new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, columnCount - 1);
                HSSFRegionUtil.SetBorderBottom(BorderStyle.Thin, region, (HSSFSheet)row.Sheet, book);
                HSSFRegionUtil.SetBorderTop(BorderStyle.Thin, region, (HSSFSheet)row.Sheet, book);
                HSSFRegionUtil.SetBorderLeft(BorderStyle.Thin, region, (HSSFSheet)row.Sheet, book);
                HSSFRegionUtil.SetBorderRight(BorderStyle.Thin, region, (HSSFSheet)row.Sheet, book);
                row.Sheet.AddMergedRegion(region);
            }
        }
 
        /// <summary>
        ///     获取表头样式
        /// </summary>
        /// <param name="book"></param>
        /// <returns></returns>
        private static ICellStyle GetHeaderStyle(HSSFWorkbook book)
        {
            ICellStyle cellStyle = book.CreateCellStyle();
            IFont font = book.CreateFont();
            font.Color = new HSSFColor.Black().Indexed;
            font.IsBold = true;
            cellStyle.SetFont(font);
            cellStyle.FillPattern = FillPattern.SolidForeground;
            return cellStyle;
        }
 
        /// <summary>
        ///     获取内容单元格样式
        /// </summary>
        /// <param name="book"></param>
        /// <returns></returns>
        private static ICellStyle GetItemStyle(HSSFWorkbook book)
        {
            var style = book.CreateCellStyle();
            var font = book.CreateFont();
            //font.FontName = "方正舒体";
            font.Color = new HSSFColor.Black().Indexed;
            //font.IsItalic = true;
            //font.FontHeightInPoints = 16;
            font.IsBold = false;
            style.SetFont(font);
            //style.FillBackgroundColor = new HSSFColor.Grey50Percent().Indexed;
 
            //style.FillForegroundColor = 0;
            style.FillPattern = FillPattern.SolidForeground;
            //((XSSFColor)style.FillForegroundColorColor).SetRgb(new byte[] { 0, 176, 240 });
            return style;
        }
 
        /// <summary>
        ///     设置列宽
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="columnCount"></param>
        private static void SetColumnWith(ISheet sheet, int columnCount)
        {
            int num = 4096;
            for (int i = 0; i < columnCount; i++)
            {
                sheet.SetColumnWidth(i, num);
            }
        }
}

导出需要用到的Model

    public class ColumnHeaderEntity
    {
        public PropertyInfo Property { get; set; }
        public string Header { get; set; }
    }
 
 
    public class ColumnEntry
    {
        public PropertyInfo Property { get; set; }
 
        public ExportHeaderAttribute Header { get; set; }
    }
 
public class ExportHeaderAttribute : Attribute
{
    //
    // 摘要:
    //     列名称
    public string Name { get; }
 
    //
    // 摘要:
    //     列顺序
    public short Order { get; }
 
    public ExportHeaderAttribute(string name)
        : this(name, -1)
    {
    }
 
    public ExportHeaderAttribute(string name, short order)
    {
        Name = name;
        Order = order;
    }
}