JAVA使用easyexcel导入获取多sheet实现

1.引入maven依赖

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

2.新建实体类

演示导入两个sheet，@ExcelProperty(index = 0)代表第一列对应的字段

 @Data
public class BoxServerConfig {

    @ExcelProperty(index = 0)
    private String sn;

    @ExcelProperty(index = 1)
    private String name;

    @ExcelProperty(index = 2)
    private String ip;

}

@Data
public class PlatformConfig {

    @ExcelProperty(index = 0)
    private String NTP;

    @ExcelProperty(index = 1)
    private String log;

    @ExcelProperty(index = 2)
    private String MQTT;

    @ExcelProperty(index = 3)
    private String MQTTPort;

    @ExcelProperty(index = 4)
    private String usr;

    @ExcelProperty(index = 5)
    private String passwd;

}

3.新建监听类

 public class ExcelListener extends AnalysisEventListener<Object> {
    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelListener.class);

    private List<Object> data = new ArrayList<>();
 
    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(o));
        data.add(o);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();
        LOGGER.info("所有数据解析完成！");
    }

    /**
     * 入库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", data.size());
        //这个方法自己实现  能完成保存数据入库即可
        LOGGER.info("存储数据库成功！");
    }

    public List<Object> getData() {
        return data;
    }

    public void setData(List<Object> data) {
        this.data = data;
    }
}

4.控制层代码

    public void importDetail(@RequestParam(value = "file") MultipartFile serviceFile) throws IOException {
        ExcelReader excelReader = null;
        InputStream in = null;
        try {
            in = serviceFile.getInputStream();
            excelReader = EasyExcel.read(in).build();

            ExcelListener boxServerListener = new ExcelListener();
            ExcelListener platformListener = new ExcelListener();

            //获取sheet对象
            ReadSheet readBoxServerSheet =
                    EasyExcel.readSheet(0).head(BoxServerConfig.class).registerReadListener(boxServerListener).build();
            ReadSheet readPlatformSheet =
                    EasyExcel.readSheet(1).head(PlatformConfig.class).registerReadListener(platformListener).build();

            //读取数据
            excelReader.read(readBoxServerSheet, readPlatformSheet);

            //业务处理
            System.out.println(boxServerListener.getData());
            System.out.println(platformListener.getData());

        
        } catch (Exception ex) {
            logger.error("import excel to db fail", ex);
        } finally {
            in.close();
            if (excelReader != null) {
                excelReader.finish();
            }
        }
    }

5.踩坑记录

如果在项目中已经使用了POI，再引入easyexcel可能会存在冲突，要注意对应的版本

官方文档：
https://www.yuque.com/easyexcel/doc/quickstart.