EasyPoi导出Excel

1.导入maven依赖

<dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-base</artifactId> <version>3.2.0</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-web</artifactId> <version>3.2.0</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-annotation</artifactId> <version>3.2.0</version> </dependency>

2.一个简单的测试

实体

package com.cw.demo.entity;

import cn.afterturn.easypoi.excel.annotation.Excel; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor;

@Data @NoArgsConstructor @AllArgsConstructor public class Person { private Integer id; @Excel(name = “姓名”,width= 20,height = 30) private String name; @Excel(name = “年龄”,width= 20,height = 30) private Integer age; @Excel(name = “性别”,width = 20,height = 30,replace = {“男_1”,“女_0”},suffix = “生”) private Integer sex; } 2.控制器

/利用easypoi导出excel/ @RequestMapping("/exportExcel") public void exportExcel(HttpServletResponse response){ String fileName=“个人信息.xlsx”; List list=new ArrayList<>(); Person p1=new Person(1,“lks”,23,1); Person p2=new Person(2,“lks”,23,0); Person p3=new Person(3,“lks”,23,1); Person p4=new Person(4,“lks”,23,0); list.add(p1); list.add(p2); list.add(p3); list.add(p4); Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(“个人信息”, “person”), Person.class, list); try { response.setCharacterEncoding(“UTF-8”); response.setHeader(“content-Type”, “application/vnd.ms-excel”); response.setHeader(“Content-Disposition”, “attachment;filename=” + URLEncoder.encode(fileName, “UTF-8”)); workbook.write(response.getOutputStream()); } catch (IOException e) { throw new RuntimeException(e); } }

3.功能拓展

*实体上的注解** 课程实体 @ExcelTarget("courseEntity") public class CourseEntity implements java.io.Serializable { /** 主键 */ private String id; /** 课程名称 */ @Excel(name = "课程名称", orderNum = "1", width = 25) private String name; /** 老师主键 */ @ExcelEntity(id = "absent") private TeacherEntity mathTeacher; @ExcelCollection(name = "学生", orderNum = "4") private List<StudentEntity> students; }

4.导出带图片的excel

List<CompanyHasImgModel> list; @Before public void initData() { list = new ArrayList<CompanyHasImgModel>(); list.add(new CompanyHasImgModel("百度", "imgs/company/baidu.png", "北京市海淀区西北旺东路10号院百度科技园1号楼")); list.add(new CompanyHasImgModel("阿里巴巴", "imgs/company/ali.png", "北京市海淀区西北旺东路10号院百度科技园1号楼")); list.add(new CompanyHasImgModel("Lemur", "imgs/company/lemur.png", "亚马逊热带雨林")); list.add(new CompanyHasImgModel("一众", "imgs/company/one.png", "山东济宁俺家")); } @Test public void exportCompanyImg() throws Exception { File savefile = new File("D:/excel/"); if (!savefile.exists()) { savefile.mkdirs(); } Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), CompanyHasImgModel.class, list); FileOutputStream fos = new FileOutputStream("D:/excel/ExcelExportHasImgTest.exportCompanyImg.xls"); workbook.write(fos); fos.close(); }

5.导入excel 需要把实体的注解修改 @RequestMapping("/importExcel") public void importExcel(){ String filepath = “/Users/qiuhaigang/Desktop/个人信息.xlsx”; ImportParams params=new ImportParams(); params.setTitleRows(1); params.setHeadRows(1); List person = ExcelImportUtil.importExcel(new File(filepath), PersonImport.class, params); System.out.println(person); if(person != null){ for (PersonImport p: person) { System.out.println§; } }

}