Java中使用EasyExcel

Java中使用EasyExcel

一：EasyExcel介绍

• EasyExcel是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。它能让你在不用考虑性能、内存的等因素的情况下，快速完成Excel的读、写等功能，主体由阿里团队封装并维护。
• 本文示例所呈现的主体功能为导入，其中涵盖导入过程中自动记录解析的数据错误，并以 excel 的形式返回给前端，以及 excel 模板校验错误等情况；

<!-- easyexcel -->
<dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>easyexcel</artifactId>
     <version>4.0.3</version>
</dependency>

对于简单使用：EasyExcel简单Demo，查看基础Demo示例

官网链接：EasyExcel官网

1.1、核心函数

导入数据

String filePath = "C:\\Users\\fkjas\\Desktop\\lixl\\test.xlsx";
EasyExcel.read(filePath, User.class, new ExcelListener()).sheet().doRead();

导出数据

String filePath = "C:\\Users\\fkjas\\Desktop\\lixl\\test.xlsx";
EasyExcel.write(filePath, User.class).sheet("学生信息表").doWrite(list);

1.2、项目实际应用

导入数据

List<User> userList = EasyExcel.read(newBufferedInputStream(file.getInputStream())).head(User.class).sheet().doReadSync();

导出数据

EasyExcel.write(httpServletResponse.getOutputStream(), User.class).sheet("用户信息").doWrite(userList);

1.3、相关注解

@ExcelProperty

作用

标注列名以及各列的前后顺序

示例

@ExcelProperty(index = 0, value = "姓名")
private String userName;

二：EasyExcel使用

2.1、导入功能

思路：读取文件-》创建ExcelListener-》在Listener中封装处理数据-》批量处理

    /**
     * 文件上传
     * <p>
     * 1. 创建excel对应的实体对象 参照{@link UploadData}
     * <p>
     * 2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link UploadDataListener}
     * <p>
     * 3. 直接读即可
     */
    @PostMapping("upload")
    @ResponseBody
    public String upload(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(), UploadData.class, new UploadDataListener(uploadDAO)).sheet().doRead();
        return "success";
    }

2.2、导出功能

思路1：创建导出数据集-》调用EasyExcel的能力-》批量处理–》将结果传递到浏览器请求中，直接响应到浏览器下载

   /**
     * 文件下载（失败了会返回一个有部分数据的Excel）
     * <p>
     * 1. 创建excel对应的实体对象 参照{@link DownloadData}
     * <p>
     * 2. 设置返回的 参数
     * <p>
     * 3. 直接写，这里注意，finish的时候会自动关闭OutputStream,当然你外面再关闭流问题不大
     */
    @GetMapping("download")
    public void download(HttpServletResponse response) throws IOException {
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        EasyExcel.write(response.getOutputStream(), DownloadData.class).sheet("模板").doWrite(data());
    }

思路2：创建导出数据集-》调用EasyExcel的能力-》批量处理–》将结果保存成为一个文件，记录任务，通过接口调用任务id进行下载文件

										//5.开始保存错误日志,将错误信息导出excel表格
                    //本地文件全路径
                    String fileExportPath = fileFolderPath + File.separator + taskCodeEnum.getDesc() + "-失败-" + DateUtil.getUniqueIdentifier() + "." + fileNamePrefix;
                    try (ExcelWriter excelWriter = EasyExcel.write(fileExportPath, WarehouseGoodsPriceErrorWriteReq.class).build()) {
                        // 这里注意 如果同一个sheet只要创建一次
                        WriteSheet writeSheet = EasyExcel.writerSheet("失败数据1").build();
                        excelWriter.write(failList, writeSheet);
                    } catch (Exception e) {
                        //如果Linux中出现null,https://easyexcel.opensource.alibaba.com/qa/#%E6%88%91%E5%9C%A8%E6%9C%AC%E5%9C%B0%E5%8F%AF%E4%BB%A5%E5%8F%91%E5%B8%83%E5%88%B0%E7%BA%BF%E4%B8%8A%E7%8E%AF%E5%A2%83%E6%80%8E%E4%B9%88%E4%B8%8D%E5%8F%AF%E4%BB%A5%E4%BA%86
                        //Linux下执行:yum install dejavu-sans-fonts fontconfig -y  /或者重装: sudo yum reinstall fontconfig -y
                        //确认: find /usr -name libfontconfig.so.1
                        log.error("easyExcel:导出excel失败", e);
                        puTaskRepo.updateTaskStatus(taskId, ExcelHandlerStatusEnum.FAILED.getCode());
                    }

