Vue+easyexcel实现excel导入导出

1.引入easyexcel依赖

        
            com.alibaba
            easyexcel
            3.0.5

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;


public class LocalDateConverter implements Converter {
 
    @Override
    public Class 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 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;


public class LocalDateTimeConverter  implements Converter {
    @Override
    public Class 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 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;


@Slf4j
public class EasyExcelUtil {
    public static  void writeExcel(HttpServletRequest request,HttpServletResponse response, List 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使用

@RestController
@RequestMapping("/mall/account")
@Api
public class AccountController {

    @Autowired
    private IAccountService iAccountService;
    
    @GetMapping("/export")
    @ApiOperation(value = "文件导出")
    public void export(HttpServletResponse response){
        List 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(
  

  
  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中的使用

效果如下：

点击导出后

当然这里的账户状态可以自定义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;


@Slf4j
public class AccountimportListener extends AnalysisEventListener {

    private static final int BATCH_COUNT = 1000;

    List 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();
        }
    }

    
    @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();
    }

Vue+easyexcel实现excel导入导出

Java相关栏目本月热门文章