Vue+easyexcel实现excel导入导出

1.引入easyexcel依赖

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

2.导出实体

@Data
@EqualsAndHashCode(callSuper = false)
@ApiModel(value="Account对象", description="")
public class Account implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "id")
    @TableId(value = "id", type = IdType.AUTO)
    @ExcelIgnore
    private Integer id;

    @ApiModelProperty(value = "昵称")
    @ExcelProperty("用户名")
    private String username;

    @ApiModelProperty(value = "用户密码")
    @ExcelIgnore
    private String password;

    @ApiModelProperty(value = "真实姓名")
    @ExcelProperty("真实姓名")
    private String pname;

    @ApiModelProperty(value = "性别")
    @ExcelProperty("性别")
    private String sex;

    @ApiModelProperty(value = "手机号码")
    @ExcelProperty("性别")
    private String phone;

    @ApiModelProperty(value = "头像")
    @ExcelIgnore
    private String touxiang;

    @ApiModelProperty(value = "会员")
    @ExcelProperty(value = "等级")
    private Integer member;

    @ApiModelProperty(value = "地址")
    @ExcelProperty(value = "地址")
    private String address;

    @ApiModelProperty(value = "生日")
    @ExcelProperty(value = "生日")
    @JsonFormat(pattern = "yyyy-MM-dd")
    private LocalDate birthday;

    @ApiModelProperty(value = "注册日期")
    @ExcelProperty(value = "注册日期")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private LocalDateTime createTime;

    @ApiModelProperty(value = "存在状态")
    @ExcelProperty(value = "账户状态")
    private Integer estate;

    @ApiModelProperty(value = "身份证号码")
    @ExcelProperty(value = "身份证号码")
    private String idCard;


}

3.导出时设置转化器

因为我用的是localdate和localdatetime

1.定义转化器，如下

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;

/**
 * @Author: ChenTaoTao
 * @Date: 2022/3/28 10:07
 * @Describe:
 */
public class LocalDateConverter implements Converter<LocalDate> {
 
    @Override
    public Class<LocalDate> supportJavaTypeKey() {
        return LocalDate.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }


    @Override
    public LocalDate convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        return LocalDate.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd"));
    }

    @Override
    public WriteCellData<String> convertToExcelData(LocalDate value, ExcelContentProperty contentProperty,
                                                    GlobalConfiguration globalConfiguration) {
        return new WriteCellData<>(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));
    }
 
}

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

/**
 * @Author: ChenTaoTao
 * @Date: 2022/3/28 10:07
 * @Describe:
 */
public class LocalDateTimeConverter  implements Converter<LocalDateTime> {
    @Override
    public Class<LocalDateTime> supportJavaTypeKey() {
        return LocalDateTime.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }


    @Override
    public LocalDateTime convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        return LocalDateTime.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
    }

    @Override
    public WriteCellData<String> convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty,
                                                    GlobalConfiguration globalConfiguration) {
        return new WriteCellData<>(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
    }

}

2.导出工具类

import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.List;

/**
 * @Author: ChenTaoTao
 * @Date: 2022/3/19 16:51
 * @Describe: 文件导出
 */
@Slf4j
public class EasyExcelUtil {
    public static <T> void writeExcel(HttpServletRequest request,HttpServletResponse response, List<T> data, Class tClass, String name){
        try {
            String dateStr = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss"));
            name = name+dateStr;
            //防止下载时中文乱码
            name = new String(name.getBytes("UTF-8"), "ISO-8859-1");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            //添加这个是防止前端拿不到Content-disposition
            response.setHeader("Content-Disposition", "attachment;filename=" + name+".xlsx");
            response.setHeader("Access-Control-Expose-Headers","Content-disposition");
            // 这里需要设置不关闭流
            EasyExcel.write(response.getOutputStream(), tClass).autoCloseStream(Boolean.FALSE).sheet(name).registerConverter(new LocalDateConverter()).registerConverter(new LocalDateTimeConverter())
                    .doWrite(data);
        } catch (Exception e) {
            log.error("文件导出失败,错误信息{}",e);
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            R r = R.err("文件下载失败");
            try {
                response.getWriter().println(JSON.toJSONString(r));
            } catch (IOException ioException) {
                ioException.printStackTrace();
            }
        }
    }
}

4.controller使用

/**
 * <p>
 *  前端控制器
 * </p>
 *
 * @author chentaotao
 * @since 2022-03-23
 */
@RestController
@RequestMapping("/mall/account")
@Api
public class AccountController {

