关于java:初次邂逅-EasyExcel

47次阅读

共计 13688 个字符，预计需要花费 35 分钟才能阅读完成。

前言

因为工作起因，有这种需要，就是把数据库中的数据导出成 Excel 表格，同时，也得反对人家用 Excel 表格导入数据到数据库。以后我的项目也是在用 EasyExcel，所以我不得不学啦！

以前学习的过程中，有听过 EasyExcel 这么一个货色，不过素来没用过，所以，正好借此机会学习，看看如何应用它来实现需求。

在学习 EasyExcel 的这段时间里，也理解到工作中这种导入导出的需要还是挺常见的，所以决定记录下来。

官网文档：https://easyexcel.opensource.alibaba.com/docs/current/

需要

用户点击导入按钮，就可能上传 Excel 文件，将 Excel 文件的数据导入到零碎中。

用户勾选指标数据 id，点击导出按钮，就能将零碎中的数据以 Excel 文件的格局下载到本地。

剖析

导入 ，从用户的视角来看，就是导入 Excel 文件；从开发者的视角，或者说零碎的视角来看，就是 读取 用户的 Excel 文件的数据到零碎中（实际上是读取到计算机的内存中 ），最初将读取到的数据存储到数据库，EasyExcel 在导入的过程中进行了 读操作。

导出 ，同理，用户的视角就是导出，开发者的视角就是把零碎的数据 写入 到用户的计算机上，即 写操作。

简而言之，波及 IO 操作的，视角不同，说法不同（初学 IO 时就没搞清楚，为我后续的学习留下了大坑 T_T！）。

当然咱们也能够把导入说成写操作，毕竟数据是最终是存储在零碎的数据库中的，即写到了零碎的数据库里了。本人别搞混了就行。

筹备

本 Demo 应用 Spring Boot 构建，配合 MyBaits Plus，以 游戏数据 的导入和导出 作为需要；一些工具依赖如下：

<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.7.10</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.72</version>
</dependency>
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-lang3</artifactId>
    <version>3.1</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-devtools</artifactId>
    <scope>runtime</scope>
    <optional>true</optional>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

依赖

明天 EasyExcel 主菜，须要加其依赖能力食用~

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.7</version>
</dependency>

配置

server:
  port: 4790

spring:
  application:
    name: easyexcel-demo
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/easy_excel_demo?useUnicode=true&autoReconnect=true&serverTimezone=GMT%2B8&zeroDateTimeBehavior=convertToNull&characterEncoding=utf8
    username: root
    password: 123456

游戏实体类

咱们的游戏类就这些属性：id、游戏名称、价格、uuid，发售日期、创立工夫、批改工夫

/**
 * @author god23bin
 * @version 1.0
 * @description 游戏
 * @date 2022/10/21 16:51:02
 */
@Data
@TableName("t_game")
public class Game {@TableId(type = IdType.AUTO)
    private Long id;

    private String name;

    private Double price;

    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss" ,timezone = "GMT+8")
    private Date releaseDate;

    @TableField(fill = FieldFill.INSERT)
    private String uuid;

    @TableField(fill = FieldFill.INSERT)
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss" ,timezone = "GMT+8")
    private Date gmtCreate;

    @TableField(fill = FieldFill.INSERT_UPDATE)
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss" ,timezone = "GMT+8")
    private Date gmtModified;
}

模型

啥是模型？别慌，先假如须要导入的 Excel 表格长这样：

那么 这个就是所谓的模型 啦，不论是 读还是 写，都须要绝对应的对象，所以 个别会编写一个读对象的类和写对象的类，当然，如果读写的表头字段都是截然不同，间接一个类就能够了，导入导出都用这个类。

读对象

读对象 -GameImportExcelModel：

/**
 * @author god23bin
 * @version 1.0
 * @description Game 导入的 Excel 数据模型（读对象）* @date 2022/10/21 17:18:50
 */
@Data
public class GameImportExcelModel {

    private String name;

    private Double price;