三：EasyExcel完整代码，异步导入导出+任务管理

3.1、异步导入+记录导入失败文件导出+模版检查+模版参数检查

1、controller层：

    @ApiOperation(value = "导入excel模版,根据模版code来创建任务")
    @Valid
    @PostMapping("/importFile")
    public Result<Long> importFile(@ApiParam(name = "file", value = "文件")  @RequestParam("file") MultipartFile file,
                                     @ApiParam(name = "templateCode", value = "模版templateCode")  @NotBlank(message = "模版templateCode不能为空") String templateCode) {
        Long taskId= excelFileService.importFile(file, templateCode);
        return Result.ok(taskId);
    }

2、serviceImpl层：

@Override
    public Long importFile(MultipartFile multipartFile, String templateCode) {
        Map<String, String> map = ExcelImportTaskEnum.getMap();
        Long currentUserId = UserContext.getCurrentUserId();
        if (ObjectUtil.isEmpty(map)) {
            throw new ServiceException("任务不存在");
        }
        if (ObjectUtil.isNotEmpty(map)) {
            if (!map.containsKey(templateCode)) {
                throw new ServiceException("不支持此类任务,请联系管理员");
            }
        }
        Template template = templateRepo.getByCode(templateCode);
        if (ObjectUtil.isEmpty(template)) {
            throw new ServiceException("模板不存在");
        }
        String originalFilename = multipartFile.getOriginalFilename();
        String fileNamePrefix = FileUtil.getFileNamePrefix(originalFilename);
        ExcelImportTaskEnum taskCodeEnum = ExcelImportTaskEnum.getEnum(template.getCode());
        Long taskId;
        switch (taskCodeEnum) {
            //批量仓库门店价格修改
            case WAREHOUSE_GOODS_PRICE:
                taskId = handlerImportWarehouseGoodsPrice(currentUserId, multipartFile, taskCodeEnum, fileNamePrefix, template);
                break;
            default:
                throw new ServiceException("不支持此类型模版code");
        }
        return taskId;
    }
    
    
    /**
     * 获取文件名只获取后缀
     *
     * @param fileName
     * @return
     */
    public static String getFileNamePrefix(String fileName) {
        String extName = fileName.substring(fileName.lastIndexOf(".") + 1);
        return ObjectUtil.isEmpty(extName) ? "" : extName;
    }

3、枚举方法

package com.kkd.web.admin.model.enums;

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;

import java.util.EnumSet;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @beLongProjecet: kkd-all
 * @beLongPackage: com.kkd.web.admin.model.enums
 * @author: xiaoxiangyuan
 * @createTime: 2024/08/23 15:05
 * @description:
 * @version: v1.0
 */
@Getter
@AllArgsConstructor
@Slf4j
public enum ExcelImportTaskEnum {

    //模版任务
    WAREHOUSE_GOODS_PRICE("warehouseGoodsPrice", "批量修改仓库商品价格"),

    ;
    private String code;
    private String desc;

    public static ExcelImportTaskEnum getEnum(String code) {
        for (ExcelImportTaskEnum thirdTypeEnum : ExcelImportTaskEnum.values()) {
            if (thirdTypeEnum.getCode().equals(code)) {
                return thirdTypeEnum;
            }
        }
        return null;
    }

