java excel 动态表头 导出 150行代码搞定 不需要创建其他类、实体

效果图

  @Override
    public void export(Map<String, Object> params, HttpServletResponse response){
     
        //数据
        List<SupplierEvaluationEntity> dataList = supplierEvaluationDao.selectByTypeAndSupplier(params);
        //表头
        List<String> dateList = supplierEvaluationDao.selectDateList(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("供应商绩效考核表");
        XSSFRow row0 = sheet.createRow(0);
        XSSFRow row1 = sheet.createRow(1);

        //起始行，终止行，起始列，终止列 3  9
        CellRangeAddress cellRange0 = new CellRangeAddress((short)0, (short)1, (short)0, (short)0);
        sheet.addMergedRegion(cellRange0);
        XSSFCell title0 = row0.createCell(0);
        title0.setCellValue("物料分类");
        sheet.setColumnWidth(0, 5000);
        title0.setCellStyle(cellStyle1);

        //起始行，终止行，起始列，终止列 3  9
        CellRangeAddress cellRange1 = new CellRangeAddress((short)0, (short)1, (short)1, (short)1);
        sheet.addMergedRegion(cellRange1);
        XSSFCell title1 = row0.createCell(1);
        title1.setCellValue("供应商名称");
        sheet.setColumnWidth(1, 5000);
        title1.setCellStyle(cellStyle1);
        //起始行，终止行，起始列，终止列 3  9
        CellRangeAddress cellRange2 = new CellRangeAddress((short)0, (short)1, (short)2, (short)2);
        sheet.addMergedRegion(cellRange2);
        XSSFCell title2 = row0.createCell(2);
        title2.setCellValue("供应商代码");
        sheet.setColumnWidth(2, 5000);
        title2.setCellStyle(cellStyle1);

        int count = 0;
        Map<String,Object> dateMap = new HashMap<>();
        for (int i = 0; i < dateList.size(); i++) {
            if (i == 0) {
                CellRangeAddress cellRange = new CellRangeAddress((short)0, (short)0, (short) (2 + i + 1), (short) (3+ i + 1));
                sheet.addMergedRegion(cellRange);
                XSSFCell title = row0.createCell(3);
                title.setCellValue(dateList.get(i));
//                sheet.setColumnWidth(3, 5000);
                title.setCellStyle(cellStyle1);
                XSSFCell title3 =row1.createCell(3);
                title3.setCellValue("分数");
                title3.setCellStyle(cellStyle1);
                XSSFCell title4 =row1.createCell(4);
                title4.setCellValue("排名");
                title4.setCellStyle(cellStyle1);
                dateMap.put(dateList.get(i),3 + "," + 4);
                count +=2;
            } else {
                CellRangeAddress cellRange = new CellRangeAddress((short)0, (short)0, (short) (3 + count), (short) (4+ count));
                sheet.addMergedRegion(cellRange);
                XSSFCell title = row0.createCell(3 + count);
                title.setCellValue(dateList.get(i));
//                sheet.setColumnWidth(3 + count, 5000);
                title.setCellStyle(cellStyle1);
                XSSFCell title3 =row1.createCell(3 + count);
                title3.setCellValue("分数");
                title3.setCellStyle(cellStyle1);
                XSSFCell title4 =row1.createCell(4 + count);
                title4.setCellValue("排名");
                title4.setCellStyle(cellStyle1);
                dateMap.put(dateList.get(i),(3 + count) + "," + (4 + count));
                count +=2;
            }
        }
        Map<String,Object> map = new HashMap<>();
        for (int i = 0; i < dataList.size(); i++) {
            Integer dex = (Integer) map.get(dataList.get(i).getSupplierCode().toString() + dataList.get(i).getTypeName());
            if (dex == null) {
                int lastRowNum = sheet.getLastRowNum();
                XSSFRow row3 = sheet.createRow(lastRowNum + 1 );
                int rowNum = row3.getRowNum();
                XSSFCell title = row3.createCell(0);
                title.setCellValue(dataList.get(i).getTypeName());
                title.setCellStyle(cellStyle1);
                XSSFCell titl1 = row3.createCell(1);
                titl1.setCellValue(dataList.get(i).getSupplierName());
                titl1.setCellStyle(cellStyle1);
                XSSFCell titl2 = row3.createCell(2);
                titl2.setCellValue(dataList.get(i).getSupplierCode());
                titl2.setCellStyle(cellStyle1);
                String str = (String) dateMap.get(dataList.get(i).getCircleEnd());
                String[] Index = str.split(",");
                XSSFCell titl3 = row3.createCell(Integer.parseInt(Index[0]));
                titl3.setCellValue(dataList.get(i).getSumScore());
                titl3.setCellStyle(cellStyle1);
                XSSFCell titl4 = row3.createCell(Integer.parseInt(Index[1]));
                titl4.setCellValue(dataList.get(i).getRank());
                titl4.setCellStyle(cellStyle1);
                map.put(dataList.get(i).getSupplierCode().toString() + dataList.get(i).getTypeName(),rowNum);
            }
            else {
                String str = (String) dateMap.get(dataList.get(i).getCircleEnd());
                String[] Index = str.split(",");

                Cell cell = null;
                XSSFRow row2 = null;
                row2 = sheet.getRow(dex);

                XSSFCell test=  row2.createCell(Integer.parseInt(Index[0]));
                test.setCellValue(dataList.get(i).getSumScore());
                test.setCellStyle(cellStyle1);
                XSSFCell test2 = row2.createCell(Integer.parseInt(Index[1]));
                test2.setCellValue(dataList.get(i).getRank());
                test2.setCellStyle(cellStyle1);
            }
        }
        ServletOutputStream fileOut = null;
        try {
            fileOut = response.getOutputStream();
            String fileName = new String("供应商绩效考核表".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();
                }
            }
        }

    }```