05 easyPoi的使用

使用easyPoi导出导入excel

1. 引入依赖

<dependency>
    <groupId>cn.afterturngroupId>
    <artifactId>easypoi-spring-boot-starterartifactId>
    <version>4.2.0version>
dependency>
1
2
3
4
5

2. 使用方式

1. 简单的excel表格可以使用在实体类属性上添加注解使用

实体类

属性上有@Excel注解的将会作为列被导出到excel中，列的名称就是Excel注解中name属性

package com.ztbase.erp.domain.dto;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.handler.inter.IExcelDataModel;
import cn.afterturn.easypoi.handler.inter.IExcelModel;
import com.ztbase.erp.common.enums.JobRankEnums;
import com.ztbase.system.domain.dto.BaseDTO;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

import java.math.BigDecimal;

/**
 * 

 * 工资发放途径表
 * 
 *
 * @author shunyuM
 * @since 2022-04-21
 */
@Data
@EqualsAndHashCode(callSuper = true)
@ApiModel(value = "SalaryWay对象", description = "工资发放途径表")
@Accessors(chain = true)
public class SalaryWayDTO extends BaseDTO implements IExcelDataModel, IExcelModel {

    private static final long serialVersionUID = 1L;

    private Integer rowNum;

    private String errorMsg;

    @ApiModelProperty(value = "员工姓名")
    @Excel(name = "员工姓名", orderNum = "2")
    private String employeeName;

    @ApiModelProperty(value = "月份")
    @Excel(name = "月份", orderNum = "1")
    private String months;

    @ApiModelProperty(value = "证件号码")
    @Excel(name = "身份证号", orderNum = "3", width = 20D)
    private String idCard;

    @ApiModelProperty(value = "手机号")
    @Excel(name = "手机号", orderNum = "4", width = 15D)
    private String mobile;

    @ApiModelProperty(value = "城市")
    @Excel(name = "城市", orderNum = "5")
    private String city;

    @ApiModelProperty(value = "岗位名称")
    @Excel(name = "岗位名称", orderNum = "6", width = 15D)
    private String jobName;

    @ApiModelProperty(value = "岗位职级")
    @Excel(name = "岗位级别", replace = {"初级_PRIMARY", "中级_INTERMEDIATE", "高级_SENIOR", "资深_VETERAN", "专家_EXPERT", "_EMPTY"},
            isImportField = "true", orderNum = "7")
    private JobRankEnums jobRank;

    @ApiModelProperty(value = "员工id")
    private Long employeeId;

    @ApiModelProperty(value = "员工状态：为0离职,为1正常,2试用")
    private Integer employeeStatus;

    @ApiModelProperty(value = "项目id")
    private Long projectId;

    @ApiModelProperty(value = "应发工资id")
    private Long payableSalaryId;

    @ApiModelProperty(value = "应发工资")
    @Excel(name = "应发工资", orderNum = "8")
    private BigDecimal payableSalary;

    @ApiModelProperty(value = "欣悦")
    @Excel(name = "欣悦", orderNum = "9")
    private BigDecimal xinYue;

    @ApiModelProperty(value = "易才")
    @Excel(name = "易才", orderNum = "10")
    private BigDecimal yiCai;

    @ApiModelProperty(value = "邦芒")
    @Excel(name = "邦芒", orderNum = "11")
    private BigDecimal bangMang;

    @ApiModelProperty(value = "北京外服")
    @Excel(name = "北京外服", orderNum = "12")
    private BigDecimal beijingForeignService;

    @ApiModelProperty(value = "云账户")
    @Excel(name = "云账户", orderNum = "13")
    private BigDecimal cloudAccount;

    @Override
    public void setRowNum(Integer rowNum) {
        this.rowNum = rowNum;
    }

