public static ExcelPackage AddSheet(this ExcelPackage ep, DataTable data)
{
ExcelWorkbook workbook = ep.Workbook;
string sheetName = data.TableName;
IExcelTypeFormater <ExcelWorksheet> defaultExcelTypeFormater = new DefaultExcelTypeFormater();
ExcelWorksheet sheet = workbook.Worksheets[sheetName];
if (sheet == null)
{
sheet = workbook.Worksheets.Add(sheetName);
}
defaultExcelTypeFormater.SetExcelWorksheet()?.Invoke(sheet);
var headerNames = new List <string>();
for (int i = 0; i < data.Columns.Count; i++)
{
headerNames.Add(data.Columns[i].ColumnName);
}
IExcelExportFormater <ExcelRangeBase> defaultExcelExportFormater = new DefaultExcelExportFormater();
int row = (sheet?.Dimension?.Rows ?? 0) + 1;
int column = 1;
//表头行
foreach (var headerName in headerNames)
{
defaultExcelExportFormater.SetHeaderCell()?.Invoke(sheet.Cells[row, column], headerName);
column++;
}
row++;
//数据行
if (data != null && data.Rows.Count > 0)
{
for (int i = 0; i < data.Rows.Count; i++)
{
column = 1;
foreach (var headerName in headerNames)
{
var mainValue = data.Rows[i][headerName];
defaultExcelExportFormater.SetBodyCell()?.Invoke(sheet.Cells[row, column], mainValue);
column++;
}
row++;
}
}
return(ep);
}
public static ExcelPackage AddBody(this ExcelPackage ep, string sheetName, IList <IDictionary <string, object> > data)
{
ExcelWorkbook workbook = ep.Workbook;
ExcelWorksheet ws = workbook.Worksheets[sheetName];
if (ws == null)
{
ws = workbook.Worksheets.Add(sheetName);
}
if (data != null && data.Any())
{
IExcelExportFormater <ExcelRangeBase> defaultExcelExportFormater = new DefaultExcelExportFormater();
int row = (ws?.Dimension?.Rows ?? 0) + 1;
foreach (var dic in data)
{
int column = 1;
foreach (var item in dic)
{
if (item.Value is ExportCellValue <ExcelRangeBase> cellValue)
{
if (cellValue?.ExportFormater != null)
{
cellValue?.ExportFormater.SetBodyCell()?.Invoke(ws.Cells[row, column], cellValue.Value);
}
else
{
defaultExcelExportFormater.SetBodyCell()?.Invoke(ws.Cells[row, column], cellValue.Value);
}
}
else
{
var valuePropertyInfo = item.Value.GetType().GetProperties().Where(o => o.Name.Equals("Value", StringComparison.InvariantCultureIgnoreCase)).FirstOrDefault();
var value = valuePropertyInfo?.GetValue(item.Value);
if (valuePropertyInfo == null || value == null)
{
throw new Exception("Value值不能为空");
}
var formatterPropertyInfo = item.Value.GetType().GetProperties().Where(o => typeof(IExcelExportFormater <ExcelRangeBase>).IsAssignableFrom(o.PropertyType)).FirstOrDefault();
if (formatterPropertyInfo != null)
{
var formatterValue = formatterPropertyInfo.GetValue(item.Value) as IExcelExportFormater <ExcelRangeBase>;
if (formatterValue != null)
{
formatterValue.SetBodyCell()?.Invoke(ws.Cells[row, column], value);
}
else
{
defaultExcelExportFormater.SetBodyCell()?.Invoke(ws.Cells[row, column], value);
}
}
else
{
defaultExcelExportFormater.SetBodyCell()?.Invoke(ws.Cells[row, column], value);
}
}
column++;
}
row++;
}
}
return(ep);
}