/// <summary>
/// 创建数据列构造器
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="columns"></param>
/// <returns></returns>
private static ExcelColumnBuilder <T> CreateColumnBuilder <T>(Action <IRootExcelColumnBuilder <T> > columns)
where T : class
{
var builder = new ExcelColumnBuilder <T>();
if (columns != null)
{
columns(builder);
}
return(builder);
}
/// <summary>
/// 生成工作表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sheetName">工作表名称</param>
/// <param name="items">数据源</param>
/// <param name="columns">数据列构建器</param>
public void GenerateSheet <T>(String sheetName, IEnumerable <T> items, ExcelColumnBuilder <T> columns)
where T : class
{
//设置Table样式
var sheet = String.IsNullOrEmpty(sheetName) ? this.Workbook.CreateSheet() : this.Workbook.CreateSheet(sheetName);
this.CurrentSheet = sheet;
//生成样式
this.GenerateHeader(columns, sheet);
//生成Body
this.GenerateItems(items, columns, sheet);
}
/// <summary>
/// 生成数据列头
/// </summary>
/// <returns></returns>
protected virtual void GenerateHeader <T>(ExcelColumnBuilder <T> columns, ISheet sheet)
where T : class
{
//设置默认的行高、列宽
sheet.DefaultColumnWidth = 15;
sheet.DefaultRowHeight = 14;
var headRow = sheet.CreateRow(0);
Int32 columnIndex = 0;
foreach (var column in columns)
{
//设置为自动调整数据列宽度
sheet.AutoSizeColumn(columnIndex, true);
var headerStyle = this.Workbook.CreateCellStyle();
var columnCell = headRow.CreateCell(columnIndex);
this.SetDefaultHeaderStyle(headerStyle);
if (column.HeaderStyle != null)
{
//自定义设置数据列标题样式
column.HeaderStyle(headerStyle);
}
if (column.CustomHeader != null)
{
//自定义生成数据列标题单元格
column.CustomHeader(columnCell);
}
else
{
columnCell.SetCellValue(column.Name);
}
//设置样式
columnCell.CellStyle = headerStyle;
columnIndex++;
}
//创建最后一空列，防止读取时出现问题。
headRow.CreateCell(columnIndex);
}
/// <summary>
/// 生成数据行
/// </summary>
protected virtual void GenerateItems <T>(IEnumerable <T> dataSource, ExcelColumnBuilder <T> columns, ISheet sheet)
where T : class
{
if (!dataSource.Any())
{
return;
}
//保存单元格样式信息
List <ICellStyle> bodyStyles = new List <ICellStyle>(columns.ColumnCount);
//生成样式信息
foreach (var column in columns)
{
#region 设置样式
var bodyStyle = this.Workbook.CreateCellStyle();
this.SetDefaultBodyStyle(bodyStyle);
if (column.BodyStyle != null)
{
column.BodyStyle(bodyStyle);
}
bodyStyle.WrapText = false; //设置为自动换行
bodyStyle.Alignment = HorizontalAlignment.CenterSelection; //设置为水平居中
bodyStyle.VerticalAlignment = VerticalAlignment.Center; //设置为垂直居中
bodyStyles.Add(bodyStyle);
#endregion
}
//第一行用户数据列头
Int32 rowIndex = 1;
foreach (var item in dataSource)
{
var row = sheet.CreateRow(rowIndex);
Int32 columnIndex = 0;
rowIndex++;
foreach (var column in columns)
{
var cell = row.CreateCell(columnIndex);
cell.CellStyle = bodyStyles[columnIndex];
/************如果是集合就将集合的数据显示到一个Cell并换行***********/
if (column.IsCollection)
{
if (column.CustomRenderer == null)
{
#region 获取数据 并 格式化单元格数据
Object value = null;
if (column.ColumnDelegate != null)
{
value = column.ColumnDelegate(item);
}
else
{
var property = item.GetType().GetProperty(column.Name);
if (property != null)
{
value = property.GetValue(item, null);
}
}
String formattedValue = null;
if (value != null)
{
if (column.Format != null)
{
formattedValue = String.Format(column.Format, value);
}
else
{
if (value is IEnumerable)
{
var IValue = value as IEnumerable;
var enumerable = IValue as object[] ?? IValue.Cast <object>().ToArray();
var list = Enumerable.OfType <object>(enumerable);
if (list.Count() > 1)
{
foreach (var obj in list)
{
formattedValue += "\r\n" + obj.ToString();
}
}
else
{
var firstOrDefault = list.FirstOrDefault();
if (firstOrDefault != null)
{
formattedValue = firstOrDefault.ToString();
}
}
}
//formattedValue = value.ToString();
}
}
#endregion
cell.SetCellValue(formattedValue);
}
else
{
//自定义呈现单元格
column.CustomRenderer(item, cell);
}
}
/***********************/
else
{
if (column.CustomRenderer == null)
{
#region 获取数据 并 格式化单元格数据
dynamic value = null;
if (column.ColumnDelegate != null)
{
value = column.ColumnDelegate(item);
}
else
{
var property = item.GetType().GetProperty(column.Name);
if (property != null)
{
value = property.GetValue(item, null);
}
}
if (value != null)
{
if (column.Format != null)
{
var formattedValue = String.Format(column.Format, value);
cell.SetCellValue(formattedValue);
}
else
{
if (value is decimal)
{
cell.SetCellValue((double)value);
}
else if (value is int)
{
cell.SetCellValue((double)value);
}
else
{
cell.SetCellValue(value.ToString());
}
}
}
#endregion
}
else
{
//自定义呈现单元格
column.CustomRenderer(item, cell);
}
}
#region 设置链接
if (null != column.HrefDelegate)
{
var href = column.HrefDelegate(item);
if (null != href)
{
var link = new HSSFHyperlink(new HyperlinkType())
{
Address = href.ToString()
};
cell.Hyperlink = link;
}
}
#endregion
columnIndex++;
}
}
}