POI导出Excel

实现效果

详情生成文件-见附件xls文件

技术

springcloud，POI

1.注意事项

1）设置单元格自适应大小，对中文支持不太好，需要额外设置
2）注意，createRow指定创建行后，再次重复创建指定行，会导致上次createRow创建的row失效
示例

		HSSFRow row2 = sheet.createRow(4);		//指定创建第三行
        HSSFCell cell2 = row2.createCell(4);
        cell2.setCellValue("4");

        //有创建了一次第三行，所以会导致上面的第四行信息没了
        HSSFRow row3 = sheet.createRow(4);
        HSSFCell cell3 = row3.createCell(3);
        cell3.setCellValue("3");

代码示例

Resource：

 @PostMapping("/quote-lines/_export")
    public ResponseEntity<byte[]> exportQuoteLine(@RequestBody List<String> quoteLineIdList) throws IOException {

        List<QuoteLineDTO> quoteLineDTOList = quoteLineService.findQuoteLineByIds(quoteLineIdList);
        if(CollectionUtils.isEmpty(quoteLineDTOList)){
            log.info("exportQuoteLine->The request parameter is empty");
            return null;
        }

        List<QuoteLineDTO> quoteLineDTOS = new ArrayList<>();
        int h = 0;
        for (int i = 0; i < quoteLineDTOList.size() && h < 30; i++) {
            if (org.apache.commons.lang3.StringUtils.isNotBlank(quoteLineDTOList.get(i).getProductType()) && quoteLineDTOList.get(i).getProductType().equalsIgnoreCase(QuoteEnum.CTO.getName())) {
                quoteLineDTOS.add(h, quoteLineDTOList.get(i));
                h++;
            }
        }

        if (CollectionUtils.isEmpty(quoteLineDTOS)) {
            log.info("exportQuoteLine->The request parameter(CTO-QuoteLine) is empty");
            return null;
        }
        String responseFileName = null;
        Quote quote = quoteService.findOne(quoteLineDTOS.get(0).getQuoteId()).get();
        if (quote.getBusinessUnit().equalsIgnoreCase(QuoteEnum.PCSD.getName())) {
            responseFileName = ExportQuoteLineEnum.PCSD_FILENAME.getName();
        } else {
            responseFileName = ExportQuoteLineEnum.DCG_FILENAME.getName();
        }

        byte[] bytes = quoteLineService.exportQuoteLineByCTO(quoteLineDTOS, quote);

        HttpHeaders headers = new HttpHeaders();
        headers.set("file-name", responseFileName);
        headers.setContentDispositionFormData("attachment", responseFileName);
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        //new FileOutputStream("/home/111.xls").write(bytes, 0, bytes.length)
        return new ResponseEntity<>(
            bytes,
            headers,
            HttpStatus.CREATED
        );


    }

