easypoi方便简单的写出Excel导出,Excel模板导出,Excel导入,Word模板导出,通过简单的注解和模板 语言(熟悉的表达式语法),完成以前复杂的写法
使用easypoi
引入依赖
<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>
注解
-
@ExcelTarget 用在对象上面,设置一个唯一标识,表示这是要导出的对象
-
@Excel 作用到属性上面,是属性和excel列的映射 id=编号,name=姓名 ....
-
@ExcelCollection 表示一个集合,主要针对一对多的导出,比如一个学生对应多个科目,科目就可以用集合表示
-
@ExcelEntity 表示一个继续深入导出的实体,对象里面包含对象同样有导出的字段
-
@ExcelIgnore 用在属性上面,表示这个属性不被导出
@ExcelTarget
//标识这个类是可以被easypoi导入导出的类
value : id唯一标识
height:设置单元格高度
width:这只单元格宽度
@ExcelTarget(value = "users") // 唯一标识作用
public class User implements Serializable {
}
@Excel
//用在属性上面,对表格列的设置
name:表格的列名
orderNum:属性在列中的顺序
format:导入导出时间的格式
suffix:设置数据的后缀
width:设置表格的宽度
replace:原数据替换指定数据
savePath:设置导入excel中图片的保存路径
type:导出类型 1 是文本 2 是图片,3 是函数,10 是数字 默认是文本
@Excel(name="编号",orderNum = "0")
private String id;
@Excel(name="姓名" ,orderNum = "1")
private String name;
@Excel(name="年龄",suffix = "岁",orderNum = "3")
private Integer age;
@Excel(name = "生日",width = 30,format = "yyyy-MM-dd HH:mm:ss",orderNum = "2")
private Date birthday;
@Excel(name = "状态",replace = {"可用_0","不可用_1"},orderNum = "4")
private String status;
@ExcelEntity
对象中包含对象
public class Info implements Serializable{
private String card;
private String address;
}
@ExcelTarget(value = "users") // 唯一标识作用
public class User implements Serializable {
@ExcelEntity
private Info info;
}
导出excel
导出基本数据
1,创建对象必须实现Serializable
@ExcelTarget(value = "users") // 唯一标识作用
public class User implements Serializable {
@Excel(name="编号",orderNum = "0") // orderNum:在excel中的顺序
private Integer id;
@Excel(name="姓名" ,orderNum = "1")
private String name;
@Excel(name="年龄",suffix = "岁",orderNum = "3")//suffix:导出后在数据后面加后缀
private Integer age;
@Excel(name = "生日",width = 30,format = "yyyy-MM-dd HH:mm:ss",orderNum = "2")//width:设置excel表格的宽度
private Date birthday;
@Excel(name = "状态",replace = {"可用_0","不可用_1"},orderNum = "4")//replace:把0替换成可用
private String status;
}
2,测试数据
public List<User> getUser(){
ArrayList<User> users = new ArrayList<User>();
for (int i = 0;i<10;i++){
User user = new User();
user.setId(i);
user.setName("小明");
user.setAge(10+i);
user.setBirthday(new Date());
if (i%2==0){
user.setStatus("1");
}else {
user.setStatus("0");
}
users.add(user);
}
return users;
}
3,导出Excel
//导出excel
@Test
public void setExcel() throws IOException {
//获取数据
List<User> user = getUser();
//导出excel
//excel的描述,导出对象类型,导出数据集合
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户信心列表", "用户信息"), User.class, user);
//导出到指定位置
workbook.write(new FileOutputStream("C:/upload/cc.xls"));
workbook.close();
}
4,查看excel
导出list集合
1,自定义list集合的导出格式
// 存储爱好的集合
private List<String> hobby;
@Excel(name="爱好",orderNum = "5",width = 30)
private String hobbyList; // 返回爱好的字符串
//自定义格式
public String getHobbyList() {
StringBuilder stringBuilder = new StringBuilder();
for (String str : this.hobby){
stringBuilder.append(str).append(",");
}
return stringBuilder.toString();
}
2,结果
导出对象内的对象
1,定义对象
@ExcelEntity:表示一对一关系,对象中包含对象
public class User implements Serializable {
@ExcelEntity
private Info info;
}
@ExcelTarget("info")
@Data
public class Info {
@Excel(name = "身份证号",width = 30,orderNum = "6")
private String card;
@Excel(name = "地址",orderNum = "7")
private String address;
}
2,返回对象数据
// 返回数据
public List<User> getUser(){
ArrayList<User> users = new ArrayList<User>();
for (int i = 0;i<10;i++){
User user = new User();
user.setId(String.valueOf(i));
user.setName("小明");
user.setAge(10+i);
user.setBirthday(new Date());
user.setHobby(Arrays.asList("篮球","拉伸"));
if (i%2==0){
user.setStatus("1");
}else{
user.setStatus("0");
}
user.setInfo(new Info("1231234325423423","日本"+i));
users.add(user);
}
return users;
}
3,导出exce
// 导出到Excel
@Test
public void setExcel()throws IOException {
List<User> user = getUser();
// 导出Excel
//ExportParams导出配置的对象,导出的类型,导出的数据集合
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户信息列表", "用户信息"),User.class, user);
//把Excel写到指定位置
FileOutputStream fileOutputStream = new FileOutputStream("C:/upload/333.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();
workbook.close();
}
4,结果
导出一对多
1,创建order对象实现Serializable
//订单类
@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("order")
public class Order implements Serializable {
@Excel(name = "订单号",orderNum = "8",width = 20)
private String orderId;
@Excel(name = "订单名称",orderNum = "9")
private String orderName;
}
// 用户类
@Data
@ExcelTarget(value = "userId")
public class User implements Serializable {
//一对多
@ExcelCollection(name = "订单信息",orderNum = "8")
private List<Order> orders;
}
//返回订单集合
ArrayList<Order> orders = new ArrayList<>();
orders.add(new Order("123123124"+i,"卫生纸"));
orders.add(new Order("123123124"+i,"裤子"));
orders.add(new Order("123123124"+i,"鞋子"));
user.setOrders(orders);
2,结果
导出图片
1,添加图片字段,根据路径导出
@Data
@ExcelTarget(value = "userId")
public class User implements Serializable {
//图片导出
@Excel(name = "图片",type = 2,width = 15,height = 15)//type=2,标识导出类型为图片
private String image;
}
user.setImage("C:\\Users\\1\\Pictures\\Camera Roll\\IMG_0013.JPG");
2,结果
基本数据类型图片的导入
1,excel数据
2,创建实体对象
@ExcelTarget("emp")
@Data
public class Emp implements Serializable {
// name要和excel的标题一致
@Excel(name="编号")
private Integer id;
@Excel(name="姓名")
private String name;
@Excel(name="年龄" )
private Integer age;
@Excel(name = "生日",format = "yyyy-MM-dd")
private Date birthday;
@Excel(name = "状态",replace = {"可用_0","不可用_1"})//replace:替换
private String status;
//图片 type:标识导入导出类型为图片,savePath:图片保存的位置
@Excel(name = "头像",type = 2,savePath = "C:/Users/1/IdeaProjects/easypoi/src/main/resources/static")
private String image;
}
3,导入excel
//导入excel
@Test
public void importExcel() throws Exception {
//导入配置对象
ImportParams importParams = new ImportParams();
//标题占了几行
importParams.setTitleRows(1);
//字段标题占了几行
importParams.setHeadRows(2);
importParams.setNeedSave(false);//是否保存上传的excel
importParams.setSaveUrl("C:/Users/1/IdeaProjects/easypoi/src/main/resources/static");
// excel文件,导入的类型,导入的配置对象
List<Emp> emps = ExcelImportUtil.importExcel(new FileInputStream("C:/upload/cc.xls"), Emp.class, importParams);
for (Emp emp : emps) {
System.out.println(emp.toString());
}
}
4,结果