当前位置：首页 > news >正文

EasyPoi使用指定的模板导入导出excel

news 来源：原创 2024/9/20 19:45:49

1. 引入pom文件

<!-- EasyPoi依赖 --><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>4.1.0</version></dependency>

2. 导出接口

 @ApiOperation(value = "easypoi导出（模板）")@GetMapping("/export")public void export(HttpServletResponse response) {List<StudentBean> data = new ArrayList<>();StudentBean studentBean = new StudentBean();studentBean.setAdress("洋县");studentBean.setName("0"); // 0 黎明StudentBean studentBean2 = new StudentBean();studentBean2.setAdress("汉中");studentBean2.setName("1"); // 1 张三StudentBean studentBean3 = new StudentBean();studentBean3.setAdress("西安");studentBean3.setName("2");data.add(studentBean);data.add(studentBean2);data.add(studentBean3);Map<String, Object> hashMap = new HashMap<>();hashMap.put("data", data);try {TemplateExportParams templatePath = new TemplateExportParams("docx/test.xlsx");templatePath.setHeadingStartRow(3);Workbook workbook = ExcelExportUtil.exportExcel(templatePath, hashMap);response.setContentType("application/msword;charset=UTF-8");response.setCharacterEncoding("utf-8");//前端存在跨域不成功，设置可访问response.setHeader("Access-Control-Allow-Origin", "*");//设置不要缓存response.setHeader("Pragma", "No-cache");// 这里URLEncoder.encode可以防止中文乱码response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode("学生信息", "UTF-8") + ".xlsx");//输出流OutputStream outStream = response.getOutputStream();//浏览器下载workbook.write(outStream);//关闭流outStream.flush();outStream.close();} catch (IOException e) {throw new IllegalArgumentException(e.getMessage());}}

3. 导入模板

    @ApiOperation(value = "easypoi导入（模板）")@PostMapping("/import")public List<StudentBean> importPoi(@Validated MultipartFile file) {InputStream inputStream = null;try {inputStream = file.getInputStream();//设置导入参数ImportParams importParams = new ImportParams();// excel标题占多少行，而不是第几行
//            importParams.setTitleRows(3); //标题占1行，默认0
//            // excel表头占几行，而不是第几行（多行的意思是合并的行数）
//            importParams.setHeadRows(1); //表头占1行，默认1//excel转POJOList<StudentBean> studentList = ExcelImportUtil.importExcel(inputStream,StudentBean.class, importParams);return studentList;} catch (IOException e) {e.printStackTrace();System.out.println("IOException：" + e);} catch (Exception e) {throw new RuntimeException(e);} finally {if (inputStream != null) {try {inputStream.close();} catch (IOException e) {e.printStackTrace();}}}return null;}
}

4.bean

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Size;@Data
public class StudentBean {@Excel(name = "姓名",replace={"1_张三","0_黎明"})  // 导入的时候才会映射
//    @Size(min = 0,max = 1,message = "只能为0或者1")private String name;@Excel(name = "地址")
//    @NotBlank(message = "必须不为空")private String adress;
}