    public static String getDesc(String code) {
        for (ExcelImportTaskEnum thirdTypeEnum : ExcelImportTaskEnum.values()) {
            if (thirdTypeEnum.getCode().equals(code)) {
                return thirdTypeEnum.getDesc();
            }
        }
        return null;
    }


    public static boolean checkCodeExist(String code) {
        for (ExcelImportTaskEnum thirdTypeEnum : ExcelImportTaskEnum.values()) {
            if (thirdTypeEnum.getCode().equals(code)) {
                return true;
            }
        }
        return false;
    }

    public static Map<String, String> getMap() {
        Map<String, String> map = EnumSet.allOf(ExcelImportTaskEnum.class).stream()
                .collect(Collectors.toMap(ExcelImportTaskEnum::getCode, ExcelImportTaskEnum::getDesc));
        return map;
    }

    public static void main(String[] args) {
        log.info("getMap()的值为:{}", getMap());
    }
}

/**
     * 批量仓库门店价格修改
     *
     * @param currentUserId
     * @param multipartFile
     * @param taskCodeEnum
     * @param fileNamePrefix
     * @param template
     */
    private Long handlerImportWarehouseGoodsPrice(Long currentUserId, MultipartFile multipartFile, ExcelImportTaskEnum taskCodeEnum, String fileNamePrefix, Template template) {
        //1.校验模版头信息
        try {
            CheckHeaderListener headerValidationListener = new CheckHeaderListener(WarehouseGoodsPriceReadReq.class);
            EasyExcel.read(new BufferedInputStream(multipartFile.getInputStream()), WarehouseGoodsPriceReadReq.class, headerValidationListener).headRowNumber(1).sheet().doRead();
        } catch (IOException e) {
            throw new ServiceException("导入文件失败,请稍后重试");
        }
        //2.将导入文件临时存入服务器
        String fileFolderPath = getFileFolderPath();
        FileUtil.createFolder(fileFolderPath);
        //本地文件全路径
        String fileImportPath = fileFolderPath + File.separator + taskCodeEnum.getDesc() + "-" + DateUtil.getUniqueIdentifier() + "." + fileNamePrefix;
        try {
            //将文件保存到指定路径
            multipartFile.transferTo(new File(fileImportPath));
        } catch (IOException e) {
            log.error("保存文件异常", e);
            throw new ServiceException("保存文件异常");
        }
        //3.创建任务
        PuTask puTask = new PuTask();
        puTask.setFunctionType(ExcelFunctionTypeEnum.IMPORT_FILE.getCode());
        puTask.setOrgId(UserContext.getCurrentOrgId());
        puTask.setOperatorId(currentUserId);
        puTask.setTemplateCode(template.getCode());
        puTask.setStatus(ExcelHandlerStatusEnum.IN_PROGRESS.getCode());
        puTaskRepo.save(puTask);
        Long taskId = puTask.getId();
        try {
            //4.提交异步任务
            CompletableFuture<Void> completableFuture = CompletableFuture.runAsync(() -> {
                puTaskRepo.updateTaskStatus(taskId, ExcelHandlerStatusEnum.IN_PROGRESS.getCode());
                WarehouseGoodsPriceListener listener = new WarehouseGoodsPriceListener();
                listener.setCurrentUserId(currentUserId);
                EasyExcel.read(fileImportPath, WarehouseGoodsPriceReadReq.class, listener).sheet().doRead();
                Integer successCount = listener.getSuccessCount();
                Integer allCount = listener.getAllCount();
                List<WarehouseGoodsPriceErrorWriteReq> failList = listener.getFailList();
                Integer errorCount = ObjectUtil.isEmpty(failList) ? 0 : failList.size();
                String handlerResult = "总条数:" + allCount + ",成功条数:" + successCount + ",失败条数:" + errorCount;
                puTask.setHandleResult(handlerResult);
                if (ObjectUtil.isNotEmpty(failList)) {
                    //5.开始保存错误日志,将错误信息导出excel表格
                    //本地文件全路径
                    String fileExportPath = fileFolderPath + File.separator + taskCodeEnum.getDesc() + "-失败-" + DateUtil.getUniqueIdentifier() + "." + fileNamePrefix;
                    try (ExcelWriter excelWriter = EasyExcel.write(fileExportPath, WarehouseGoodsPriceErrorWriteReq.class).build()) {
                        // 这里注意 如果同一个sheet只要创建一次
                        WriteSheet writeSheet = EasyExcel.writerSheet("失败数据1").build();
                        excelWriter.write(failList, writeSheet);
                    } catch (Exception e) {
                        //如果Linux中出现null,https://easyexcel.opensource.alibaba.com/qa/#%E6%88%91%E5%9C%A8%E6%9C%AC%E5%9C%B0%E5%8F%AF%E4%BB%A5%E5%8F%91%E5%B8%83%E5%88%B0%E7%BA%BF%E4%B8%8A%E7%8E%AF%E5%A2%83%E6%80%8E%E4%B9%88%E4%B8%8D%E5%8F%AF%E4%BB%A5%E4%BA%86
                        //Linux下执行:yum install dejavu-sans-fonts fontconfig -y  /或者重装: sudo yum reinstall fontconfig -y
                        //确认: find /usr -name libfontconfig.so.1
                        log.error("easyExcel:导出excel失败", e);
                        puTaskRepo.updateTaskStatus(taskId, ExcelHandlerStatusEnum.FAILED.getCode());
                    }
                    puTask.setErrorData(JSON.toJSONString(failList));
                    puTask.setErrorUrl(fileExportPath);
                }
                puTaskRepo.updateById(puTask);
            }, poolExecutor);
            //6.同步数据库状态,删除临时文件
            completableFuture.thenRun(() -> {
                //修改状态已完成
                puTaskRepo.updateTaskStatus(taskId, ExcelHandlerStatusEnum.COMPLETED.getCode());
                //删除临时文件
                FileUtil.deleteTempFile(new File(fileImportPath));
            });
        } catch (Exception e) {
            log.error("处理失败,taskId:{},失败原因:{}", taskId, e.getMessage(), e);
            puTaskRepo.updateTaskStatus(taskId, ExcelHandlerStatusEnum.FAILED.getCode());
        }
        return taskId;
    }

