public static Workbook AddSheet(this Workbook workbook, DataTable data)
{
string sheetName = data.TableName;
IExcelTypeFormater <Worksheet> defaultExcelTypeFormater = new SpireExcelTypeFormater();
Worksheet 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 <CellRange> defaultExcelExportFormater = new SpireExcelExportFormater();
int row = (sheet.CellRecords.LastRow == -1 ? 0 : sheet.CellRecords.LastRow) + 1;
int column = 1;
//表头行
foreach (var headerName in headerNames)
{
defaultExcelExportFormater.SetHeaderCell()?.Invoke(sheet[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[row, column], mainValue);
column++;
}
row++;
}
}
return(workbook);
}
public static Workbook AddSheetHeader(this Workbook workbook, string sheetName, IList <SpireHeaderInfo> headers)
{
if (string.IsNullOrEmpty(sheetName))
{
throw new ArgumentNullException(nameof(sheetName));
}
if (headers == null)
{
throw new ArgumentNullException(nameof(headers));
}
IExcelTypeFormater <Worksheet> defaultExcelTypeFormater = new SpireExcelTypeFormater();
Worksheet sheet = workbook.Worksheets[sheetName];
if (sheet == null)
{
sheet = workbook.Worksheets.Add(sheetName);
}
defaultExcelTypeFormater.SetExcelWorksheet()?.Invoke(sheet);
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 headers)
{
if (item.Action == null)
{
defaultExcelExportFormater.SetHeaderCell()(sheet[row, column], item.HeaderName);
}
else
{
item.Action.Invoke(sheet[row, column], item.HeaderName);
}
column++;
}
row++;
return(workbook);
}