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 AddSheetHeader(this ExcelPackage ep, string sheetName, IList <string> headers, Action <ExcelRangeBase, object> action = null) { if (string.IsNullOrEmpty(sheetName)) { throw new ArgumentNullException(nameof(sheetName)); } if (headers == null) { throw new ArgumentNullException(nameof(headers)); } ExcelWorkbook workbook = ep.Workbook; IExcelTypeFormater <ExcelWorksheet> defaultExcelTypeFormater = new DefaultExcelTypeFormater(); ExcelWorksheet sheet = workbook.Worksheets[sheetName]; if (sheet == null) { sheet = workbook.Worksheets.Add(sheetName); } defaultExcelTypeFormater.SetExcelWorksheet()?.Invoke(sheet); IExcelExportFormater <ExcelRangeBase> defaultExcelExportFormater = new DefaultExcelExportFormater(); int row = (sheet?.Dimension?.Rows ?? 0) + 1; int column = 1; //表头行 foreach (var item in headers) { if (action == null) { defaultExcelExportFormater.SetHeaderCell()(sheet.Cells[row, column], item); } else { action.Invoke(sheet.Cells[row, column], item); } 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); }
public static ExcelPackage AddSheet <T>(this ExcelPackage ep, IList <T> data = null) where T : class, new() { ExcelWorkbook workbook = ep.Workbook; string sheetName = null; IExcelTypeFormater <ExcelWorksheet> defaultExcelTypeFormater = null; var excelAttribute = typeof(T).GetCustomAttribute <ExcelAttribute>(); if (excelAttribute == null) { sheetName = typeof(T).Name; defaultExcelTypeFormater = new DefaultExcelTypeFormater(); } 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 <ExcelWorksheet>; } if (defaultExcelTypeFormater == null) { defaultExcelTypeFormater = new DefaultExcelTypeFormater(); } } ExcelWorksheet sheet = workbook.Worksheets[sheetName]; if (sheet == null) { sheet = workbook.Worksheets.Add(sheetName); } defaultExcelTypeFormater.SetExcelWorksheet()?.Invoke(sheet); var mainPropertieList = typeof(T).ToColumnDic(); IList <IExcelExportFormater <ExcelRangeBase> > excelTypes = new List <IExcelExportFormater <ExcelRangeBase> >(); IExcelExportFormater <ExcelRangeBase> defaultExcelExportFormater = new DefaultExcelExportFormater(); int row = (sheet?.Dimension?.Rows ?? 0) + 1; int column = 1; //表头行 foreach (var item in mainPropertieList) { IExcelExportFormater <ExcelRangeBase> 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 <ExcelRangeBase>; excelTypes.Add(excelType); } } else { excelType = defaultExcelExportFormater; } excelType.SetHeaderCell()?.Invoke(sheet.Cells[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 <ExcelRangeBase> 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 <ExcelRangeBase>; excelTypes.Add(excelType); } } else { excelType = defaultExcelExportFormater; } excelType.SetBodyCell()?.Invoke(sheet.Cells[row, column], mainValue); column++; } row++; } } return(ep); }