EasyExcel导入导出

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>${easyexcel.version}</version>
</dependency>

<properties>
    <java.version>1.8</java.version>
    <ts-common.version>1.0.0-SNAPSHOT</ts-common.version>
    <mybatis-plus.version>3.4.2</mybatis-plus.version>
    <easyexcel.version>3.2.1</easyexcel.version>
</properties>

工具类:

public class ExportUtil {

    /**
     * 导出设置response属性
     * @param name 文件名
     * @param response 响应体
     * @throws UnsupportedEncodingException 异常信息
     */
    public static void setResponse(String name, HttpServletResponse response) throws UnsupportedEncodingException {
        response.setContentType(ExportConst.CONTENT_TYPE);
        response.setCharacterEncoding(StandardCharsets.UTF_8.name());
        String fileName = URLEncoder.encode(name, StandardCharsets.UTF_8.name()).replaceAll("\\+", "%20");
        response.setHeader(ExportConst.HEADER_NAME, ExportConst.HEADER_VALUE + fileName + ExportConst.SUFFIX);
    }

}

Controller:

/**
 * 项目类服务单验收导出
 *
 * @param searchBO 入参BO
 */
@PostMapping("project-check-accept-export")
public void projectCheckAcceptExport(@RequestBody OrderProjectCheckAcceptSearchBO searchBO, HttpServletResponse response) throws IOException {
    ExportUtil.setResponse(ExportConst.PROJECT_CHECK_ACCEPT, response);
    List<OrderProjectCheckExportVO> voList = orderService.projectCheckExport(searchBO);
    EasyExcel.write(response.getOutputStream(), OrderProjectCheckExportVO.class)
            .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
            .sheet(ExportConst.PROJECT_CHECK_ACCEPT + ExportConst.LIST).doWrite(voList);
}

 

1.导入:

/**
 * 服务商价格批量导入
 */
@PostMapping("unit-price-import")
public ResponseResult unitPriceImport(@RequestPart("file") MultipartFile file) throws IOException {
    EasyExcel.read(file.getInputStream(), UnitPriceImportReadBO.class, new PageReadListener<UnitPriceImportReadBO>(dataList -> {
        supplierUnitPriceService.unitPriceImport(dataList);
    }, 20000)).sheet().doRead();
    return ResponseResult.success("价格批量导入结束");
}

2.使用监听器导入:优点-可以获取导入内的数据返回

/**
 * excelListener
 *
 * @author wangdelongWX1
 * @date 2023/6/28 9:41
 */
public final class ExcelListener<T> extends AnalysisEventListener<T> {

    /**
     * 自定义用于暂时存储data
     * 可以通过实例获取该值
     */
    private List<T> dataList = new ArrayList<>();

    /**
     * 每解析一行都会回调invoke()方法
     *
     * @param object  读取后的数据对象
     * @param context 内容
     */
    @Override
    @SuppressWarnings("unchecked")
    public void invoke(Object object, AnalysisContext context) {
        T map = (T) object;
        //数据存储到list，供批量处理，或后续自己业务逻辑处理。
        dataList.add(map);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        //解析结束销毁不用的资源
        //注意不要调用dataList.clear(),否则getDataList为null
    }

    /**
     * 返回数据
     *
     * @return 返回读取的数据集合
     **/
    public List<T> getDataList() {
        return dataList;
    }

    /**
     * 设置读取的数据集合
     *
     * @param dataList 设置读取的数据集合
     **/
    public void setDataList(List<T> dataList) {
        this.dataList = dataList;
    }

Controller:

/**
 * 项目类订单批量导入
 */
@PostMapping("project-import")
public ResponseResult projectImport(@RequestPart("file") MultipartFile file) throws IOException {
    ExcelListener<ProjectImportReadBO> listener = new ExcelListener<>();
    EasyExcelFactory.read(file.getInputStream(), ProjectImportReadBO.class, listener).headRowNumber(1).sheet().doRead();
    List<ProjectImportReadBO> list = listener.getDataList();
    List<ErrorExportVO> voList = orderProcessService.projectImport(list);
    return ResponseResult.success(voList);
}