EasyExcel模板导出(行和列自动合并)

目录

1.需求背景:

2.初步实现:

3.列合并

4.行合并

5.excel字体样式内容居中

6.将三个工具类初始化后注册后最终代码:


1.需求背景:

①需要从第三方获取数据,第三方接口有两个参数,开始时间和结束时间

②获取回来的数据并没有入库,所以不能通过数据库将数据归类统计,excel合并大概的流程是判断上一行或者左右相邻列是否相同,然后进行合并,所以不能是零散的数据且客户要求每一个自治区和每一个航站要统计总数(后续会出一个数据整合文章),咱们默认数据已经整理好了.效果如下:

③最终效果:

2.初步实现:

①利用easyExcel模板填充,实现效果如下图

代码:

        //模板位置
        InputStream template = new PathMatchingResourcePatternResolver()
        .getResource("templates/飞机扑救火场统计表.xlsx").getInputStream();

        response.setContentType("application/octet-stream");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        response.setHeader("Content-Disposition",
                "attachment;filename=" + java.net.URLEncoder.encode("飞机扑救火场统计表.xlsx", "UTF-8"));
        //ExcelWriter该对象用于通过POI将值写入Excel
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(template).build();
        //构建excel的sheet         
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        Map<String, String> fileData = new HashMap<>();
        fileData.put("beginDate", beginDate);
        fileData.put("endDate", endDate);
        excelWriter.fill(list, writeSheet);
        excelWriter.fill(fileData, writeSheet);
        excelWriter.finish();

模板:

3.列合并

 列合并工具类,合并代码在afterCellDispose这个方法中,不管是列合并还是行合并其实是重写这个方法,将你的合并逻辑写在里面就可以.

//列合并工具类
public class ExcelFillCellMergePrevColUtils implements CellWriteHandler {
    private static final String KEY ="%s-%s";
    //所有的合并信息都存在了这个map里面
    Map<String, Integer> mergeInfo = new HashMap<>();

    public ExcelFillCellMergePrevColUtils() {
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

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

    }

    @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();
        
        Integer num = mergeInfo.get(String.format(KEY, curRowIndex, curColIndex));
        if(null != num){
            // 合并最后一行 ,列
            mergeWithPrevCol(writeSheetHolder, cell, curRowIndex, curColIndex,num);
        }
    }
    public void mergeWithPrevCol(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex, int num) {
        Sheet sheet = writeSheetHolder.getSheet();
        CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex, curRowIndex, curColIndex, curColIndex + num);
        sheet.addMergedRegion(cellRangeAddress);
    }
    //num从第几列开始增加多少列,(6,2,7)代表的意思就是第6行的第2列至第2+7也就是9列开始合并
    public void add (int curRowIndex,  int curColIndex , int num){
        mergeInfo.put(String.format(KEY, curRowIndex, curColIndex),num);
    }

}

可以参考下面的这个excel看一下,广西壮族自治区的航站合计是从第8行,第2列开始+2列的范围合并

列合并效果图:

4.行合并

行合并工具类初级版本: 

报错位置:ExcelFillCellMergeStrategyUtils合并策略类的 mergeWithPrevRow()方法中

这一行代码会报空指针异常 java.lang.NullPointerException

Row preRow = cell.getSheet().getRow(curRowIndex - 1);

 

原因:

debug发现,cell.getSheet() 行的下标第0到42的数据行,获取的是同一个 sheet 实例

当下标为43时,执行cell.getSheet()获取到的 sheet 实例不一样

而下标0到42的行数据被存储到 存储sheet中。如果上一行为空则去缓存中获取上一行,

writeSheetHolder.getCachedSheet()
   Row preRow = cell.getSheet().getRow(curRowIndex - 1);
        if (preRow == null) {
            // 当获取不到上一行数据时,使用缓存sheet中数据
            preRow = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1);
        }
        Cell preCell=preRow.getCell(curColIndex);

行合并工具类最终版:

public class ExcelFillCellMergeStrategyUtils implements CellWriteHandler {

