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); }
public static IWorkbook AddBody(this IWorkbook workbook, string sheetName, IList <IDictionary <string, object> > data) { ISheet sheet = workbook.GetSheet(sheetName); if (sheet == null) { sheet = workbook.CreateSheet(sheetName); } if (data != null && data.Any()) { IExcelExportFormater <ICell> defaultExcelExportFormater = new NpoiExcelExportFormater(); int row = (sheet?.PhysicalNumberOfRows ?? 0); foreach (var dic in data) { int column = 0; var rowCell = sheet.CreateRow(row); foreach (var item in dic) { if (item.Value is ExportCellValue <ICell> cellValue) { if (cellValue?.ExportFormater != null) { cellValue?.ExportFormater.SetBodyCell()?.Invoke(rowCell.CreateCell(column), cellValue.Value); } else { defaultExcelExportFormater.SetBodyCell()?.Invoke(rowCell.CreateCell(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 <ICell>).IsAssignableFrom(o.PropertyType)).FirstOrDefault(); if (formatterPropertyInfo != null) { var formatterValue = formatterPropertyInfo.GetValue(item.Value) as IExcelExportFormater <ICell>; if (formatterValue != null) { formatterValue.SetBodyCell()?.Invoke(rowCell.CreateCell(column), value); } else { defaultExcelExportFormater.SetBodyCell()?.Invoke(rowCell.CreateCell(column), value); } } else { defaultExcelExportFormater.SetBodyCell()?.Invoke(rowCell.CreateCell(column), value); } } 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); }