public static IWorkbook AddSheet(this IWorkbook workbook, DataTable data) { string sheetName = data.TableName; IExcelTypeFormater <ISheet> defaultExcelTypeFormater = new NpoiExcelTypeFormater(); ISheet sheet = workbook.GetSheet(sheetName); if (sheet == null) { sheet = workbook.CreateSheet(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 <ICell> defaultExcelExportFormater = new NpoiExcelExportFormater(); int row = (sheet?.PhysicalNumberOfRows ?? 0); int column = 0; //表头行 var headerRowCell = sheet.CreateRow(row); foreach (var headerName in headerNames) { defaultExcelExportFormater.SetHeaderCell()?.Invoke(headerRowCell.CreateCell(column), headerName); column++; } row++; //数据行 if (data != null && data.Rows.Count > 0) { for (int i = 0; i < data.Rows.Count; i++) { var rowCell = sheet.CreateRow(row); column = 0; foreach (var headerName in headerNames) { var mainValue = data.Rows[i][headerName]; defaultExcelExportFormater.SetBodyCell()?.Invoke(rowCell.CreateCell(column), mainValue); column++; } row++; } } return(workbook); }
public static IWorkbook AddSheetHeader(this IWorkbook workbook, string sheetName, IList <string> headers, Action <ICell, object> action = null) { if (string.IsNullOrEmpty(sheetName)) { throw new ArgumentNullException(nameof(sheetName)); } if (headers == null) { throw new ArgumentNullException(nameof(headers)); } IExcelTypeFormater <ISheet> defaultExcelTypeFormater = new NpoiExcelTypeFormater(); ISheet sheet = workbook.GetSheet(sheetName); if (sheet == null) { sheet = workbook.CreateSheet(sheetName); } defaultExcelTypeFormater.SetExcelWorksheet()?.Invoke(sheet); IExcelExportFormater <ICell> defaultExcelExportFormater = new NpoiExcelExportFormater(); int row = (sheet?.PhysicalNumberOfRows ?? 0); int column = 0; //表头行 var headerRowCell = sheet.CreateRow(row); foreach (var item in headers) { if (action == null) { defaultExcelExportFormater.SetHeaderCell()(headerRowCell.CreateCell(column), item); } else { action.Invoke(headerRowCell.CreateCell(column), item); } column++; } row++; return(workbook); }