SpringBoot+Vue实现EasyPOI导入导出的方法详解

 cn.afterturneasypoi-base3.2.0 cn.afterturneasypoi-web3.2.0 cn.afterturneasypoi-annotation3.2.0

 data() { return { importUrl: this.settings.apiUrl+'/sys/user/import_excel', }; }, 

 methods: { exportExcel() { window.open(this.settings.apiUrl + "/sys/user/export_excel") } } 

package com.chen.excel; import cn.afterturn.easypoi.excel.annotation.Excel; import lombok.Data; import java.util.Date; /** * Excel 类，主要用于映射excel表 * @author whc */ @Data public class SysUserExcel { @Excel(name = "序号", orderNum = "0", format = "isAddIndex") private Integer index = 1; @Excel(name = "用户账号") private String username; @Excel(name = "真实姓名") private String realName; @Excel(name = "性别", replace = {"男_1", "女_2"}) private Integer gender; @Excel(name = "电子邮箱", width = 25) private String email; @Excel(name = "手机号码") private String mobile; @Excel(name = "创建时间", format = "yyyy-MM-dd", width = 15) private Date createTime; } 

@PostMapping("import_excel") public ResultBean import_excel(MultipartFile file) { ResultBean res = sysUserService.importExcel(file); return res; } 

@Override public ResultBean importExcel(MultipartFile file) { ImportParams importParams = new ImportParams(); //标题行设置为1行，默认是0，可以不设置；依实际情况设置。 importParams.setTitleRows(1); // 表头设置为1行 importParams.setHeadRows(1); try { //读取excel List sysUserExcelList = ExcelImportUtil.importExcel(file.getInputStream(), SysUserExcel.class, importParams); batchInsert(sysUserExcelList); return ResultBean.create(0, "success"); } catch (Exception e) { log.error("导入 Excel 异常！ e ==> {}", e); return ResultBean.create(10, "导入excel 异常！"+e.getMessage()); } } public void batchInsert(List param) throws Exception{ //转换为dto集合 List sysUserDTOList = BeanUtil.copyToList(param, SysUserDTO.class); //转换集合 List userDTOList = new ArrayList<>(); for (SysUserDTO sysUserDTO : sysUserDTOList) { if (!StringUtils.isEmpty(sysUserDTO.getUsername())) { sysUserDTO.setPassword("123"); userDTOList.add(sysUserDTO); } } //批量插入数据库 sysUserMapper.batchInsert(userDTOList); }

Integer batchInsert(@Param("list") List param); 

 insert sys_user (username, password, real_name, gender, email, mobile, create_time) values  (#{item.username}, #{item.password}, #{item.realName}, #{item.gender}, #{item.email}, #{item.mobile}, now()) 

@GetMapping("export_excel") public void export_excel(Map param, HttpServletResponse response) { sysUserService.exportExcel(param, response); } 

@Override public void exportExcel(Map param, HttpServletResponse response) { try { List res = sysUserMapper.list(null); List sysUserExcelList = BeanUtil.copyToList(res, SysUserExcel.class); //设置信息头，告诉浏览器内容为excel类型 response.setHeader("content-Type", "application/vnd.ms-excel"); //设置下载名称 response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("用户列表.xls", StandardCharsets.UTF_8.name())); //字节流输出 ServletOutputStream out = response.getOutputStream(); //设置excel参数 ExportParams params = new ExportParams(); //设置sheet名名称 params.setSheetName("用户列表"); //设置标题 params.setTitle("用户信息表"); Workbook workbook = ExcelExportUtil.exportExcel(params, SysUserExcel.class, sysUserExcelList); workbook.write(out); } catch (Exception e) { log.error("导出失败！ e ==> {}", e); } }