easyexcle处理复杂动态单元格合并问题，合并动态行列

最新推荐文章于 2024-07-25 19:28:17 发布

小破孩笔记

最新推荐文章于 2024-07-25 19:28:17 发布

阅读量2.1k

点赞数 16

文章标签： windows microsoft linux

本文链接：https://blog.csdn.net/piyingcheng/article/details/135130336

版权

@GetMapping("getAddDelSummaryExport")
@ApiOperation("新增删除比例报表--导出")
@ApiImplicitParams({
        @ApiImplicitParam(name = "season", value = "季节", paramType = "query", dataType = "String"),
        @ApiImplicitParam(name = "brand", value = "品牌", paramType = "query", dataType = "String"),
        @ApiImplicitParam(name = "series", value = "系列", paramType = "query", dataType = "String"),
        @ApiImplicitParam(name = "stage", value = "阶段", paramType = "query", dataType = "String")
})
public void getAddDelSummaryExport(@ApiIgnore @RequestParam Map<String, Object> params, HttpServletResponse response) throws Exception {
    if(!ParamUtil.paramsIsNotNull(params,"limit") || !ParamUtil.paramsIsNotNull(params,"page")){
        params.put("limit", "1000");
        params.put("page", "1");
    }
    List<AddAndDelSummaryDTO> list = styleSummaryService.getAddAndDelReportPage(params).getList();
    //标记位
    AtomicInteger mergeLineIndex = new AtomicInteger(0);
    //合并的列
    List<Integer> merge = new ArrayList<>();
    //合并的行
    List<Integer> lines = new ArrayList<>();
    merge.add(mergeLineIndex.intValue());
    merge.add(mergeLineIndex.intValue() + 1);
    merge.add(mergeLineIndex.intValue() + 2);
    lines.add(mergeLineIndex.intValue());
    lines.add(mergeLineIndex.intValue() + 1);
    List<List<String>> dataList = new ArrayList<>();
    AtomicInteger seasonCount = new AtomicInteger();
    if(CollectionUtils.isNotEmpty(list)){
        Map<String, List<AddAndDelSummaryDTO>> collectMap = list.stream().collect(Collectors.groupingBy(AddAndDelSummaryDTO::getSeason, Collectors.toList()));
        Map<String, List<AddAndDelSummaryDTO>> treeMap = new TreeMap<>(collectMap);
        MapUtil.sort(treeMap);
        treeMap.forEach((season, fooListBySeason) -> {
            seasonCount.addAndGet(1);
            List<String> itemList = new ArrayList<>();
            itemList.add(season);
            itemList.add("头版");
            itemList.add("二选");
            itemList.add("二选");
            itemList.add("二选");
            itemList.add("二选");
            itemList.add("二选");
            itemList.add("二选");
            itemList.add("二选");
            itemList.add("销样");
            itemList.add("销样");
            itemList.add("销样");
            itemList.add("销样");
            itemList.add("销样");
            itemList.add("销样");
            itemList.add("销样");
            itemList.add("大货");
            itemList.add("大货");
            dataList.add(itemList);
            itemList = new ArrayList<>();
            itemList.add(season);
            itemList.add("合计款数");
            itemList.add("新增");
            itemList.add("新增");
            itemList.add("删除");
            itemList.add("删除");
            itemList.add("续存");
            itemList.add("续存");
            itemList.add("合计款数");
            itemList.add("新增");
            itemList.add("新增");
            itemList.add("删除");
            itemList.add("删除");
            itemList.add("续存");
            itemList.add("续存");
            itemList.add("合计款数");
            itemList.add("大货合计款数");
            itemList.add("大货数量");
            dataList.add(itemList);
            itemList = new ArrayList<>();
            itemList.add(season);
            itemList.add("合计款数");
            itemList.add("数量");
            itemList.add("新增比例");
            itemList.add("数量");
            itemList.add("删除比例");
            itemList.add("数量");
            itemList.add("续存比例");
            itemList.add("合计款数");
            itemList.add("数量");
            itemList.add("新增比例");
            itemList.add("数量");
            itemList.add("删除比例");
            itemList.add("数量");
            itemList.add("续存比例");
            itemList.add("合计款数");
            itemList.add("大货合计款数");
            itemList.add("大货数量");
            dataList.add(itemList);
            AtomicInteger seriesIndex = new AtomicInteger();
            fooListBySeason.stream().collect(Collectors.groupingBy(AddAndDelSummaryDTO::getSeries, Collectors.toList())).forEach((series, fooListBySeries) -> {
                seriesIndex.addAndGet(1);
                AddAndDelSummaryDTO dto = fooListBySeries.stream().filter(oo -> "头版".equals(oo.getStage())).findFirst().isPresent() ? fooListBySeries.stream().filter(oo -> "头版".equals(oo.getStage())).findFirst().get() : null;
                AddAndDelSummaryDTO dto2 = fooListBySeries.stream().filter(oo -> "二选".equals(oo.getStage())).findFirst().isPresent() ? fooListBySeries.stream().filter(oo -> "二选".equals(oo.getStage())).findFirst().get() : null;
                AddAndDelSummaryDTO dto3 = fooListBySeries.stream().filter(oo -> "销样".equals(oo.getStage())).findFirst().isPresent() ? fooListBySeries.stream().filter(oo -> "销样".equals(oo.getStage())).findFirst().get() : null;
                AddAndDelSummaryDTO dto4 = fooListBySeries.stream().filter(oo -> "大货".equals(oo.getStage())).findFirst().isPresent() ? fooListBySeries.stream().filter(oo -> "大货".equals(oo.getStage())).findFirst().get() : null;
                List<String> itemList1 = new ArrayList<>();
                itemList1.add(series);
                if(dto != null){
                    itemList1.add(dto.getStyleCount() + "");
                }else {
                    itemList1.add("");
                }
                if(dto2 != null){
                    itemList1.add(dto2.getAddCount() + "");
                    itemList1.add(dto2.getAddProportion() + "%");
                    itemList1.add(dto2.getDelCount() + "");
                    itemList1.add(dto2.getDelProportion() + "%");
                    itemList1.add(dto2.getXcCount() + "");
                    itemList1.add(dto2.getXcProportion() + "%");
                    itemList1.add(dto2.getStyleCount() + "");
                }else {
                    itemList1.add("");
                    itemList1.add("");
                    itemList1.add("");
                    itemList1.add("");
                    itemList1.add("");
                    itemList1.add("");
                    itemList1.add("");
                }
                if(dto3 != null){
                    itemList1.add(dto3.getAddCount() + "");
                    itemList1.add(dto3.getAddProportion() + "%");
                    itemList1.add(dto3.getDelCount() + "");
                    itemList1.add(dto3.getDelProportion() + "%");
                    itemList1.add(dto3.getXcCount() + "");
                    itemList1.add(dto3.getXcProportion() + "%");
                    itemList1.add(dto3.getStyleCount() + "");
                }else {
                    itemList1.add("");
                    itemList1.add("");
                    itemList1.add("");
                    itemList1.add("");
                    itemList1.add("");
                    itemList1.add("");
                    itemList1.add("");
                }
                if(dto4 != null){
                    itemList1.add(dto4.getStyleCount() + "");
                    itemList1.add("");
                }else {
                    itemList1.add("");
                    itemList1.add("");
                }
                dataList.add(itemList1);
            });
            //合计
            List itemList3 = new ArrayList<>();
            itemList3.add("合计");
            int aSum = fooListBySeason.stream().filter(oo -> "头版".equals(oo.getStage())).mapToInt(AddAndDelSummaryDTO::getStyleCount).sum();
            int bSum = fooListBySeason.stream().filter(oo -> "二选".equals(oo.getStage())).mapToInt(AddAndDelSummaryDTO::getStyleCount).sum();
            int bSum1 = fooListBySeason.stream().filter(oo -> "二选".equals(oo.getStage())).mapToInt(AddAndDelSummaryDTO::getAddCount).sum();
            int bSum2 = fooListBySeason.stream().filter(oo -> "二选".equals(oo.getStage())).mapToInt(AddAndDelSummaryDTO::getDelCount).sum();
            int bSum3 = fooListBySeason.stream().filter(oo -> "二选".equals(oo.getStage())).mapToInt(AddAndDelSummaryDTO::getXcCount).sum();
            int cSum = fooListBySeason.stream().filter(oo -> "销样".equals(oo.getStage())).mapToInt(AddAndDelSummaryDTO::getStyleCount).sum();
            int cSum1 = fooListBySeason.stream().filter(oo -> "销样".equals(oo.getStage())).mapToInt(AddAndDelSummaryDTO::getAddCount).sum();
            int cSum2 = fooListBySeason.stream().filter(oo -> "销样".equals(oo.getStage())).mapToInt(AddAndDelSummaryDTO::getDelCount).sum();
            int cSum3 = fooListBySeason.stream().filter(oo -> "销样".equals(oo.getStage())).mapToInt(AddAndDelSummaryDTO::getXcCount).sum();
            int dSum = fooListBySeason.stream().filter(oo -> "大货".equals(oo.getStage())).mapToInt(AddAndDelSummaryDTO::getStyleCount).sum();
            BigDecimal decimal = BigDecimal.valueOf(0.00);
            itemList3.add(aSum + "");
            itemList3.add(bSum1 + "");
            if(aSum > 0){
                decimal = BigDecimal.valueOf((float)  100 * bSum1 / aSum);
            }
            itemList3.add(decimal.setScale(2, BigDecimal.ROUND_HALF_UP) + "%");
            itemList3.add(bSum2 + "");
            if(aSum > 0){
                decimal = BigDecimal.valueOf((float)  100 * bSum2 / aSum);
            }
            itemList3.add(decimal.setScale(2, BigDecimal.ROUND_HALF_UP) + "%");
            itemList3.add(bSum3 + "");
            if(aSum > 0){
                decimal = BigDecimal.valueOf((float)  100 * bSum3 / aSum);
            }
            itemList3.add(decimal.setScale(2, BigDecimal.ROUND_HALF_UP) + "%");
            itemList3.add(bSum + "");
            itemList3.add(cSum1 + "");
            if(bSum > 0){
                decimal = BigDecimal.valueOf((float)  100 * cSum1 / bSum);
            }
            itemList3.add(decimal.setScale(2, BigDecimal.ROUND_HALF_UP) + "%");
            itemList3.add(cSum2 + "");
            if(bSum > 0){
                decimal = BigDecimal.valueOf((float)  100 * cSum2 / bSum);
            }
            itemList3.add(decimal.setScale(2, BigDecimal.ROUND_HALF_UP) + "%");
            itemList3.add(cSum3 + "");
            if(bSum > 0){
                decimal = BigDecimal.valueOf((float)  100 * cSum3 / bSum);
            }
            itemList3.add(decimal.setScale(2, BigDecimal.ROUND_HALF_UP) + "%");
            itemList3.add(cSum + "");
            itemList3.add(dSum + "");
            itemList3.add("");
            dataList.add(itemList3);
            //处理单元格合并逻辑
            if(1 < collectMap.size() && collectMap.size() > seasonCount.intValue()){
                List itemList2 = new ArrayList<>();
                for (int i = 0; i < 18; i++) {
                    itemList2.add("");
                }
                dataList.add(itemList2);
                mergeLineIndex.addAndGet(5 + seriesIndex.intValue());
                merge.add(mergeLineIndex.intValue());
                merge.add(mergeLineIndex.intValue() + 1);
                merge.add(mergeLineIndex.intValue() + 2);
                lines.add(mergeLineIndex.intValue() - 1);
                lines.add(mergeLineIndex.intValue());
                lines.add(mergeLineIndex.intValue() + 1);
            }
        });
    }
    response.setContentType("application/json;charset=utf-8");
    response.setCharacterEncoding("UTF-8");
    String fileName = URLEncoder.encode("开发新增删除比例统计表-"+  DateUtils.format(new Date()), "UTF-8");
    response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
    ExcelWriter excelWriter = null;
    try{
        excelWriter = EasyExcel.write(response.getOutputStream()).build();
        //excelWriter = EasyExcel.write("D:\\2023\\test456.xlsx").build();
        if(null != excelWriter) {
            //需要合并的列
            int[] mergeColumeIndex = {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17};
            // 从那一列开始合并
            int mergeRowIndex = 0;
            // 内容的策略
            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);

            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.index);
            HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                    new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
            WriteSheet writeSheet = EasyExcel.writerSheet( "开发新增删除比例统计")
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .registerWriteHandler(new MergeSameRowsStrategy(mergeRowIndex, mergeColumeIndex, merge))
                    .registerWriteHandler(new MergeSameLinesStrategy(lines))
                    .build();
            excelWriter.write(dataList, writeSheet);
        }
    }finally {
        //关闭流
        if(excelWriter != null){
            excelWriter.finish();
        }
    }
}

