poi 导出合并

在这里插入代码片public void export(Map<String, Object> params, HttpServletResponse response) {
        List<StockUpOrderEntity> list = shangguanPurchaseDao.selectDataStockUpOrder(params);

        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFFont createFont = workbook.createFont();
        createFont.setFontHeightInPoints((short) 12);// 设置字体

        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.LEFT); // 内容居左
        cellStyle.setFont(createFont);

        XSSFCellStyle cellStyle1 = workbook.createCellStyle();
        cellStyle1.setAlignment(HorizontalAlignment.CENTER);// 水平居中
        cellStyle1.setFont(createFont);
        cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);	//上下居中
        XSSFSheet sheet = workbook.createSheet("PMC任务分配列表");
        XSSFRow row0 = sheet.createRow(0);

        String[] title = {"生管下单时间","生管要求交货日期","下单主体","物料号","物料名称","数量","总数量","金重","银重","金总重合计(克)","银总重合计(克)","备注"};

        for (int i = 0; i < title.length; i++) {
            XSSFCell title0 = row0.createCell(i);
            title0.setCellValue(title[i]);
            sheet.setColumnWidth(i, 5000);
            title0.setCellStyle(cellStyle1);
        }

        List<String> codeList = new ArrayList<>();
        List<String> remarkList = new ArrayList<>();
        List<String> orderNumList = new ArrayList<>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        for (int i = 0; i < list.size(); i++) {
            StockUpOrderEntity  entity = list.get(i);
            XSSFRow row = sheet.createRow(i+1);
            //生管下单时间
            XSSFCell td0 = row.createCell(0);
            td0.setCellValue(sdf.format(entity.getCreateAt()));
            td0.setCellStyle(cellStyle1);

            //生管要求交货日期
            XSSFCell td1 = row.createCell(1);
            td1.setCellValue(entity.getHopeDeliveryDate());
            td1.setCellStyle(cellStyle1);
            //下单主体
            XSSFCell td2 = row.createCell(2);
            td2.setCellValue(entity.getOrderBody());
            td2.setCellStyle(cellStyle1);
            //物料号
            XSSFCell td3= row.createCell(3);
            td3.setCellValue(entity.getMaterialCode());
            td3.setCellStyle(cellStyle1);
            //物料名称
            XSSFCell td4= row.createCell(4);
            td4.setCellValue(entity.getMaterialName());
            td4.setCellStyle(cellStyle1);
            //数量
            XSSFCell td5= row.createCell(5);
            td5.setCellValue(entity.getNum());
            td5.setCellStyle(cellStyle1);
            //总数量
            XSSFCell td6= row.createCell(6);
            td6.setCellValue(entity.getTotalQuantity());
            td6.setCellStyle(cellStyle1);
            //金重
            XSSFCell td7= row.createCell(7);
            td7.setCellValue(entity.getGoldWeight());
            td7.setCellStyle(cellStyle1);
            //银重
            XSSFCell td8= row.createCell(8);
            td8.setCellValue(entity.getSilverWeight());
            td8.setCellStyle(cellStyle1);
            //金总重合计(克)
            XSSFCell td9= row.createCell(9);
            td9.setCellValue(entity.getGoldWeightTotal());
            td9.setCellStyle(cellStyle1);
            //银总重合计(克)
            XSSFCell td10= row.createCell(10);
            td10.setCellValue(entity.getSilverWeightTotal());
            td10.setCellStyle(cellStyle1);
            //备注
            XSSFCell td11= row.createCell(11);
            td11.setCellValue(entity.getRemark());
            td11.setCellStyle(cellStyle1);

//            codeList.add(entity.getShengguanOrderNum()+entity.getMaterialCode());
            remarkList.add(entity.getShengguanOrderNum() + entity.getRemark());
            orderNumList.add(entity.getShengguanOrderNum());

        }
        //合并单元格
//        List<Map<Object, String>> codeMapsList = getclList(codeList);
        List<Map<Object, String>> remarkMapsList = getclList(remarkList);
        List<Map<Object, String>> orderNumMapList = getclList(orderNumList);

