easyexcel导出 复杂表格

 easyexcel版本

<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.3.2</version>
        </dependency>

样式大全：

EasyExcel 样式注解大全_easyexcel样式-CSDN博客

之前写过读取复杂表格的处理，现在需求是导出复杂表格。

导出大概的格式如下。

处理层代码

@Override
    public void exportTable(String condition, HttpServletResponse response) {

//获取表格数据
        List<xxx> list = service.list(condition);

        StringBuffer sb = new StringBuffer();
        try {
            //中文处理（防止中文乱码）
            String encode = URLEncoder.encode("评价表", "UTF-8").replaceAll("\\+", "%20");
            sb.append(encode);
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } finally {
            sb.append("_");
            sb.append(DateUtils.format(new Date(), DateUtils.DATE_FORMAT_14));
            sb.append(".xlsx");
        }

        //setContentType：设置响应内容类型。这表示是一个Excel文件
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

        //setCharacterEncoding:设置字符集。
        response.setCharacterEncoding("utf-8");
        //设置表名
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + sb);
//用于记录那两列要合并
        int [] mergeColumnIndex =  {1,2};
//用于记录从哪一行开始写数据
        int mergeRowIndex = 1;
        try {
//新增一个写excel文件的核心类，并且写处理策略使用自定义的策略
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
                    .registerWriteHandler(new ExcelFillCellRowMergeStrategy(mergeRowIndex, mergeColumnIndex))
                    .build();

            WriteSheet writeSheet = EasyExcel.writerSheet("sheet1").build();  
//新增一个数据表，xxx为你所需导出的表对应的对象，可新增多个不同的表写在同一页中，  
            WriteTable writeTable = EasyExcel.writerTable(0).head(xxx.class).needHead(Boolean.TRUE).build();

            excelWriter.write(list,writeSheet,writeTable);

            excelWriter.finish();

        } catch (IOException e) {
            e.printStackTrace();
        }

    }

监听器代码如下

public class ExcelFillCellRowMergeStrategy implements CellWriteHandler {

        private int[] mergeColumnIndex;

        private int mergeRowIndex;


        public ExcelFillCellRowMergeStrategy() {

        }

        public ExcelFillCellRowMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {

            this.mergeRowIndex = mergeRowIndex;

            this.mergeColumnIndex = mergeColumnIndex;

        }

        @Override

        public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> 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;
                    }
                }
            }
            // 新增列合并逻辑 
            //因为我只想合并前两列的数据，所以此处的curColIndex<=2
            if (curColIndex <=2 &&curColIndex>0) {

                mergeWithPrevCol(writeSheetHolder, cell, curRowIndex, curColIndex);

            }

        }



    /**

     * 当前单元格向上合并

     *

     * @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 perColCell = cell.getSheet().getRow(curRowIndex).getCell(curColIndex-1);

        Object perColCellData = perColCell.getCellTypeEnum() == CellType.STRING ? perColCell.getStringCellValue() : perColCell.getNumericCellValue();



        //获取上一行前一列的数据

        Cell perRowColCell = cell.getSheet().getRow(curRowIndex-1).getCell(curColIndex-1);

        Object perRowColCellDate =  perRowColCell.getCellTypeEnum() == CellType.STRING ? perRowColCell.getStringCellValue() : perRowColCell.getNumericCellValue();

        //获取同一列上一行的数据

        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);

        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();

        // 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行

        if(curColIndex==1){

            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);

                }

            }

        }else {



            if (perColCellData.equals(perRowColCellDate)&&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);

                }



            }

        }



    }



        /**

         * 当前单元格向左合并

         *

         * @param writeSheetHolder

         * @param cell             当前单元格

         * @param curRowIndex      当前行

         * @param curColIndex      当前列

         */

        private void mergeWithPrevCol(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {

            //获取当前行的当前列的数据和当前行的上一列列数据，通过上一列数据是否相同进行合并

            Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();

            Cell preCell = cell.getSheet().getRow(curRowIndex).getCell(curColIndex - 1);

            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, curColIndex - 1)) {

                        sheet.removeMergedRegion(i);

                        cellRangeAddr.setLastColumn(curColIndex);

                        sheet.addMergedRegion(cellRangeAddr);

                        isMerged = true;

                    }

                }

                if (!isMerged) {

                    CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex, curRowIndex, curColIndex - 1, curColIndex);

                    sheet.addMergedRegion(cellRangeAddress);

                }

            }

        }

    }

此文章还存在缺陷，需要更加复杂的导出但是由于时间关系未能深入研究，待补充。