service：

   public byte[] exportQuoteLineByCTO(List<QuoteLineDTO> quoteLineDTOList, Quote quote) throws IOException {

        HSSFWorkbook workbook = new HSSFWorkbook();
        setHeaderCellStyle(workbook);
        if (quote.getBusinessUnit().equalsIgnoreCase(QuoteEnum.PCSD.getName())) {

            HSSFSheet sheet = workbook.createSheet(QuoteEnum.PCSD.getName());

            sheet.setDefaultRowHeight((short) (2 * 256));
            sheet.setDefaultColumnWidth(17);

            //max row
            int maxRow = 0;
            for (int g = 0; g < quoteLineDTOList.size(); g++) {
                //There are no database fields in dto->com.fasterxml.jackson.databind.exc.UnrecognizedPropertyException: Unrecognized field "XXX"
                List<CvPairsDTO> cvPairsDTOList = new ObjectMapper().readValue(quoteLineDTOList.get(g).getConfiguration(), new TypeReference<List<CvPairsDTO>>() {
                });
                if (cvPairsDTOList.size() > maxRow) {
                    maxRow = cvPairsDTOList.size();
                }
            }

            List<HSSFRow> rowList = Arrays.asList(new HSSFRow[maxRow + 2]);
            setHeaderStyle(sheet, quoteLineDTOList, rowList, maxRow);
            setCellData(sheet, quoteLineDTOList, rowList);
            return FileToByte.byteFromFile(workbook);

        } else {//dcg

            HSSFSheet sheet = workbook.createSheet(QuoteEnum.DCG.getName());

            int number = 2;
            for (int i = 0; i < quoteLineDTOList.size(); i++) {
                sheet.createRow(number - 2).createCell(0).setCellValue(ExportQuoteLineEnum.DCG_FIRSTROW_ONE.getName());
                HSSFRow sheetRow = sheet.createRow(number - 1);
                sheetRow.createCell(0).setCellValue(ExportQuoteLineEnum.DCG_SECONDROW_ONE.getName());
                sheetRow.createCell(1).setCellValue(ExportQuoteLineEnum.DCG_SECONDROW_TWO.getName());
                sheetRow.createCell(2).setCellValue(ExportQuoteLineEnum.DCG_SECONDROW_THREE.getName());
                sheetRow.createCell(3).setCellValue(ExportQuoteLineEnum.DCG_SECONDROW_FOUR.getName());
                sheetRow.createCell(4).setCellValue(ExportQuoteLineEnum.DCG_SECONDROW_FIVE.getName());
                sheetRow.createCell(5).setCellValue(ExportQuoteLineEnum.DCG_SECONDROW_SIX.getName());

                List<FeatureCodeConfigurationDTO> quoteConfigurationDTOList = new ObjectMapper().readValue(quoteLineDTOList.get(i).getConfiguration(), new TypeReference<List<FeatureCodeConfigurationDTO>>() {
                });
                for (int j = 0; j < quoteConfigurationDTOList.size(); j++) {
                    HSSFRow row = sheet.createRow(number + j);
                    row.createCell(0).setCellValue(quoteLineDTOList.get(i).getSolutionId());
                    row.createCell(1).setCellValue(quoteLineDTOList.get(i).getProductNumber());
                    row.createCell(3).setCellValue(quoteConfigurationDTOList.get(j).getFeatureCode());
                    row.createCell(4).setCellValue(quoteConfigurationDTOList.get(j).getDescription());
                    row.createCell(5).setCellValue(quoteConfigurationDTOList.get(j).getQuantity());
                }
                number += quoteConfigurationDTOList.size() + 2;
            }
            return FileToByte.byteFromFile(workbook);
        }
    }


    //Set Header Style
    public void setHeaderStyle(HSSFSheet sheet, List<QuoteLineDTO> quoteLineDTOList, List<HSSFRow> rowList, int maxRow) {

        HSSFRow row0 = sheet.createRow(0);
        HSSFRow row1 = sheet.createRow(1);

        for (int i = 0; i < quoteLineDTOList.size(); i++) {
            //Set the first line of the head
            HSSFCell cell11 = row0.createCell(4 * i);
            cell11.setCellValue(ExportQuoteLineEnum.PCSD_FIRSTROW_ONE.getName());
            cell11.setCellStyle(cellStyle);
            HSSFCell cell12 = row0.createCell(4 * i + 1);
            cell12.setCellValue(quoteLineDTOList.get(i).getProductNumber());
            cell12.setCellStyle(cellStyle);
            //Set the second line of the head
            HSSFCell cell21 = row1.createCell(4 * i);
            cell21.setCellValue(ExportQuoteLineEnum.PCSD_SECONDROW_ONE.getName());
            cell21.setCellStyle(cellStyle);
            HSSFCell cell22 = row1.createCell(4 * i + 1);
            cell22.setCellValue(ExportQuoteLineEnum.PCSD_SECONDROW_TWO.getName());
            cell22.setCellStyle(cellStyle);
            HSSFCell cell23 = row1.createCell(4 * i + 2);
            cell23.setCellValue(ExportQuoteLineEnum.PCSD_SECONDROW_THREE.getName());
            cell23.setCellStyle(cellStyle);
        }

        //other row
        for (int i = 0; i < maxRow; i++) {
            rowList.set(i + 2, sheet.createRow(i + 2));
        }

    }

    //Set data
    public void setCellData(HSSFSheet sheet, List<QuoteLineDTO> quoteLineDTOList, List<HSSFRow> rowList) throws IOException {

        for (int i = 0; i < quoteLineDTOList.size(); i++) {
            List<CvPairsDTO> cvPairsDTOList = new ObjectMapper().readValue(quoteLineDTOList.get(i).getConfiguration(), new TypeReference<List<CvPairsDTO>>() {
            });
            //other row is data
            for (int j = 0; j < cvPairsDTOList.size(); j++) {
                rowList.get(j + 2).createCell(4 * i).setCellValue(cvPairsDTOList.get(j).getValueDescription());
            }
        }

    }

    //set cell style
    public void setHeaderCellStyle(HSSFWorkbook workbook) {
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);

    }
    
private static HSSFCellStyle cellStyle = null;

POI好的链接