POI工具类的使用（基于SpringCloud）

POI操作EXCEL并转PDF

参考博客：Java:Excel转PDF实现方案;基于POI与Itext进行搭配.
pom.xml

 <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-scratchpad</artifactId>
      <version>4.0.1</version>
    </dependency>

 		<!-- excel转pdf的itext依赖 start -->
        <dependency>
            <groupId>com.itextpdf</groupId>
            <artifactId>itextpdf</artifactId>
            <version>5.5.6</version>
        </dependency>
        <dependency>
            <groupId>com.itextpdf</groupId>
            <artifactId>itext-asian</artifactId>
            <version>5.2.0</version>
        </dependency>
        <!-- excel转pdf的itext依赖 end -->

Controller入口

@GetMapping("/test")
public ResponseEntity<?> test(@PathVariable("organizationId") Long organizationId,
                                                    @RequestParam List<String> resolutionNumberList,
                                                    HttpServletResponse response) throws IOException {
   
        Number2Excel.number2Excel(resolutionNumberList,response);
        return Results.success();
}

Number2Excel:

public class Number2Excel {
   
    private final static String FILE_NAME = "TEST";
    private final static String SHEET_NAME = "TEST";

    public static void number2Excel(List<String> resolutionNumberList, HttpServletResponse response) {
   
        if (CollectionUtils.isEmpty(resolutionNumberList)) {
   
            return;
        }
        Workbook workbook = new SXSSFWorkbook(10000);
        Sheet sheet = workbook.createSheet(SHEET_NAME);
        sheet.setDisplayGridlines(false);
        // 创建通用样式
        CellStyle cellStyle = ExeclCommonUtil.cellStyleFactory(workbook);
        int numberListSize = resolutionNumberList.size();
        int rowNumber = numberListSize / 80 * 16;
        if (numberListSize % 80 != 0) {
   
            rowNumber = rowNumber + 16;
        }
        // 初始化表格
        initSheet(sheet, cellStyle, rowNumber);

        int i = 0, j = 0;
        for (String resolutionNumber : resolutionNumberList) {
   
            Row row = sheet.getRow(i);
            ExeclCommonUtil.setCell(row, j, resolutionNumber, cellStyle);
            j++;
            if (j > 4) {
   
                j = 0;
                i++;
            }
        }

        // excel转pdf并返回给前端
        ExeclCommonUtil.excelToPDF2(workbook, response, FILE_NAME);
    }

    private static void initSheet(final Sheet sheet, final CellStyle cellStyle, int rowNumber) {
   
        for (int i = 0; i < rowNumber; i++) {
   
            Row row = sheet.createRow(i);
            for (int j = 0; j < 5; j++) {
   
                row.setHeightInPoints((float) 50.5);
                ExeclCommonUtil.setCellNull(row, j, cellStyle);
                ExeclCommonUtil.setCell(row, j, null, cellStyle);
            }
        }
    }
}

ExeclCommonUtil:

    /**
     * 获取单元格样式-默认白色底水平垂直居中
     *
     * @param workbook
     * @return
     */
    public static CellStyle cellStyleFactory(Workbook workbook) {
   
        //默认值
        short foregroundColor = HSSFColor.HSSFColorPredefined.WHITE.getIndex();
        HorizontalAlignment alignment = HorizontalAlignment.CENTER;
        VerticalAlignment verticalAlignment = VerticalAlignment.CENTER;
        CellStyle cellStyle = cellStyleFactory(workbook, foregroundColor, alignment, verticalAlignment);
        cellStyle.setFont(cellFontFactory(workbook, (short) 10));
        return cellStyle;
    }

	/**
     * 获取单元格样式-默认白色底水平垂直居中
     *
     * @param workbook
     * @param foregroundColor   背景颜色 HSSFColor.HSSFColorPredefined
     * @param alignment         水平 HorizontalAlignment.CENTER/RIGHT/LEFT/...
     * @param verticalAlignment 垂直 VerticalAlignment.CENTER/RIGHT/LEFT/...
     * @return
     */
    public static CellStyle cellStyleFactory(Workbook workbook, short foregroundColor, HorizontalAlignment alignment, VerticalAlignment verticalAlignment) {
   
        //默认值
        foregroundColor = foregroundColor == 0 ? HSSFColor.HSSFColorPredefined.WHITE.getIndex() : foregroundColor;
        alignment = Objects.isNull(alignment) ? HorizontalAlignment.CENTER : alignment;
        verticalAlignment = Objects.isNull(verticalAlignment) ? VerticalAlignment.CENTER : verticalAlignment;
        //设置格式
        CellStyle cellStyle = workbook.createCellStyle();

        //背景颜色
        cellStyle.setFillForegroundColor(foregroundColor);
        //水平居中
        cellStyle.setAlignment(alignment);
        //垂直居中
        cellStyle.setVerticalAlignment(verticalAlignment);
        return cellStyle;
    }

 	/**
     * 将excel转换成pdf
     *  设置A4不旋转，表格无框线
     * @param workbook
     * @return
     */
    public static void excelToPDF2(Workbook workbook, HttpServletResponse response, String fileName) {
   
        try {
   
            List<ExcelObject> objects = new ArrayList<ExcelObject>();
            Excel excel = new Excel(workbook);
            objects.add(new ExcelObject("测试", excel));
            response.reset();
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/pdf");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(String.format("%s.pdf", fileName), "utf-8"));
            OutputStream outputStream = response.getOutputStream();
            Excel2Pdf pdf = new Excel2Pdf(objects, outputStream);
            pdf.convert2();
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
   
            e.printStackTrace();
        }
    }

用到的工具类：
Excel：

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.