【业务功能篇02】Springboot+POI Excel通用工具类总结

一个系统，涉及导出功能的需求是非常多的，需要设计一个工具类，提供给常规导出功能所需，减少代码量。展示相关的excel工具类

 举个实例：

controller层

@RestController
@Slf4j
public class PpmExportDataController {
    @Autowired
    IPpmExportDataService ppmExportDataService;

    @PostMapping("/PPMService/getExcelData")
    public void getExcelData(@RequestBody PpmQueryParam queryParam,
                             HttpServletResponse response) {
        ExcelSheet<Object> excelSheet = ppmExportDataService.getExcelData(queryParam);
        File file = new File(excelSheet.getSheetName());
        // 设置响应的头和客户端保存的文件名
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/octet-stream");
        response.setHeader("Content-Disposition", "attachment;fileName=" + file.getName());
        try (ServletOutputStream out = response.getOutputStream()) {
            ExcelUtil.exportExcel(excelSheet.getHeaders(), excelSheet.getDataset(), out);
        } catch (IOException e) {
            log.error("数据流出现异常：", e);
        }
    }

}

service层实现类  getExcelData方法：

//表头列名    
private final String[] fdppmHeadMP = {"a", "b", "c", "d", ...};    

@Override
    public ExcelSheet<Object> getExcelData(PpmQueryParam queryParam) {
        ExcelSheet<Object> result = new ExcelSheet<>();
        // 缺陷明细数据
        Collection list;
        String[] head;
        if (StringUtils.isNotEmpty(queryParam.getPdu()) && queryParam.getPdu().equals("MP")) {
            head = fdppmHeadMP;
            list = baseMapper.getFdppmDefectPage(queryParam);
        } else {
            head = fdppmHeadCM;
            list = baseMapper.getCommonFdppmDefectPage(queryParam);
        }
        result.setSheetName("缺陷明细数据").setHeaders(ExcelUtil.getHeaders(head)).setDataset(list);
        return result;
    }

实体类对象：注意这里用了一些自定义的注解，根据对应的工具类中的方法可能会涉及到比如一些排序等操作  @Excel  @ImportExcel

package xxx.domain.model;

import com.baomidou.mybatisplus.annotation.*;

import java.util.Date;
import java.io.Serializable;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.annotation.TableField;
import xxx.annotation.Excel;
import xxx.annotation.ImportExcel;
import lombok.NoArgsConstructor;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

import static xxx.annotation.ImportExcel.STRING_TYPE;

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@NoArgsConstructor
@TableName("dwr_quality_fdppm_defect_f")
public class QualityFdppmDefectF implements Serializable {

    private static final long serialVersionUID = 1L;

    /**
     * ID
     */
    @TableField("ID")
    @TableId(value = "ID",type = IdType.AUTO)
    private Integer id;

    /**
     * 单号
     */
    @Excel(index = 0)
    @TableField("NO")
    @ImportExcel(index = 0,type = STRING_TYPE)
    private String no;

    /**
     * 编码
     */
    @Excel(index = 1)
    @TableField("ITEM_CODE")
    @ImportExcel(index = 1,type = STRING_TYPE)
    private String itemCode;

    /**
     * 编码前4位
     */
    @Excel(index = 2)
    @TableField("ITEM_CODE4")
    @ImportExcel(index = 2,type = STRING_TYPE)
    private String itemCode4;

    /**
     * 编码前2位
     */
    @Excel(index = 3)
    @TableField("ITEM_CODE2")
    @ImportExcel(index = 3,type = STRING_TYPE)
    private String itemCode2;

    /**
     * 编码状态
     */
    @Excel(index = 4)
    @TableField("CODE_STATUS")
    @ImportExcel(index = 4,type = STRING_TYPE)
    private String codeStatus;

    /**
     * 编码属性
     */
    @Excel(index = 5)
    @TableField("CODE_ATTR")
    @ImportExcel(index = 5,type = STRING_TYPE)
    private String codeAttr;

    /**
     * 编码描述
     */
    @Excel(index = 6)
    @TableField("CODE_DESC")
    @ImportExcel(index = 6,type = STRING_TYPE)
    private String codeDesc;

    /**
     * 专家团_l3
     */
    @Excel(index = 7)
    @TableField("EXPERT_GROUP")
    @ImportExcel(index = 7,type = STRING_TYPE)
    private String expertGroup;

    /**
     * 物料小类代码
     */
    @Excel(index = 8)
    @TableField("SUB_CODE")
    @ImportExcel(index = 8,type = STRING_TYPE)
    private String subCode;