    private Date releaseDate;
}

写对象

写对象 -GameExportExcelModel：

/**
 * @author god23bin
 * @version 1.0
 * @description Game 导出的 Excel 数据模型（写对象）* @date 2022/10/21 17:18:50
 */
@Data
public class GameExportExcelModel {@ExcelProperty("游戏 ID")
    private Long id;

    @ExcelProperty("游戏名")
    private String name;

    @ExcelProperty("价格")
    private Double price;

    @ExcelProperty("发售日期")
    private Date releaseDate;
}

实现

导入性能

用户点击导入按钮，就可能上传 Excel 文件，将 Excel 文件的数据导入到零碎中。

前端实现一个上传文件的按钮，后端就接管这个文件，读取这个文件的数据，存储到数据库中。

开胃菜 - 后端

搭个整体的代码框架先！

长久层

GameMapper：

@Mapper
public interface GameMapper extends BaseMapper<Game> {}
业务层

GameService：

/**
 * @author god23bin
 * @version 1.0
 * @description
 * @date 2022/11/8 14:36:43
 */
public interface GameService {

    /**
     * 导入 Excel 数据到数据库
     * @date 2022/11/8 14:38
     * @param file Excel 文件
     * @return boolean
     **/
    boolean importExcel(MultipartFile file);
    
}

GameServiceImpl：

/**
 * @author god23bin
 * @version 1.0
 * @description
 * @date 2022/11/8 14:40:08
 */
@Slf4j
@Service
public class GameServiceImpl extends ServiceImpl<GameMapper, Game> implements GameService {

    @Resource
    private GameMapper gameMapper;

    /**
     * 导入 Excel 数据到数据库
     *
     * @param file Excel 文件
     * @return boolean
     * @date 2022/11/8 14:38
     **/
    @Override
    public boolean importExcel(MultipartFile file) {
        // 这里就须要用到「读监听器」了，须要咱们本人实现
        return null;
    }

}
管制层

GameController：

/**
 * @author god23bin
 * @version 1.0
 * @description
 * @date 2022/11/8 14:31:50
 */
@RestController
public class GameController {

    @Resource
    private GameService gameService;

    @PostMapping("/excel/import/game")
    public ResponseEntity<String> importExcel(@RequestPart("file") MultipartFile file) {gameService.importExcel(file);
        return new ResponseEntity<>("OK", HttpStatus.OK);
    }
}

正餐 - 读数据须要用到的监听器

对于读取，有一个监听器须要咱们实现，依据文档的阐明，这个监听器是 不能够 让 Spring 来治理的。

有个很重要的点 DemoDataListener 不能被 spring 治理，要每次读取 excel 都要 new，而后外面用到 spring 能够构造方法传进去

所以咱们也 不须要 加上 @Component 注解把这个类作为组件让 Spring 扫描。间接一个一般的类就行。

具体代码如下，须要晓得的是：

  • 须要继承 AnalysisEventListener 类，参数化的类型（泛型）为 GameImportExcelModel（读对象）

GameImportExcelListener：

/**
 * @author god23bin
 * @version 1.0
 * @description
 * @date 2022/10/24 08:45:15
 */
@Slf4j
public class GameImportExcelListener extends AnalysisEventListener<GameImportExcelModel> {

    /**
     * 每隔 100 条存储到数据库，而后清理 list，不便内存回收
     */
    private static final int BATCH_COUNT = 100;

    /**
     * 缓存的数据
     */
    private List<Game> cachedDataList = new ArrayList<>(BATCH_COUNT);

