SpringBoot+Vue+EasyExcel实现Excel的导入导出

导入EasyExcel的依赖：

        
            com.alibaba
            easyexcel
        

准备好实体类和VO：

import com.baomidou.mybatisplus.annotation.*;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.fasterxml.jackson.annotation.JsonIgnore;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
 
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
 
@Data
@ApiModel(description = "Subject")
@TableName("subject")
public class Subject {
 
	private static final long serialVersionUID = 1L;
	@ApiModelProperty(value = "id")
	private Long id;
 
	@ApiModelProperty(value = "创建时间")
	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
	@TableField("create_time")
	private Date createTime;
 
	@ApiModelProperty(value = "更新时间")
	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
	@TableField("update_time")
	private Date updateTime;
 
	@ApiModelProperty(value = "逻辑删除(1:已删除，0:未删除)")
	@JsonIgnore
	@TableLogic
	@TableField("is_deleted")
	private Integer isDeleted;
 
	@ApiModelProperty(value = "其他参数")
	@TableField(exist = false)
	private Map param = new HashMap<>();
 
	@ApiModelProperty(value = "类别名称")
	@TableField("title")
	private String title;
 
	@ApiModelProperty(value = "父ID")
	@TableField("parent_id")
	private Long parentId;
 
	@ApiModelProperty(value = "排序字段")
	@TableField("sort")
	private Integer sort;
 
	@ApiModelProperty(value = "是否包含子节点")
	@TableField(exist = false)
	private boolean hasChildren;
 
}

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
 
/**
 * 
 * Dict
 * 
 *
 * @author qy
 */
@Data
public class SubjectEeVo {
 
	@ExcelProperty(value = "id" ,index = 0)
	private Long id;
 
	@ExcelProperty(value = "课程分类名称" ,index = 1)
	private String title;
 
	@ExcelProperty(value = "上级id" ,index = 2)
	private Long parentId;
 
	@ExcelProperty(value = "排序" ,index = 3)
	private Integer sort;
 
 
}

导出：

 后端：

service：

    @Override
    public void exportData(HttpServletResponse response) {
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("课程分类","utf-8");
            response.setHeader("Content-disposition", "attachment;filename="+ fileName + ".xlsx");
            List list = this.list();
            List listE = new ArrayList<>();
            for(Subject s:list){
                SubjectEeVo e = new SubjectEeVo();
                BeanUtils.copyProperties(s,e);
                listE.add(e);
            }
            EasyExcel.write(response.getOutputStream(),SubjectEeVo.class).sheet("课程分类").doWrite(listE);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

controller：

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

前端：

导出按钮：

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

方法：

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

导入：

后端：

先创建一个监听器：

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.atguigu.ggkt.model.vod.Subject;
import com.atguigu.ggkt.vo.vod.SubjectEeVo;
import com.atguigu.ggkt.vod.service.SubjectService;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
 
@Component
public class SubjectListener extends AnalysisEventListener {
 
    @Autowired
    private SubjectService service;
 
    //一行一行读取
    @Override
    public void invoke(SubjectEeVo subjectEeVo, AnalysisContext analysisContext) {
        //调用方法添加数据库
        Subject subject = new Subject();
        BeanUtils.copyProperties(subjectEeVo,subject);
        service.save(subject);
    }
 
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
 
    }
}

接着在service中使用：

    @Autowired
    private SubjectListener subjectListener;
 
    @Override
    public void importDictData(MultipartFile file) {
        try {
            EasyExcel.read(file.getInputStream(),SubjectEeVo.class,subjectListener).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

controller：

    @ApiOperation(value = "导入")
    @PostMapping("importData")
    public Result importData(MultipartFile file) {
        subjectService.importDictData(file);
        return Result.ok(null);
    }

前端：

按钮：

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

上传模块：

    <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:8301/admin/vod/subject/importData'"
            class="upload-demo"
          >
            <el-button size="small" type="primary">点击上传el-button>
            <div slot="tip" class="el-upload__tip">只能上传xls文件，且不超过500kbdiv>
          el-upload>
        el-form-item>
      el-form>
      <div slot="footer" class="dialog-footer">
        <el-button @click="dialogImportVisible = false">取消el-button>
      div>
    el-dialog>

return中加入：

    return {
      dialogImportVisible: false
    }

方法中加入：

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