自定义注解导出Excel文件
核心思想
1：按注解思想解构表头，样式，条件注解，内容块
2：根据注解的数据体标识，定义数据属于哪个Sheet，和sheet块内容位置
3：收集字段标识的注解属性，实现，表头字段、单元格样式、条件注解
4：总体来说，就是根据注解收集属性，在根据属性生成表格内容
4：样式工具类和条件格式工具类，都定义了一个Map对象，用来收集生成的样式，都是针对当前Sheet的，所以使用完之后，一定要调用destroy方法，清空当前map.
5：关于条件格式，可以使用注解@ExcelTableData 的conditionStyle属性内置一些常规的颜色条件，也可以使用@ConditionalFormat注解，写公式，建议常规的可以封装进ExcelConditionUtil的工具类，直接调用，复杂的不满足条件的可以写注解公式，例如
@ConditionalFormat(formula = "AND($=\"601899\")", fontColor = ExcelConditionColor.RED, backGroundColor = ExcelConditionColor.WHITE, formulaType = 1)
解释：AND($=\"601899\") 区域值==601899， $：这是固定写法（类似占位符），后续会根据单元格的索引替换成具体的单元格引用转换为具体的某列，最终的表达式 AND(A2=\"601899\")
导出样例
ExcelTable注解：定义整体数据对象
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelTable {
/** sheet 名称*/
String sheetName() default "sheet1";
/** 块行索引*/
int blockRowIndex() default 0;
/** 块列索引*/
int bockColIndex() default 0;
/** 当前区域块边距*/
int[] margins() default {0,1,1,0};
}
1：sheetName:sheetName的名称，多数据块 使用同一个SheetName
2：blockRowIndex和bockColIndex 组成当前内容块的区域位置，每个数据集都标识为一个数据块
3：margins 标识当前数据块与四周的外边距（间隔多少行），值为行数，按照上-右-下-左的顺序
ExcelTableData 注解：定义数据体对象
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelTableData {
/**
* 字段名
*/
String titleName();
/**
* 字段索引
*/
int titleIndex() default 0;
/**
* 字体大小
*/
int fontSize() default Constants.NUMBER_12;
/**
* 列宽
*/
int width() default Constants.NUMBER_14;
/**
* 行高
*/
int height() default Constants.NUMBER_20;
/**
* 单元格批注
*/
String comment() default "";
/**
* 基础样式
*/
ExcelStyle style() default ExcelStyle.NORMAL;
/**
* 条件格式
*/
ExcelConditionStyle conditionStyle() default ExcelConditionStyle.NONE;
}
titleName：标识导出到excel的数据表头列名称
titleIndex：字段排序位置，当前未实现，表头输出顺序按照字段排序位置，后续进行优化
fontSize：数据内容的字体大小，当前表头和字体大小都是默认的，后续进行优化
width：列宽属性，默认是14*256 256是个常量，每次定义数值就可以，默认*256
height：废弃，无实现
comment：单元格表头的批注内容
ExcelStyle：定义基础的样式格式
ExcelConditionStyle：条件格式
ConditionalFormat：条件注解
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ConditionalFormat {
byte operator() default ComparisonOperator.GT;
String formula() default "0";
// 如果选用公式写法，必须设置该值切非0
int formulaType() default 0;
ExcelConditionColor fontColor() default ExcelConditionColor.RED;
ExcelConditionColor backGroundColor() default ExcelConditionColor.NONE;
}
1：operator 操作符号，小于 大于等，直接饮用内部定义的ComparisonOperator的常量属性
2：formula 比较值：0 等常量数字，中文比较建议使用公式写法，
3：formulaType 使用公式写法，必须定义当前属性且!=0，重要
4：fontColor 字体样式，是个枚举对象 获取Color
5：backGroundColor 背景色
ConditionalFormatCompose：组合条件注解
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ConditionalFormatCompose {
ConditionalFormat [] formats() default {};
}
1：组合条件注解，每个条件注解都是最小单元，需要多个条件注解，使用ConditionalFormatCompose组合，
例如：正反例，股市 红涨绿跌
@ConditionalFormatCompose(
formats = {
@ConditionalFormat(formula = "AND($=\"是\")", formulaType = 1, fontColor = ExcelConditionColor.RED),
@ConditionalFormat(formula = "AND($=\"否\")", formulaType = 1, fontColor = ExcelConditionColor.GREEN)
}
)
核心工具类：ExcelUtil
@Slf4j
public class ExcelUtil {
// 样式工具类
private static ExcelStyleUtil excelStyleUtil;
// 条件格式工具类
private static ExcelConditionUtil conditionUtil;
private ExcelUtil() {
}
// 唯一入口
public static <T extends ExcelEntity> Workbook exportExcel(T data) {
Workbook workbook = new XSSFWorkbook();
excelStyleUtil = new ExcelStyleUtil(workbook);
// 根据 对象标记的属性，进行属性收集
List<ExcelTableDescribe<ExcelValueEntity>> tableDescribeList = collectTableDescribe(data);
// 根据sheetName进行分组
tableDescribeList.stream().collect(Collectors.groupingBy(ExcelTableDescribe::getSheetName)).forEach((key, value) -> {
// key = sheetName
// 保存行索引
Set<Integer> maxRowIndex = new HashSet<>();
// 开始创建sheet页
Sheet sheet = workbook.createSheet(key);
// 初始化条件格式
conditionUtil = new ExcelConditionUtil(sheet);
// 无网格线模式
sheet.setDisplayGridlines(false);
// 根据块的行索引排序，在根据行索引分组
Function<ExcelTableDescribe<ExcelValueEntity>, Integer> blockRowIndex = ExcelTableDescribe::getBlockRowIndex;
value.stream().sorted(Comparator.comparing(blockRowIndex)).collect(Collectors.groupingBy(blockRowIndex)).forEach((blockKey, blockValue) -> {
// key = 块的行索引
// 在根据列索引排序，这样就可以确定 每个内容块的布局了
// 保存列索引
Set<Integer> maxColIndex = new HashSet<>();
blockValue.sort(Comparator.comparing(ExcelTableDescribe::getBockColIndex));
Set<Integer> blockRowIndexSet = new HashSet<>();
blockValue.forEach(block -> {
// 开始写入每一块内容
setSheetValue(sheet, block, maxRowIndex, maxColIndex);
// 获取当前内容块的最大行索引
Integer getMaxRowIndex = getSetMaxIndex(maxRowIndex);
blockRowIndexSet.add(getMaxRowIndex);
// 清空行索引
maxRowIndex.clear();
});
// 重新设置行索引
maxRowIndex.addAll(blockRowIndexSet);
});
});
log.info("开始销毁map对象");
// 写完sheet对象 销毁样式map对象
excelStyleUtil.destroyStyleMap();
conditionUtil.destroyConditionStyleMap();
log.info("销毁map对象结束");
return workbook;
}
private static void setSheetValue(Sheet sheet, ExcelTableDescribe<ExcelValueEntity> describe, Set<Integer> maxRowIndexSet, Set<Integer> maxColIndexSet) {
// 表格写入
if (Objects.equals(ExcelDisplayType.TABLE.getValue(), describe.getDisplayType())) {
setSheetTableValue(sheet, describe, maxRowIndexSet, maxColIndexSet);
}
// 支持各类写入，例如comment 文本注释，图片等类型格式
}
private static void setSheetTableValue(Sheet sheet, ExcelTableDescribe<ExcelValueEntity> describe, Set<Integer> maxRowIndexSet, Set<Integer> maxColIndexSet) {
// 表头属性收集
List<ExcelTableValueDescribe> tableValueDescribes = collectValueDescribes(describe.getData());
// 表头写入
Integer tableStartColIndex = setTableTitle(sheet, describe, tableValueDescribes, maxRowIndexSet, maxColIndexSet);
// 内容写入
setTableValue(sheet, describe, tableValueDescribes, maxRowIndexSet, tableStartColIndex);
}
private static Integer setTableTitle(Sheet sheet, ExcelTableDescribe<ExcelValueEntity> describe, List<ExcelTableValueDescribe> tableValueDescribes, @NonNull Set<Integer> maxRowIndexSet, @NonNull Set<Integer> maxColIndexSet) {
Integer maxRowIndex = getSetMaxIndex(maxRowIndexSet);
maxRowIndexSet.add(maxRowIndex);
// 根据边距 计算开始的行对象随意 -1 边距是隔多少行，新增的使用的是索引，所以需要减1
//处理上边距
int startRowIndex = describe.getMargins()[0] + maxRowIndex;
Row row = getRow(sheet, startRowIndex);
boolean isFirst = true;
int tableStartColIndex = 0;
for (ExcelTableValueDescribe titleProperty : tableValueDescribes) {
Integer maxColIndex = getSetMaxIndex(maxColIndexSet);
int startColIndex = maxColIndex;
if (isFirst) {
// 处理左边距
startColIndex = describe.getMargins()[3] + maxColIndex;
tableStartColIndex = startColIndex;
}
Cell cell = createTableTitleCell(titleProperty, sheet, row, startColIndex);
String titleName = titleProperty.getTitleName();
cell.setCellValue(titleName);
// 创建单元格批注
setTableTitleComment(sheet, cell, titleProperty);
maxColIndexSet.add(++startColIndex);
isFirst = false;
}
// 处理右边距
maxColIndexSet.add(getSetMaxIndex(maxColIndexSet) + describe.getMargins()[1]);
maxRowIndexSet.add(++startRowIndex);
return tableStartColIndex;
}
private static void setTableValue(Sheet sheet, ExcelTableDescribe<ExcelValueEntity> describe, List<ExcelTableValueDescribe> list, @NonNull Set<Integer> maxRowIndexSet, Integer tableStartColIndex) {
Optional.ofNullable(describe.getData()).ifPresent(describeData -> describeData.forEach(data -> {
int rowIndex = getSetMaxIndex(maxRowIndexSet);
Row row = getRow(sheet, rowIndex);
AtomicInteger startColIndex = new AtomicInteger(tableStartColIndex);
// 遍历表头属性 列
Optional.ofNullable(list).orElseGet(ArrayList::new).forEach(item -> {
// 获取单元格
Cell cell = getCell(sheet, row, startColIndex.get());
// 反射获取值
Object fieldValue = ReflectionUtils.getField(item.getField(), data);
if (fieldValue != null) {
// 写入tableValue 单元格，并设置样式
setTableCellValue(cell, fieldValue, item);
// 写入单元格格式
setTableConditionFormatting(cell, item);
} else {
cell.setBlank();
}
// 列索引递增
startColIndex.addAndGet(1);
});
// 行索引递增 加入Set集合
maxRowIndexSet.add(++rowIndex);
}));
// 处理下边距
maxRowIndexSet.add(getSetMaxIndex(maxRowIndexSet) + describe.getMargins()[2]);
}
private static <T extends ExcelEntity> void setTableDescribeValue(ExcelTableDescribe<ExcelValueEntity> describe, Field field, T data) {
if (field.isAnnotationPresent(ExcelTable.class)) {
ReflectionUtils.makeAccessible(field);
describe.setDisplayType(ExcelDisplayType.TABLE.getValue());
ExcelTable tableAnnotation = field.getAnnotation(ExcelTable.class);
describe.setSheetName(tableAnnotation.sheetName());
describe.setBlockRowIndex(tableAnnotation.blockRowIndex());
describe.setBockColIndex(tableAnnotation.bockColIndex());
describe.setMargins(tableAnnotation.margins());
try {
describe.setData((List<ExcelValueEntity>) field.get(data));
} catch (IllegalAccessException e) {
throw new BusinessException(e);
}
}
}
private static List<ExcelTableValueDescribe> collectValueDescribes(List<ExcelValueEntity> data) {
// 收集表头
return Optional.ofNullable(data).orElseGet(ArrayList::new).stream().findAny().map(titleProperty -> {
List<ExcelTableValueDescribe> tableValueDescribeList = new LinkedList<>();
for (Field field : titleProperty.getClass().getDeclaredFields()) {
ReflectionUtils.makeAccessible(field);
ExcelTableValueDescribe valueDescribe = new ExcelTableValueDescribe();
if (field.isAnnotationPresent(ExcelTableData.class)) {
ExcelTableData tableData = field.getAnnotation(ExcelTableData.class);
valueDescribe.setFieldName(field.getName());
valueDescribe.setTitleName(tableData.titleName());
valueDescribe.setTitleIndex(tableData.titleIndex());
valueDescribe.setHeight(tableData.height());
valueDescribe.setWidth(tableData.width());
valueDescribe.setFontSize(tableData.fontSize());
valueDescribe.setField(field);
valueDescribe.setStyle(tableData.style());
valueDescribe.setAnnotation(field.getAnnotations());
valueDescribe.setComment(tableData.comment());
valueDescribe.setConditionStyle(tableData.conditionStyle());
tableValueDescribeList.add(valueDescribe);
}
}
return tableValueDescribeList;
}).orElseGet(ArrayList::new);
}
private static <T extends ExcelEntity> List<ExcelTableDescribe<ExcelValueEntity>> collectTableDescribe(T data) {
return Optional.ofNullable(data).map(obj -> {
List<ExcelTableDescribe<ExcelValueEntity>> list = new LinkedList<>();
for (Field field : data.getClass().getDeclaredFields()) {
ExcelTableDescribe<ExcelValueEntity> describe = new ExcelTableDescribe<>();
setTableDescribeValue(describe, field, data);
list.add(describe);
}
return list;
}).orElseGet(ArrayList::new);
}
private static void setTableCellValue(Cell cell, Object cellValue, ExcelTableValueDescribe valueDescribe) {
// 获取单元格样式
XSSFCellStyle cellStyle = valueDescribe.getStyle().getFormatFunc().apply(excelStyleUtil);
cell.setCellStyle(cellStyle);
Optional.ofNullable(cellValue).ifPresent(value -> {
if (value instanceof String) {
cell.setCellValue(value.toString());
} else if (value instanceof BigDecimal) {
double doubleCellValue = ((BigDecimal) value).doubleValue();
if (valueDescribe.getField().isAnnotationPresent(Percent.class)) {
doubleCellValue = doubleCellValue / 100;
}
cell.setCellValue(doubleCellValue);
}
});
}
private static void setTableTitleComment(Sheet sheet, Cell cell, ExcelTableValueDescribe valueDescribe) {
if (!StringUtils.isEmpty(valueDescribe.getComment())) {
Drawing<?> draw = sheet.createDrawingPatriarch();
ClientAnchor clientAnchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6);
XSSFComment comment = (XSSFComment) draw.createCellComment(clientAnchor);
comment.setString(valueDescribe.getComment());
comment.setAuthor("system");
cell.setCellComment(comment);
}
}
/**
* 设置单元格条件格式
*/
private static void setTableConditionFormatting(Cell cell, ExcelTableValueDescribe describe) {
SheetConditionalFormatting formatting = cell.getSheet().getSheetConditionalFormatting();
// 单元格区域
CellRangeAddress[] regions = new CellRangeAddress[]{new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex(), cell.getColumnIndex(), cell.getColumnIndex())};
// 查询条件格式注解
ConditionalFormatCompose formatCompose = findAnnotation(describe.getField(), ConditionalFormatCompose.class);
ConditionalFormat format = findAnnotation(describe.getField(), ConditionalFormat.class);
// 组合注解优先级最大->单一条件注解->默认ExcelTableData注解的conditionStyle属性
if (Objects.nonNull(formatCompose)) {
Optional.ofNullable(formatCompose.formats()).ifPresent(item -> Arrays.stream(item)
.forEach(anno -> Optional.ofNullable(anno).ifPresent(call -> setConditionStyle(formatting, call, regions, cell))));
} else if (Objects.nonNull(format)) {
setConditionStyle(formatting, format, regions, cell);
} else {
Optional.ofNullable(describe.getConditionStyle()).flatMap(style -> Optional.ofNullable(style.getFormatFunc())).ifPresent(arrayStyleFunc ->
// 可能存在多个条件格式
Arrays.stream(arrayStyleFunc).forEach(func -> Optional.ofNullable(func).ifPresent(condition -> {
ConditionalFormattingRule rule = condition.apply(conditionUtil);
formatting.addConditionalFormatting(regions, rule);
})));
}
}
private static <T extends Annotation> T findAnnotation(Field field, Class<T> annotationClass) {
if (field.isAnnotationPresent(annotationClass)) {
return field.getAnnotation(annotationClass);
}
return null;
}
private static void setConditionStyle(SheetConditionalFormatting formatting
, ConditionalFormat format
, CellRangeAddress[] regions, Cell cell) {
ConditionalFormattingRule rule;
// 默认是常规条件格式
if (format.formulaType() == 0) {
rule = formatting.createConditionalFormattingRule(format.operator(), format.formula());
} else {
CellReference cellReference = new CellReference(cell.getRowIndex(), cell.getColumnIndex());
// 公式
String formula = StringUtils.replace(format.formula(), "$", cellReference.formatAsString());
log.info("输出条件格式公式：{}", formula);
rule = formatting.createConditionalFormattingRule(formula);
}
Optional.ofNullable(format.fontColor().getColor()).ifPresent(setColor -> {
XSSFColor color = new XSSFColor(setColor, null);
rule.createFontFormatting().setFontColor(color);
});
Optional.ofNullable(format.backGroundColor().getColor()).ifPresent(setColor -> {
XSSFColor backGroundColor = new XSSFColor(setColor, null);
rule.createPatternFormatting().setFillBackgroundColor(backGroundColor);
});
formatting.addConditionalFormatting(regions, rule);
}
private static Row getRow(Sheet sheet, int rowIndex) {
// 不允许使用orElse ，orElse 无论先前判断是否成功，都会执行 createRow方法
return Optional.ofNullable(sheet.getRow(rowIndex)).orElseGet(() -> sheet.createRow(rowIndex));
}
public static Cell getCell(Row row, int colIndex) {
return Optional.ofNullable(row.createCell(colIndex)).orElseGet(() -> row.createCell(colIndex));
}
public static Cell getCell(Sheet sheet, Row row, int colIndex) {
Cell cell = getCell(row, colIndex);
XSSFCellStyle cellStyle = excelStyleUtil.getBorderStyle(sheet.getWorkbook());
cell.setCellStyle(cellStyle);
return cell;
}
private static Integer getSetMaxIndex(Set<Integer> set) {
return Optional.ofNullable(set).orElseGet(HashSet::new).stream().max(Comparator.comparing(Integer::intValue)).orElse(0);
}
private static Cell createTableTitleCell(ExcelTableValueDescribe property, @NonNull Sheet sheet, Row row, int colIndex) {
Cell cell = getCell(row, colIndex);
Workbook workbook = sheet.getWorkbook();
// 设置列宽
setCellWidth(sheet, colIndex, property.getWidth());
// 设置行高
row.setHeight((short) (Constants.NUMBER_20 * 20));
// 获取表头单元格的样式
cell.setCellStyle(excelStyleUtil.getTableTitleStyle(workbook));
return cell;
}
private static void setCellWidth(Sheet sheet, int colIndex, int width) {
int columnWidth = sheet.getColumnWidth(colIndex);
int widthValue = width * 256;
if (columnWidth < widthValue) {
// 重新设置列宽
sheet.setColumnWidth(colIndex, widthValue);
}
}
}
样式工具类：ExcelStyleUtil
@Getter
public class ExcelStyleUtil {
public final XSSFCellStyle normal;
public final XSSFCellStyle normalLeftStyle;
public final XSSFCellStyle numberStyle;
public final XSSFCellStyle numberCenterStyle;
public final XSSFCellStyle percentStyle;
// 调用了当前方法 一定要记住销毁cellStyleMap对象，
// 当前样式是基于生成的Workbook的样式，
// 如果不销毁，下一次调用样式写入的时候 就会报错，非同一个Workbook 构造的样式map
// 使用map 缓存 避免重复创建样式
private final Map<String, XSSFCellStyle> styleMap = new HashedMap<>();
public ExcelStyleUtil(Workbook workbook) {
this.normal = getNormal(workbook);
this.normalLeftStyle = getNormalLeftStyle(workbook);
this.numberStyle = getNumberStyle(workbook);
this.numberCenterStyle = getNumberCenterStyle(workbook);
this.percentStyle = getPercentStyle(workbook);
}
public XSSFCellStyle getNormal(Workbook workbook) {
return Optional.of(styleMap).get().computeIfAbsent("normal", key -> {
XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
setBorder(cellStyle);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
return cellStyle;
});
}
public XSSFCellStyle getNormalLeftStyle(Workbook workbook) {
return Optional.of(styleMap).get().computeIfAbsent("normalLeftStyle", key -> {
XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
setBorder(cellStyle);
cellStyle.setAlignment(HorizontalAlignment.LEFT);
return cellStyle;
});
}
public XSSFCellStyle getNumberStyle(Workbook workbook) {
return getNumberStyle(workbook, HorizontalAlignment.RIGHT, "numberStyle");
}
public XSSFCellStyle getNumberCenterStyle(Workbook workbook) {
return getNumberStyle(workbook, HorizontalAlignment.CENTER, "numberCenterStyle");
}
private XSSFCellStyle getNumberStyle(Workbook workbook, HorizontalAlignment alignment, String keyName) {
return Optional.of(styleMap).get().computeIfAbsent(keyName, key -> {
XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
cellStyle.setDataFormat(workbook.createDataFormat().getFormat("#,##0.00"));
setBorder(cellStyle);
cellStyle.setAlignment(alignment);
return cellStyle;
});
}
private XSSFCellStyle getPercentStyle(Workbook workbook) {
return Optional.of(styleMap).get().computeIfAbsent("percentStyle", key -> {
XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
cellStyle.setDataFormat(workbook.createDataFormat().getFormat("0.00%"));
setBorder(cellStyle);
cellStyle.setAlignment(HorizontalAlignment.RIGHT);
return cellStyle;
});
}
public XSSFCellStyle getTableTitleStyle(Workbook workbook) {
return Optional.of(styleMap).get().computeIfAbsent("tableTitleCellStyle", key -> {
XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle();
XSSFFont font = (XSSFFont) workbook.createFont();
font.setBold(true);
font.setFontHeightInPoints((short) 12);
font.setFontName("宋体");
// 字体颜色
font.setColor(IndexedColors.WHITE.getIndex());
style.setFont(font);
// 边框属性
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
// 字体背景色
java.awt.Color color = new java.awt.Color(48, 84, 150);
style.setFillForegroundColor(new XSSFColor(color, null));
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return style;
});
}
private void setBorder(XSSFCellStyle cellStyle) {
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
}
public XSSFCellStyle getBorderStyle(Workbook workbook) {
return Optional.of(styleMap).get().computeIfAbsent("borderStyle", key -> {
XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
return cellStyle;
});
}
// 销毁styleMap
public void destroyStyleMap() {
styleMap.clear();
}
}
条件格式工具类：ExcelConditionUtil
@Getter
public class ExcelConditionUtil {
private final Map<String, ConditionalFormattingRule> conditionalFormattingMap = new ConcurrentHashMap<>(8);
private final Sheet sheet;
private final ConditionalFormattingRule fontGreen;
private final ConditionalFormattingRule fontRed;
public ExcelConditionUtil(Sheet sheet) {
this.sheet = sheet;
this.fontGreen = getFontGreen();
this.fontRed = getFontRed();
}
public ConditionalFormattingRule getFontGreen() {
return getConditionalFormattingRule("FontGreen", ComparisonOperator.LT, IndexedColors.GREEN);
}
public ConditionalFormattingRule getFontRed() {
return getConditionalFormattingRule("FontRed", ComparisonOperator.GT, IndexedColors.RED);
}
private ConditionalFormattingRule getConditionalFormattingRule(String fontRed, byte gt, IndexedColors indexedColors) {
return conditionalFormattingMap.computeIfAbsent(fontRed, key -> {
SheetConditionalFormatting formatting = sheet.getSheetConditionalFormatting();
ConditionalFormattingRule rule = formatting.createConditionalFormattingRule(gt, "0");
FontFormatting fontFormatting = rule.createFontFormatting();
fontFormatting.setFontColorIndex(indexedColors.getIndex());
return rule;
});
}
public void destroyConditionStyleMap() {
this.conditionalFormattingMap.clear();
}
}
枚举类
@Getter
public enum ExcelConditionColor {
RED("红色", Color.RED),
YELLOW("黄色", Color.YELLOW),
GREEN("绿色", Color.GREEN),
BLUE("蓝色", Color.BLUE),
BLACK("黑色", Color.BLACK),
WHITE("白色", Color.WHITE),
NONE("NONE", null);
private final String colorName;
private final Color color;
ExcelConditionColor(String colorName, Color color) {
this.colorName = colorName;
this.color = color;
}
}
@Getter
public enum ExcelConditionStyle {
FONT_GREEN("FONT_GREEN", ExcelConditionUtil::getFontGreen),
FONT_RED("FONT_RED", ExcelConditionUtil::getFontRed),
FONT_ALL("FONT_ALL", ExcelConditionUtil::getFontRed, ExcelConditionUtil::getFontGreen),
NONE("NONE", (Function<ExcelConditionUtil, ConditionalFormattingRule>) null);
final Function<ExcelConditionUtil, ConditionalFormattingRule>[] formatFunc;
final String conditionStyleName;
@SafeVarargs
ExcelConditionStyle(String conditionStyleName, Function<ExcelConditionUtil, ConditionalFormattingRule>... formatFunc) {
this.conditionStyleName = conditionStyleName;
this.formatFunc = formatFunc;
}
}
@Getter
public enum ExcelDisplayType {
TABLE("表格","table"),
TITLE("标题","title"),
COMMENT("注释","comment"),
PIC("图标","pic"),
NONE("置底","NONE")
;
private final String desc;
private final String value;
ExcelDisplayType(String desc, String value) {
this.desc = desc;
this.value = value;
}
}
@Getter
public enum ExcelStyle {
NORMAL_LEFT("NORMAL_LEFT", ExcelStyleUtil::getNormalLeftStyle),
NORMAL("NORMAL", ExcelStyleUtil::getNormal),
NUMBER("NUMBER", ExcelStyleUtil::getNumberStyle),
NUMBER_CENTER("NUMBER_CENTER", ExcelStyleUtil::getNumberCenterStyle),
PERCENT("PERCENT", ExcelStyleUtil::getPercentStyle),
;
final Function<ExcelStyleUtil, XSSFCellStyle> formatFunc;
final String styleName;
ExcelStyle(String styleName, Function<ExcelStyleUtil, XSSFCellStyle> formatFunc) {
this.styleName = styleName;
this.formatFunc = formatFunc;
}
}
实体类
public class ExcelEntity {
}
public class ExcelValueEntity {
}
@Data
public class ExcelTableDescribe <T extends ExcelValueEntity>{
/**
* 展示形式 table：表格 title:标题 ,comment:注释 pic:图标
*/
private String displayType;
private String sheetName;
/**
* 块行索引
*/
private int blockRowIndex;
/**
* 块列索引
*/
private int bockColIndex;
/**
* 当前区域块边距
*/
private int[] margins;
private List<T> data;
}
@Data
public class ExcelTableValueDescribe {
/**
* 字段名
*/
private String fieldName;
private String titleName;
private Field field;
/**
* 字段索引
*/
private int titleIndex;
/**
* 字体大小
*/
private int fontSize;
/**
* 列宽
*/
private int width;
/**
* 行高
*/
private int height;
/**
* 单元格样式
*/
private ExcelStyle style;
/**
* 单元格批注
*/
private String comment;
private Annotation[] annotation;
private ExcelConditionStyle conditionStyle;
}
测试对象
public void exportFile(HttpServletResponse response) {
try (Workbook workbook = ExcelUtil.exportExcel(getData())) {
String fileName = "fund_monitor.xlsx";
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
} catch (IOException e) {
throw new BusinessException(e);
}
}
private FundMonitor getData() {
FundMonitor monitor = new FundMonitor();
List<FundEntity> list = new ArrayList<>();
FundEntity fund = new FundEntity();
fund.setDate("20230930");
fund.setFundCode("630002");
fund.setFundName("华商盛世成长混合");
fund.setStockCode("601899");
fund.setStockName("紫金矿业");
fund.setNetValProportion(new BigDecimal("0.0517"));
fund.setPositionMarketVal(new BigDecimal("225763725"));
fund.setNewFundFlag("是");
list.add(fund);
fund = new FundEntity();
fund.setDate("20230930");
fund.setFundCode("630002");
fund.setFundName("华商盛世成长混合");
fund.setStockCode("601111");
fund.setStockName("中国国航");
fund.setNetValProportion(new BigDecimal("-0.1"));
fund.setPositionMarketVal(new BigDecimal("131481256"));
fund.setNewFundFlag("否");
list.add(fund);
FieldUtil.formatFieldValue(list);
monitor.setFundEntities(list);
monitor.setSecondFundEntities(list);
monitor.setThreeFundEntities(list);
return monitor;
}
@EqualsAndHashCode(callSuper = true)
@Data
public class FundEntity extends ExcelValueEntity {
@ExcelTableData(titleName = "日期", comment = "数据日期")
private String date;
@ExcelTableData(titleName = "基金代码", style = ExcelStyle.NORMAL_LEFT)
private String fundCode;
@ExcelTableData(titleName = "基金名称")
private String fundName;
@ExcelTableData(titleName = "股票代码")
@ConditionalFormat(formula = "AND($=\"601899\")", fontColor = ExcelConditionColor.RED, backGroundColor = ExcelConditionColor.WHITE, formulaType = 1)
private String stockCode;
@ExcelTableData(titleName = "股票名称", conditionStyle = ExcelConditionStyle.NONE)
private String stockName;
@ExcelTableData(titleName = "持仓市值（万）", style = ExcelStyle.NUMBER, comment = "持仓市值")
@ConditionalFormatCompose(formats = {
@ConditionalFormat(),
@ConditionalFormat(operator = ComparisonOperator.LT, fontColor = ExcelConditionColor.BLUE)
})
@TenThousand(scale = Constants.NUMBER_4)
private BigDecimal positionMarketVal;
@ExcelTableData(titleName = "市值占净值比（%）", width = Constants.NUMBER_20, style = ExcelStyle.PERCENT, conditionStyle = ExcelConditionStyle.FONT_ALL)
@Percent
private BigDecimal netValProportion;
@ExcelTableData(titleName = "是否新股")
@ConditionalFormatCompose(
formats = {
@ConditionalFormat(formula = "AND($=\"是\")", formulaType = 1, fontColor = ExcelConditionColor.RED),
@ConditionalFormat(formula = "AND($=\"否\")", formulaType = 1, fontColor = ExcelConditionColor.GREEN)
}
)
private String newFundFlag;
}
@EqualsAndHashCode(callSuper = true)
@Data
public class FundMonitor extends ExcelEntity {
@ExcelTable(sheetName = "基金监控")
List<FundEntity> fundEntities;
@ExcelTable(sheetName = "基金监控", bockColIndex = 1)
List<FundEntity> secondFundEntities;
@ExcelTable(sheetName = "基金监控", blockRowIndex = 1)
List<FundEntity> threeFundEntities;
}
1208
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
