public static void ListToSheetXlsx <T>(XSSFWorkbook workbook, List <T> list, string sheetName)
{
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(sheetName);
XSSFCellStyle headStyle = workbook.GetHeadStyle();
//值类型直接返回第一列
Type tp = typeof(T);
//属性列表
PropertyInfo[] properties = tp.GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance);
//property.Name是属性的英文名，怎么转换成中文？使用DescriptionAttribute特性
List <string> fieldStringArray = new List <string>();
List <PropertyInfo> propertiesUsed = new List <PropertyInfo>();
foreach (var property in properties)
{
if (Attribute.IsDefined(property, typeof(DescriptionAttribute)))
{
fieldStringArray.Add(property.GetEnumDescription());
propertiesUsed.Add(property);
}
}
int fieldCount = fieldStringArray.Count;
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 20;
for (int i = 0; i < fieldCount; i++)
{
#region 表头及样式
headerRow.CreateCell(i).SetCellValue(fieldStringArray[i]);
headerRow.GetCell(i).CellStyle = headStyle;
sheet.AutoSizeColumn(i);
#endregion
}
var count = list.Count();
#region 单元格样式
ICellStyle styleCell = workbook.CreateCellStyle();
styleCell.Alignment = HorizontalAlignment.Center; //居中
styleCell.VerticalAlignment = VerticalAlignment.Center; //垂直居中
#endregion
for (int i = 0; i < count; i++)
{
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(i + 1);
var data = list[i];
for (int cellIndex = 0; cellIndex < fieldCount; cellIndex++)
{
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(cellIndex, CellType.String);
var property = propertiesUsed[cellIndex];
if (Attribute.IsDefined(property, typeof(TimeAttribute)))
{
try
{
TimeSpan ts = new TimeSpan(0, 0, (int)property.GetValue(data));
StringBuilder sb = new StringBuilder();
if ((int)ts.TotalHours > 0)
{
sb.Append((int)ts.TotalHours + "h");
}
if (ts.Minutes > 0)
{
sb.Append(ts.Minutes + "m");
}
if (ts.Seconds > 0)
{
sb.Append(ts.Seconds + "s");
}
newCell.SetCellValue(sb.ToString());
}
catch (Exception ex)
{
ILogger logger = ServiceProviderServiceExtensions.GetRequiredService <ILogger>(
ServiceProviderExtension.ServiceProvider);
logger.LogError($"Second转换失败：" + ex.Source + Environment.NewLine + ex.StackTrace + Environment.NewLine + ex.Message + Environment.NewLine + ex.InnerException);
newCell.SetCellValue(property.GetValue(data).ToString());
}
}
else
{
var propertyValue = property.GetValue(data);
if (propertyValue == null)
{
newCell.SetCellValue("");
}
else
{
newCell.SetCellValue(propertyValue.ToString());
}
}
newCell.CellStyle = styleCell;
}
}
//统一设置列宽度
sheet.SetColumnWidth(fieldCount);
}