EasyPoi的使用-第三天🚲
导出excel之对象&集合导出
导出List集合(1)之@Excel
1、修改User.java👀
package com.demo1poi.day01.entity;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelIgnore;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.Data;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
@Data//Lombok这个插件的注解 @Data
@ExcelTarget("users")//没有什么实际意义,就是对于easypoi有个唯一的标识
public class User implements Serializable {
//@Excel(name = "编号",orderNum = "0")
@ExcelIgnore
private String id;
@Excel(name = "姓名",orderNum = "1")
private String name;
@Excel(name = "年龄",orderNum = "3",suffix = "岁")
private Integer age;
@Excel(name = "生日",width = 35.0,format = "yyyy-MM-dd HH:mm:ss",orderNum = "2")
private Date bir;
@Excel(name = "状态",replace = {"激活_1","未激活_0"},orderNum = "4")
private String status;
@Excel(name = "爱好",width = 20.0,orderNum = "5")
private List<String> habbys;
}
2、修改TestPoiDay01.java👀
package com.demo1poi.day01.test;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import com.demo1poi.day01.entity.User;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
/**
* @author 安静的茶叶
*/
public class TestPoiDay01 {
/**
* 查询所有用户
*/
public List<User> getUsers() {
List<User> users = new ArrayList<>();
for (int i = 0; i < 5; i++) {
User user = new User();
user.setId(String.valueOf(i));
user.setName("茶叶_" + i);
user.setAge(10 + i);
user.setBir(new Date());
if (i%2==0){
user.setStatus("1");
user.setHabbys(Arrays.asList("化妆","美甲","购物"));
}else{
user.setStatus("0");
user.setHabbys(Arrays.asList("看书","跳舞","品茶"));
}
users.add(user);
}
return users;
}
/**
* 导出excel
*/
@Test
public void TestPoiDay01() throws IOException {//FileNotFoundException->IOException
//获取数据
List<User> users = getUsers();
//导出excel
//参数 1:exportParams,导出配置对象 参数 2:导出的类型 参数 3:导出数据集合
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户信息列表", "用户信息"), User.class, users);
//写入指定的位置
FileOutputStream fileOutputStream = new FileOutputStream("C:/Users/Administrator/Desktop/expoerExcel.xls");
workbook.write(fileOutputStream);
//关流
fileOutputStream.close();
workbook.close();
}
}
3、运行单元测试测试该类,可以看到运行成功
4、查看导出的expoerExcel.xls,可以看到@Excel也可以导出一个List
导出List集合(2)之格式自定义
1、修改User.java👀
package com.demo1poi.day01.entity;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelIgnore;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.Data;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
@Data//Lombok这个插件的注解 @Data
@ExcelTarget("users")//没有什么实际意义,就是对于easypoi有个唯一的标识
public class User implements Serializable {
//@Excel(name = "编号",orderNum = "0")
@ExcelIgnore
private String id;
@Excel(name = "姓名",orderNum = "1")
private String name;
@Excel(name = "年龄",orderNum = "3",suffix = "岁")
private Integer age;
@Excel(name = "生日",width = 35.0,format = "yyyy-MM-dd HH:mm:ss",orderNum = "2")
private Date bir;
@Excel(name = "状态",replace = {"激活_1","未激活_0"},orderNum = "4")
private String status;
//@Excel(name = "爱好",width = 20.0,orderNum = "5")
@ExcelIgnore
private List<String> habbys;
@Excel(name = "爱好",width = 20.0,orderNum = "5")
private String habbyStr;
public String getHabbyStr(){
StringBuffer stringBuffer=new StringBuffer();
habbys.forEach(e->{
stringBuffer.append(e).append("、");
});
return stringBuffer.toString();
}
}
2、运行单元测试测试该类,可以看到运行成功
3、查看导出的expoerExcel.xls,可以看到我们自定义的格式成功了
导出对象的对象含有对象(1)
1、在实体类包(entity)下新建实体类,命名(Card)
Card.java
package com.demo1poi.day01.entity;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.Data;
import java.io.Serializable;
@Data
@ExcelTarget("cards")
public class Card implements Serializable {
@Excel(name = "身份证号码",width = 20.0,orderNum = "6")
private String no;
@Excel(name = "籍贯",width = 40.0,orderNum = "7")
private String address;
}
2、修改User.java👀
package com.demo1poi.day01.entity;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelEntity;
import cn.afterturn.easypoi.excel.annotation.ExcelIgnore;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.Data;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
@Data//Lombok这个插件的注解 @Data
@ExcelTarget("users")//没有什么实际意义,就是对于easypoi有个唯一的标识
public class User implements Serializable {
//@Excel(name = "编号",orderNum = "0")
@ExcelIgnore
private String id;
@Excel(name = "姓名",orderNum = "1")
private String name;
@Excel(name = "年龄",orderNum = "3",suffix = "岁")
private Integer age;
@Excel(name = "生日",width = 35.0,format = "yyyy-MM-dd HH:mm:ss",orderNum = "2")
private Date bir;
@Excel(name = "状态",replace = {"激活_1","未激活_0"},orderNum = "4")
private String status;
//@Excel(name = "爱好",width = 20.0,orderNum = "5")
@ExcelIgnore
private List<String> habbys;
@Excel(name = "爱好",width = 20.0,orderNum = "5")
private String habbyStr;
public String getHabbyStr(){
StringBuffer stringBuffer=new StringBuffer();
habbys.forEach(e->{
stringBuffer.append(e).append("、");
});
return stringBuffer.toString();
}
@ExcelEntity //当我们的对象与对象是一对一关系时,我们要用这样的标识(标识一对一)
private Card card;
}
3、修改TestPoiDay01.java👀
package com.demo1poi.day01.test;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import com.demo1poi.day01.entity.Card;
import com.demo1poi.day01.entity.User;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
/**
* @author 安静的茶叶
*/
public class TestPoiDay01 {
/**
* 查询所有用户
*/
public List<User> getUsers() {
List<User> users = new ArrayList<>();
for (int i = 0; i < 5; i++) {
User user = new User();
user.setId(String.valueOf(i));
user.setName("茶叶_" + i);
user.setAge(10 + i);
user.setBir(new Date());
Card card=new Card();
card.setNo("6326384674844333");
card.setAddress("广东省广州市广州塔");
user.setCard(card);
if (i%2==0){
user.setStatus("1");
user.setHabbys(Arrays.asList("化妆","美甲","购物"));
}else{
user.setStatus("0");
user.setHabbys(Arrays.asList("看书","跳舞","品茶"));
}
users.add(user);
}
return users;
}
/**
* 导出excel
*/
@Test
public void TestPoiDay01() throws IOException {//FileNotFoundException->IOException
//获取数据
List<User> users = getUsers();
//导出excel
//参数 1:exportParams,导出配置对象 参数 2:导出的类型 参数 3:导出数据集合
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户信息列表", "用户信息"), User.class, users);
//写入指定的位置
FileOutputStream fileOutputStream = new FileOutputStream("C:/Users/Administrator/Desktop/expoerExcel.xls");
workbook.write(fileOutputStream);
//关流
fileOutputStream.close();
workbook.close();
}
}
4、运行单元测试测试该类,可以看到运行成功
5、查看导出的expoerExcel.xls,可以看到对象导出成功
导出对象的对象含有对象集合(2)
1、在实体类包(entity)下新建实体类,命名(Order)
Order.java
package com.demo1poi.day01.entity;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
@Data
@ExcelTarget("orders")
@AllArgsConstructor
@NoArgsConstructor
public class Order implements Serializable {
@Excel(name = "订单编号",orderNum = "8",width = 20.0)
private String no;
@Excel(name = "订单名称",orderNum = "9",width = 15.0)
private String name;
}
2、修改User.java👀
package com.demo1poi.day01.entity;
import cn.afterturn.easypoi.excel.annotation.*;
import lombok.Data;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
@Data//Lombok这个插件的注解 @Data
@ExcelTarget("users")//没有什么实际意义,就是对于easypoi有个唯一的标识
public class User implements Serializable {
//@Excel(name = "编号",orderNum = "0")
@ExcelIgnore
private String id;
@Excel(name = "姓名",orderNum = "1")
private String name;
@Excel(name = "年龄",orderNum = "3",suffix = "岁")
private Integer age;
@Excel(name = "生日",width = 35.0,format = "yyyy-MM-dd HH:mm:ss",orderNum = "2")
private Date bir;
@Excel(name = "状态",replace = {"激活_1","未激活_0"},orderNum = "4")
private String status;
//@Excel(name = "爱好",width = 20.0,orderNum = "5")
@ExcelIgnore
private List<String> habbys;
@Excel(name = "爱好",width = 20.0,orderNum = "5")
private String habbyStr;
public String getHabbyStr(){
StringBuffer stringBuffer=new StringBuffer();
habbys.forEach(e->{
stringBuffer.append(e).append("、");
});
return stringBuffer.toString();
}
@ExcelEntity //当我们的对象与对象是一对一关系时,我们要用这样的标识(标识一对一)
private Card card;
@ExcelCollection(name = "订单列表")//当我们的对象与对象是一对多关系时,我们要用这样的标识(标识一对多)
private List<Order> orders;
}
3、修改TestPoiDay01.java👀
package com.demo1poi.day01.test;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import com.demo1poi.day01.entity.Card;
import com.demo1poi.day01.entity.Order;
import com.demo1poi.day01.entity.User;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
/**
* @author 安静的茶叶
*/
public class TestPoiDay01 {
/**
* 查询所有用户
*/
public List<User> getUsers() {
List<User> users = new ArrayList<>();
for (int i = 0; i < 5; i++) {
User user = new User();
user.setId(String.valueOf(i));
user.setName("茶叶_" + i);
user.setAge(10 + i);
user.setBir(new Date());
//身份信息
Card card=new Card();
card.setNo("6326384674844333");
card.setAddress("广东省广州市广州塔");
user.setCard(card);
//订单信息
List<Order> orders=new ArrayList<>();
orders.add(new Order("12","休闲裤"));
orders.add(new Order("13","跑鞋"));
orders.add(new Order("14","笔记本电脑"));
user.setOrders(orders);
if (i%2==0){
user.setStatus("1");
user.setHabbys(Arrays.asList("化妆","美甲","购物"));
}else{
user.setStatus("0");
user.setHabbys(Arrays.asList("看书","跳舞","品茶"));
}
users.add(user);
}
return users;
}
/**
* 导出excel
*/
@Test
public void TestPoiDay01() throws IOException {//FileNotFoundException->IOException
//获取数据
List<User> users = getUsers();
//导出excel
//参数 1:exportParams,导出配置对象 参数 2:导出的类型 参数 3:导出数据集合
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户信息列表", "用户信息"), User.class, users);
//写入指定的位置
FileOutputStream fileOutputStream = new FileOutputStream("C:/Users/Administrator/Desktop/expoerExcel.xls");
workbook.write(fileOutputStream);
//关流
fileOutputStream.close();
workbook.close();
}
}
4、运行单元测试测试该类,可以看到运行成功
5、查看导出的expoerExcel.xls,可以看到对象导出成功,但是看到订单列表在最前面,如果我们想把它放到后面去,我们要怎么办?
6、再次修改User.java👀
package com.demo1poi.day01.entity;
import cn.afterturn.easypoi.excel.annotation.*;
import lombok.Data;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
@Data//Lombok这个插件的注解 @Data
@ExcelTarget("users")//没有什么实际意义,就是对于easypoi有个唯一的标识
public class User implements Serializable {
//@Excel(name = "编号",orderNum = "0")
@ExcelIgnore
private String id;
@Excel(name = "姓名",orderNum = "1")
private String name;
@Excel(name = "年龄",orderNum = "3",suffix = "岁")
private Integer age;
@Excel(name = "生日",width = 35.0,format = "yyyy-MM-dd HH:mm:ss",orderNum = "2")
private Date bir;
@Excel(name = "状态",replace = {"激活_1","未激活_0"},orderNum = "4")
private String status;
//@Excel(name = "爱好",width = 20.0,orderNum = "5")
@ExcelIgnore
private List<String> habbys;
@Excel(name = "爱好",width = 20.0,orderNum = "5")
private String habbyStr;
public String getHabbyStr(){
StringBuffer stringBuffer=new StringBuffer();
habbys.forEach(e->{
stringBuffer.append(e).append("、");
});
return stringBuffer.toString();
}
@ExcelEntity //当我们的对象与对象是一对一关系时,我们要用这样的标识(标识一对一)
private Card card;
@ExcelCollection(name = "订单列表",orderNum = "8")//当我们的对象与对象是一对多关系时,我们要用这样的标识(标识一对多)
private List<Order> orders;
}