4.检查模板Listener

package com.kkd.web.admin.model.excel.base;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.kkd.web.admin.model.util.EasyExcelUtil;
import lombok.extern.slf4j.Slf4j;

import java.util.Map;

@Slf4j
public class CheckHeaderListener extends AnalysisEventListener<Object> {
    private final Class<?> clazz;
    private boolean position = true;

    public CheckHeaderListener(Class<?> clazz) {
        this.clazz = clazz;
    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        EasyExcelUtil.checkExcelHeader(headMap, clazz);
        position = false;
    }

    @Override
    public void invoke(Object data, AnalysisContext context) {

    }

    /**
     * 通过该方法控制是否继续解析
     * @param context
     * @return
     */
    @Override
    public boolean hasNext(AnalysisContext context) {
        return position;
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
    }
}

package com.kkd.web.admin.model.excel.warehouse.goodsPrice.read.req;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

/**
 * @beLongProjecet: kkd-all
 * @beLongPackage: com.kkd.web.admin.test.excel
 * @author: xiaoxiangyuan
 * @createTime: 2024/12/17 17:28
 * @description:
 * @version: v1.0
 */
@Data
public class WarehouseGoodsPriceReadReq {
    @ExcelProperty(index = 0,value = "*组织ID")
    private String orgId;

    @ExcelProperty(index = 1,value = "*SKU编码")
    private String sku;

    @ExcelProperty(index = 2,value = "*门店编码")
    private String qnhStoreId;

