胖虎的工具箱-编程导航 胖虎的工具箱-编程导航

EasyExcel合并相同内容单元格及动态标题功能的实现

2年前 (2022) 程序员胖胖胖虎阿
309 0 0

        相信很多同学都用过EasyExcel做过导出功能，现在阿Q也碰到一个需求，就是要把导出的Excel中内容相同的纵向单元格给合并，然后还需要给Excel设置动态标题，再进行格式定义调整，现将这个功能的实现过程记录下，给同学们做个参考避免踩到同样的坑。

        jar包版本：EasyExcel合并相同内容单元格及动态标题功能的实现

一、最初版本

导出的结果：

EasyExcel合并相同内容单元格及动态标题功能的实现

 对应实体类代码：

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentLoopMerge;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.*;

import java.io.Serializable;

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Data
@ContentRowHeight(30)
@HeadRowHeight(40)
@ColumnWidth(25)
public class StudentExportVo implements Serializable {

    private static final long serialVersionUID = -5809782578272943999L;

    @ExcelProperty(value = "学校", order = 1)
    @ContentLoopMerge(eachRow = 3)
    private String school;

    @ExcelProperty(value = "姓名", order = 2)
    private String name;

    @ExcelProperty(value = "性别", order = 3)
    private String sex;

    @ExcelProperty(value = "年龄", order = 4)
    private String age;

    @ExcelProperty(value = "城市", order = 5)
    private String city;

    @ExcelProperty(value = "备注", order = 6)
    private String remarks;

    }

对应业务代码：

@ApiOperation(value = "导出学生信息", notes = "导出学生信息")
@PostMapping("/exportStudent")
public void exportStudent(@RequestBody String str, HttpServletResponse response) {
    List<StudentExportVo> list = this.getStudentExportVos();
    ExcelUtils.writeExcel(response, StudentExportVo.class, list, "导出学生信息", "sheet1");
}
//数据制造
private List<StudentExportVo> getStudentExportVos() {
    List<StudentExportVo> list = new ArrayList<>();
    StudentExportVo v1 = new StudentExportVo();
    v1.setSchool("北京大学");
    v1.setName("张三");
    v1.setSex("男");
    v1.setAge("20");
    v1.setCity("北京");
    v1.setRemarks("无");
    list.add(v1);
    StudentExportVo v2 = new StudentExportVo();
    v2.setSchool("北京大学");
    v2.setName("李四");
    v2.setSex("男");
    v2.setAge("22");
    v2.setCity("上海");
    v2.setRemarks("无");
    list.add(v2);
    StudentExportVo v3 = new StudentExportVo();
    v3.setSchool("北京大学");
    v3.setName("王五");
    v3.setSex("女");
    v3.setAge("22");
    v3.setCity("青岛");
    v3.setRemarks("无");
    list.add(v3);
    StudentExportVo v4 = new StudentExportVo();
    v4.setSchool("清华大学");
    v4.setName("赵六");
    v4.setSex("女");
    v4.setAge("21");
    v4.setCity("重庆");
    v4.setRemarks("无");
    list.add(v4);
    StudentExportVo v5 = new StudentExportVo();
    v5.setSchool("武汉大学");
    v5.setName("王强");
    v5.setSex("男");
    v5.setAge("24");
    v5.setCity("长沙");
    v5.setRemarks("无");
    list.add(v5);
    StudentExportVo v6 = new StudentExportVo();
    v6.setSchool("武汉大学");
    v6.setName("赵燕");
    v6.setSex("女");
    v6.setAge("21");
    v6.setCity("深圳");
    v6.setRemarks("无");
    list.add(v6);
    StudentExportVo v7 = new StudentExportVo();
    v7.setSchool("厦门大学");
    v7.setName("陆仟");
    v7.setSex("女");
    v7.setAge("21");
    v7.setCity("广州");
    v7.setRemarks("无");
    list.add(v7);
    return list;
}

二、使用注解的版本

导出的结果：

EasyExcel合并相同内容单元格及动态标题功能的实现

  对应实体类代码：

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Data
@ContentRowHeight(30)
@HeadRowHeight(40)
@ColumnWidth(25)
public class StudentExportVo implements Serializable {

    private static final long serialVersionUID = -5809782578272943999L;

    @ExcelProperty(value = {"学生信息","学校"}, order = 1)
    @ContentLoopMerge(eachRow = 3)
    private String school;

    @ExcelProperty(value = {"学生信息","姓名"}, order = 2)
    private String name;

    @ExcelProperty(value = {"学生信息","性别"}, order = 3)
    private String sex;

    @ExcelProperty(value = {"学生信息","年龄"}, order = 4)
    private String age;

    @ExcelProperty(value = {"学生信息","城市"}, order = 5)
    private String city;

    @ExcelProperty(value = {"学生信息","备注"}, order = 6)
    private String remarks;

    }

