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 AddBody(this Workbook workbook, string sheetName, IList <IDictionary <string, object> > data) { Worksheet sheet = workbook.Worksheets[sheetName]; if (sheet == null) { sheet = workbook.Worksheets.Add(sheetName); } if (data != null && data.Any()) { IExcelExportFormater <CellRange> defaultExcelExportFormater = new SpireExcelExportFormater(); int row = (sheet.CellRecords.LastRow == -1 ? 0 : sheet.CellRecords.LastRow) + 1; foreach (var dic in data) { int column = 1; foreach (var item in dic) { if (item.Value is ExportCellValue <CellRange> cellValue) { if (cellValue?.ExportFormater != null) { cellValue?.ExportFormater.SetBodyCell()?.Invoke(sheet[row, column], cellValue.Value); } else { defaultExcelExportFormater.SetBodyCell()?.Invoke(sheet[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 <CellRange>).IsAssignableFrom(o.PropertyType)).FirstOrDefault(); if (formatterPropertyInfo != null) { var formatterValue = formatterPropertyInfo.GetValue(item.Value) as IExcelExportFormater <CellRange>; if (formatterValue != null) { formatterValue.SetBodyCell()?.Invoke(sheet[row, column], value); } else { defaultExcelExportFormater.SetBodyCell()?.Invoke(sheet[row, column], value); } } else { defaultExcelExportFormater.SetBodyCell()?.Invoke(sheet[row, column], value); } } column++; } row++; } } return(workbook); }