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§;
}
}
}