    /**
     * 每解析一行数据就会执行这个办法
     *
     * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(GameImportExcelModel data, AnalysisContext context) {log.info("解析到一条数据:{}", JSON.toJSONString(data));
        Game game = new Game();
        BeanUtil.copyProperties(data, game);
        cachedDataList.add(game);
        if (cachedDataList.size() >= BATCH_COUNT) {saveData();
            // 存储实现清理 list
            cachedDataList.clear();}
    }

    private void saveData() {// 这里写存储到数据库的逻辑代码}

    /**
     * 解析完之后会执行这个办法，如果有其余事件须要做，能够在这里加上代码来实现
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保留数据，确保最初遗留的数据也存储到数据库
        saveData();
        log.info("所有数据解析实现！");
    }
}

然而！如果咱们想要应用 Spring IOC 治理对象，比方 Dao、Mapper 这些对象，当初以后类是用不了 @Autowired 注解将这些对象注入的，那咱们怎么获取它们？

办法就是：在该类中写一个构造方法，将这些被 Spring 治理的对象作为参数传入进来！

比方我这里须要用到 GameMapper 对象，那么就将它作构造方法的参数传进来。

@Slf4j
public class GameImportExcelListener extends AnalysisEventListener<GameImportExcelModel> {

    // 省略其余代码
    
    private GameMapper gameMapper;
    
    public GameImportExcelListener(GameMapper gameMapper) {this.gameMapper = gameMapper;}

    @Override
    public void invoke(GameImportExcelModel data, AnalysisContext context) {// ...}

    private void saveData() {// ...}

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {// ...}
}
残缺的监听器代码

GameImportExcelListener：

package cn.god23bin.demo.excel.listener;

import cn.god23bin.demo.entity.Game;
import cn.god23bin.demo.excel.bean.GameImportExcelModel;
import cn.god23bin.demo.mapper.GameMapper;
import cn.hutool.core.bean.BeanUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.List;

/**
 * @author zwb
 * @version 1.0
 * @description
 * @date 2022/10/24 08:45:15
 */
@Slf4j
public class GameImportExcelListener extends AnalysisEventListener<GameImportExcelModel> {

    /**
     * 每隔 100 条存储到数据库，而后清理 list，不便内存回收
     */
    private static final int BATCH_COUNT = 100;

    /**
     * 缓存的数据
     */
    private List<Game> cachedDataList = new ArrayList<>(BATCH_COUNT);

    private GameMapper gameMapper;

    public GameImportExcelListener(GameMapper gameMapper) {this.gameMapper = gameMapper;}

    /**
     * 每解析一行数据就会执行这个办法
     *
     * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(GameImportExcelModel data, AnalysisContext context) {log.info("解析到一条数据:{}", JSON.toJSONString(data));
        Game game = new Game();
        BeanUtil.copyProperties(data, game);
        cachedDataList.add(game);
        if (cachedDataList.size() >= BATCH_COUNT) {saveData();
            // 存储实现清理 list
            cachedDataList.clear();}
    }

    private void saveData() {
        // 这里写存储到数据库的逻辑代码
        for (Game game : cachedDataList) {gameMapper.insert(game);
        }
    }

    /**
     * 解析完之后会执行这个办法，如果有其余事件须要做，能够在这里加上代码来实现
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保留数据，确保最初遗留的数据也存储到数据库
        saveData();
        log.info("所有数据解析实现！");
    }
}

欠缺业务层

  1. 应用 EasyExcel.read() 办法构建一个 Excel reader builder，第一个参数是文件输出流，第二个参数是读对象，指定它这个 class 去读，第三个参数就是读监听器
  2. 接着链式调用 sheet() 办法和 doRead() 办法，实现整个 Excel 的读取操作。
@Slf4j
@Service
public class GameServiceImpl extends ServiceImpl<GameMapper, Game> implements GameService {

    @Resource
    private GameMapper gameMapper;

    /**
     * 导入 Excel 数据到数据库
     *
     * @param file Excel 文件
     * @return boolean
     * @date 2022/11/8 14:38
     **/
    @Override
    public boolean importExcel(MultipartFile file) {
        try {// 应用 EasyExcel.read() 办法构建一个 Excel reader builder，第一个参数是文件输出流，第二个参数是读对象，指定它这个 class 去读，第三个参数就是读监听器
            EasyExcel.read(file.getInputStream(), GameImportExcelModel.class, new GameImportExcelListener(gameMapper))
                    .sheet()
                    .doRead();} catch (IOException e) {log.error("Error importing: {}", e.getMessage());
            return false;
        }
        return true;
    }

}

测试

应用 Postman 测试，申请后端的导入 Excel 的接口，在 Postman 当选好 Post 申请并且输出申请门路。

点击 Headers 设置申请头：