    @Override
    public void setErrorMsg(String errorMsg) {
        this.errorMsg = errorMsg;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110

接收前端请求

导入

	@PostMapping(value = "/import")
    @ApiOperation(tags = "导入工资发放途径", value = "导入工资发放途径")
    public R<ImportResultDTO<SalaryWayDTO>> importInsurance(@RequestParam("file") MultipartFile file) throws Exception {
        ImportParams params = new ImportParams();
        params.setTitleRows(0);
        params.setHeadRows(1);
        params.setNeedVerify(true); // 开启校验
        params.setVerifyHandler(salaryWayImportVerifyHandler); // todo 在这里可以设置数据的校验格式
        ExcelImportResult<SalaryWayDTO> result = ExcelImportUtil.importExcelMore(file.getInputStream(), SalaryWayDTO.class, params);
        salaryWayService.importSalaryWay(result.getList());

        ImportResultDTO<SalaryWayDTO> resultDTO = new ImportResultDTO<>(result);
        return R.success(resultDTO);
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14

导出

	@GetMapping("/export")
    @ApiOperation(tags = "导出应发工资表", value = "导出应发工资表")
    public void exportExcel(SalaryWayQuery salaryWayQuery, HttpServletRequest request, HttpServletResponse response) {
        // salaryWayQuery.setSize(999);
        // salaryWayQuery.setCurrent(1L);
        // Page salaryWayPage = salaryWayService.listEditPage(salaryWayQuery);
        // List salaryWayDTOList = salaryWayPage.getRecords();
        List<SalaryWayDTO> salaryWayDTOList = new ArrayList<>();
        salaryWayDTOList.add(new SalaryWayDTO());
        if (CollectionUtils.isEmpty(salaryWayDTOList)) {
            return;
        }
        FileExportUtils exportUtils = FileExportUtils.getInstance();
        // todo 设置响应给浏览器的是文件
        exportUtils.exportPrepare("应发工资表.xlsx", request, response);
        try (OutputStream os = response.getOutputStream()) {
            // todo 调用FileExportUtils进行文件导出
            exportUtils.exportExcel(null, "应发工资表", SalaryWayDTO.class, salaryWayDTOList, os);
        } catch (IOException e) {
            throw BizException.build(ResultEnum.IO_OPERATION_FAILED);
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

导入时校验导入的数据

package com.ztbase.erp.handle.excel;

import cn.afterturn.easypoi.excel.entity.result.ExcelVerifyHandlerResult;
import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;
import com.ztbase.erp.domain.dto.SalaryWayDTO;
import com.ztbase.erp.domain.entity.Employee;
import com.ztbase.erp.domain.entity.PayableSalary;
import com.ztbase.erp.service.EmployeeService;
import com.ztbase.erp.service.PayableSalaryService;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.Objects;
import java.util.StringJoiner;

/**
 * @Author: shunyuM
 * @Date: 2022-11-05 18:45
 * @Description: 工资发放途径导入校验， 校验每一行数据
 */
@Component
public class SalaryWayImportVerifyHandler implements IExcelVerifyHandler<SalaryWayDTO> {

    @Resource
    private EmployeeService employeeService;
    @Resource
    private PayableSalaryService payableSalaryService;

    @Override
    public ExcelVerifyHandlerResult verifyHandler(SalaryWayDTO salaryWay) {
        StringJoiner errorMsg = new StringJoiner(";");
        Employee employee = employeeService.getByIdCard(salaryWay.getIdCard());
        if (Objects.isNull(employee)) {
            errorMsg.add(String.format("身份证号为%s的技术员不存在", salaryWay.getIdCard()));
            return new ExcelVerifyHandlerResult(false, errorMsg.toString());
        }
        Long employeeId = employee.getId();
        PayableSalary payableSalary = payableSalaryService.getByEmployeeIdAndMonths(employeeId, salaryWay.getMonths());
        if (Objects.isNull(payableSalary)) {
            errorMsg.add(String.format("技术员[%s]的没有找到应发薪资", employee.getName()));
            return new ExcelVerifyHandlerResult(false, errorMsg.toString());
        }
        Long payableSalaryId = payableSalary.getId();

        BigDecimal salary = salaryWay.getPayableSalary();
        fillingSalaryWay(salaryWay);
        BigDecimal wayTotal = BigDecimal.ZERO.add(salaryWay.getXinYue())
                .add(salaryWay.getBangMang())
                .add(salaryWay.getYiCai())
                .add(salaryWay.getCloudAccount())
                .add(salaryWay.getBeijingForeignService());
        if (!salary.equals(wayTotal)) {
            errorMsg.add(String.format("技术员[%s]工资发放途径总和不等于应发薪资", salaryWay.getEmployeeName()));
        }

        if (errorMsg.length() > 0) {
            return new ExcelVerifyHandlerResult(false, errorMsg.toString());
        }

        salaryWay.setEmployeeId(employeeId);
        salaryWay.setPayableSalaryId(payableSalaryId);
        salaryWay.setProjectId(employee.getProjectId());
        salaryWay.setEmployeeStatus(employee.getStatus());
        salaryWay.setOrgId(employee.getOrgId());
        salaryWay.setInternalCode(employee.getInternalCode());

        return new ExcelVerifyHandlerResult(true);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

导入导出工具类

package com.ztbase.system.util;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import com.ztbase.system.common.enums.ResultEnum;
import com.ztbase.system.exception.BizException;
import com.ztbase.system.handle.excel.BigDecimalHandler;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.util.Assert;
import org.springframework.web.context.request.RequestAttributes;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.ObjectOutputStream;
import java.io.OutputStream;
import java.io.PrintStream;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @description: 文件相关操作
 * @author: shunyuM
 * @create: 2019-09-10 11:19
 **/
@Slf4j
public class FileExportUtils {

    private static final String EXPORT_FILE_ERROR_MESSAGE = "导出文件出错，请稍后再试！";

    /**
     * 私有构造
     */
    private FileExportUtils() {
    }

    /**
     * 获取对象实例
     *
     * @return
     */
    public static FileExportUtils getInstance() {
        return new FileExportUtils();
    }

    /**
     * 导出文本为文件
     *
     * @param fileName    导出文件名称
     * @param fileContent 导出文件的内容
     * @param request     http请求对象
     * @param response    http响应对象
     */
    public void exportFile(String fileName, String fileContent, HttpServletRequest request, HttpServletResponse response) {
        try (OutputStream os = response.getOutputStream(); PrintStream ps = new PrintStream(os, true, "utf8")) {
            exportPrepare(fileName, request, response);
            ps.print(fileContent);
            ps.flush();
        } catch (IOException e) {
            log.error(EXPORT_FILE_ERROR_MESSAGE);
        }
    }

    /**
     * 直接导出response流中内容
     *
     * @param fileName 导出文件名称
     * @param request  http请求对象
     * @param response http响应对象
     */
    public void exportFile(String fileName, HttpServletRequest request, HttpServletResponse response) {
        try (OutputStream os = response.getOutputStream(); PrintStream ps = new PrintStream(os, true, "utf8")) {
            exportPrepare(fileName, request, response);
            ps.flush();
        } catch (IOException e) {
            log.error(EXPORT_FILE_ERROR_MESSAGE);
        }

    }

    /**
     * 导出文件, 使用对象反序列化处理
     *
     * @param fileName
     * @param object
     * @param request
     * @param response
     */
    public void exportFile(String fileName, Object object, HttpServletRequest request, HttpServletResponse response) {
        exportPrepare(fileName, request, response);
        try (OutputStream os = response.getOutputStream(); ObjectOutputStream oos = new ObjectOutputStream(os)) {
            oos.writeObject(object);
        } catch (IOException e) {
            log.error(EXPORT_FILE_ERROR_MESSAGE);
        }
    }

    /**
     * 导出文件, 使用对象反序列化处理
     *
     * @param fileName 文件名
     * @param object   导出对象
     */
    public void exportFile(String fileName, Object object) {
        RequestAttributes requestAttributes = RequestContextHolder.getRequestAttributes();
        Assert.isTrue(null != requestAttributes, "request attributes is required be non-blank");
        ServletRequestAttributes attributes = (ServletRequestAttributes) requestAttributes;
        HttpServletRequest request = attributes.getRequest();
        HttpServletResponse response = attributes.getResponse();
        exportFile(fileName, object, request, response);
    }

    /**
     * 导出文件前, 处理request和response的请求头, 使请求已文件形式被下载
     *
     * @param fileName
     * @param request
     * @param response
     */
    public void exportPrepare(String fileName, HttpServletRequest request, HttpServletResponse response) {
        String agent = request.getHeader("user-agent");
        String fireFox = "Firefox";
        try {
            if (agent.contains(fireFox)) {
                // 火狐浏览器
                Base64.Encoder encoder = Base64.getEncoder();
                fileName = "=?utf-8?B?" + encoder.encodeToString(fileName.getBytes("utf-8")) + "?=";
            } else {
                // 其它浏览器
                fileName = URLEncoder.encode(fileName, "utf-8");
            }
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setHeader("file-name", fileName);
            response.setContentType("application/vnd.ms-excel");
        } catch (Exception e) {
            log.error("set export env failed");
        }
    }

    public void exportExcel(String title, String sheetName, Class<?> pojoClass, Collection<?> dataSet, OutputStream os) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        BigDecimalHandler bigDecimalHandler = new BigDecimalHandler();
        List<String> excelField = Arrays.stream(pojoClass.getDeclaredFields())
                .map(field -> field.getAnnotation(Excel.class))
                .filter(Objects::nonNull)
                .map(Excel::name)
                .collect(Collectors.toList());
        bigDecimalHandler.setNeedHandlerFields(excelField.toArray(new String[excelField.size()]));
        exportParams.setDataHandler(bigDecimalHandler);
        try (Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, dataSet)) {
            workbook.write(os);
        } catch (IOException e) {
            throw BizException.build(ResultEnum.IO_OPERATION_FAILED);
        }
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162

2.复杂的excel可以先建造一个模板

模板

接收前端请求

	@GetMapping("/export")
    @ApiOperation(tags = "导出应发工资表", value = "导出应发工资表")
    public void exportExcel(PayableSalaryQuery payableSalaryQuery, HttpServletRequest request, HttpServletResponse response) {
        // 01 设置模板
        TemplateExportParams templateParams = new TemplateExportParams("templates/excel/salary_export_template_v1.0.xlsx", true);
        templateParams.setSheetName(new String[]{"应发工资表"});
        payableSalaryQuery.setCurrent(1L);
        payableSalaryQuery.setSize(999L);
        List<PayableSalaryDTO> payableSalaryDTOList = payableSalaryService.list(payableSalaryQuery).stream()
                .sorted(Comparator.comparing(PayableSalaryDTO::getProjectId).thenComparing(PayableSalaryDTO::getEmployeeId))
                .collect(toList());
        if (CollectionUtils.isEmpty(payableSalaryDTOList)) {
            return;
        }

        // 02 准备数据源，excel中的表达式即为map的key
        Map<String, Object> map = payableSalaryService.convertToExcelFormat(payableSalaryDTOList);
        
        // 03 导出
        FileExportUtils exportUtils = FileExportUtils.getInstance();
        exportUtils.exportPrepare("应发工资表.xlsx", request, response);
        try (Workbook workbook = ExcelExportUtil.exportExcel(templateParams, map)) {
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw BizException.build(ResultEnum.IO_OPERATION_FAILED);
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

附上官方文档地址：http://doc.wupaas.com/docs/easypoi

导出
FileExportUtils exportUtils = FileExportUtils.getInstance();
exportUtils.exportPrepare(“应发工资表.xlsx”, request, response);
try (Workbook workbook = ExcelExportUtil.exportExcel(templateParams, map)) {
workbook.write(response.getOutputStream());
} catch (IOException e) {
throw BizException.build(ResultEnum.IO_OPERATION_FAILED);
}
}

附上官方文档地址：http://doc.wupaas.com/docs/easypoi





1
2
3
4
5
6
7