    /**
     * 物料小类名称
     */
    @Excel(index = 9)
    @TableField("MATERIAL_SUB")
    @ImportExcel(index = 9,type = STRING_TYPE)
    private String materialSub;

    /**
     * 产品线
     */
    @Excel(index = 10)
    @TableField("PRODUCT_LINE")
    @ImportExcel(index = 10,type = STRING_TYPE)
    private String productLine;

    /**
     * 产品名称
     */
    @Excel(index = 11)
    @TableField("PRODUCT_NAME")
    @ImportExcel(index = 11,type = STRING_TYPE)
    private String productName;

    /**
     * 供应商代码
     */
    @Excel(index = 12)
    @TableField("VENDOR_CODE")
    @ImportExcel(index = 12,type = STRING_TYPE)
    private String vendorCode;

    /**
     * 供应商
     */
    @Excel(index = 13)
    @TableField("SUPPLIER")
    @ImportExcel(index = 13,type = STRING_TYPE)
    private String supplier;

    /**
     * 供应商工厂代码
     */
    @Excel(index = 14)
    @TableField("VENDOR_FACTORY_CODE")
    @ImportExcel(index = 14,type = STRING_TYPE)
    private String vendorFactoryCode;

    /**
     * 供应商工厂
     */
    @Excel(index = 15)
    @TableField("VENDOR_FACTORY")
    @ImportExcel(index = 15,type = STRING_TYPE)
    private String vendorFactory;

    /**
     * 制造商
     */
    @Excel(index = 16)
    @TableField("MANUFACTURER")
    @ImportExcel(index = 16,type = STRING_TYPE)
    private String manufacturer;

    /**
     * 组织id
     */
    @Excel(index = 17)
    @TableField("GROUP_ID")
    @ImportExcel(index = 17,type = STRING_TYPE)
    private String groupId;

    /**
     * 使用数
     */
    @Excel(index = 18)
    @TableField("USE_QTY")
    @ImportExcel(index = 18,type = STRING_TYPE)
    private String useQty;

    /**
     * 缺陷数
     */
    @Excel(index = 19)
    @TableField("DEFECT_QTY")
    @ImportExcel(index = 19,type = STRING_TYPE)
    private String defectQty;

    /**
     * 区分标识
     */
    @Excel(index = 20)
    @TableField("TAG")
    @ImportExcel(index = 20,type = STRING_TYPE)
    private String tag;

    /**
     * M-Con
     */
    @Excel(index = 21)
    @TableField("CON")
    @ImportExcel(index = 21,type = STRING_TYPE)
    private String con;

    /**
     * 科室
     */
    @Excel(index = 22)
    @TableField("MQE_DEPART")
    @ImportExcel(index = 22,type = STRING_TYPE)
    private String mqeDepart;

    /**
     * 缺陷类型
     */
    @Excel(index = 23)
    @TableField("DEFECT_TYPE")
    @ImportExcel(index = 23,type = STRING_TYPE)
    private String defectType;

    /**
     * 反馈公司
     */
    @Excel(index = 24)
    @TableField("FBACK_COM")
    @ImportExcel(index = 24,type = STRING_TYPE)
    private String fbackCom;

    /**
     * 公司代码
     */
    @Excel(index = 25)
    @TableField("COM_CODE")
    @ImportExcel(index = 25,type = STRING_TYPE)
    private String comCode;

    /**
     * 任务令
     */
    @Excel(index = 26)
    @TableField("TASK_NO")
    @ImportExcel(index = 26,type = STRING_TYPE)
    private String taskNo;

    /**
     * 故障条码
     */
    @Excel(index = 27)
    @TableField("FAULT_BARCODE")
    @ImportExcel(index = 27,type = STRING_TYPE)
    private String faultBarcode;

    /**
     * 坏件条码
     */
    @Excel(index = 28)
    @TableField("PART_BARCODE")
    @ImportExcel(index = 28,type = STRING_TYPE)
    private String partBarcode;

    /**
     * 发生工序
     */
    @Excel(index = 29)
    @TableField("VR_OPER")
    @ImportExcel(index = 29,type = STRING_TYPE)
    private String vrOper;

    /**
     * 缺陷大类
     */
    @Excel(index = 30)
    @TableField("DEFECT_TYPE_BIG")
    @ImportExcel(index = 30,type = STRING_TYPE)
    private String defectTypeBig;