    @ExcelProperty(index = 3,value = "*商品零售价（元）")
    private String onLinePrice;
}

    /**
     * 创建文件夹
     *
     * @param path
     */
    public static void createFolder(String path) {
        File folder = new File(path);
        if (!folder.exists()) {
            folder.mkdirs();
        }
    }
    
    
        /**
     * 获取存储文件夹路径
     *
     * @return
     */
    private String getFileFolderPath() {
        //获取本地文件夹地址
        String savePath = null;
        if (SystemUtil.SystemNameEnum.TYPE1.getDesc().equals(SystemUtil.getSystemName())) {
            savePath = windowsPath;
        } else if (SystemUtil.SystemNameEnum.TYPE2.getDesc().equals(SystemUtil.getSystemName())) {
            savePath = linuxPath;
        } else {
            savePath = macPath;
        }
        return savePath;
    }

package com.kkd.common.model.util;

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;

/**
 * @beLongProjecet: ai_ability_gateway
 * @beLongPackage: com.wondertek.util
 * @author: xiaoxiangyuan
 * @createTime: 2022/09/26 9:53
 * @description:
 * @version: v1.0
 */
@Slf4j
public class SystemUtil {

    /**
     * desc: 获取当前运行系统名称
     *
     * @param
     * @return
     */
    public static String getSystemName() {
        String os = System.getProperty("os.name");
        //Windows操作系统
        if (os != null && os.toLowerCase().startsWith("windows")) {
            log.debug("当前系统版本是:{}", os);
            return SystemNameEnum.TYPE1.getDesc();
        } else if (os != null && os.toLowerCase().startsWith("linux")) {//Linux操作系统
            log.debug("当前系统版本是:{}", os);
            return SystemNameEnum.TYPE2.getDesc();
        } else { //其它操作系统
            log.debug("当前系统版本是:{}", os);
            return SystemNameEnum.TYPE3.getDesc();
        }

    }


    @AllArgsConstructor
    @Getter
    public enum SystemNameEnum {
        //
        TYPE1("windows"),
        TYPE2("linux"),
        TYPE3("mac");
        private String desc;

    }
}

package com.kkd.web.admin.model.excel.warehouse.goodsPrice.write.req;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import com.alibaba.excel.enums.poi.FillPatternTypeEnum;
import com.kkd.web.admin.model.excel.warehouse.goodsPrice.read.req.WarehouseGoodsPriceReadReq;
import lombok.Data;

/**
 * @beLongProjecet: kkd-all
 * @beLongPackage: com.kkd.web.admin.test.excel
 * @author: xiaoxiangyuan
 * @createTime: 2024/12/17 17:28
 * @description:
 * @version: v1.0
 */
@Data
@HeadRowHeight(35)
@ColumnWidth(30)
@ContentRowHeight(20)
public class WarehouseGoodsPriceErrorWriteReq extends WarehouseGoodsPriceReadReq {

    @ExcelProperty(index = 4,value = "失败原因")
    @HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 10)
    private String errorMsg;

}

    /**
     * 删除本地临时文件
     *
     * @param file :删除file对象
     */
    public static void deleteTempFile(File file) {
        if (file != null) {
            File del = new File(file.toURI());
            del.delete();
        }
    }

