EasyExcel

介绍

EasyExcel是阿里巴巴开源的一个excel处理框架,以使用简单、节省内存著称.EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中,而是从磁盘上一行行读取数据,逐个解析.

写操作

创建实体类,设置表头和添加的数据字段

@Data
public class Stu {
    
    //表头名称
    @ExcelProperty("学生编号")
    private int sno;
    @ExcelProperty("学生姓名")
    private String sname;
}

public class StuWrite {
    
    //写入excel
    private static List<Stu> data(){
    
        ArrayList<Stu> stus = new ArrayList<>();
        for(int i=0;i<10;i++){
    
            Stu stu = new Stu();
            stu.setSno(i);
            stu.setSname("张三"+i);
            stus.add(stu);
        }
        return stus;
    }

    public static void main(String[] args) {
    
        String filename = "C:\\Users\\y50027918\\Desktop\\aaa.xlsx";
        //Data needs to be specifiedclass去写 Which one to writesheet名里 然后文件流会自动关闭
        EasyExcel.write(filename,Stu.class).sheet("第一个sheet").doWrite(data());
    }
}

EasyExcel读操作

Listeners for read operations need to be created

public class ExcelListener extends AnalysisEventListener<Stu> {
    
    //List集合封装最终的数据
    List<Stu> list = new ArrayList<Stu>();
    //一行一行去读excel的内容
    @Override
    public void invoke(Stu stu, AnalysisContext analysisContext) {
    
        System.out.println("***"+stu);
        list.add(stu);
    }
    //读取表头

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
    
        System.out.println("表头信息:"+headMap);
    }
    //读取完成后执行
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    

    }
}

调用

    public static void main(String[] args) {
    
        String filename = "C:\\Users\\y50027918\\Desktop\\aaa.xlsx";
        //指定文件名 class 监听者 sheet名 进行读取
        EasyExcel.read(filename, Stu.class,
                new ExcelListener()).sheet().doRead();
    }

功能实现：课程分类导出

First look at what entities are

编写SubjectService

定义接口方法

    //导出
    void exportData(HttpServletResponse response);

impl实现

    @Override
    public void exportData(HttpServletResponse response) {
    
        try {
    
            //设置为excel
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            //给客户端的excel文件名
            String filename = URLEncoder.encode("课程分类", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename="+ filename + ".xlsx");
            //传null就是所有的意思
            List<Subject> subjects = mapper.selectList(null);
            //将subjectConvert to the type of output
            List<SubjectEeVo> dictVoList = new ArrayList<>(subjects.size());
            //复制对象
            for(Subject dict:subjects){
    
                SubjectEeVo subjectEeVo = new SubjectEeVo();
                BeanUtils.copyProperties(dict,subjectEeVo);
                dictVoList.add(subjectEeVo);
            }
            EasyExcel.write(response.getOutputStream(),SubjectEeVo.class).sheet("课程分类").doWrite(dictVoList);
        }catch (IOException e){
    
            e.printStackTrace();
        }
    }

添加controller方法

要向客户机输出数据,需要找response对象,It will return itself at the end of the method.

    @ApiOperation("导出")
    @GetMapping(value="/exportData")
    public void exportData(HttpServletResponse response){
    
        subjectService.exportData(response);
    }

数据字典导入前端

在list.vueAdd an export button
点击调用exportData方法

    <div class="el-toolbar-body" style="justify-content: flex-start;">
      <el-button type="text" @click="exportData"><i class="fa fa-plus"/> 导出</el-button>
    </div>

编写这个exportData方法,Make it call the background method

exportData() {
    
            window.open("http://localhost:8301/admin/vod/subject/exportData")
        },

功能实现-课程分类导入

Listeners are required for import,So create a read listener,首先创建一个listener文件夹,Create a listener in it.

//监听的是SubjectEeVo Written specifically for exportmodel
@Component
public class SubjectListener extends AnalysisEventListener<SubjectEeVo> {
    
    @Autowired
    private SubjectMapper dictmMapper;
    //一行一行读取
    @Override
    public void invoke(SubjectEeVo subjectEeVo, AnalysisContext analysisContext) {
    
        //调用方法添加数据库
        Subject subject = new Subject();
        //Because it is connected to the databasesubject So do the conversion 存储它
        BeanUtils.copyProperties(subjectEeVo,subject);
        dictMapper.insert(subject);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    

    }
}

servece方法添加

First in the interface declaration

    //导出
    void importData(MultipartFile file);

再在impl里面写入

    //导入数据
    @Override
    public void importData(MultipartFile file) {
    
        try{
    
            //文件名 class 监听者
            EasyExcel.read(file.getInputStream(),
                    SubjectEeVo.class,subjectListener).sheet().doRead();
        }catch (IOException e) {
    
            e.printStackTrace();
        }
    }

添加controller方法

在SubjectControllerAdd the import method inside

    @ApiOperation("导入")
    @PostMapping("importData")
    public Result<Object> importData(MultipartFile file){
    
        subjectService.importData(file);
        return Result.ok(null);
    }

数据字典导入前端

一样的,First add the import button

      <el-button type="text" @click="importData"><i class="fa fa-plus"/> 导入</el-button>

Then add the pop-up layer after clicking Import,就在div里添加

        <el-dialog title="导入" :visible.sync="dialogImportVisible" width="480px">
            <el-form label-position="right" label-width="170px">
                <el-form-item label="文件">
                    <el-upload
                            :multiple="false"
                            :on-success="onUploadSuccess"
                            :action="'http://localhost:8333/admin/vod/subject/importData'"
                            class="upload-demo">
                        <el-button size="small" type="primary">点击上传</el-button>
                        <div slot="tip" class="el-upload__tip">只能上传xls文件,且不超过500kb</div>
                    </el-upload>
                </el-form-item>
            </el-form>
            <div slot="footer" class="dialog-footer">
                <el-button @click="dialogImportVisible = false">取消</el-button>
            </div>
        </el-dialog>

Note that clicking is useless at this point,It needs to be set later.
in the previous codevisible.sync=“dialogImportVisible”
其意思是dialogImportVisible为truepop-up box,为falseThat is, the frame does not pop up.
首先默认为false

    data() {
    
        return {
    
            dialogImportVisible: false,
            list:[] //数据字典列表数组
        }
    },

添加导入方法,首先调用importData,Make it show the popup,Click OK to start parsing and indicate success,Adjust it back againfalse.

importData() {
    
    this.dialogImportVisible = true
},
onUploadSuccess(response, file) {
    
    this.$message.info('上传成功')
    this.dialogImportVisible = false
    this.getSubList(0)
},