public static Workbook AddSheet <T>(this Workbook workbook, IList <T> data = null) where T : class, new()
{
string sheetName = null;
IExcelTypeFormater <Worksheet> defaultExcelTypeFormater = null;
var excelAttribute = typeof(T).GetCustomAttribute <ExcelAttribute>();
if (excelAttribute == null)
{
sheetName = typeof(T).Name;
defaultExcelTypeFormater = new SpireExcelTypeFormater();
}
else
{
if (excelAttribute.IsIncrease)
{
if (workbook.Worksheets.Count == 0)
{
sheetName = $"{excelAttribute.SheetName}";
}
else
{
sheetName = $"{excelAttribute.SheetName}{ workbook.Worksheets.Count}";
}
}
else
{
sheetName = excelAttribute.SheetName;
}
if (excelAttribute.ExportExcelType != null)
{
defaultExcelTypeFormater = Activator.CreateInstance(excelAttribute.ExportExcelType) as IExcelTypeFormater <Worksheet>;
}
if (defaultExcelTypeFormater == null)
{
defaultExcelTypeFormater = new SpireExcelTypeFormater();
}
}
Worksheet sheet = workbook.Worksheets[sheetName];
if (sheet == null)
{
sheet = workbook.Worksheets.Add(sheetName);
}
defaultExcelTypeFormater.SetExcelWorksheet()?.Invoke(sheet);
var mainPropertieList = typeof(T).ToColumnDic();
IList <IExcelExportFormater <CellRange> > excelTypes = new List <IExcelExportFormater <CellRange> >();
IExcelExportFormater <CellRange> defaultExcelExportFormater = new SpireExcelExportFormater();
int row = (sheet.CellRecords.LastRow == -1 ? 0 : sheet.CellRecords.LastRow) + 1;
int column = 1;
//表头行
foreach (var item in mainPropertieList)
{
IExcelExportFormater <CellRange> excelType = null;
if (item.Value.ExportExcelType != null)
{
excelType = excelTypes.Where(o => o.GetType().FullName == item.Value.ExportExcelType.FullName).FirstOrDefault();
if (excelType == null)
{
excelType = Activator.CreateInstance(item.Value.ExportExcelType) as IExcelExportFormater <CellRange>;
excelTypes.Add(excelType);
}
}
else
{
excelType = defaultExcelExportFormater;
}
excelType.SetHeaderCell()?.Invoke(sheet[row, column], item.Value.Name);
column++;
}
row++;
//数据行
if (data != null && data.Any())
{
foreach (var item in data)
{
column = 1;
foreach (var mainPropertie in mainPropertieList)
{
IExcelExportFormater <CellRange> excelType = null;
var mainValue = mainPropertie.Key.GetValue(item);
if (mainPropertie.Value.ExportExcelType != null)
{
excelType = excelTypes.Where(o => o.GetType().FullName == mainPropertie.Value.ExportExcelType.FullName).FirstOrDefault();
if (excelType == null)
{
excelType = Activator.CreateInstance(mainPropertie.Value.ExportExcelType) as IExcelExportFormater <CellRange>;
excelTypes.Add(excelType);
}
}
else
{
excelType = defaultExcelExportFormater;
}
excelType.SetBodyCell()?.Invoke(sheet[row, column], mainValue);
column++;
}
row++;
}
}
return(workbook);
}
public static IWorkbook AddSheet <T>(this IWorkbook workbook, IList <T> data = null) where T : class, new()
{
string sheetName = null;
IExcelTypeFormater <ISheet> defaultExcelTypeFormater = null;
var excelAttribute = typeof(T).GetCustomAttribute <ExcelAttribute>();
if (excelAttribute == null)
{
sheetName = typeof(T).Name;
defaultExcelTypeFormater = new NpoiExcelTypeFormater();
}
else
{
if (excelAttribute.IsIncrease)
{
if (workbook.NumberOfSheets == 0)
{
sheetName = $"{excelAttribute.SheetName}";
}
else
{
sheetName = $"{excelAttribute.SheetName}{workbook.NumberOfSheets}";
}
}
else
{
sheetName = excelAttribute.SheetName;
}
if (excelAttribute.ExportExcelType != null && typeof(IExcelTypeFormater <ISheet>).IsAssignableFrom(excelAttribute.ExportExcelType))
{
defaultExcelTypeFormater = Activator.CreateInstance(excelAttribute.ExportExcelType) as IExcelTypeFormater <ISheet>;
}
if (defaultExcelTypeFormater == null)
{
defaultExcelTypeFormater = new NpoiExcelTypeFormater();
}
}
ISheet sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
sheet = workbook.CreateSheet(sheetName);
}
defaultExcelTypeFormater.SetExcelWorksheet()?.Invoke(sheet);
var mainPropertieList = typeof(T).ToColumnDic();
IList <IExcelExportFormater <ICell> > excelTypes = new List <IExcelExportFormater <ICell> >();
IExcelExportFormater <ICell> defaultExcelExportFormater = new NpoiExcelExportFormater();
int row = (sheet?.PhysicalNumberOfRows ?? 0);
int column = 0;
//表头行
var headerRowCell = sheet.CreateRow(row);
foreach (var item in mainPropertieList)
{
IExcelExportFormater <ICell> excelType = null;
if (item.Value.ExportExcelType != null)
{
excelType = excelTypes.Where(o => o.GetType().FullName == item.Value.ExportExcelType.FullName).FirstOrDefault();
if (excelType == null)
{
excelType = Activator.CreateInstance(item.Value.ExportExcelType) as IExcelExportFormater <ICell>;
excelTypes.Add(excelType);
}
}
else
{
excelType = defaultExcelExportFormater;
}
excelType.SetHeaderCell()?.Invoke(headerRowCell.CreateCell(column), item.Value.Name);
column++;
}
row++;
//数据行
if (data != null && data.Any())
{
foreach (var item in data)
{
var rowCell = sheet.CreateRow(row);
column = 0;
foreach (var mainPropertie in mainPropertieList)
{
IExcelExportFormater <ICell> excelType = null;
var mainValue = mainPropertie.Key.GetValue(item);
if (mainPropertie.Value.ExportExcelType != null)
{
excelType = excelTypes.Where(o => o.GetType().FullName == mainPropertie.Value.ExportExcelType.FullName).FirstOrDefault();
if (excelType == null)
{
excelType = Activator.CreateInstance(mainPropertie.Value.ExportExcelType) as IExcelExportFormater <ICell>;
excelTypes.Add(excelType);
}
}
else
{
excelType = defaultExcelExportFormater;
}
excelType.SetBodyCell()?.Invoke(rowCell.CreateCell(column), mainValue);
column++;
}
row++;
}
}
return(workbook);
}