/// <summary>
/// 设置页脚样式
/// </summary>
/// <param name="table">工作表</param>
/// <param name="style">单元格样式</param>
/// <returns></returns>
public IExcel FootStyle(IWorkSheet table, CellStyle style)
{
if (_footStyle == null)
{
_footStyle = CellStyleResolver.Resolve(_workbook, style);
}
Style(table.HeadRowCount + table.BodyRowCount, table.RowCount - 1, 0, table.ColumnCount - 1, _footStyle);
return(this);
}
/// <summary>
/// 设置表头样式
/// </summary>
/// <param name="table">工作表</param>
/// <param name="style">单元格样式</param>
/// <returns></returns>
public IExcel HeadStyle(IWorkSheet table, CellStyle style)
{
if (_headStyle == null)
{
_headStyle = CellStyleResolver.Resolve(_workbook, style);
}
Style(0, table.HeadRowCount - 1, 0, table.ColumnCount - 1, _headStyle);
return(this);
}
/// <summary>
/// 设置正文样式
/// </summary>
/// <param name="table">工作表</param>
/// <param name="style">单元格样式</param>
/// <returns></returns>
public IExcel BodyStyle(IExcelSheet table, CellStyle style)
{
if (_bodyStyle == null)
{
_bodyStyle = CellStyleResolver.Resolve(_workbook, style);
}
Style(table.HeadRowCount, table.HeadRowCount + table.BodyRowCount - 1, 0, table.ColumnNumber - 1,
_bodyStyle);
return(this);
}
/// <summary>
/// 创建日期样式
/// </summary>
/// <returns></returns>
private NPOI.SS.UserModel.ICellStyle CreateDateStyle()
{
if (_dateStyle != null)
{
return(_dateStyle);
}
_dateStyle = CellStyleResolver.Resolve(_workbook, CellStyle.Body());
var format = _workbook.CreateDataFormat();
_dateStyle.DataFormat = format.GetFormat(_dateFormat);
return(_dateStyle);
}
public static IWorkbook ToWorkbook <T>(this IEnumerable <T> source, IWorkbook workbook, ExcelSetting excelSetting,
string sheetName = "sheet0", bool overwrite = false) where T : class
{
if (null == source)
{
throw new ArgumentNullException(nameof(source));
}
if (null == workbook)
{
throw new ArgumentNullException(nameof(workbook));
}
if (string.IsNullOrWhiteSpace(sheetName))
{
throw new ArgumentNullException($"工作表名称不能为空或空字符串", nameof(sheetName));
}
var properties =
typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty);
bool fluentConfigEnabled = false;
if (excelSetting.FluentConfigs.TryGetValue(typeof(T), out var fluentConfig))
{
fluentConfigEnabled = true;
(fluentConfig as FluentConfiguration <T>)?.AdjustAutoIndex();
}
// 查找输出配置
var propertyConfigurations = new PropertyConfiguration[properties.Length];
for (var i = 0; i < properties.Length; i++)
{
var property = properties[i];
if (fluentConfigEnabled && fluentConfig.PropertyConfigurations.TryGetValue(property.Name, out var pc))
{
propertyConfigurations[i] = pc;
}
else
{
propertyConfigurations[i] = null;
}
}
// 再次检查工作表名称
var sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
sheet = workbook.CreateSheet(sheetName);
}
else
{
// 如果不需要，则不会覆盖现有工作表
if (!overwrite)
{
sheet = workbook.CreateSheet();
}
}
// 缓存单元格样式
var cellStyles = new Dictionary <int, ICellStyle>();
// 标题行单元格样式
ICellStyle titleStyle = null;
if (excelSetting.TitleCellStyleApplier != null)
{
var style = new CellStyle();
excelSetting.TitleCellStyleApplier(style);
titleStyle = CellStyleResolver.Resolve(workbook, style);
}
var titleRow = sheet.CreateRow(0);
var rowIndex = 1;
foreach (var item in source)
{
var row = sheet.CreateRow(rowIndex);
for (var i = 0; i < properties.Length; i++)
{
var property = properties[i];
int index = i;
var config = propertyConfigurations[i];
if (config != null)
{
if (config.IgnoreExport)
{
continue;
}
index = config.Index;
if (index < 0)
{
throw new Exception($"{property.Name} 索引值不能小于0。请参考 HasExcelIndex(int index) 方法以获取更多信息。");
}
}
if (rowIndex == 1)
{
// 没有标题的时候，使用属性名作为标题
var title = property.Name;
if (!string.IsNullOrWhiteSpace(config?.Title))
{
title = config.Title;
}
if (!string.IsNullOrWhiteSpace(config?.Formatter))
{
try
{
var style = workbook.CreateCellStyle();
var dataFormat = workbook.CreateDataFormat();
style.DataFormat = dataFormat.GetFormat(config.Formatter);
cellStyles[i] = style;
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine(e.ToString());
}
}
var titleCell = titleRow.CreateCell(index);
if (titleCell != null)
{
titleCell.CellStyle = titleStyle;
}
titleCell.SetValue(title);
}
var unwrapType = property.PropertyType.UnwrapNullableType();
var value = property.GetValue(item, null);
// 进行值转换
if (config?.CellValueConverter != null)
{
value = config.CellValueConverter(rowIndex, index, value);
if (value == null)
{
continue;
}
unwrapType = value.GetType().UnwrapNullableType();
}
if (value == null)
{
continue;
}
var cell = row.CreateCell(index);
if (cellStyles.TryGetValue(i, out var cellStyle))
{
cell.CellStyle = cellStyle;
}
else if (!string.IsNullOrWhiteSpace(config?.Formatter) && value is IFormattable fv)
{
// C#格式化程序
cell.SetCellValue(fv.ToString(config.Formatter, CultureInfo.CurrentCulture));
continue;
}
cell.SetValue(value);
}
rowIndex++;
}
// 合并单元格
var mergableConfigs = propertyConfigurations.Where(c => c != null && c.AllowMerge).ToList();
if (mergableConfigs.Any())
{
var vStyle = workbook.CreateCellStyle();
vStyle.VerticalAlignment = VerticalAlignment.Center;
foreach (var config in mergableConfigs)
{
object previous = null;
int rowSpan = 0, row = 1;
for (row = 1; row < rowIndex; row++)
{
var value = sheet.GetRow(row).GetCellValue(config.Index, _formulaEvaluator);
if (object.Equals(previous, value) && value != null)
{
rowSpan++;
}
else
{
if (rowSpan > 1)
{
sheet.GetRow(row - rowSpan).Cells[config.Index].CellStyle = vStyle;
sheet.AddMergedRegion(new CellRangeAddress(row - rowSpan, row - 1, config.Index,
config.Index));
}
rowSpan = 1;
previous = value;
}
}
if (rowSpan > 1)
{
sheet.GetRow(row - rowSpan).Cells[config.Index].CellStyle = vStyle;
sheet.AddMergedRegion(new CellRangeAddress(row - rowSpan, row - 1, config.Index, config.Index));
}
}
}
if (rowIndex > 1 && fluentConfigEnabled)
{
var statistics = fluentConfig.StatisticsConfigurations;
var filterConfigs = fluentConfig.FilterConfigurations;
var freezeConfigs = fluentConfig.FreezeConfigurations;
// 统计信息
foreach (var item in statistics)
{
var lastRow = sheet.CreateRow(rowIndex);
var cell = lastRow.CreateCell(0);
cell.SetValue(item.Name);
foreach (var column in item.Columns)
{
cell = lastRow.CreateCell(column);
cell.CellStyle = sheet.GetRow(rowIndex - 1)?.GetCell(column)?.CellStyle;
cell.CellFormula =
$"{item.Formula}({GetCellPosition(1, column)}:{GetCellPosition(rowIndex - 1, column)})";
}
rowIndex++;
}
// 冻结窗口
foreach (var freeze in freezeConfigs)
{
sheet.CreateFreezePane(freeze.ColumnSplit, freeze.RowSpit, freeze.LeftMostColumn, freeze.TopRow);
}
// 筛选器
foreach (var filter in filterConfigs)
{
sheet.SetAutoFilter(new CellRangeAddress(filter.FirstRow, filter.LastRow ?? rowIndex,
filter.FirstColumn, filter.LastColumn));
}
}
// 自动列宽
if (excelSetting.AutoSizeColumnsEnabled)
{
for (var i = 0; i < properties.Length; i++)
{
sheet.AutoSizeColumn(i);
}
}
return(workbook);
}