    /**
     * 合并字段的下标
     */
    private int[] mergeColumnIndex;
    /**
     * 合并几行
     */
    private int mergeRowIndex;

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

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
                                 Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

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




    }



    @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;
                }
            }
        }

    }

    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        //获取当前行的当前列的数据和上一行的当前列列数据,通过上一行数据是否相同进行合并
        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() :
                cell.getNumericCellValue();
        Row preRow = cell.getSheet().getRow(curRowIndex - 1);
        if (preRow == null) {
            // 当获取不到上一行数据时,使用缓存sheet中数据
            preRow = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1);
        }
        Cell preCell=preRow.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);
                }
            }





    }
}

5.excel字体样式内容居中

样式工具类: 

public class CellStyleStrategy extends AbstractCellStyleStrategy {

    private WriteCellStyle headWriteCellStyle;
    private List<WriteCellStyle> contentWriteCellStyleList;

    private CellStyle headCellStyle;
    private List<CellStyle> contentCellStyleList;

    public CellStyleStrategy(WriteCellStyle headWriteCellStyle,
                                       List<WriteCellStyle> contentWriteCellStyleList) {
        this.headWriteCellStyle = headWriteCellStyle;
        this.contentWriteCellStyleList = contentWriteCellStyleList;
    }

    public CellStyleStrategy(WriteCellStyle headWriteCellStyle, WriteCellStyle contentWriteCellStyle) {
        this.headWriteCellStyle = headWriteCellStyle;
        contentWriteCellStyleList = new ArrayList<WriteCellStyle>();
        contentWriteCellStyleList.add(contentWriteCellStyle);
    }

    @Override
    protected void initCellStyle(Workbook workbook) {
        if (headWriteCellStyle != null) {
            headCellStyle = StyleUtil.buildHeadCellStyle(workbook, headWriteCellStyle);
        }
        if (contentWriteCellStyleList != null && !contentWriteCellStyleList.isEmpty()) {
            contentCellStyleList = new ArrayList<CellStyle>();
            for (WriteCellStyle writeCellStyle : contentWriteCellStyleList) {
                contentCellStyleList.add(StyleUtil.buildContentCellStyle(workbook, writeCellStyle));
            }
        }
    }

    @Override
    protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
        if (headCellStyle == null) {
            return;
        }
        cell.setCellStyle(headCellStyle);
    }

    @Override
    protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
        if (contentCellStyleList == null || contentCellStyleList.isEmpty()) {
            return;
        }
        cell.setCellStyle(contentCellStyleList.get(0));
    }

}
 public CellStyleStrategy horizontalCellStyleStrategyBuilder() {
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置头字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 13);
        headWriteFont.setBold(true);
        headWriteCellStyle.setWriteFont(headWriteFont);
        //设置头居中
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //内容策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        return new CellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

6.将三个工具类初始化后注册后最终代码:

 public void exportExcel(HttpServletResponse response, String beginDate, String endDate) throws IOException {

        ExcelFillCellMergePrevColUtils excelFillCellMergePrevColUtils = new ExcelFillCellMergePrevColUtils();

        String terminalTotal = "航站合计";
        String provinceTotal = "省区合计";
        Map<String, ProvinceInfo> map = handlePlaneDownFire(beginDate, endDate);
        List<PlaneDownFire> list = new ArrayList<>();
        map.forEach((k, v) -> {
            //添加航站合计
            v.getTerminalInfos().forEach((k1, v1) -> {
                list.addAll(v1.getList());
                int size = list.size();
                excelFillCellMergePrevColUtils.add(size + 3, 2, 2);
                CommissionInfo terminalCommissionInfoTotal = v1.getSum();
                PlaneDownFire planeDownFire = CommissionInfoConvert.INSTANCE.commissionInfo2planeDownFire(terminalCommissionInfoTotal);
                planeDownFire.setProvincialArea(v.getName())
                        .setMachineNumber(String.valueOf(size))
                        .setFlyingCommission(String.valueOf(size))
                        .setTerminal(v1.getName())
                        .setModel(terminalTotal);
                list.add(planeDownFire);
            });
            int size = list.size();
            excelFillCellMergePrevColUtils.add(size + 3, 1, 3);
            //省区合计
            CommissionInfo provinceCommissionInfoTotal = v.getSum();
            PlaneDownFire planeDownFire = CommissionInfoConvert.INSTANCE.commissionInfo2planeDownFire(provinceCommissionInfoTotal);
            planeDownFire.setProvincialArea(v.getName())
                    .setTerminal(provinceTotal)
                    .setModel(String.valueOf(size));
            list.add(planeDownFire);

        });


        //设置第几列开始合并
        int[] mergeColumnIndex = {0, 1, 2, 3};
        //设置第几行开始合并
        int mergeRowIndex = 3;

        ExcelFillCellMergeStrategyUtils excelFillCellMergeStrategyUtils = new ExcelFillCellMergeStrategyUtils(mergeRowIndex, mergeColumnIndex);

        InputStream template = new PathMatchingResourcePatternResolver().getResource("templates/飞机扑救火场统计表.xlsx").getInputStream();


            response.setContentType("application/octet-stream");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + java.net.URLEncoder.encode("飞机扑救火场统计表.xlsx", "UTF-8"));
            //ExcelWriter该对象用于通过POI将值写入Excel
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(template)
                     //样式注册
                    .registerWriteHandler(horizontalCellStyleStrategyBuilder())
                     //行注册
                    .registerWriteHandler(excelFillCellMergeStrategyUtils)
                     //列注册
                    .registerWriteHandler(excelFillCellMergePrevColUtils)
                    .build();
            //构建excel的sheet
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            Map<String, String> fileData = new HashMap<>();
            fileData.put("beginDate", beginDate);
            fileData.put("endDate", endDate);
            excelWriter.fill(fileData, writeSheet);
            excelWriter.fill(list, writeSheet);
            excelWriter.finish();

    }

