/// <summary>获得标题</summary>
public static string GetTitle(ISheet sheet, ExcelTableOptions options)
{
var row = sheet.GetRow(options.TitleRow - 1);
if (row == null)
{
return(null);
}
var values = row.Cells.Where(cell => cell != null).Select(cell => cell.GetCellValue(cell.CellType).ToString().Trim()).ToArray();
var values2 = values.Where(e => !e.IsNullOrEmpty()).ToList();
if (values2.Count == 0)
{
return(string.Empty);
}
var title = values2[0];
if (values2.Count == 1)
{
return(title);
}
values2.RemoveAt(0);
var subTitle = string.Join("", values2);
return($"{title} - {subTitle}");
}
private void Test()
{
var excelFile = @"C:\Users\Administrator\Documents\Tencent Files\4112630\FileRecv\科研项目统计表.xlsx";
var options = new Index.ExcelTableOptions();
options.TitleRow = 1;
options.BeginColumnRow = 2;
options.EndColumnRow = 2;
ExcelBuilder.BuildTable(excelFile, options);
}
/// <summary>生成表</summary>
public static HtmlTable BuildTable(SubmissionProduct product)
{
var match = Regex.Match(product.Options, ExcelTableOptions.Pattern);
if (!match.Success)
{
throw new Exception("Excel表格选项无效!");
}
var options = new ExcelTableOptions();
options.TitleRow = match.Groups[nameof(options.TitleRow)].Value.ToInt32();
options.BeginColumnRow = match.Groups[nameof(options.BeginColumnRow)].Value.ToInt32();
options.EndColumnRow = match.Groups[nameof(options.EndColumnRow)].Value.ToInt32();
options.RememberFormat = match.Groups[nameof(options.RememberFormat)].Value.ToInt32() == 1;
return(BuildTable(product.File, options));
}
/// <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);
}