CREATE TABLE `pu_task` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `org_id` bigint NOT NULL DEFAULT '0' COMMENT '组织id',
  `function_type` tinyint(1) NOT NULL DEFAULT '0' COMMENT '功能 0:导入  1:导出',
  `operator_id` bigint NOT NULL COMMENT '操作人',
  `template_code` varchar(64) NOT NULL COMMENT '模版code',
  `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '执行状态  0:未执行  1:执行中   2:执行完成   3:执行失败',
  `handle_result` varchar(64) NOT NULL DEFAULT '' COMMENT '执行结果',
  `success_url` varchar(255) DEFAULT '' COMMENT '处理导出成功数据文件路径',
  `error_url` varchar(255) DEFAULT '' COMMENT '处理失败数据文件路径',
  `error_data` text COMMENT '处理失败数据',
  `redundancy` varchar(255) DEFAULT NULL COMMENT '冗余字段',
  `is_delete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除状态  0:未删除  1:已删除',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  KEY `idx_org_id` (`org_id`),
  KEY `idx_template_code` (`template_code`),
  KEY `idx_operator_id` (`operator_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='导入导出任务表';

CREATE TABLE `pu_template` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL DEFAULT '' COMMENT '模版名称',
  `code` varchar(64) NOT NULL DEFAULT '' COMMENT '模版code',
  `url` varchar(255) NOT NULL DEFAULT '' COMMENT '模板链接',
  `is_delete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除状态  0:未删除  1:已删除',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `create_user_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '创建者',
  `update_user_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '更新者',
  PRIMARY KEY (`id`),
  KEY `idx_code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='导入导出模板管理';

3.2、下载导出文件

@ApiOperation(value = "下载处理异常的excel,根据任务id")
@Valid
@GetMapping("/downloadFile")
public Result<String> downloadFile(HttpServletResponse httpServletResponse,
                                 @ApiParam(name = "taskId", value = "任务ID")  @NotNull(message = "任务id不能为空") Long taskId,
                                 @ApiParam(name = "functionType", value = "功能 0:导入  1:导出")  @NotNull(message = "任务类型functionType不能为空") @Min(value = 0,message = "最小为0") @Max(value = 1,message = "最大为1")Integer functionType,
                                 @ApiParam(name = "downloadType", value = "下载成功/失败文件,导入类型只有失败文件  0:成功  1:失败") @NotNull(message = "下载类型downloadType不能为空") @Min(value = 0,message = "最小为0") @Max(value = 1,message = "最大为1")Integer downloadType

) {
    excelFileService.downloadFile(httpServletResponse, taskId,functionType,downloadType);
    return Result.ok();
}

@Override
public void downloadFile(HttpServletResponse httpServletResponse, Long taskId, Integer functionType, Integer downloadType) {
    PuTask puTask = puTaskRepo.getById(taskId);
    if (ObjectUtil.isEmpty(puTask)) {
        throw new ServiceException("任务不存在");
    }
    String path = getFileUrl(functionType, downloadType, puTask);
    String fileName = Paths.get(path).getFileName().toString();
    try {
        // 将数据用户信息导出成Excel文件并以流的形式返回
        httpServletResponse.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        // 设置字符集
        httpServletResponse.setCharacterEncoding("utf-8");
        // 设置文件名，并且进行编码
        fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
        // 告诉浏览器将以下载的方式处理响应，而不是在浏览器中直接打开(attachment表示下载、filename*=utf-8''" + fileName + ".xlsx"表示下载的文件名)
        httpServletResponse.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);
        File file = new File(path);
        if (!file.exists()) {
            throw new ServiceException("文件不存在:" + path);
        }
        // 将文件内容写入到响应流中
        try (InputStream inputStream = new FileInputStream(file); OutputStream outputStream = httpServletResponse.getOutputStream()) {
            byte[] buffer = new byte[8192];
            int bytesRead;
            while ((bytesRead = inputStream.read(buffer)) != -1) {
                outputStream.write(buffer, 0, bytesRead);
            }
            outputStream.flush();
        }
    } catch (Exception e) {
        throw new ServiceException("导出文件失败,请联系管理员");
    }
}

3.3、异常情况处理

1、字符库缺失：Mac或者Windows正常，Linux报错