总结:EasyExcel动态导出几乎能够满足大部分需求,说到底还是实现CellWriteHandler 类里面的

afterCellDispose方法,里面实现你想要的效果.
可以通过实现EasyExcel的WriteHandler接口来实现相同数据合并单元格的功能。具体步骤如下: 1. 新建一个类实现WriteHandler接口,并重写对应的方法。 2. 在实现的方法中,通过Excel的API获取到要合并的单元格的起始行、起始列、结束行、结束列。可以使用Map来记录每一种相同数据的位置信息,然后再遍历Map合并单元格。 3. 使用EasyExcel的write方法时,通过指定Handler参数,将编写好的WriteHandler实例传递进去即可。 下面是示例代码: ```java public class MergeCellWriteHandler implements WriteHandler { private Map<String, List<CellRangeAddress>> mergeMap = new HashMap<>(); @Override public void row(int i, List<Object> list) { //处理行数据,将相同的数据合并单元格 String key = list.get(0).toString(); //以第一列为key if (mergeMap.containsKey(key)) { List<CellRangeAddress> cellRangeList = mergeMap.get(key); CellRangeAddress lastCellRange = cellRangeList.get(cellRangeList.size() - 1); if (i - lastCellRange.getLastRow() == 1) { //如果上一个单元格的结尾行是当前行的上一行,则可以合并单元格 lastCellRange.setLastRow(i); } else { cellRangeList.add(new CellRangeAddress(i, i, 0, list.size() - 1)); //如果不连续,则新建一个单元格范围 } } else { List<CellRangeAddress> cellRangeList = new ArrayList<>(); cellRangeList.add(new CellRangeAddress(i, i, 0, list.size() - 1)); mergeMap.put(key, cellRangeList); } } @Override public void sheet(int i) { } @Override public void start() { } @Override public void end() { //处理完整个sheet后,将记录的单元格范围进行合并 Sheet sheet = EasyExcel.writerSheet().build().getSheet(); for (Map.Entry<String, List<CellRangeAddress>> entry : mergeMap.entrySet()) { String key = entry.getKey(); List<CellRangeAddress> cellRangeList = entry.getValue(); for (CellRangeAddress cellRange : cellRangeList) { sheet.addMergedRegion(cellRange); } } } } ``` 使用方法: ```java EasyExcel.write("test.xlsx") .sheet() .registerWriteHandler(new MergeCellWriteHandler()) .head(head) .doWrite(data); ``` 其中head和data分别是表头和数据,可以从数据库或其他数据源中获取。要实现合并单元格,需要将第一列相同的数据合并。示例中以第一列为key,记录每一种相同数据所对应的单元格范围,最后进行合并
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值