/// <summary>
/// 复合表头项(只考虑总分式)
/// </summary>
public class CompositeHeaderItem
{
/// <summary>
/// 实体Field
/// </summary>
public string Key { get; set; }
/// <summary>
/// ColumnName
/// </summary>
public string Label { get; set; }
/// <summary>
/// 子表头
/// </summary>
public List<CompositeHeaderItem> Children { get; set; }
}
/// <summary>
/// 创建表头
/// </summary>
/// <param name="book"></param>
/// <param name="sheet"></param>
/// <param name="Headers"></param>
/// <returns></returns>
private int CreateHeader(HSSFWorkbook book, HSSFSheet sheet, List<CompositeHeaderItem> Headers)
{
(var rowCount, var colCount) = GetHeaderDepthAndWidth(Headers);
int rowIndex = 0;
//表头样式
ICellStyle headerStyle = book.CreateCellStyle();
headerStyle.Alignment = HorizontalAlignment.Center;
IFont fontHeader = book.CreateFont();
fontHeader.FontName = "Cambria";
fontHeader.IsBold = true;
fontHeader.FontHeightInPoints = 12;//设置字体大小
fontHeader.Boldweight = (short)FontBoldWeight.Bold;
headerStyle.SetFont(fontHeader);
表体样式
//ICellStyle bodyStyle = book.CreateCellStyle();
//bodyStyle.Alignment = HorizontalAlignment.Center;
//IDataFormat dataformat = book.CreateDataFormat();
//bodyStyle.DataFormat = dataformat.GetFormat("@");//文本格式
//IFont fontBody = book.CreateFont();
//fontBody.FontName = "Cambria";
//fontBody.IsBold = false;
//fontBody.FontHeightInPoints = 10;
//bodyStyle.SetFont(fontBody);
//标题
if (!string.IsNullOrWhiteSpace(Title))
{
ICellStyle styleTitle = book.CreateCellStyle();
styleTitle.Alignment = HorizontalAlignment.Center;
IFont fontTitle = book.CreateFont();
fontTitle.FontName = "Cambria";
fontTitle.IsBold = true;
fontTitle.FontHeightInPoints = 14;//设置字体大小
fontTitle.Boldweight = (short)FontBoldWeight.Bold;
styleTitle.SetFont(fontTitle);
//添加合并单元格
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, colCount - 1));
var titleRow = sheet.CreateRow(rowIndex);
var cell = titleRow.CreateCell(0);
cell.SetCellValue(Title);
cell.CellStyle = styleTitle;
rowIndex++;
}
IRow headerRow = sheet.CreateRow(rowIndex);
//横向的单元格的列数,这里设置一个方法外的变量,初始化为零
var cellIndex = 0;
rowIndex += SetRowValue(sheet, headerRow, headerStyle, Headers, rowIndex, cellIndex, rowCount);
//设置每一行的自适应单元格内容的长度
sheet.SetColumnWidth(cellIndex, ColumnWidth * 256);
//返回生成表头之后的行数
return rowIndex;
}
/// <summary>
/// 递归画表头
/// </summary>
/// <param name="sheet"></param>
/// <param name="rowIndex"></param>
/// <param name="cellStyle"></param>
/// <param name="row"></param>
/// <param name="cellIndex"></param>
/// <param name="columns"></param>
/// <param name="maxRowCount"></param>
/// <returns></returns>
private int SetRowValue(HSSFSheet sheet, IRow row, ICellStyle cellStyle, List<CompositeHeaderItem> columns, int rowIndex, int cellIndex, int maxRowCount)
{
IRow nextRow = null;
//遍历json格式的字段描述,并横向生成单元格
foreach (var column in columns)
{
List<CompositeHeaderItem> children = column.Children;
if (children != null)
{
//记录单元格起始位置,子表和当前单元格都基于这个画
int subCellStartPosition = cellIndex;
//因为存在子表格,先画子表
if (nextRow == null)
{
nextRow = sheet.CreateRow(++rowIndex);
}
//先递归画子表
var endCellIndex = SetRowValue(sheet, nextRow, cellStyle, children, rowIndex, subCellStartPosition, maxRowCount);
//设置父表格,并返回结尾单元格的列数
cellIndex = SetParentCellValue(sheet, cellStyle, row, column, subCellStartPosition, endCellIndex, maxRowCount);
++cellIndex;
}
else
{
//如果不是复合表头,直接生成并设置单元格
SetCellValue(sheet, cellStyle, row, column, cellIndex, maxRowCount);
++cellIndex;
}
}
//递归,生成这一行的单元格,当前结束单元列数
return cellIndex;
}
/// <summary>
/// 设置复合单元格的父表格
/// </summary>
/// <param name="sheet"></param>
/// <param name="cellStyle"></param>
/// <param name="row"></param>
/// <param name="startRowIndex"></param>
/// <param name="startCellIndex"></param>
/// <param name="endCellIndex"></param>
/// <param name="maxRowCount"></param>
/// <param name="field"></param>
/// <param name="childrenSize"></param>
/// <returns></returns>
private int SetParentCellValue(HSSFSheet sheet, ICellStyle cellStyle, IRow row, CompositeHeaderItem field, int startCellIndex, int endCellIndex, int maxRowCount)
{
SetCellValue(sheet, cellStyle, row, field, startCellIndex, maxRowCount);
CreateMergeCell(sheet, row, startCellIndex, endCellIndex);
return endCellIndex;
}
/// <summary>
/// 设置单元格
/// </summary>
/// <param name="sheet"></param>
/// <param name="cellStyle"></param>
/// <param name="row"></param>
/// <param name="cellIndex"></param>
/// <param name="maxRowCount"></param>
/// <param name="column"></param>
/// <returns></returns>
private void SetCellValue(HSSFSheet sheet, ICellStyle cellStyle, IRow row, CompositeHeaderItem column, int cellIndex, int maxRowCount)
{
ICell subCell = row.CreateCell(cellIndex);
subCell.CellStyle = cellStyle;
subCell.SetCellValue(column.Label.ToString());
sheet.AddMergedRegion(new CellRangeAddress(row.RowNum, maxRowCount, cellIndex, cellIndex));
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="sheet"></param>
/// <param name="row"></param>
/// <param name="startRowIndex"></param>
/// <param name="columnFrom"></param>
/// <param name="columnTo"></param>
private void CreateMergeCell(HSSFSheet sheet, IRow row, int columnFrom, int columnTo)
{
sheet.AddMergedRegion(new CellRangeAddress(row.RowNum, row.RowNum, columnFrom, columnTo));
}
/// <summary>
/// 获取表头(树)深度合宽度
/// </summary>
/// <param name="headers"></param>
/// <returns></returns>
private static (int depth, int width) GetHeaderDepthAndWidth(List<CompositeHeaderItem> headers)
{
return (GetHeaderDepth(headers), GetHeaderWidth(headers));
}
/// <summary>
/// 获取表头(树)深度
/// </summary>
/// <param name="headers"></param>
/// <returns></returns>
private static int GetHeaderDepth(List<CompositeHeaderItem> headers)
{
if (headers == null || headers.Count <= 0)
return 0;
else
{
var list = new List<int>();
for (var i = 0; i < headers.Count; i++)
{
list.Add(GetHeaderDepth(headers[i].Children));
}
return list.Max() + 1;
}
}
/// <summary>
/// 获取表头(树)宽度
/// </summary>
/// <param name="headers"></param>
/// <param name="colCount"></param>
/// <returns></returns>
private static int GetHeaderWidth(List<CompositeHeaderItem> headers, int colCount = 0)
{
if (headers == null || headers.Count <= 0)
return colCount;
colCount += headers.Count;
foreach (var header in headers)
{
if (header.Children != null && header.Children.Count > 0)
{
colCount--;
colCount = GetHeaderWidth(header.Children, colCount);
}
}
return colCount;
}