05 easyPoi的使用

使用easyPoi导出导入excel

1. 引入依赖

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>4.2.0</version>
</dependency>

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;

/**
 * <p>
 * 工资发放途径表
 * </p>
 *
 * @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;
    }
}

接收前端请求

导入

	@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);
    }

导出

	@GetMapping("/export")
    @ApiOperation(tags = "导出应发工资表", value = "导出应发工资表")
    public void exportExcel(SalaryWayQuery salaryWayQuery, HttpServletRequest request, HttpServletResponse response) {
        // salaryWayQuery.setSize(999);
        // salaryWayQuery.setCurrent(1L);
        // Page<SalaryWayDTO> salaryWayPage = salaryWayService.listEditPage(salaryWayQuery);
        // List<SalaryWayDTO> 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);
        }
    }

导入时校验导入的数据

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);
    }
}

导入导出工具类

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);
        }
    }

}

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);
        }
    }

附上官方文档地址：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