POI全家桶

1. 导入依赖包

        <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>${commons-lang3.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-text</artifactId>
            <version>1.6</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/commons-lang/commons-lang -->
        <dependency>
            <groupId>commons-lang</groupId>
            <artifactId>commons-lang</artifactId>
            <version>${commons-lang.version}</version>
        </dependency>
        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.3.1</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.4</version>
        </dependency>

        <dependency>
            <groupId>org.apache.httpcomponents</groupId>
            <artifactId>httpcore</artifactId>
            <version>4.4.10</version>
        </dependency>

2.生成cell 样式

package cn.cncommdata.template.utils;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;

    /**
     * 生成 HSSFCellStyle 样式
     *
     * @param workbook    当前文档
     * @param required    是否必须，必须是color为红色，否者为绿色
     * @param fieldBaseVO 单元格
     * @return HSSFCellStyle样式
     */
    private HSSFCellStyle getCellStyle(HSSFWorkbook workbook, Boolean required, FieldBaseVO fieldBaseVO) {

        // Sheet样式
        HSSFCellStyle columnHeadStyle = workbook.createCellStyle();
        // 背景色的设定
        columnHeadStyle.setWrapText(true); // 自动换行
        //设置字体颜色
        HSSFFont font = workbook.createFont();
        //如果字段为required(必填)则字体设置为红色,如果不是则设置为黑色
        font.setColor(needEmphasizeCell(required, fieldBaseVO)
                ? HSSFColor.HSSFColorPredefined.RED.getIndex() : HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        //将字体设置应用到当前样式
        columnHeadStyle.setFont(font);
        // 作色 setFillForegroundColor设置前景色 不需要
        //columnHeadStyle.setFillForegroundColor(required != null && required ? HSSFColor.RED.index : HSSFColor.LIME.index);
        //solid 填充  foreground  前景色  setFillPattern设置填充图案 不需要
        //columnHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 设置边框 暂时不需要
//        columnHeadStyle.setBorderBottom(BorderStyle.THIN);
//        columnHeadStyle.setBorderLeft(BorderStyle.THIN);
//        columnHeadStyle.setBorderRight(BorderStyle.THIN);
//        columnHeadStyle.setBorderTop(BorderStyle.THIN);

        //水平居中,垂直居中
        columnHeadStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
        columnHeadStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return columnHeadStyle;
    }

    /**
     * 判断填色逻辑，除了对Required进行判断外，另外开放了对几种类型的默认选项
     *
     * @param required    field字段定义
     * @param fieldBaseVO 字段本身，需要获取其类型
     * @return boolean, true代表需要填充颜色，false为不需要
     * @author LiuLuhao
     */
    private boolean needEmphasizeCell(Boolean required, FieldBaseVO fieldBaseVO) {

        String type = fieldBaseVO.getType();
        boolean typeCheck = !"increment".equals(type)
                && !"current".equals(type)
                && !"date".equals(type);
        return required != null && required && typeCheck;
    }

3.创建单元格、合并单元格、设置单元格其他样式

    /**
     * 创建并合并list类型的一级header
     *
     * @param workbook    当前表
     * @param sheet       当前sheet
     * @param row0        首行
     * @param fieldBaseVO 对象
     * @param column      列下标
     */
    private void createAndMergeListCell(HSSFWorkbook workbook, HSSFSheet sheet, HSSFRow row0, FieldBaseVO fieldBaseVO, int column) {

        createRowCell(workbook, row0, fieldBaseVO, column);
        List<FieldBaseVO> seList = ((TableVO) fieldBaseVO).getFields();

        //合并单元格
        CellRangeAddress range;
        range = new CellRangeAddress(0, 0, column, column + seList.size() - 1);
        sheet.addMergedRegion(range);
    }

    /**
     * 创建rowCell
     *
     * @param workbook    当前workbook
     * @param row0        第一行
     * @param fieldBaseVO 单元格对象
     * @param column      单元格列下标
     */
    private void createRowCell(HSSFWorkbook workbook, HSSFRow row0, FieldBaseVO fieldBaseVO, int column) {
        HSSFCell cell0 = row0.createCell(column);
        cell0.setCellValue(fieldBaseVO.getName());
        cell0.setCellStyle(getCellStyle(workbook, fieldBaseVO.getRequired(), fieldBaseVO));
    }

    /**
     * 合并一级表头
     *
     * @param workbook    当前workbook
     * @param sheet       当前sheet
     * @param row0        第一行
     * @param row1        第二行
     * @param fieldBaseVO 单元格对象
     * @param column      单元格列下标
     */
    private void createAndMergeTwoRowCell(HSSFWorkbook workbook, HSSFSheet sheet, HSSFRow row0, HSSFRow row1, FieldBaseVO fieldBaseVO, int column) {
        //0,i
        HSSFCell cell0 = row0.createCell(column);
        cell0.setCellValue(fieldBaseVO.getName());
        cell0.setCellStyle(getCellStyle(workbook, fieldBaseVO.getRequired(), fieldBaseVO));

        //设置第二行样式   设置表头样式 getCellStyle()方法
        HSSFCell cellNull = row1.createCell(column);
        cellNull.setCellStyle(getCellStyle(workbook, fieldBaseVO.getRequired(), fieldBaseVO));

        //合并单元格
        CellRangeAddress range = new CellRangeAddress(0, 1, column, column);
        sheet.addMergedRegion(range);
    }

    /**
     * 设置表格的列宽
     *
     * @param fieldBaseVOS 源表头
     * @param sheet        当前表
     */
    private void setColWidth(List<FieldBaseVO> fieldBaseVOS, HSSFSheet sheet) {
        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(1, true);
        int colNum = getColumnNumber(fieldBaseVOS, 0);
        for (int i = 0; i < colNum; i++) {
            sheet.setColumnWidth(i, CELL_WIDTH);
        }

    }

4.yaml

不需要配置yml