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;}
}