  • Key 中输出 Content-Type，属性的值输出 multipart/form-data

点击 Body 设置申请体：

  • 抉择 form-data 格局，Key 中输出 file，便能够抉择文件进行上传了

测试后果：

能够看到 Excel 中的数据胜利存储到数据库中了，这就实现了导入的性能！

导出性能

用户勾选指标数据 id，点击导出按钮，就能将零碎中的数据以 Excel 文件的格局下载到本地。

细节：须要导出的文件名称为这种格局：游戏列表 -2022-11-11-12-30-00.xlsx

在这个导出文件的场景下，就须要后端返回前端文件数据，后端有两种形式能够返回，让前端进行下载。

  1. 后端返回文件所在的 URL，前端间接依据 URL 进行下载。
  2. 后端以二进制流的模式返回文件流，前端再承受这个流下载到本地。

因为咱们的数据是在数据库中的，并不是间接存储 Excel 文件的，所以咱们以二进制流的模式返回文件流给前端，让前端下载。

看看 EasyExcel 的 write 办法签名，外面有好多个重载的 write 办法，咱们看看这个就行：

public static ExcelWriterBuilder write(OutputStream outputStream, Class head)

参数阐明：

  • 第一个参数是文件输入流
  • 第二个参数是指定咱们要参照哪个模型类去写这个 Excel，head 意思就是该类的属性将作为 Excel 的表头。

代码

次要逻辑
// 假如这里是从数据库获取的汇合
List<Game> data = ...;
// 文件格式
String fileName = new String("");
String format = "yyyy-MM-dd-HH-mm-ss";
fileName = fileName + DateUtil.format(new Date(), format);
// 将数据写到输入流返回给前端
EasyExcel.write(ExcelUtil.getResponseOutputStream(fileName, response), GameExportExcelModel.class)
    .sheet("工作簿")
    .doWrite(dataList);
Excel 工具类
/**
 * @author god23bin
 * @version 1.0
 * @description Excel 工具类
 * @date 2022/11/18 17:55:48
 */
public class ExcelUtil {

    /**
     * 获取响应输入流
     * @date 2022/11/18 18:10
     * @param fileName 文件名
     * @param response 响应
     * @return java.io.OutputStream
     **/
    public static OutputStream getResponseOutputStream(String fileName, HttpServletResponse response) {
        try {
            // 给文件名编码，则前端接管后进行解码
            fileName = URLEncoder.encode(fileName, "UTF-8");
            // 指定客户端接管的响应内容类型为 Excel 以及字符编码为 UTF-8
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf8");
            // 让浏览器提供关上、保留的对话框，以附件的模式下载，同时设置文件名格局
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
            // 禁止缓存
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            return response.getOutputStream();} catch (IOException e) {e.printStackTrace();
        }
        return null;
    }
}

管制层

用 Set 汇合去重，避免屡次查问同一数据。

    @PostMapping("/excel/export/game")
    public ResponseEntity<String> exportExcel(@RequestBody Set<String> uuidList, HttpServletResponse response) {gameService.exportExcel(new ArrayList<>(uuidList), response);
        return new ResponseEntity<>("OK", HttpStatus.OK);
    }

业务层

次要逻辑是去数据库查问出须要导出的数据，而后转成对应的导出模型对象，最初应用 EasyExcel 的 write() 办法将数据写到输入流。