对应业务代码：同上

注意：

@ContentLoopMerge(eachRow = 3) 可以合并单元格，但是他是按指定行数合并，并不能实现内容相同的合并

@ExcelProperty(value = {"学生信息","备注"}，能实现多个标题，但标题是固定的，不是动态的

三、自定义改造

导出的结果：

EasyExcel合并相同内容单元格及动态标题功能的实现

对应实体类代码：

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Data
@ContentRowHeight(30)
@HeadRowHeight(40)
@ColumnWidth(25)
public class StudentExportVo implements Serializable {

    private static final long serialVersionUID = -5809782578272943999L;

    @ExcelProperty(value = {"学生信息","学校"}, order = 1)
    //@ContentLoopMerge(eachRow = 3)
    private String school;

    @ExcelProperty(value = {"学生信息","姓名"}, order = 2)
    private String name;

    @ExcelProperty(value = {"学生信息","性别"}, order = 3)
    private String sex;

    @ExcelProperty(value = {"学生信息","年龄"}, order = 4)
    private String age;

    @ExcelProperty(value = {"学生信息","城市"}, order = 5)
    private String city;

    @ExcelProperty(value = {"学生信息","备注"}, order = 6)
    private String remarks;

}

对应业务代码：

@ApiOperation(value = "导出学生信息", notes = "导出学生信息")
    @PostMapping("/exportStudent")
    public void exportStudent(@RequestBody String dynamicTitle, HttpServletResponse response) {
        List<StudentExportVo> list = this.getStudentExportVos();
        try {
            String fileName = "学生信息";
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/json;charset=utf-8");
            response.setCharacterEncoding("utf-8");
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
            ServletOutputStream output = response.getOutputStream();
            //需要合并的列
            int[] mergeColumeIndex = {0};
            // 从第二行后开始合并
            int mergeRowIndex = 2;
            //设置动态标题
            List<List<String>> headers = this.getHeaders("学生信息" + dynamicTitle);
            // 头的策略
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            // 背景设置为白色
            headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            /*WriteFont headWriteFont = new WriteFont();
            headWriteFont.setFontHeightInPoints((short)20);
            headWriteCellStyle.setWriteFont(headWriteFont);*/

            // 内容的策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
            //contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
            // 背景绿色
            //contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            //设置 自动换行
            contentWriteCellStyle.setWrapped(true);
            //设置 垂直居中
            contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置 水平居中
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            /*WriteFont contentWriteFont = new WriteFont();
            // 字体大小
            contentWriteFont.setFontHeightInPoints((short)20);
            contentWriteCellStyle.setWriteFont(contentWriteFont);*/
            // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
            HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                    new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

            EasyExcel.write(output, StudentExportVo.class)
                    .sheet("学生信息")
                    .head(headers)
                    .registerWriteHandler(new ExcelMergeHandler(mergeRowIndex, mergeColumeIndex))
                    .registerWriteHandler(horizontalCellStyleStrategy)
//                    .registerWriteHandler(new SimpleColumnWidthStyleStrategy(30))
                    .registerWriteHandler(new AbstractColumnWidthStyleStrategy() {
                        @Override
                        protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
                            Sheet sheet = writeSheetHolder.getSheet();
                            int columnIndex = cell.getColumnIndex();
                            if(columnIndex == 5){
                                // 列宽100
                                sheet.setColumnWidth(columnIndex, 10000);
                            }else {
                                // 列宽50
                                sheet.setColumnWidth(columnIndex, 5000);
                            }
                            // 行高40
                            sheet.setDefaultRowHeight((short) 4000);
                        }
                    })
                    .doWrite(list);
            output.flush();
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        }
    }


    private List<List<String>> getHeaders(String dynamicTitle) {
        List<List<String>> headers=new ArrayList<>();
        List<String> schoolHead=new ArrayList<>();
        schoolHead.add(dynamicTitle);
        schoolHead.add("学校");
        List<String> nameHead=new ArrayList<>();
        nameHead.add(dynamicTitle);
        nameHead.add("姓名");
        List<String> sexHead=new ArrayList<>();
        sexHead.add(dynamicTitle);
        sexHead.add("性别");
        List<String> ageHead=new ArrayList<>();
        ageHead.add(dynamicTitle);
        ageHead.add("年龄");
        List<String> cityHead=new ArrayList<>();
        cityHead.add(dynamicTitle);
        cityHead.add("城市");
        List<String> remarksHead=new ArrayList<>();
        remarksHead.add(dynamicTitle);
        remarksHead.add("备注");
        headers.add(schoolHead);
        headers.add(nameHead);
        headers.add(sexHead);
        headers.add(ageHead);
        headers.add(cityHead);
        headers.add(remarksHead);
        return headers;
    }

    //数据制造
    private List<StudentExportVo> getStudentExportVos() {
        List<StudentExportVo> list = new ArrayList<>();
        StudentExportVo v1 = new StudentExportVo();
        v1.setSchool("北京大学");
        v1.setName("张三");
        v1.setSex("男");
        v1.setAge("20");
        v1.setCity("北京");
        v1.setRemarks("无");
        list.add(v1);
        StudentExportVo v2 = new StudentExportVo();
        v2.setSchool("北京大学");
        v2.setName("李四");
        v2.setSex("男");
        v2.setAge("22");
        v2.setCity("上海");
        v2.setRemarks("无");
        list.add(v2);
        StudentExportVo v3 = new StudentExportVo();
        v3.setSchool("北京大学");
        v3.setName("王五");
        v3.setSex("女");
        v3.setAge("22");
        v3.setCity("青岛");
        v3.setRemarks("无");
        list.add(v3);
        StudentExportVo v4 = new StudentExportVo();
        v4.setSchool("清华大学");
        v4.setName("赵六");
        v4.setSex("女");
        v4.setAge("21");
        v4.setCity("重庆");
        v4.setRemarks("无");
        list.add(v4);
        StudentExportVo v5 = new StudentExportVo();
        v5.setSchool("武汉大学");
        v5.setName("王强");
        v5.setSex("男");
        v5.setAge("24");
        v5.setCity("长沙");
        v5.setRemarks("无");
        list.add(v5);
        StudentExportVo v6 = new StudentExportVo();
        v6.setSchool("武汉大学");
        v6.setName("赵燕");
        v6.setSex("女");
        v6.setAge("21");
        v6.setCity("深圳");
        v6.setRemarks("无");
        list.add(v6);
        StudentExportVo v7 = new StudentExportVo();
        v7.setSchool("厦门大学");
        v7.setName("陆仟");
        v7.setSex("女");
        v7.setAge("21");
        v7.setCity("广州");
        v7.setRemarks("无");
        list.add(v7);
        return list;
    }