    /**
     * 缺陷小类
     */
    @Excel(index = 31)
    @TableField("DEFECT_TYPE_SMALL")
    @ImportExcel(index = 31,type = STRING_TYPE)
    private String defectTypeSmall;

    /**
     * 缺陷位置
     */
    @Excel(index = 32)
    @TableField("DEFECT_POSITION")
    @ImportExcel(index = 32,type = STRING_TYPE)
    private String defectPosition;

    /**
     * 缺陷代码
     */
    @Excel(index = 33)
    @TableField("DEFECT_CODE")
    @ImportExcel(index = 33,type = STRING_TYPE)
    private String defectCode;

    /**
     * 缺陷代码描述
     */
    @Excel(index = 34)
    @TableField("DEFECT_CODE_DESC")
    @ImportExcel(index = 34,type = STRING_TYPE)
    private String defectCodeDesc;

    /**
     * 故障现象
     */
    @Excel(index = 35)
    @TableField("DEFECT_PHENOMENON")
    @ImportExcel(index = 35,type = STRING_TYPE)
    private String defectPhenomenon;

    /**
     * LOT_CODE
     */
    @Excel(index = 36)
    @TableField("LOT_CODE")
    @ImportExcel(index = 36,type = STRING_TYPE)
    private String lotCode;

    /**
     * DATE_CODE
     */
    @Excel(index = 37)
    @TableField("DATE_CODE")
    @ImportExcel(index = 37,type = STRING_TYPE)
    private String dateCode;

    /**
     * 创建人
     */
    @Excel(index = 38)
    @TableField("CREATION_USER")
    @ImportExcel(index = 38,type = STRING_TYPE)
    private String creationUser;

    /**
     * 故障日期
     */
    @Excel(index = 39)
    @TableField("FAILURE_DATE")
    @ImportExcel(index = 39,type = STRING_TYPE)
    private String failureDate;

    /**
     * 数据来源
     */
    @Excel(index = 40)
    @TableField("DATA_SOURCE")
    @ImportExcel(index = 40,type = STRING_TYPE)
    private String dataSource;

    @TableField("CREATION_DATE")
    private Date creationDate;

    /**
     * 产品
     */
    @Excel(index = 41)
    private String product;

    /**
     * 项目
     */
    @Excel(index = 42)
    private String project;

    /**
     * 品类
     */
    @Excel(index = 43)
    private String category;

    /**
     * 子品类
     */
    @Excel(index = 44)
    private String subCategory;

}

ExcelSheet工具类：

/**
 * Excel表参数
 */
@Data
@Accessors(chain = true)    
@NoArgsConstructor
public class ExcelSheet<T> {
    /**
     * Excel表名
     */
    private String sheetName;
    /**
     * Excel表头
     */
    private Map<String,String> headers;
    /**
     * 需要导出的Excel数据集合
     */
    private Collection<T> dataset;
}

ExcelUtil工具类：

package xxx.utils.excel;

import xxx.annotation.Excel;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.beanutils.BeanComparator;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.ComparatorUtils;
import org.apache.commons.collections.comparators.ComparableComparator;
import org.apache.commons.collections.comparators.ComparatorChain;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellReference;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.text.MessageFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * Excel的导入与导出工具
 */
@Slf4j
public class ExcelUtil {

    private static final String[] LETTER = {"a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m",
            "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z"};

    /**
     * 用来验证excel与Vo中的类型是否一致
     * Map<栏位类型,只能是哪些Cell类型>
     */
    private static final Map<Class<?>, CellType[]> validateMap = new HashMap<>();

    static {
        validateMap.put(String[].class, new CellType[]{CellType.STRING});
        validateMap.put(Double[].class, new CellType[]{CellType.NUMERIC});
        validateMap.put(String.class, new CellType[]{CellType.STRING});
        validateMap.put(Double.class, new CellType[]{CellType.NUMERIC});
        validateMap.put(Date.class, new CellType[]{CellType.NUMERIC, CellType.STRING});
        validateMap.put(Integer.class, new CellType[]{CellType.NUMERIC});
        validateMap.put(Float.class, new CellType[]{CellType.NUMERIC});
        validateMap.put(Long.class, new CellType[]{CellType.NUMERIC});
        validateMap.put(Boolean.class, new CellType[]{CellType.BOOLEAN});
    }

    /**
     * 获取cell类型的文字描述
     * @param cellType Excel的单元格类型
     * @return
     */
    private static String getCellTypeByInt(CellType cellType) {
        if(cellType == CellType.BLANK)
            return "Null type";
        else if(cellType == CellType.BOO