POI报表导出（三）POI报表导出

一. 简介

POI的报表导出功能是软件开发中最常用的功能之一。

二. 实现步骤 

• 构建报表导出的实体对象
• 构造Excel表格数据
• 创建工作簿
• 创建sheet
• 创建行对象
• 创建单元格对象
• 填充数据，设置样式
• 下载

三. 代码实现 

1. 构建报表导出的实体对象

package com.ihrm.domain.employee.response;

import com.ihrm.domain.employee.EmployeeResignation;
import com.ihrm.domain.employee.UserCompanyPersonal;
//import com.ihrm.domain.poi.ExcelAttribute;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;
import org.springframework.beans.BeanUtils;

import javax.persistence.Id;

@Getter
@Setter
@NoArgsConstructor
@ToString
public class EmployeeReportResult {

//    @ExcelAttribute(sort = 0)
    private String userId;
//    @ExcelAttribute(sort = 1)
    private String username;
    private String departmentName;
    private String mobile;
    private String timeOfEntry;
    private String companyId;
    private String sex;
    /**
     * 出生日期
     */
    private String dateOfBirth;
    /**
     * 最高学历
     */
    private String theHighestDegreeOfEducation;
    /**
     * 国家地区
     */
    private String nationalArea;
    /**
     * 护照号
     */
    private String passportNo;
    /**
     * 身份证号
     */
    private String idNumber;
    /**
     * 身份证照片-正面
     */
    private String idCardPhotoPositive;
    /**
     * 身份证照片-背面
     */
    private String idCardPhotoBack;
    /**
     * 籍贯
     */
    private String nativePlace;
    /**
     * 民族
     */
    private String nation;
    /**
     * 英文名
     */
    private String englishName;
    /**
     * 婚姻状况
     */
    private String maritalStatus;
    /**
     * 员工照片
     */
    private String staffPhoto;
    /**
     * 生日
     */
    private String birthday;
    /**
     * 属相
     */
    private String zodiac;
    /**
     * 年龄
     */
    private String age;
    /**
     * 星座
     */
    private String constellation;
    /**
     * 血型
     */
    private String bloodType;
    /**
     * 户籍所在地
     */
    private String domicile;
    /**
     * 政治面貌
     */
    private String politicalOutlook;
    /**
     * 入党时间
     */
    private String timeToJoinTheParty;
    /**
     * 存档机构
     */
    private String archivingOrganization;
    /**
     * 子女状态
     */
    private String stateOfChildren;
    /**
     * 子女有无商业保险
     */
    private String doChildrenHaveCommercialInsurance;
    /**
     * 有无违法违纪行为
     */
    private String isThereAnyViolationOfLawOrDiscipline;
    /**
     * 有无重大病史
     */
    private String areThereAnyMajorMedicalHistories;
    /**
     * QQ
     */
    private String qq;
    /**
     * 微信
     */
    private String wechat;
    /**
     * 居住证城市
     */
    private String residenceCardCity;
    /**
     * 居住证办理日期
     */
    private String dateOfResidencePermit;
    /**
     * 居住证截止日期
     */
    private String residencePermitDeadline;
    /**
     * 现居住地
     */
    private String placeOfResidence;
    /**
     * 通讯地址
     */
    private String postalAddress;
    /**
     * 联系手机
     */
    private String contactTheMobilePhone;
    /**
     * 个人邮箱
     */
    private String personalMailbox;
    /**
     * 紧急联系人
     */
    private String emergencyContact;
    /**
     * 紧急联系电话
     */
    private String emergencyContactNumber;
    /**
     * 社保电脑号
     */
    private String socialSecurityComputerNumber;
    /**
     * 公积金账号
     */
    private String providentFundAccount;
    /**
     * 银行卡号
     */
    private String bankCardNumber;
    /**
     * 开户行
     */
    private String openingBank;
    /**
     * 学历类型
     */
    private String educationalType;
    /**
     * 毕业学校
     */
    private String graduateSchool;
    /**
     * 入学时间
     */
    private String enrolmentTime;
    /**
     * 毕业时间
     */
    private String graduationTime;
    /**
     * 专业
     */
    private String major;
    /**
     * 毕业证书
     */
    private String graduationCertificate;
    /**
     * 学位证书
     */
    private String certificateOfAcademicDegree;
    /**
     * 上家公司
     */
    private String homeCompany;
    /**
     * 职称
     */
    private String title;
    /**
     * 简历
     */
    private String resume;
    /**
     * 有无竞业限制
     */
    private String isThereAnyCompetitionRestriction;
    /**
     * 前公司离职证明
     */
    private String proofOfDepartureOfFormerCompany;
    /**
     * 备注
     */
    private String remarks;

