阿里巴巴EasyExcel自定义表格样式

阿里巴巴EasyExcel自定义表格样式

基于注解方式设置标题栏-------@ExcelProperty

/**
     * 行政区名称
     */
@ExcelProperty({"行政区名称","行政区名称"})
@ColumnWidth(9)
private String areaName;
/**
     * 疫木处置总重量（公斤）
     */
@ExcelProperty({"1","疫木处置总体情况","疫木处置总重量（公斤）"})
@ColumnWidth(12)
private Double totalWeight;
/**
     * 疫木处置树干重量（公斤）
     */
@ExcelProperty({"2","疫木处置总体情况","疫木处置树干重量（公斤）"})
@ColumnWidth(14)
private Double trunkWeight;
/**
     * 疫木处置枝桠重量（公斤）
     */
@ExcelProperty({"3","疫木处置总体情况","疫木处置枝桠重量（公斤）"})
@ColumnWidth(14)
private Double branchWeight;

导出样式

在@ExcelProperty({"","",…,""})中可以放置多个字段名称,有几个双引号代表表格头就有几行，名称相同会自动合并单元格。

设置单元格样式

可以是基于注解方式设置表头格式

@ColumnWidth(12)
@HeadStyle(fillBackgroundColor = 9)
@HeadFontStyle(bold = false,fontHeightInPoints = 10)

也可以是通过代码方式自定义表格头部和内容样式

//内容样式策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
//垂直居中,水平居中
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
//设置 自动换行
contentWriteCellStyle.setWrapped(true);
// 字体策略
WriteFont contentWriteFont = new WriteFont();
// 字体大小，加粗，字体类型
contentWriteFont.setFontName("宋体");
contentWriteFont.setFontHeightInPoints((short) 10);
contentWriteCellStyle.setWriteFont(contentWriteFont);
//头部样式策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setWriteFont(contentWriteFont);

// 输出为文件
EasyExcel.write("C:\\Users\\Desktop\\Excel自定义样式.xlsx", WeightCountySummaryDTO.class)
                .sheet("自定义")
             .registerWriteHandler(HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle))
                .relativeHeadRowIndex(1) // 设置表格头从第几行开始
                .doWrite(dataList);

代码自定义表格的样式优先级高于基于注解定义。也就是说你已经基于注解方式定义了表格样式，但后来你又用代码设置了头部样式，那么导出的表格样式是通过代码设置的样式。

扩展-自动合并连续出现的相同单元格

编写一个实现CellWriteHandler的类

public class ExcelCellMergeStrategy implements CellWriteHandler {
    // 设置合并相同名称的单元列
    private int[] mergeColumnIndex;
    // 从哪行开始合并
    private int mergeRowIndex;

    public ExcelFillCellMergeStrategy() {
    }

    public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();

        if (curRowIndex > mergeRowIndex) {
            for (int i = 0; i < mergeColumnIndex.length; i++) {
                if (curColIndex == mergeColumnIndex[i]) {
                    mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }

    /**
     * 当前单元格向上合并
     *
     * @param writeSheetHolder
     * @param cell             当前单元格
     * @param curRowIndex      当前行
     * @param curColIndex      当前列
     */
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
       //获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并
        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();

        // 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
        if (curData.equals(preData)) {
            Sheet sheet = writeSheetHolder.getSheet();
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                    sheet.removeMergedRegion(i);
                    cellRangeAddr.setLastRow(curRowIndex);
                    sheet.addMergedRegion(cellRangeAddr);
                    isMerged = true;
                }
            }
            // 若上一个单元格未被合并，则新增合并单元
            if (!isMerged) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                sheet.addMergedRegion(cellRangeAddress);
            }
        }
    }
}

使用时仅需将该策略注册即可

EasyExcel.write(response.getOutputStream(), clazz)
                    .sheet(sheetName)
                    .registerWriteHandler(new ExcelCellMergeStrategy(mergeRowIndex,mergeColumnIndex))
                    .doWrite(dataList);

总结

通过以上步骤设置导出表格样式即可导出类似于这样的excel数据。当然也有其他方式，我这只是其中一种，并且存在一些问题，欢迎指正！！！