    /**
     * 导出数据库记录到 Excel
     *
     * @param uuidList uuid 汇合
     * @param response 响应
     * @return boolean
     * @date 2022/11/11 14:23
     **/
    @Override
    public boolean exportExcel(List<String> uuidList, HttpServletResponse response) {
        // 依据 uuid 找到须要导出的记录汇合
        LambdaQueryWrapper<Game> achievementWrapper = new LambdaQueryWrapper<>();
        achievementWrapper.in(Game::getUuid, uuidList);
        achievementWrapper.orderByDesc(Game::getGmtCreate);
        List<Game> games = this.baseMapper.selectList(achievementWrapper);
        // 将查问到的数据转换成对应 Excel 的导出模型对象
        List<GameExportExcelModel> dataList = games.stream().map(game -> {GameExportExcelModel gameExportExcelModel = new GameExportExcelModel();
            BeanUtil.copyProperties(game, gameExportExcelModel);
            return gameExportExcelModel;
        }).collect(Collectors.toList());
        // 文件格式
        String fileName = new String("");
        String format = "yyyy-MM-dd-HH-mm-ss";
        fileName = fileName + DateUtil.format(new Date(), format);
        // 将数据写到输入流返回给前端
        EasyExcel.write(ExcelUtil.getResponseOutputStream(fileName, response), GameExportExcelModel.class)
                .sheet("工作簿")
                .doWrite(dataList);
        return true;
    }

测试

以申请体的形式传递一个须要导出的游戏的 uuid 数组

点击 Send 按钮旁边的三角符号，点击 Send and Download，这样就能够下载了，最初下载的 Excel 关上后如下：

额，翻车，格局有点点问题，问题不大，这时候就须要一个自定义的拦截器帮咱们处理单元格。

自定义拦截器

拿来主义，写法根本是这样：

EasyExcel 导出主动适应列宽 Excel 款式

public class CustomCellWriteHandler extends AbstractColumnWidthStyleStrategy {

    private static final int MAX_COLUMN_WIDTH = 255;

    private Map<Integer, Map<Integer, Integer>> cache = new HashMap<>(16);

    public CustomCellWriteHandler() {}
    /**
     * Sets the column width when head create
     *
     * @param writeSheetHolder
     * @param cellDataList
     * @param cell
     * @param head
     * @param relativeRowIndex
     * @param isHead
     */
    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {Map<Integer, Integer> maxColumnWidthMap = cache.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {maxColumnWidthMap = new HashMap<>(16);
                cache.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {if (columnWidth > 255) {columnWidth = 255;}
                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), 7250);
                }
            }
        }
    }

    private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {if (isHead) {return cell.getStringCellValue().getBytes().length;} else {CellData cellData = (CellData) cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {return -1;} else {switch (type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
}

然而为什么这样写，好吧，目前不理解 T_T，有待钻研。

接着 注册这个拦截器，让它晓得该如何解决，批改业务层的代码：

EasyExcel.write(ExcelUtil.getResponseOutputStream(fileName, response), GameExportExcelModel.class)
                .sheet("工作簿")
                .registerWriteHandler(new CustomCellWriteHandler())
                .doWrite(dataList);

最初导出成果：

总结

某张 Excel 表须要你导入到零碎中，这里零碎指的就是你所做的我的项目，更精确来说将 Excel 的数据插入到你零碎中的数据库里。那么就能够应用 EasyExcel。

常见的需要就是对 Excel 数据的导入导出，咱们须要做的就是依据 Excel 表进行建模，创立对应的读对象和写对象。

对于导入，就须要读对象配合读监听器来实现。

对于导出，就间接通过 write 办法，以二进制流的形式将数据写到响应体中。

最初的最初

由自己程度所限，不免有谬误以及不足之处，屏幕前的靓仔靓女们 如有发现，恳请指出！

最初，谢谢你看到这里，谢谢你认真对待我的致力，心愿这篇博客对你有所帮忙！

你轻轻地点了个赞，那将在我的心里世界削减一颗亮堂而夺目的星！

正文完
java
发表至： java
2022-11-30
 0
关于java:SpringCloud-Hystrix
关于java:Spring源码分析spring容器总览
关于java:全局视角看技术Java多线程演进史
关于java:豆瓣平均-9x分布式领域的-5-本神书