    @Autowired
    private IAccountService iAccountService;
    
    @GetMapping("/export")
    @ApiOperation(value = "文件导出")
    public void export(HttpServletResponse response){
        List<Account> accountList = iAccountService.list();
        EasyExcelUtil.writeExcel(accountList,Account.class,"账户");
    }

}

5.自定义vue 导出请求

此处使用axios

import axios from 'axios'
//此处忽略了部分非必要的导入

// create an axios instance
const service = axios.create({
//baseUrl请修改为你自己的url
  baseURL: process.env.VUE_APP_BASE_API, // url = base url + request url
  // withCredentials: true, // send cookies when cross-domain requests
  timeout: 5000 // request timeout
})
// response interceptor
service.interceptors.response.use(
  /**
   * If you want to get http information such as headers or status
   * Please return  response => response
   */

  /**
   * Determine the request status by custom code
   * Here is just an example
   * You can also judge the status by HTTP Status Code
   */
  response => {
      let disposition = response.headers['content-disposition'];
      //以下部分有需要优化，如后端返回时没有携带文件后缀名,没有.时会有问题等等
      let filename = disposition?disposition.substring(disposition.indexOf('=')+1,disposition.indexOf('.')):"下载文件";
      let newName = decodeURI(escape(filename))
      let extName =disposition.substring(disposition.indexOf('.')+1)
      let blob = new Blob([response.data],{type: 'application/vnd.ms-excel'});
      let link = document.createElement("a");
      let evt = document.createEvent("HTMLEvents");
      evt.initEvent("click", false, false);
      link.href = URL.createObjectURL(blob);
      link.download = newName+"."+extName;
      link.style.display = "none";
      document.body.appendChild(link);
      link.click();
      window.URL.revokeObjectURL(link.href);
  },
  error => {
    console.log('err' + error) // for debug
    Message({
      message: error.message,
      type: 'error',
      duration: 5 * 1000
    })
    return Promise.reject(error)
  }
)

export default service

6.vue中的导出使用

//exportExcel 则是刚刚5.自定义导出请求的代码
import exportExcel from '@/utils/export'

export function exportExcels(data) {
  return exportExcel({
    url: '/mall/account/export',
    method: 'get',
    responseType: 'blob'
    // params: { id: params }
  })
}

vue中的使用

<template>
  <div class="app-container">

      <el-card style="width: 98%;margin: 0 auto">
        <el-form :inline="true" :model="query" style="padding-left: 3%" class="demo-form-inline" >
          <el-form-item label="用户名" >
            <el-input v-model="query.userName" placeholder="用户名" />
          </el-form-item>
          <el-form-item label="真实姓名">
            <el-input v-model="query.realName" placeholder="真实姓名" />
          </el-form-item>
          <el-form-item label="状态">
            <el-select v-model="query.state" placeholder="状态">
              <el-option label="全部" value="" />
              <el-option label="启用" value="1" />
              <el-option label="禁用" value="0" />
            </el-select>
          </el-form-item>
          <el-form-item>
            <el-button type="primary" @click="getList">查询</el-button>
          </el-form-item>

          <el-button style="float: right" @click="open">新增用户</el-button>
        </el-form>

        <el-button @click="exportExcel">导出</el-button>
      </el-card>



      <Dialog ref="dialog" :config="config" @fresh ="getList" :before-close="beforeClose" @close="resetForm" />

      <el-card style="margin-top: 20px;width: 98%;margin: 0 auto;padding-left: 2%">
        <!--数据列表-->
        <el-table
          v-loading="listLoading"
          :data="list"
          element-loading-text="载入中"
          border
          fit
          stripe
          highlight-current-row
          :height="tableHeight"
        >
          <el-table-column label="id" width="120px" align="center">
            <template slot-scope="scope">
              <span>{{ scope.row.id }}</span>
            </template>
          </el-table-column>
          <el-table-column label="用户名" width="120px" align="center">
            <template slot-scope="scope">
              <span>{{ scope.row.userName }}</span>
            </template>
          </el-table-column>
          <el-table-column label="用户姓名" width="300px" align="center">
            <template slot-scope="scope">
              <span>{{ scope.row.realName }}</span>
            </template>
          </el-table-column>
          <el-table-column label="状态" width="120px" align="center">
            <template slot-scope="scope">
              <span>{{ scope.row.state=='1'?'启用':'禁用' }}</span>
            </template>
          </el-table-column>
          <el-table-column label="创建时间" width="180px" align="center">
            <template slot-scope="scope">
              <i class="el-icon-time" />
              <span style="margin-left: 10px">{{ scope.row.createTime }}</span>
            </template>
          </el-table-column>
          <el-table-column label="更新时间" width="180px" align="center">
            <template slot-scope="scope">
              <i class="el-icon-time" />
              <span style="margin-left: 10px">{{ scope.row.updateTime }}</span>
            </template>
          </el-table-column>
          <el-table-column label="操作">
            <template slot-scope="scope">
              <el-button
                size="mini"
                @click="handleEdit(scope.$index, scope.row)"
              >编辑</el-button>
              <el-button
                size="mini"
                type="danger"
                @click="handleDelete(scope.$index, scope.row)"
              >删除</el-button>
            </template>
          </el-table-column>
        </el-table>
        <el-pagination
          :current-page="query.pageNo"
          :page-sizes="[1, 20, 50, 100]"
          :page-size="query.pageSize"
          layout="total, sizes, prev, pager, next, jumper"
          :total="total"
          @size-change="pageSizeChange"
          @current-change="pageCurrentChange"
        />

      </el-card>
    </div>



