/// <summary> /// 设置页脚样式 /// </summary> /// <param name="table">工作表</param> /// <param name="style">单元格样式</param> /// <returns></returns> public IExcel FootStyle(IWorkSheet table, CellStyle style) { if (_footStyle == null) { _footStyle = CellStyleResolver.Resolve(_workbook, style); } Style(table.HeadRowCount + table.BodyRowCount, table.RowCount - 1, 0, table.ColumnCount - 1, _footStyle); return(this); }
/// <summary> /// 设置表头样式 /// </summary> /// <param name="table">工作表</param> /// <param name="style">单元格样式</param> /// <returns></returns> public IExcel HeadStyle(IWorkSheet table, CellStyle style) { if (_headStyle == null) { _headStyle = CellStyleResolver.Resolve(_workbook, style); } Style(0, table.HeadRowCount - 1, 0, table.ColumnCount - 1, _headStyle); return(this); }
/// <summary> /// 设置正文样式 /// </summary> /// <param name="table">工作表</param> /// <param name="style">单元格样式</param> /// <returns></returns> public IExcel BodyStyle(IExcelSheet table, CellStyle style) { if (_bodyStyle == null) { _bodyStyle = CellStyleResolver.Resolve(_workbook, style); } Style(table.HeadRowCount, table.HeadRowCount + table.BodyRowCount - 1, 0, table.ColumnNumber - 1, _bodyStyle); return(this); }
/// <summary> /// 创建日期样式 /// </summary> /// <returns></returns> private NPOI.SS.UserModel.ICellStyle CreateDateStyle() { if (_dateStyle != null) { return(_dateStyle); } _dateStyle = CellStyleResolver.Resolve(_workbook, CellStyle.Body()); var format = _workbook.CreateDataFormat(); _dateStyle.DataFormat = format.GetFormat(_dateFormat); return(_dateStyle); }
public static IWorkbook ToWorkbook <T>(this IEnumerable <T> source, IWorkbook workbook, ExcelSetting excelSetting, string sheetName = "sheet0", bool overwrite = false) where T : class { if (null == source) { throw new ArgumentNullException(nameof(source)); } if (null == workbook) { throw new ArgumentNullException(nameof(workbook)); } if (string.IsNullOrWhiteSpace(sheetName)) { throw new ArgumentNullException($"工作表名称不能为空或空字符串", nameof(sheetName)); } var properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty); bool fluentConfigEnabled = false; if (excelSetting.FluentConfigs.TryGetValue(typeof(T), out var fluentConfig)) { fluentConfigEnabled = true; (fluentConfig as FluentConfiguration <T>)?.AdjustAutoIndex(); } // 查找输出配置 var propertyConfigurations = new PropertyConfiguration[properties.Length]; for (var i = 0; i < properties.Length; i++) { var property = properties[i]; if (fluentConfigEnabled && fluentConfig.PropertyConfigurations.TryGetValue(property.Name, out var pc)) { propertyConfigurations[i] = pc; } else { propertyConfigurations[i] = null; } } // 再次检查工作表名称 var sheet = workbook.GetSheet(sheetName); if (sheet == null) { sheet = workbook.CreateSheet(sheetName); } else { // 如果不需要,则不会覆盖现有工作表 if (!overwrite) { sheet = workbook.CreateSheet(); } } // 缓存单元格样式 var cellStyles = new Dictionary <int, ICellStyle>(); // 标题行单元格样式 ICellStyle titleStyle = null; if (excelSetting.TitleCellStyleApplier != null) { var style = new CellStyle(); excelSetting.TitleCellStyleApplier(style); titleStyle = CellStyleResolver.Resolve(workbook, style); } var titleRow = sheet.CreateRow(0); var rowIndex = 1; foreach (var item in source) { var row = sheet.CreateRow(rowIndex); for (var i = 0; i < properties.Length; i++) { var property = properties[i]; int index = i; var config = propertyConfigurations[i]; if (config != null) { if (config.IgnoreExport) { continue; } index = config.Index; if (index < 0) { throw new Exception($"{property.Name} 索引值不能小于0。请参考 HasExcelIndex(int index) 方法以获取更多信息。"); } } if (rowIndex == 1) { // 没有标题的时候,使用属性名作为标题 var title = property.Name; if (!string.IsNullOrWhiteSpace(config?.Title)) { title = config.Title; } if (!string.IsNullOrWhiteSpace(config?.Formatter)) { try { var style = workbook.CreateCellStyle(); var dataFormat = workbook.CreateDataFormat(); style.DataFormat = dataFormat.GetFormat(config.Formatter); cellStyles[i] = style; } catch (Exception e) { System.Diagnostics.Debug.WriteLine(e.ToString()); } } var titleCell = titleRow.CreateCell(index); if (titleCell != null) { titleCell.CellStyle = titleStyle; } titleCell.SetValue(title); } var unwrapType = property.PropertyType.UnwrapNullableType(); var value = property.GetValue(item, null); // 进行值转换 if (config?.CellValueConverter != null) { value = config.CellValueConverter(rowIndex, index, value); if (value == null) { continue; } unwrapType = value.GetType().UnwrapNullableType(); } if (value == null) { continue; } var cell = row.CreateCell(index); if (cellStyles.TryGetValue(i, out var cellStyle)) { cell.CellStyle = cellStyle; } else if (!string.IsNullOrWhiteSpace(config?.Formatter) && value is IFormattable fv) { // C#格式化程序 cell.SetCellValue(fv.ToString(config.Formatter, CultureInfo.CurrentCulture)); continue; } cell.SetValue(value); } rowIndex++; } // 合并单元格 var mergableConfigs = propertyConfigurations.Where(c => c != null && c.AllowMerge).ToList(); if (mergableConfigs.Any()) { var vStyle = workbook.CreateCellStyle(); vStyle.VerticalAlignment = VerticalAlignment.Center; foreach (var config in mergableConfigs) { object previous = null; int rowSpan = 0, row = 1; for (row = 1; row < rowIndex; row++) { var value = sheet.GetRow(row).GetCellValue(config.Index, _formulaEvaluator); if (object.Equals(previous, value) && value != null) { rowSpan++; } else { if (rowSpan > 1) { sheet.GetRow(row - rowSpan).Cells[config.Index].CellStyle = vStyle; sheet.AddMergedRegion(new CellRangeAddress(row - rowSpan, row - 1, config.Index, config.Index)); } rowSpan = 1; previous = value; } } if (rowSpan > 1) { sheet.GetRow(row - rowSpan).Cells[config.Index].CellStyle = vStyle; sheet.AddMergedRegion(new CellRangeAddress(row - rowSpan, row - 1, config.Index, config.Index)); } } } if (rowIndex > 1 && fluentConfigEnabled) { var statistics = fluentConfig.StatisticsConfigurations; var filterConfigs = fluentConfig.FilterConfigurations; var freezeConfigs = fluentConfig.FreezeConfigurations; // 统计信息 foreach (var item in statistics) { var lastRow = sheet.CreateRow(rowIndex); var cell = lastRow.CreateCell(0); cell.SetValue(item.Name); foreach (var column in item.Columns) { cell = lastRow.CreateCell(column); cell.CellStyle = sheet.GetRow(rowIndex - 1)?.GetCell(column)?.CellStyle; cell.CellFormula = $"{item.Formula}({GetCellPosition(1, column)}:{GetCellPosition(rowIndex - 1, column)})"; } rowIndex++; } // 冻结窗口 foreach (var freeze in freezeConfigs) { sheet.CreateFreezePane(freeze.ColumnSplit, freeze.RowSpit, freeze.LeftMostColumn, freeze.TopRow); } // 筛选器 foreach (var filter in filterConfigs) { sheet.SetAutoFilter(new CellRangeAddress(filter.FirstRow, filter.LastRow ?? rowIndex, filter.FirstColumn, filter.LastColumn)); } } // 自动列宽 if (excelSetting.AutoSizeColumnsEnabled) { for (var i = 0; i < properties.Length; i++) { sheet.AutoSizeColumn(i); } } return(workbook); }