合并单元格相同内容处理类：ExcelMergeHandler

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;
public class ExcelMergeHandler implements CellWriteHandler {
    private int[] mergeColumnIndex;
    private int mergeRowIndex;

    public ExcelMergeHandler() {
    }

    public ExcelMergeHandler(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 relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

        //当前行
        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();
        // 将当前单元格数据与上一个单元格数据比较
        Boolean dataBool = preData.equals(curData);
        //此处需要注意：因为我是按照工程名称确定是否需要合并的，所以获取每一行第二列数据和上一行第一列数据进行比较，如果相等合并，getCell里面的值，是工程名称所在列的下标
        String s1 = cell.getRow().getCell(0).getStringCellValue();
        String s2 = cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue();
        /*BigDecimal d1 = new BigDecimal(cell.getRow().getCell(0).getNumericCellValue());
        BigDecimal d2 = new BigDecimal(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getNumericCellValue());*/
        Boolean bool = s1.compareTo(s2) == 0 ? true:false;
        // 原始的
        // Boolean bool =  cell.getRow().getCell(1).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(1).getStringCellValue());
        if (dataBool && bool) {
            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);
            }
        }
    }
}

总结：

1、使用自定义合并相同内容单元格时，实体类中的注解@ContentLoopMerge需要去掉，要不然会受到影响

2、该段自定义代码中如内容合并、设置动态标题、内容、以及每个列的宽度及字体都是可以根据自定义策略来进行设置的，具体用法可以参考代码注释说明，根据需要进行使用

3、可以参考官网文档获取更多知识内容 https://alibaba-easyexcel.github.io/index.html

最后：
        相信前面两种导出方法同学们都使用的非常熟练，重点是第三种自定义的导出方法，像这种自定义的使用场景还是比较多的，希望到时候开发中遇到类似的场景，把代码拿过去直接用就好，少走些弯路。

版权声明：程序员胖胖胖虎阿 发表于 2022年9月14日 下午1:56。
转载请注明：EasyExcel合并相同内容单元格及动态标题功能的实现 | 胖虎的工具箱-编程导航

相关文章

IDEA专业版 破解教程(IntelliJ IDEA最新永久激活码,绝对有效)
程序员胖胖胖虎阿
419
「面试必背」Redis面试题（2022最新版）
程序员胖胖胖虎阿
307
IDEA2020注册码(Intellij IDEA专业版破解教程,最新有效IDEA激活码)
程序员胖胖胖虎阿
120
springBoot(9)---定时任务,异步任务
程序员胖胖胖虎阿
359
windows/mac idea全新破解图文教程(IDEA专业版 破解教程,每日更新)
程序员胖胖胖虎阿
151
IDEA安装及破解方法(IDEA2020破解教程,永久有效)
程序员胖胖胖虎阿
110

暂无评论

暂无评论...
致力于称为最好的程序员导航网站

友链申请 免责声明 关于我们

Copyright © 2022  胖虎的工具箱-编程导航   陇ICP备2022001249号