SpringBoot+EasyExcel实现excel导入功能（含postman调试教程）

￡神魔★判官ぃ 2023-07-11 15:29 93阅读 0赞

**需求**：现在需要完成通过post请求上传excel文件，并保存入库的功能

**技术**：SpringBoot + EasyExcel + postman

--------------------

###  1.新增EasyExcel 依赖 ###

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

###  2.新增excel数据对应的对象 ###

package com.example.demo.model;
    
    import com.alibaba.excel.annotation.ExcelProperty;
    import lombok.Data;
    
    /**
     * @author LWong
     * @date 2020/03/06
     */
    @Data
    public class ExportInfo {
        //@ExcelProperty注解可以使用value或者index进行匹配字段，不建议 index 和 name 同时用
        @ExcelProperty(value = "web-scraper-order", index = 0)
        private String order;
    
        @ExcelProperty(value = "web-scraper-start-url", index = 1)
        private String url;
    
        @ExcelProperty(value = "num", index = 2)
        private String num;
    
    }

对应的excel数据是这样的，@ExcelProperty对应着表头信息

![20200306170034464.png][]

### 3. 新建监听器 ###

package com.example.demo.util.excel;
    
    import com.alibaba.excel.context.AnalysisContext;
    import com.alibaba.excel.event.AnalysisEventListener;
    import com.alibaba.fastjson.JSON;
    import com.google.common.collect.Lists;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    import java.util.List;
    
    
    /**
     * @author LWong
     * @date 2020/03/06
     */
    public class ExcelListener extends AnalysisEventListener {
    
        private final static Logger LOGGER = LoggerFactory.getLogger(ExcelListener.class);
    
        private static final int BATCH_COUNT = 5;
    
         private List<Object> datas = Lists.newArrayList();
    
        @Override
        public void invoke(Object o, AnalysisContext analysisContext) {
            LOGGER.info("解析到一条数据:{}", JSON.toJSONString(o));
            //数据存储到list，
            datas.add(o);
            // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
            if (datas.size() >= BATCH_COUNT) {
                saveData();
                // 存储完成清理 list
                datas.clear();
            }
        }
    
        @Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            saveData();
            LOGGER.info("所有数据解析完成！");
        }
    
        /**
         * 入库
         */
        private void saveData() {
            LOGGER.info("{}条数据，开始存储数据库！", datas.size());
            //这个方法自己实现  能完成保存数据入库即可
            demoDAO.save(datas);
            LOGGER.info("存储数据库成功！");
        }
    
        public List<Object> getDatas() {
            return datas;
        }
    
        public void setDatas(List<Object> datas) {
            this.datas = datas;
        }
    }

### 4.编写接口，即数据入口 ###

package com.example.demo.controller;
    
    import com.alibaba.excel.EasyExcel;
    import com.alibaba.excel.ExcelReader;
    import com.alibaba.excel.read.metadata.ReadSheet;
    import com.example.demo.model.ExportInfo;
    import com.example.demo.util.excel.ExcelListener;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.web.bind.annotation.PostMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestParam;
    import org.springframework.web.bind.annotation.RestController;
    import org.springframework.web.multipart.MultipartFile;
    
    import java.io.IOException;
    import java.io.InputStream;
    
    /**
     * @author LWong
     * @date 2020/03/06
     */
    @RestController
    @RequestMapping("excel")
    public class ExcelController {
    
        private final static Logger logger = LoggerFactory.getLogger(ExcelController.class);
    
        @PostMapping("import")
        public void excelImport(@RequestParam(value = "file") MultipartFile serviceFile) throws IOException {
            ExcelReader excelReader = null;
            InputStream in = null;
            try {
                in = serviceFile.getInputStream();
                excelReader = EasyExcel.read(in, ExportInfo.class,
                        new ExcelListener()).build();
                ReadSheet readSheet = EasyExcel.readSheet(0).build();
                excelReader.read(readSheet);
            } catch (IOException ex) {
                logger.error("import excel to db fail", ex);
            } finally {
                in.close();
                // 这里一定别忘记关闭，读的时候会创建临时文件，到时磁盘会崩
                if (excelReader != null) {
                    excelReader.finish();
                }
            }
        }
    }

### 5.postman调试接口 ###

postman上传文件教程：[https://blog.csdn.net/maowendi/article/details/80537304][https_blog.csdn.net_maowendi_article_details_80537304]

注意body的key是file  即@RequestParam指定的value![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzI5Nzc0NDc5_size_16_color_FFFFFF_t_70][]

--------------------

官网文档：

[https://alibaba-easyexcel.github.io/][https_alibaba-easyexcel.github.io]

[20200306170034464.png]: https://img-blog.csdnimg.cn/20200306170034464.png
[https_blog.csdn.net_maowendi_article_details_80537304]: https://blog.csdn.net/maowendi/article/details/80537304
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzI5Nzc0NDc5_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200306171100952.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzI5Nzc0NDc5,size_16,color_FFFFFF,t_70
[https_alibaba-easyexcel.github.io]: https://alibaba-easyexcel.github.io/