/// <summary>
/// 向excel写入数据集合
/// </summary>
public static ExcelPackage LoadFromEnumerableData <T>(this ExcelPackage doc, IEnumerable <T> data, Action <EnumerableDataOptions <T> > optionsAction)
{
Harry.Check.NotNull(doc, nameof(doc));
Harry.Check.NotNull(data, nameof(data));
var options = new EnumerableDataOptions <T>();
optionsAction?.Invoke(options);
if (string.IsNullOrEmpty(options.TableName))
{
options.TableName = "Sheet";
}
options.WorkbookAction?.Invoke(doc.Workbook);
//doc.Workbook.Worksheets.Add($"{dt.TableName}")
// .Cells[1, 1].LoadFromDataTable(dt, false);
//return;
int rownum = 0;
int currentSheetIndex = 0;
ExcelWorksheet currentSheet = null;
rownum = 1;
currentSheet = createSheetForEnumerableData(doc, ref rownum, $"{options.TableName}({(++currentSheetIndex).ToString()})", options);
int i = 0;
foreach (var item in data)
{
//写入数据行
if (options.DataHeight != null)
{
//设置行高
currentSheet.Row(rownum).Height = options.DataHeight.Value;
}
options.DataRowAction?.Invoke(currentSheet, rownum, item);
rownum++;
i++;
if (i % options.PageSize == 0)
{
//写入表头
rownum = 1;
currentSheet = createSheetForEnumerableData(doc, ref rownum, $"{options.TableName}({(++currentSheetIndex).ToString()})", options);
}
}
return(doc);
}
private static ExcelWorksheet createSheetForEnumerableData <T>(ExcelPackage doc, ref int rownum, string sheetName, EnumerableDataOptions <T> options)
{
ExcelWorksheet worksheet = doc.Workbook.Worksheets.Add(doc.GetValidSheetName(sheetName));
////写入表名称
//var titleCell = worksheet.Cells[rownum, 1];
//titleCell.Value = dt.TableName;
////设置标题样式
//dtOptions.TitleStyleAction?.Invoke(titleCell.Style);
//rownum++;
//写入表头信息
//if (options.HeaderRowActions != null && options.HeaderRowActions.Count > 0)
//{
// for (int i = 1; i <= options.HeaderRowActions.Count; i++)
// {
// var header = worksheet.Cells[rownum, i];
// var excelColumn = worksheet.Column(i);
// options.HeaderRowActions[i].Invoke(excelColumn, header);
// }
//}
options.HeaderRowAction?.Invoke(worksheet, rownum);
if (options.HeaderHeight != null)
{
//设置header行高
worksheet.Row(rownum).Height = options.HeaderHeight.Value;
}
//冻结首行
if (options.FrozenHeader)
{
worksheet.View.FreezePanes(rownum + 1, 1);
}
rownum++;
return(worksheet);
}
