easyExcel导出根据内容合并单元格

创建结果Bean,为对应的表头

@Data
@ContentRowHeight(16)
@HeadRowHeight(25)
public class ExcelExport {

    @ExcelProperty(value = {"统计表", "单位:元", "省级"})
    @ColumnWidth(9)
    private String provincial_name;

    @ExcelProperty(value = {"统计表", "单位:元", "区级"})
    @ColumnWidth(9)
    private String area_name;

    @ExcelProperty(value = {"统计表", "单位:元", "金额"})
    private String grand_total;
}

创建excel策略ExcelStyle

public class ExcelStyle extends AbstractCellStyleStrategy {

    private final List<ExcelExport> excelData;

    @Override
    protected void initCellStyle(Workbook workbook) {
    }

    public ExcelStyle(List<ExcelExport> excelData) {
        this.excelData = excelData;
    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
                                Head head, Integer relativeRowIndex, Boolean isHead) {
        // 内容行
        int contentRow = 3;
        // 当前行
        int curRowIndex = cell.getRowIndex();
        // 当前列
        int curColIndex = cell.getColumnIndex();
        if (!isHead && 0 == curColIndex)) {
            // 合并第一列
            int row = mergeWithPrevRow(curColIndex).get(curRowIndex - contentRow);
            if (row > 1) {
                Sheet sheet = writeSheetHolder.getSheet();
                CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - row + 1, curRowIndex, curColIndex, curColIndex);
                sheet.addMergedRegion(cellRangeAddress);
            }
        }
    }

    @Override
    protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
        // 头的策略
        Workbook workbook = cell.getSheet().getWorkbook();
        CellStyle headCellStyle = workbook.createCellStyle();
        headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        Font cellFont = workbook.createFont();
        cellFont.setBold(true);
        // cellFont.setColor((short) 2);
        cellFont.setFontHeightInPoints((short) 12);
        headCellStyle.setFont(cellFont);
        headCellStyle.setBorderBottom(BorderStyle.THIN);
        headCellStyle.setBorderLeft(BorderStyle.THIN);
        headCellStyle.setBorderRight(BorderStyle.THIN);
        headCellStyle.setBorderTop(BorderStyle.THIN);
        //设置 水平居中
        headCellStyle.setAlignment(HorizontalAlignment.CENTER);
        //设置 垂直居中
        headCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        if (relativeRowIndex == 1) {
            //设置 水平右对齐
            headCellStyle.setAlignment(HorizontalAlignment.RIGHT);
        }
        cell.setCellStyle(headCellStyle);
    }

    @Override
    protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
        // 内容的策略
        Sheet sheet = cell.getSheet();
        Workbook workbook = sheet.getWorkbook();
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        //设置 水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //设置 垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cell.setCellStyle(cellStyle);
    }

    // 根据相同内容返回需要合并行数
    private List<Integer> mergeWithPrevRow(int curColIndex) {
        List<Integer> spanOneArr = new ArrayList<>();
        for (int i = 0; i < excelData.size(); i++) {
            if (i == 0) {
                spanOneArr.add(1);
            } else if (excelData.get(i).getProvincial_name().equals(excelData.get(i - 1).getProvincial_name())) {
                spanOneArr.add(spanOneArr.get(i - 1) + 1);
                spanOneArr.set(i - 1, 0);
            } else {
                spanOneArr.add(1);
            }
        }
        return spanOneArr;
    }

}

导出Excel

// 往excel添加数据
List<ExcelExport> excelData = new ExcelExport();
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("统计报表", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), ExcelExport.class).registerWriteHandler(new ExcelStyle(excelData)).sheet("统计报表").doWrite(excelData);
版权声明:
标题:easyExcel导出根据内容合并单元格
作者:名晨
链接:https://www.8090mc.cn/800.html
文章版权归作者所有,未经允许请勿转载。
THE END
分享
二维码
打赏
< <上一篇
下一篇>>