/// <summary>
/// 向excel写入数据表
/// </summary>
public static ExcelPackage LoadFromDataTable(this ExcelPackage doc, DataTable dt, Action <DataTableOptions> dtOptionsAction)
{
Harry.Check.NotNull(doc, nameof(doc));
Harry.Check.NotNull(dt, nameof(dt));
var dtOptions = new DataTableOptions();
dtOptionsAction?.Invoke(dtOptions);
dtOptions.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;
if (dt.Rows != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (i % dtOptions.PageSize == 0)
{
//写入表头
rownum = 1;
currentSheet = createSheet(doc, dt, ref rownum, $"{dt.TableName}({(++currentSheetIndex).ToString()})", dtOptions);
}
//写入数据行
writeRow(currentSheet, dt.Rows[i], rownum++, dtOptions);
}
}
else
{
//无数据，写个表头然后退出
//写入表头
rownum = 1;
currentSheet = createSheet(doc, dt, ref rownum, $"{dt.TableName}({(++currentSheetIndex).ToString()})", dtOptions);
}
return(doc);
}
private static void writeRow(ExcelWorksheet sheet, DataRow dr, int rownum, DataTableOptions dtOptions)
{
if (dtOptions.DataHeight != null)
{
//设置行高
sheet.Row(rownum).Height = dtOptions.DataHeight.Value;
}
foreach (DataColumn column in dr.Table.Columns)
{
var cell = sheet.Cells[rownum, column.Ordinal + 1];
var value = dr[column];
if (value != null && value != DBNull.Value)
{
cell.Value = dr[column];
}
ValidateCellStyle(cell, column.DataType);
//设置单元格样式
dtOptions.DataCellAction?.Invoke(column, cell);
}
}
//创建一张新表，并写入表头信息
private static ExcelWorksheet createSheet(ExcelPackage doc, DataTable dt, ref int rownum, string sheetName, DataTableOptions dtOptions)
{
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++;
//写入字段信息
foreach (DataColumn column in dt.Columns)
{
var header = worksheet.Cells[rownum, column.Ordinal + 1];
header.Value = column.Caption;
var excelColumn = worksheet.Column(column.Ordinal + 1);
dtOptions.HeaderCellAction?.Invoke(column, excelColumn, header);
}
//var fieldNameCells = worksheet.Cells[rownum, 1, rownum, dt.Columns.Count];
//dtOptions.HeaderStyleAction?.Invoke(fieldNameCells.Style);
if (dtOptions.HeaderHeight != null)
{
//设置header行高
worksheet.Row(rownum).Height = dtOptions.HeaderHeight.Value;
}
//冻结首行
if (dtOptions.FrozenHeader)
{
worksheet.View.FreezePanes(rownum + 1, 1);
}
rownum++;
return(worksheet);
}