java.lang.NullPointerException: null
        at sun.awt.FontConfiguration.getVersion(FontConfiguration.java:1264)
        at sun.awt.FontConfiguration.readFontConfigFile(FontConfiguration.java:219)
        at sun.awt.FontConfiguration.init(FontConfiguration.java:107)
        at sun.awt.X11FontManager.createFontConfiguration(X11FontManager.java:774)
        at sun.font.SunFontManager$2.run(SunFontManager.java:441)
        at java.security.AccessController.doPrivileged(Native Method)
        at sun.font.SunFontManager.<init>(SunFontManager.java:386)
        at sun.awt.FcFontManager.<init>(FcFontManager.java:35)
        at sun.awt.X11FontManager.<init>(X11FontManager.java:57)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at java.lang.Class.newInstance(Class.java:442)
        at sun.font.FontManagerFactory$1.run(FontManagerFactory.java:83)
        at java.security.AccessController.doPrivileged(Native Method)
        at sun.font.FontManagerFactory.getInstance(FontManagerFactory.java:74)
        at java.awt.Font.getFont2D(Font.java:491)
        at java.awt.Font.canDisplayUpTo(Font.java:2064)
        at java.awt.font.TextLayout.singleFont(TextLayout.java:470)
        at java.awt.font.TextLayout.<init>(TextLayout.java:531)
        at org.apache.poi.ss.util.SheetUtil.getDefaultCharWidthAsFloat(SheetUtil.java:352)
        at org.apache.poi.xssf.streaming.AutoSizeColumnTracker.<init>(AutoSizeColumnTracker.java:117)
        at org.apache.poi.xssf.streaming.SXSSFSheet.<init>(SXSSFSheet.java:106)
        at org.apache.poi.xssf.streaming.SXSSFWorkbook.createAndRegisterSXSSFSheet(SXSSFWorkbook.java:694)
        at org.apache.poi.xssf.streaming.SXSSFWorkbook.createSheet(SXSSFWorkbook.java:712)
        at org.apache.poi.xssf.streaming.SXSSFWorkbook.createSheet(SXSSFWorkbook.java:104)
        at com.alibaba.excel.util.WorkBookUtil.createSheet(WorkBookUtil.java:86)
        at com.alibaba.excel.context.WriteContextImpl.createSheet(WriteContextImpl.java:223)
        at com.alibaba.excel.context.WriteContextImpl.initSheet(WriteContextImpl.java:203)
        at com.alibaba.excel.context.WriteContextImpl.currentSheet(WriteContextImpl.java:135)
        at com.alibaba.excel.write.ExcelBuilderImpl.addContent(ExcelBuilderImpl.java:54)
        at com.alibaba.excel.ExcelWriter.write(ExcelWriter.java:73)
        at com.alibaba.excel.ExcelWriter.write(ExcelWriter.java:50)
        at com.kkd.web.admin.service.impl.ExcelFileServiceImpl.handlerExcelWarehouseGoodsPrice(ExcelFileServiceImpl.java:209)
        at com.kkd.web.admin.service.impl.ExcelFileServiceImpl.importFile(ExcelFileServiceImpl.java:142)
        at com.kkd.web.admin.controller.excel.ExcelFileController.importFile(ExcelFileController.java:43)
        at com.kkd.web.admin.controller.excel.ExcelFileController$$FastClassBySpringCGLIB$$1c09eef6.invoke(<generated>)
        at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:793)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
        at org.springframework.validation.beanvalidation.MethodValidationInterceptor.invoke(MethodValidationInterceptor.java:123)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:708)
        at com.kkd.web.admin.controller.excel.ExcelFileController$$EnhancerBySpringCGLIB$$5472f61d.importFile(<generated>)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
        at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150)
        at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)
        at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1071)
        at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:964)
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
        at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:696)
        at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:779)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
        at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
        at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
        at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:360)
        at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:399)
        at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
        at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:893)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1789)
        at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
        at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
        at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.lang.Thread.run(Thread.java:750)

文档：EasyExcel官方解释

异常原因：Linux上安装的jdk是openjdk,其中字体库缺失导致

方法一：更换jdk版本，将openjdk更换为sun下的jdk即可解决
方法二：Linux下执行:yum install dejavu-sans-fonts fontconfig -y  
   		或者重装: sudo yum reinstall fontconfig -y
   		确认: find /usr -name libfontconfig.so.1

最后：重新启动项目即可