package com.jack.modules.dsr.common.utils;

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 lombok.Data;
import org.apache.commons.collections.CollectionUtils;
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;

@Data
public class MergeSameRowsStrategy implements CellWriteHandler{
    private int[] mergeColumnIndex;
    private int mergeRowIndex;
    private List<Integer> merge;

    public MergeSameRowsStrategy() {
    }
    public MergeSameRowsStrategy(int mergeRowIndex, int[] mergeColumnIndex, List<Integer> merge) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
        this.merge = merge;
    }


    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        if (CollectionUtils.isNotEmpty(merge)){
            // 当前行
            int curRowIndex = cell.getRowIndex();
            if (merge.contains(curRowIndex)){
                // 当前列
                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 sheet保持对象
     * @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();
        if (cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex) == null){
            return;
        }
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();

        // 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
        if (curData.equals(preData) || ("0.0".equals(preData.toString()) && "".equals(curData))) {
            Sheet sheet = writeSheetHolder.getSheet();
            List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            for (int i = 0; i < mergedRegions.size() && !isMerged; i++) {
                CellRangeAddress cellRangeAddr = mergedRegions.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);
            }
        }
    }

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

    }

    @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) {

    }

}

package com.jack.modules.dsr.common.utils;

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 com.baomidou.mybatisplus.core.toolkit.ObjectUtils;
import lombok.Data;
import org.apache.commons.collections.CollectionUtils;
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;

