122 lines
4.7 KiB
C#
122 lines
4.7 KiB
C#
using System.Reflection;
|
||
using NPOI.SS.UserModel;
|
||
using NPOI.SS.Util;
|
||
using NPOI.XSSF.UserModel;
|
||
|
||
namespace Infrastructure.Utilities.Excel;
|
||
|
||
public class ExcelExporter
|
||
{
|
||
/// <summary>
|
||
///
|
||
/// </summary>
|
||
/// <param name="data"></param>
|
||
/// <param name="headerTitle"></param>
|
||
/// <exception cref="ArgumentException"></exception>
|
||
public static Response<MemoryStream> ExportToExcel<T>(IEnumerable<T> data, string headerTitle) where T : class
|
||
{
|
||
var workbook = new XSSFWorkbook();
|
||
var sheet = workbook.CreateSheet("Sheet1");
|
||
|
||
// 获取实体类的属性信息，并筛选出标记了ExcelColumnAttribute的特性
|
||
PropertyInfo[] properties = typeof(T).GetProperties()
|
||
.Where(p => p.GetCustomAttribute<ExcelColumnAttribute>() != null)
|
||
.ToArray();
|
||
|
||
var rowIndex = 0;
|
||
if (!string.IsNullOrEmpty(headerTitle))
|
||
{
|
||
var headerTitleRow = sheet.CreateRow(rowIndex++);
|
||
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, properties.Length - 1));
|
||
var cell = headerTitleRow.CreateCell(0);
|
||
cell.SetCellValue(headerTitle);
|
||
var style = workbook.CreateCellStyle();
|
||
var font = workbook.CreateFont();
|
||
font.FontName = "方正小标宋简体";
|
||
font.FontHeightInPoints = 36;
|
||
font.IsBold = true;
|
||
style.SetFont(font);
|
||
style.BorderBottom = BorderStyle.Thin;
|
||
style.BorderLeft = BorderStyle.Thin;
|
||
style.BorderRight = BorderStyle.Thin;
|
||
style.BorderTop = BorderStyle.Thin;
|
||
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
|
||
style.VerticalAlignment = VerticalAlignment.Center;
|
||
style.WrapText = true;
|
||
cell.CellStyle = style;
|
||
}
|
||
|
||
// 写入表头
|
||
var headerRow = sheet.CreateRow(rowIndex++);
|
||
for (var i = 0; i < properties.Length; i++)
|
||
{
|
||
// 自动调节列宽
|
||
sheet.AutoSizeColumn(i);
|
||
var cell = headerRow.CreateCell(i);
|
||
cell.SetCellValue(properties[i].GetCustomAttribute<ExcelColumnAttribute>()?.HeaderName);
|
||
// 应用表头样式
|
||
var headerStyleAttr = properties[i].GetCustomAttribute<ExcelHeaderStyleAttribute>();
|
||
var style = workbook.CreateCellStyle();
|
||
if (headerStyleAttr != null)
|
||
{
|
||
var font = workbook.CreateFont();
|
||
font.FontName = headerStyleAttr.FontName;
|
||
font.FontHeightInPoints = (short)headerStyleAttr.FontSize;
|
||
font.IsBold = true;
|
||
style.SetFont(font);
|
||
// 填充模式
|
||
style.FillPattern = FillPattern.SolidForeground;
|
||
style.WrapText = true;
|
||
// 设置边框样式
|
||
style.BorderBottom = BorderStyle.Thin;
|
||
style.BorderLeft = BorderStyle.Thin;
|
||
style.BorderRight = BorderStyle.Thin;
|
||
style.BorderTop = BorderStyle.Thin;
|
||
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
|
||
style.VerticalAlignment = VerticalAlignment.Center;
|
||
}
|
||
else
|
||
{
|
||
var font = workbook.CreateFont();
|
||
font.FontName = "Microsoft YaHei";
|
||
font.FontHeightInPoints = 12f;
|
||
font.IsBold = true;
|
||
style.SetFont(font);
|
||
// 填充模式
|
||
style.FillPattern = FillPattern.SolidForeground;
|
||
style.WrapText = true;
|
||
// 设置边框样式
|
||
style.BorderBottom = BorderStyle.Thin;
|
||
style.BorderLeft = BorderStyle.Thin;
|
||
style.BorderRight = BorderStyle.Thin;
|
||
style.BorderTop = BorderStyle.Thin;
|
||
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
|
||
style.VerticalAlignment = VerticalAlignment.Center;
|
||
}
|
||
cell.CellStyle = style;
|
||
}
|
||
|
||
foreach (var item in data)
|
||
{
|
||
var row = sheet.CreateRow(rowIndex++);
|
||
for (var i = 0; i < properties.Length; i++)
|
||
{
|
||
var cell = row.CreateCell(i);
|
||
cell.SetCellValue(properties[i].GetValue(item)?.ToString());
|
||
}
|
||
}
|
||
|
||
var response = new Response<MemoryStream>
|
||
{
|
||
Result = new MemoryStream()
|
||
};
|
||
// MemoryStream中写入数据
|
||
workbook.Write(response.Result);
|
||
workbook.Close();
|
||
response.Result.Close();
|
||
response.Result.Dispose();
|
||
response.Code = 200;
|
||
response.Message = "获取成功";
|
||
return response;
|
||
}
|
||
} |