easypoi Excel 文件导出

1. 模板样式

2. class

/**
 * 员工信息模板
 */
@Data
public class StaffExcelDTO{

    private static final String GN_FSIAOHF = "社保、公积金、残保金";

    private static final String GN_INSURANCE = "商保信息";

    @Excel(name = "姓名")
    private String name;

    @Excel(name = "身份证号码")
    private String idCard;

    @Excel(name = "手机号码")
    private String phone;

    @Excel(name = "银行卡号")
    private String bankCard;

    @Excel(name = "银行名称")
    private String bankName;

    @Excel(name = "养老保险基数", groupName = GN_FSIAOHF)
    private BigDecimal endowmentInsurance;

    @Excel(name = "医疗保险基数", type = XkjCnst.EXCEL_CELL_TYPE_NUM, numFormat = XkjCnst.EXCEL_CELL_NUM_FORMAT, orderNum = "1", groupName = GN_FSIAOHF)
    private BigDecimal medicalInsurance;

    @Excel(name = "大病保险基数", type = XkjCnst.EXCEL_CELL_TYPE_NUM, numFormat = XkjCnst.EXCEL_CELL_NUM_FORMAT, orderNum = "2", groupName = GN_FSIAOHF)
    private BigDecimal illnessInsurance;

    @Excel(name = "失业保险基数", type = XkjCnst.EXCEL_CELL_TYPE_NUM, numFormat = XkjCnst.EXCEL_CELL_NUM_FORMAT, orderNum = "3", groupName = GN_FSIAOHF)
    private BigDecimal unemploymentInsurance;

    @Excel(name = "工伤保险基数", type = XkjCnst.EXCEL_CELL_TYPE_NUM, numFormat = XkjCnst.EXCEL_CELL_NUM_FORMAT, orderNum = "4", groupName = GN_FSIAOHF)
    private BigDecimal employmentInjuryInsurance;

    @Excel(name = "生育保险基数", type = XkjCnst.EXCEL_CELL_TYPE_NUM, numFormat = XkjCnst.EXCEL_CELL_NUM_FORMAT, orderNum = "5", groupName = GN_FSIAOHF)
    private BigDecimal maternityInsurance;

    @Excel(name = "公积金基数", type = XkjCnst.EXCEL_CELL_TYPE_NUM, numFormat = XkjCnst.EXCEL_CELL_NUM_FORMAT, orderNum = "6", groupName = GN_FSIAOHF)
    private BigDecimal accumulationFund;

    @Excel(name = "残保金基数", type = XkjCnst.EXCEL_CELL_TYPE_NUM, numFormat = XkjCnst.EXCEL_CELL_NUM_FORMAT, orderNum = "7", groupName = GN_FSIAOHF)
    private BigDecimal residualPremium;

    @Excel(name = "保险公司", groupName = GN_INSURANCE)
    private String insuranceCompany;

    @Excel(name = "保险名称", orderNum = "1", groupName = GN_INSURANCE)
    private String insuranceName;

    @Excel(name = "保险金额", type = XkjCnst.EXCEL_CELL_TYPE_NUM, numFormat = XkjCnst.EXCEL_CELL_NUM_FORMAT, orderNum = "2", groupName = GN_INSURANCE)
    private BigDecimal insuranceAmount;

    @Excel(name = "保险开始日期", orderNum = "3", groupName = GN_INSURANCE)
    private String insuranceStartTime;

    @Excel(name = "保险结束日期", orderNum = "4", groupName = GN_INSURANCE)
    private String insuranceEndTime;


}

3. 实现

/**
* 员工信息模板
 *
 * @param row 代表要导出几条数据,这个可以作为查询输了
 * @return
 */
private Map<String, Object> genStaffData(int row) {
    List<StaffExcelDTO> staffExcelDTOList = new ArrayList<>(row);
    StaffExcelDTO staff;

    for (int i = 0; i < row; i++) {
        staff = new StaffExcelDTO();

        staff.setName();
        staff.setIdType();
        staff.setIdCard();
        staff.setPhone();
        staff.setBankCard();

        staff.setEndowmentInsurance();
        staff.setMedicalInsurance();
        staff.setIllnessInsurance();
        staff.setUnemploymentInsurance();
        staff.setEmploymentInjuryInsurance();
        staff.setMaternityInsurance();
        staff.setAccumulationFund();
        staff.setResidualPremium();

        staff.setInsuranceCompany();
        staff.setInsuranceName();
        staff.setInsuranceAmount();
        staff.setInsuranceStartTime();
        staff.setInsuranceEndTime();

        staffExcelDTOList.add(staff);
    }

    Map<String, Object> sheetData = new HashMap<>();

    // 模版导出对应得实体类型
    sheetData.put("entity", StaffExcelDTO.class);
    // sheet中要填充得数据
    sheetData.put("data", staffExcelDTOList);

    return sheetData;
}