</template>
<script>
import { getPage, deleteBatch} from '@/api/user'
import Dialog from '@/pages/user/dialog/UserDialog'
import {exportExcels} from "@/api/exportExcel";
export default {
  components: {
    Dialog
  },
  data() {
    return {
      listLoading: true,
      tableHeight: window.innerHeight - 300,
      query: {
        pageNo: 1,
        pageSize: 10,
        userName: '',
        state: ''
      },
      list: [],
      deleteIds: {
        ids: []
      },
      total: 1,
      count: 1,
      config: {
        id: '',
        top: '20vh',
        width: '500px',
        title: '温馨提示',
        center: true,
        btnTxt: ['取消', '提交']
      }
    }
  },
  created() {
    this.getList()
  },
  methods: {
    getList() {
      this.listLoading = true;
      const data = getPage(this.query).then(res => {
        this.list = res.data.records
        this.total = res.data.total
      })
      this.listLoading = false
    },
    pageSizeChange(val) {
      console.log(val)
      this.query.pageSize = val
      this.getList()
    },
    pageCurrentChange(val) {
      console.log(val)
      this.query.pageNo = val
      this.getList()
    },
    handleEdit(index, row) {
      // console.log(row)
      this.config.id = row.id
      this.open()
    },
    handleDelete(index, row) {
      this.deleteIds.ids.push(row.id)
      deleteBatch(this.deleteIds).then(res => {
        this.getList()
      }).catch(err => {
        console.log(err)
      })
    },
    open() {
      this.$refs.dialog.open(cancel => {
        // cancel();

      })
      // .then(() => {console.log(this.$refs.span)});   //这里就充分利用了open方法中返回的nextTick
    },
    beforeClose() {

    },
    resetForm() {
      // 这里可以写重置表单的实现
    },
    exportExcel(){
    //此处就是导出
      exportExcels().then()
    }
  }
}
</script>

效果如下：

点击导出后

当然这里的账户状态可以自定义convert做转化

7.导入

本质就是文件上传

1.定义数据处理类

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.CollectionUtils;

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

/**
 * @Author: ChenTaoTao
 * @Date: 2022/3/28 12:25
 * @Describe:
 */
@Slf4j
public class AccountImportListener extends AnalysisEventListener<Account> {

    private static final int BATCH_COUNT = 1000;

    List<Account> data = new ArrayList<>();

    IAccountService accountService;

    public AccountImportListener(IAccountService accountService){
        this.accountService = accountService;
    }

    /**
     *  读数据的过程中要做什么,如设置默认值等等
     */
    @Override
    public void invoke(Account account, AnalysisContext analysisContext) {
        log.info("被添加的数据为i{}",account);
        data.add(account);
        if(data.size()==BATCH_COUNT){
           accountService.saveBatch(data);
           data.clear();
        }
    }

    /**
     * 所有数据读完以后要做什么
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

        if(!CollectionUtils.isEmpty(data)){
            accountService.saveBatch(data);
            data.clear();
        }
        log.info("恭喜,数据已经导入成功啦!");
    }
}

2.controller中使用：

    @PostMapping("import")
    @ApiOperation(value = "导入")
    public R importData(@RequestPart("file")MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(),Account.class,new AccountImportListener(iAccountService)).sheet().registerConverter(new LocalDateTimeConverter())
        .registerConverter(new LocalDateConverter()).doRead();
        return R.ok();
    }