@Data
public class MergeSameLinesStrategy implements CellWriteHandler{


    /**
     * 要合并的行
     */
    private List<Integer> mergeRowsIndex;

    /**
     * 开始合并的行
     */
    private int mergeRowIndex;

    /**
     * 无参构造
     */
    public MergeSameLinesStrategy() {
    }


    /**
     * 有参构造
     *
     * @param mergeRowIndex    开始合并的行
     */
    public MergeSameLinesStrategy(int mergeRowIndex) {
        this.mergeRowIndex = mergeRowIndex;
    }

    /**
     * 有参构造
     *
     * @param mergeRowIndex    开始合并的行
     * @param mergeRowsIndex   合并的行
     */
//    public MergeSameLinesStrategy(int mergeRowIndex, List<Integer> mergeRowsIndex) {
//        this.mergeRowIndex = mergeRowIndex;
//        this.mergeRowsIndex = mergeRowsIndex;
//    }
    public MergeSameLinesStrategy(List<Integer> mergeRowsIndex) {
        this.mergeRowsIndex = mergeRowsIndex;
    }





    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        //合并行（从第一列开始）
        //当前行
        final int curRowIndex = cell.getRowIndex();
        //当前列
        final int curColIndex = cell.getColumnIndex();
        if (curColIndex > 0) {
            if (!ObjectUtils.isEmpty(mergeRowsIndex)) {
                for (int i = 0; i < mergeRowsIndex.size(); i++) {
                    if (curRowIndex == mergeRowsIndex.get(i)) {
                        mergeWithPrevCol(writeSheetHolder, cell, curRowIndex, curColIndex);
                        break;
                    }
                }
            }
        }


    }


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

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



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

    }

    @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) {

    }

}

小破孩笔记

关注

16
点赞
踩
25

收藏

觉得还不错? 一键收藏
0
评论
easyexcle处理复杂动态单元格合并问题，合并动态行列

int cSum = fooListBySeason.stream().filter(oo -> "销样".equals(oo.getStage())).mapToInt(AddAndDelSummaryDTO::getStyleCount).sum();int cSum3 = fooListBySeason.stream().filter(oo -> "销样".equals(oo.getStage())).mapToInt(AddAndDelSummaryDTO::getXcCount).sum();
复制链接

扫一扫