/// <summary>生成图例</summary>
private static void BuildChartLegend(ISheet sheet, HtmlChart chart, HtmlChartSettings setting, int rowIndex, int firstColIndex)
{
var row = sheet.GetRow(rowIndex);
if (row == null)
{
return;
}
for (int i = firstColIndex; i <= row.LastCellNum; i++)
{
var cell = row.GetCell(i);
if (cell == null)
{
break;
}
var title = cell.GetCellValue(cell.CellType).ToString();
if (title.IsNullOrEmpty())
{
break;
}
var column = new HtmlTableColumn();
column.Field = "field" + i.ToString();
column.Title = title;
if (i == firstColIndex)
{
column.Type = JsonDataType.String;
}
else
{
column.Type = JsonDataType.Number;
setting.Legend.Add(title);
}
chart.Columns.Add(column);
}
}
public HtmlTableCell(HtmlTableColumn column)
{
Column = column;
}
/// <summary>生成表</summary>
public static HtmlTable BuildTable(string excelFile, ExcelTableOptions options)
{
var sheet = ExcelUtil.OpenFirst(excelFile);
var table = new HtmlTable();
table.Init();
table.Unit = "pt";
IRow row = null;
if (options.TitleRow > 0)
{
table.TableName = GetTitle(sheet, options);
table.Title = table.TableName;
}
var mergedRegions = sheet.GetMergedRegions();
//生成表头集合
var firstColumnRow = options.BeginColumnRow - 1;
var lastColumnRow = options.EndColumnRow - 1;
var rows = sheet.GetRows(firstColumnRow, lastColumnRow);
var firstColumn = rows.Min(e => e.FirstCellNum);
var lastColumn = rows.Max(e => e.LastCellNum);
var invalidColumns = new List <int>();
for (int rowIndex = 0; rowIndex < rows.Count; rowIndex++)
{
row = rows[rowIndex];
var header = new List <HtmlTableColumn>();
for (int colIndex = firstColumn; colIndex <= lastColumn; colIndex++)
{
if (invalidColumns.Contains(colIndex))
{
continue;
}
var column = new HtmlTableColumn();
var cell = row.GetCell(colIndex);
column.Field = $"field{colIndex.ToString()}";
//column.Width = sheet.GetColumnWidthInPoints(colIndex);
column.Order = colIndex;
column.Type = JsonDataType.String;
if (cell == null)
{
if (rowIndex == 0)
{
var cells = sheet.GetColumnDataCells(colIndex, row.RowNum + 1);
if (!cells.HaveData())
{
invalidColumns.Add(colIndex);
continue;
}
}
column.Title = string.Empty;
}
else if (!cell.IsMergedCell)
{
column.Title = cell.GetCellValue(cell.CellType).ToString();
//column.Align = GetHAlign(cell);
//column.VAlign = GetVAlign(cell);
}
else
{
var region = mergedRegions.GetMergedRegion(cell);
if (!region.IsMergedRegionFirstCell(cell))
{
continue;
}
column.Title = cell.GetCellValue(cell.CellType).ToString();
//column.Align = GetHAlign(cell);
//column.VAlign = GetVAlign(cell);
//column.Width = sheet.GetMergedRegionWidthInPoints(region);
column.RowSpan = region.LastRow - region.FirstRow + 1;
column.ColSpan = region.LastColumn - region.FirstColumn + 1;
colIndex += region.LastColumn - region.FirstColumn;
}
header.Add(column);
}
table.Headers.Add(header);
table.HeaderRowHeights.Add(row.HeightInPoints);
}
//生成数据行
var firstDataRow = options.EndColumnRow;
var lastDataRow = sheet.LastRowNum;
for (int rowIndex = firstDataRow; rowIndex <= lastDataRow; rowIndex++)
{
row = sheet.GetRow(rowIndex);
if (row == null)
{
continue;
}
var rowValues = sheet.GetRowValues(row, firstColumn, lastColumn, mergedRegions, invalidColumns);
table.Rows.Add(rowValues);
table.RowHeights.Add(row.HeightInPoints);
}
//生成列集合
if (table.Headers.Count == 1)
{
table.Columns.AddRange(table.Headers[0]);
table.Headers.Clear();
}
//var dataFormatter = sheet.Workbook.CreateDataFormat();
//for (int colIndex = firstColumn; colIndex <= lastColumn; colIndex++)
//{
// if (invalidColumns.Contains(colIndex)) continue;
// var column = new HtmlTableColumn();
// column.Field = $"field{colIndex.ToString()}";
// column.Title = column.Field;
// column.Type = GetColumnType(table, colIndex);
// column.Order = colIndex;
// //column.Width = sheet.GetColumnWidthInPoints(colIndex);
// var cell = sheet.GetRow(firstDataRow)?.GetCell(colIndex);
// if (cell != null)
// {
// //column.Align = GetHAlign(cell);
// //column.VAlign = GetVAlign(cell);
// var format = cell.CellStyle.DataFormat;
// if (format > 0) column.Format = dataFormatter.GetFormat(format);
// }
// //column.Visible = false;
// table.Columns.Add(column);
//}
return(table);
}