給定模板頭部單元格合并設(shè)置

public class ExcelUtil{
    /**
     * 表導(dǎo)出
     * @param userId
     * @param templeCode
     * @param reqParamter
     * @return
     * @throws Exception
     */
    public Attach exportBargainPk(String userId, String templeCode, String reqParamter) throws Exception {
        Attach attach = new Attach();
        attach.setAttachementType("xlsx");
        Map<String, Object> templet = platformConsumer.selectExpTemplet(userId, templeCode);
        templet = CommonUtil.sourceTofomater(templet);
        String realTemplePath = templetPath + templet.get("templePath");
        int startIndex = Integer.parseInt(templet.get("templeIndex").toString());
        String serviceId = templet.get("serviceId").toString();
        String methodId = templet.get("methodId").toString();
        Map<String, Object> data = exportConsumer.getExportExcelData(serviceId, methodId, reqParamter, userId);
        String[] key = templet.get("templeKey").toString().split(CommonConstant.SPLIT);
        String templeName = templet.get("templeName").toString();
        attach.setAttachementName(templeName);
        byte[] attachementData = exportBargainPk(realTemplePath, startIndex, key, data);
        attach.setAttachementData(attachementData);
        return attach;
    }
    /**
     * 表excel讀取及格式數(shù)據(jù)組裝
     * @param realTemplePath
     * @param startIndex
     * @param key
     * @param data
     * @return
     * @throws Exception
     */
    private byte[]  exportBargainPk(String realTemplePath, int startIndex, String[] key, Map<String, Object> data) throws Exception{
        byte[] bytes = null;
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        //數(shù)據(jù)獲取分析
        @SuppressWarnings("unchecked")
        List<Map<String, Object>> list = (List<Map<String, Object>>) data.get("list");
        @SuppressWarnings("unchecked")
        Map<String, Object> header = (Map<String, Object>) data.get("header");
        @SuppressWarnings("unchecked")
        List<Map<String, Object>> bj = (List<Map<String, Object>>) header.get("bj");
        String reqCode = data.get("reqCode")+"";
        //供應(yīng)商個(gè)數(shù)+一列
        int vendorSize = bj.size()+1;
        String[] abc = {"A","B","C","D","E","F","G","H","I","J","......"};
        String[] sheetHeader = {"序號(hào)","詢價(jià)單單號(hào)","物料編碼","物料名稱","直送地","基本單位","供應(yīng)商報(bào)價(jià)","目標(biāo)價(jià)","議價(jià)結(jié)果","價(jià)格單位","最終供應(yīng)商/最終報(bào)價(jià)","選中理由"};
        String[] vendorCode = new String[vendorSize];
        String[] vendorName = new String[vendorSize];
        for(int i=0; i<vendorSize-1; i++){
            Map<String, Object> map = bj.get(i);
            vendorCode[i] = map.get("field").toString();
            vendorName[i] = map.get("displayName").toString();
        }
        File excel = PropertiesUtil.getFile(realTemplePath);
        FileInputStream is = new FileInputStream(excel);
        Workbook workBook = WorkbookFactory.create(is);
        Sheet sheet = workBook.getSheetAt(0);
        //設(shè)置頭信息格式
        CellStyle style = getCellStyle(workBook);
        sheet.autoSizeColumn(1, true);
        //設(shè)置列寬
        sheet.setColumnWidth(1, 6000);
        sheet.setColumnWidth(2, 6000);
        sheet.setColumnWidth(3, 10000);
        for(int i=0; i<vendorSize-1; i++){
            sheet.setColumnWidth(6+i, 10000);
            sheet.setColumnWidth(7+vendorSize+i, 10000);
            sheet.setColumnWidth(8+2*vendorSize+i, 10000);
        }
        sheet.setDefaultColumnWidth(3000);
        //總列數(shù)
        int cellCount = 9 + 3*vendorSize;
        //前四列設(shè)置
        for(int i=0; i<4; i++){
            Row row = sheet.createRow(i);
            if(i==0){
                Cell cell = row.createCell(0);
                cell.setCellValue("價(jià)格PK表明細(xì)");
                cell.setCellStyle(style);
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, cellCount-1));
            }else if(i==1){
                for(int j=0; j<cellCount; j++){
                    if(j<6){
                        createCell(workBook,row,j).setCellValue(sheetHeader[j]);
                    }else if(j == 6+vendorSize){
                        createCell(workBook,row,j).setCellValue(sheetHeader[7]);
                    }else if(j == 7+2*vendorSize){
                        createCell(workBook,row,j).setCellValue(sheetHeader[9]);
                    }else if(j == cellCount-1){
                        createCell(workBook,row,j).setCellValue(sheetHeader[11]);
                    }
                }
                sheet.addMergedRegion(new CellRangeAddress(1,1, 6, 5+vendorSize));
                createCell(workBook,row,6).setCellValue(sheetHeader[6]);
                sheet.addMergedRegion(new CellRangeAddress(1,1,7+vendorSize,6+2*vendorSize));
                createCell(workBook,row,7+vendorSize).setCellValue(sheetHeader[8]);
                sheet.addMergedRegion(new CellRangeAddress(1,1,8+2*vendorSize,7+3*vendorSize));
                createCell(workBook,row,8+2*vendorSize).setCellValue(sheetHeader[10]);
            }else if(i==2){
                for(int j=0; j<cellCount; j++){
                    if(j<6){
                        createCell(workBook,row,j);
                    }else if(j==5+vendorSize||j==6+2*vendorSize||j==7+3*vendorSize){
                        createCell(workBook,row,j).setCellValue(abc[10]);
                    }else if(5<j&&j<5+vendorSize){
                        createCell(workBook,row,j).setCellValue(abc[j-6]);
                    }else if(6+vendorSize<j&&j<6+2*vendorSize){
                        createCell(workBook,row,j).setCellValue(abc[j-7-vendorSize]);
                    }else if(7+2*vendorSize<j&&j<7+3*vendorSize){
                        createCell(workBook,row,j).setCellValue(abc[j-8-2*vendorSize]);
                    }
                }
            }else if(i==3){
                for(int j=0; j<cellCount; j++){
                    if(j<6){
                        createCell(workBook,row,j);
                    }else if(j==5+vendorSize||j==6+2*vendorSize||j==7+3*vendorSize){
                        createCell(workBook,row,j);
                    }else if(5<j&&j<5+vendorSize){
                        createCell(workBook,row,j).setCellValue(vendorName[j-6]);
                    }else if(6+vendorSize<j&&j<6+2*vendorSize){
                        createCell(workBook,row,j).setCellValue(vendorName[j-7-vendorSize]);
                    }else if(7+2*vendorSize<j&&j<7+3*vendorSize){
                        createCell(workBook,row,j).setCellValue(vendorName[j-8-2*vendorSize]);
                    }
            }
        }
        }
        //數(shù)據(jù)
         for(int i=0; i<list.size(); i++){
             Map<String, Object> map = list.get(i);
             Row row = sheet.createRow(i+4);
             createCell(workBook,row,0).setCellValue(i+1);
             createCell(workBook,row,1).setCellValue(reqCode);
             createCell(workBook,row,2).setCellValue(map.get(key[0])+"");
             createCell(workBook,row,3).setCellValue(map.get(key[1])+"");
             createCell(workBook,row,4).setCellValue(map.get(key[2])+"");
             createCell(workBook,row,5).setCellValue(map.get(key[3])+"");
             createCell(workBook,row,6+vendorSize).setCellValue(StringUtil.isEmpty(map.get(key[4])+"")?"":map.get(key[4])+"");
             createCell(workBook,row,7+2*vendorSize).setCellValue(map.get(key[5])+"");
            for (int j = 6; j < cellCount; j++) {
                if (5 < j && j < 5 + vendorSize) {
                    createCell(workBook,row,j).setCellValue(map.get(vendorCode[j - 6])+"");
                } else if (6 + vendorSize < j && j < 6 + 2 * vendorSize) {
                    createCell(workBook,row,j).setCellValue(map.get(vendorCode[j-7-vendorSize].replace("bj", "yj"))+"");
                } else if (7 + 2 * vendorSize < j && j < 7 + 3 * vendorSize) {
                    createCell(workBook,row,j).setCellValue(map.get(vendorCode[j-8-2*vendorSize].replace("bj", "zj"))+"");
                }
            }
        }
        os = new ByteArrayOutputStream();
        try {
            workBook.write(os);
            bytes = os.toByteArray();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                os.flush();
                os.close();
            } catch (Exception e) {
                logger.error(e.getMessage(),e);
                e.printStackTrace();
            }
        }
        return bytes;
    }
    /**
     * 創(chuàng)建單元格
     * @param wb
     * @param row
     * @param i
     * @return
     */
    private static Cell createCell(Workbook wb, Row row, int i){
        CellStyle style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        if(row.getRowNum()==1){
            style.setFillForegroundColor(IndexedColors.GREEN.getIndex());
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        }
        style.setBorderBottom((short) 1);
        style.setBorderLeft((short) 1);
        style.setBorderRight((short) 1);
        style.setBorderTop((short) 1);
        Cell cell = row.createCell(i);
        cell.setCellStyle(style);
        return cell;
    }
    /**
     * 單元格格式設(shè)置
     * @param wb
     * @return
     */
    private static CellStyle getCellStyle(Workbook wb) {
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        Font font = wb.createFont();  
        font.setFontName("黑體");  
        font.setFontHeightInPoints((short) 24);//設(shè)置字體大小
        font.setColor((short) 16);
        cellStyle.setFont(font);
        return cellStyle;
    }


}

導(dǎo)出格式：