//        for (Map<Object, String> map : codeMapsList){
//            for(Object key : map.keySet()){
//                String value = map.get(key);
//                String[] split = value.split(",");
//                int cfnum = Integer.parseInt(split[0]);
//                int index = Integer.parseInt(split[1]);
//                if(1<cfnum){
//                    //起始行，终止行，起始列，终止列 3  9
//
//                    CellRangeAddress cellRange1 = new CellRangeAddress(index-cfnum+1+1, index+1, (short) 1, (short) 1);
//                    sheet.addMergedRegion(cellRange1);
//                    CellRangeAddress cellRange2 = new CellRangeAddress(index-cfnum+1+1, index+1, (short) 2, (short) 2);
//                    sheet.addMergedRegion(cellRange2);
//
                    CellRangeAddress cellRange5 = new CellRangeAddress(index-cfnum+1+1, index+1, (short) 4, (short) 4);
                    sheet.addMergedRegion(cellRange5);
//
//
//                }
//            }
//        }

        for (Map<Object, String> map : remarkMapsList){
            for(Object key : map.keySet()){
                String value = map.get(key);
                String[] split = value.split(",");
                int cfnum = Integer.parseInt(split[0]);
                int index = Integer.parseInt(split[1]);
                if(1<cfnum){
                    //起始行，终止行，起始列，终止列 3  9
                    CellRangeAddress cellRange11 = new CellRangeAddress(index-cfnum+1+1, index+1, (short) 11, (short) 11);
                    sheet.addMergedRegion(cellRange11);
                }
            }
        }

        for (Map<Object, String> map : orderNumMapList){
            for(Object key : map.keySet()){
                String value = map.get(key);
                String[] split = value.split(",");
                int cfnum = Integer.parseInt(split[0]);
                int index = Integer.parseInt(split[1]);
                if(1<cfnum){
                    //起始行，终止行，起始列，终止列 3  9
                    CellRangeAddress cellRange0 = new CellRangeAddress(index-cfnum+1+1, index+1, (short) 0, (short) 0);
                    sheet.addMergedRegion(cellRange0);
                    CellRangeAddress cellRange1 = new CellRangeAddress(index-cfnum+1+1, index+1, (short) 1, (short) 1);
                    sheet.addMergedRegion(cellRange1);
                    CellRangeAddress cellRange2 = new CellRangeAddress(index-cfnum+1+1, index+1, (short) 2, (short) 2);
                    sheet.addMergedRegion(cellRange2);
                    CellRangeAddress cellRange6 = new CellRangeAddress(index-cfnum+1+1, index+1, (short) 6, (short) 6);
                    sheet.addMergedRegion(cellRange6);
                    CellRangeAddress cellRange9 = new CellRangeAddress(index-cfnum+1+1, index+1, (short) 9, (short) 9);
                    sheet.addMergedRegion(cellRange9);
                    CellRangeAddress cellRange10 = new CellRangeAddress(index-cfnum+1+1, index+1, (short) 10, (short) 10);
                    sheet.addMergedRegion(cellRange10);
                }
            }
        }
        ServletOutputStream fileOut = null;
        try {
            fileOut = response.getOutputStream();
            String fileName = new String("PMC任务分配列表".getBytes("UTF-8"), "ISO8859-1");
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
            fileOut = response.getOutputStream();
            workbook.write(fileOut);
        } catch (Exception e1) {
            e1.printStackTrace();
        } finally {
            if (fileOut != null) {
                try {
                    fileOut.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
        }

    }


    public  List<Map<Object,String>> getclList(List<String> arrayList){
        List<Map<Object,String>> clList = new ArrayList<>();
        Set<String> ss=new HashSet<>();
        for (String i:arrayList) {
            ss.add(i);
        }
        Map<Object,String>  maps=new HashMap<>();
        for (String i:ss) {
            int num=0;//记录每个数值出现次数
            for (int r=0;r<arrayList.size();r++) {
                if(i.equals(arrayList.get(r))){
                    num+=1;
                    int numI= r;  //获取下标 拼接
                    maps.put(i,num+","+numI);
                }
            }
        }
        for (Object sss: maps.keySet() ) {
            Map<Object, String> keymap = new HashMap<>();
            keymap.put(sss,maps.get(sss));
            clList.add(keymap);
            // System.out.println(sss+"==="+maps.get(sss));
        }
        return clList;
    }