    /**
     * 离职时间
     */
    private String resignationTime;
    /**
     * 离职类型
     */
    private String typeOfTurnover;
    /**
     * 申请离职原因
     */
    private String reasonsForLeaving;

    public EmployeeReportResult(UserCompanyPersonal personal, EmployeeResignation resignation) {
        BeanUtils.copyProperties(personal,this);
        if(resignation != null) {
            BeanUtils.copyProperties(resignation,this);
        }
    }
}

 2. Controller（最重要的就是第一步构造数据，将数据从表中查出）

@RequestMapping(value = "/export/{month}", method = RequestMethod.GET)
public void export(@PathVariable(name = "month") String month) throws Exception {
    //1.构造数据
    List<EmployeeReportResult> list =
            userCompanyPersonalService.findByReport(companyId,month+"%");
    //2.创建工作簿
    XSSFWorkbook workbook = new XSSFWorkbook();
    //3.构造sheet
    String[] titles = {"编号", "姓名", "手机","最高学历", "国家地区", "护照号", "籍贯",
            "生日", "属相","入职时间","离职类型","离职原因","离职时间"};
    Sheet sheet = workbook.createSheet();
    Row row = sheet.createRow(0);
    AtomicInteger headersAi = new AtomicInteger();
    for (String title : titles) {
        Cell cell = row.createCell(headersAi.getAndIncrement());
        cell.setCellValue(title);
    }
    AtomicInteger datasAi = new AtomicInteger(1);
    Cell cell = null;
    for (EmployeeReportResult report : list) {
        Row dataRow = sheet.createRow(datasAi.getAndIncrement());
        //编号
        cell = dataRow.createCell(0);
        cell.setCellValue(report.getUserId());
        //姓名
        cell = dataRow.createCell(1);
        cell.setCellValue(report.getUsername());
        //手机
        cell = dataRow.createCell(2);
        cell.setCellValue(report.getMobile());
        //最高学历
        cell = dataRow.createCell(3);
        cell.setCellValue(report.getTheHighestDegreeOfEducation());
        //国家地区
        cell = dataRow.createCell(4);
        cell.setCellValue(report.getNationalArea());
        //护照号
        cell = dataRow.createCell(5);
        cell.setCellValue(report.getPassportNo());
        //籍贯
        cell = dataRow.createCell(6);
        cell.setCellValue(report.getNativePlace());
        //生日
        cell = dataRow.createCell(7);
        cell.setCellValue(report.getBirthday());
        //属相
        cell = dataRow.createCell(8);
        cell.setCellValue(report.getZodiac());
        //入职时间
        cell = dataRow.createCell(9);
        cell.setCellValue(report.getTimeOfEntry());
        //离职类型
        cell = dataRow.createCell(10);
        cell.setCellValue(report.getTypeOfTurnover());
        //离职原因
        cell = dataRow.createCell(11);
        cell.setCellValue(report.getReasonsForLeaving());
        //离职时间
        cell = dataRow.createCell(12);
        cell.setCellValue(report.getResignationTime());
    }
    String fileName = URLEncoder.encode(month+"人员信息.xlsx", "UTF-8");
    response.setContentType("application/octet-stream");
    response.setHeader("content-disposition", "attachment;filename=" + new
            String(fileName.getBytes("ISO8859-1")));
    response.setHeader("filename", fileName);
    workbook.write(response.getOutputStream());
}

 3. service

public List<EmployeeReportResult> findByReport(String companyId, String month) {
    return userCompanyPersonalDao.findByReport(companyId, month);
}

4. dao

@Query(value = "select new com.ihrm.domain.employee.response.EmployeeReportResult(a,b) from UserCompanyPersonal a " +
            "LEFT JOIN EmployeeResignation b on a.userId=b.userId where a.companyId=?1 and a.timeOfEntry like?2 or (" +
            "b.resignationTime like ?2)")
List<EmployeeReportResult> findByReport(String companyId, String month);

四. 测试

使用postman进行测试， 注意：不要使用send要使用send and download