文章目录
easyexcel的简单应用
读(数据的解析)
一行一行读,自定义一个监听类继承AnalysisEventListener,每读一行触发一个动作;
首先定义一个实体类:
@Data
@NoArgsConstructor
@AllArgsConstructor
@HeadRowHeight(22)//表头行高,只能作用在类上。
@ContentRowHeight(15)//内容行高,只能作用在类上,对所有行进行统一行高设置
public class Student {
/**
* index用来设置列的顺序,从0开始,如果跳过了某个index,则多出来一个空列。
*/
@ExcelProperty(value = "ID",index = 5)
@ExcelIgnore
private String id;
@ExcelProperty(value = {"学员信息表","性别"},index = 1)
private String gender;
@ExcelProperty(value = {"学员信息表","姓名"},index = 0)
@ColumnWidth(40)//可以设置在属性上或者类上
private String name;
@ExcelProperty(value = {"学员信息表","年龄"},index = 2)
private Integer age;
@ExcelProperty(value = {"学员信息表","出生日期"},index = 3)
@ColumnWidth(20)
@DateTimeFormat("yyyy/MM/dd")
private Date birthday;
}
监听类:
/**
* @author Hanxiaohan
* @date 2021/04/25 14:10
* @since 1.0
* <p>Copyright: Copyright (c) 2021</p>
*
* 读取文档的监听器类
* 加入继承类的泛型,避免类型转换
*
*
*/
@Component
@Scope("prototype")//多例
public class HxhListener extends AnalysisEventListener<Student> {
/**
*
* @param student 每次读取到的数据封装的对象
* @param context
* 每读一行内容都会调用一次invoke
*/
@Override
public void invoke(Student student, AnalysisContext context) {
System.out.println("student= "+student);
}
/**
* 读取完整个文档之后调用的方法
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
}
}
测试数据读取:
/**
* 工作簿
* sheet页(工作表)
*
* 测试读
*/
@Autowired
private HxhListener hxhListener;
@Test
void contextLoads1() {
//获得一个工作簿对象,获得工作表对象,读取工作表中内容
/**
* Build excel the read
*
* @param file
* File to read.文件路径
* @param head
* Annotate the class for configuration information.
* 文件中每一行数据要存储到的实体类型的class对象,表头
* @param readListener
* Read listener.
* 监听器:每读一行,都会调用一次该对象的invoke,在
* invoke可以操作使用读取到的数据
* @return Excel reader builder.
*/
//获取一个工作簿
//监听器设置为多例的,使用@Scope("prototype")
ExcelReaderBuilder readWorkBook = EasyExcel.read("akaman.xlsx", Student.class, hxhListener);
//默认读取第一个工作表
ExcelReaderSheetBuilder sheet = readWorkBook.sheet();
//读内容
sheet.doRead();
}
测试写入excel
/**
* 测试写
* 将Student实例写入excel
* 工作簿+工作表
*/
@Test
void contextLoads2() {
ExcelWriterBuilder writeWokeBook = EasyExcel.write("mikasa.xlsx", Student.class);
ExcelWriterSheetBuilder sheet = writeWokeBook.sheet();
sheet.doWrite(initData());
}
/**
*生成数据
* @Author Hanxiaohan
* @Date 2021/04/25 14:40
* @param
* @return java.util.List<com.hxh.easyexcel.bean.Student>
* @throws
*/
private List<Student> initData(){
List<Student> studentList = new ArrayList<>();
Calendar calendar = Calendar.getInstance();
for (int i = 0; i < 10; i++) {
Student data = new Student();
data.setName(UUID.randomUUID().toString());
data.setAge(new Random().nextInt(100));
data.setGender(i%2==0?"男":"女");
calendar.set(1993+i,12-i,28-i+2,0,0,0);
data.setBirthday(calendar.getTime());
studentList.add(data);
}
return studentList;
}
测试数据的填充1:
/**
* 单组数据的填充(实体)
* 1,创建工作簿,工作表,准备数据,填充数据
*/
@Test
void contextLoads3() {
//1,准备模板.withTemplate
String template = "C:\\Users\\10256056\\Desktop\\1234.xlsx";
ExcelWriterBuilder writerBuilder = EasyExcel.write("C:\\Users\\10256056\\Desktop\\Excel填充单组数据.xlsx", FillSimpleBean.class).withTemplate(template);
ExcelWriterSheetBuilder sheet = writerBuilder.sheet();
//2,准备数据
FillSimpleBean fillSimpleBean = new FillSimpleBean();
fillSimpleBean.setName("日报");
fillSimpleBean.setAge(18);
//3,数据的填充
sheet.doFill(fillSimpleBean);
}
测试数据的填充2:
/**
* 单组数据的填充(map形式)
* 1,创建工作簿,工作表,准备数据,填充数据
*/
@Test
void contextLoads4() {
//1,准备模板.withTemplate
String template = "C:\\Users\\10256056\\Desktop\\1234.xlsx";
ExcelWriterBuilder writerBuilder = EasyExcel.write("C:\\Users\\10256056\\Desktop\\Excel填充单组数据.xlsx", FillSimpleBean.class).withTemplate(template);
ExcelWriterSheetBuilder sheet = writerBuilder.sheet();
//2,准备数据
Map mapt = new HashMap(2);
mapt.put("name", "小明");
mapt.put("age", 35);
//3,数据的填充
sheet.doFill(mapt);
}
测试多组数据的填充(list):
/**
* 多组数据的填充(实体)
* 在模板大括号里面加上.号,如{.name}
* 对象的话使用list存放。
*/
@Test
void contextLoads5() {
//1,准备模板.withTemplate
String template = "C:\\Users\\10256056\\Desktop\\1234.xlsx";
ExcelWriterBuilder writerBuilder = EasyExcel.write("C:\\Users\\10256056\\Desktop\\Excel填充单组数据.xlsx", FillSimpleBean.class).withTemplate(template);
ExcelWriterSheetBuilder sheet = writerBuilder.sheet();
//2,准备数据
List<FillSimpleBean> fillBeanList = new ArrayList<>();
for (int i = 0; i < 10; i++) {
FillSimpleBean data = new FillSimpleBean();
data.setName(UUID.randomUUID().toString());
data.setAge(new Random().nextInt(100));
fillBeanList.add(data);
}
//3,数据的填充
sheet.doFill(fillBeanList);
}
测试数据填充(组合类型)
为防止将已填数据打乱,在填充值后设置自动换行FillConfig:
FillConfig fillConfig = FillConfig.builder().forceNewRow(true).build();
,且手动关闭writer对象
/**
* 组合填充,有单数据与多数据
*/
@Test
void contextLoads6() {
//1,准备模板.withTemplate
String template = "C:\\Users\\10256056\\Desktop\\1234.xlsx";
ExcelWriter writer = EasyExcel.write("C:\\Users\\10256056\\Desktop\\Excel填充单组数据.xlsx", FillSimpleBean.class).withTemplate(template).build();
WriteSheet sheet = EasyExcel.writerSheet().build();
//2,准备数据,1,多数据填充
List<FillSimpleBean> fillBeanList = new ArrayList<>();
for (int i = 0; i < 10; i++) {
FillSimpleBean data = new FillSimpleBean();
data.setName(UUID.randomUUID().toString());
data.setAge(new Random().nextInt(100));
fillBeanList.add(data);
}
//2,准备数据,2,单数据填充
Map<String,Object> mapt = new HashMap(2);
mapt.put("date", new Date());
mapt.put("total", 3628800);
//会在读或者写结束后自动关闭流;组合填充的时候需要手动关闭流。
FillConfig fillConfig = FillConfig.builder().forceNewRow(true).build();
//3,数据的填充,多组
writer.fill(fillBeanList,fillConfig,sheet);
//3,单组填充
writer.fill(mapt, sheet);
//关闭流!!!
writer.finish();
}
测试水平填充
FillConfig fillConfig = FillConfig.builder()
.direction(WriteDirectionEnum.HORIZONTAL)
.build();
/**
* 水平填充
*/
@Test
void contextLoads7() {
//1,准备模板.withTemplate
String template = "C:\\Users\\10256056\\Desktop\\1234.xlsx";
ExcelWriter writer = EasyExcel.write("C:\\Users\\10256056\\Desktop\\Excel填充单组数据.xlsx", FillSimpleBean.class).withTemplate(template).build();
WriteSheet sheet = EasyExcel.writerSheet().build();
//2,准备数据,1,多数据填充
List<FillSimpleBean> fillBeanList = new ArrayList<>();
for (int i = 0; i < 10; i++) {
FillSimpleBean data = new FillSimpleBean();
data.setName(UUID.randomUUID().toString());
data.setAge(new Random().nextInt(100));
fillBeanList.add(data);
}
//会在读或者写结束后自动关闭流;组合填充的时候需要手动关闭流。
FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
//3,数据的填充,多组
writer.fill(fillBeanList,fillConfig,sheet);
//关闭流!!!
writer.finish();
}
综合练习:
已知模板:
/**
* 综合练习,报表填充(复用样式)与导出
*/
@Test
void contextLoads8() {
//1,准备模板.withTemplate
String template = "C:\\Users\\10256056\\Desktop\\1234.xlsx";
ExcelWriter writer = EasyExcel.write("C:\\Users\\10256056\\Desktop\\报表.xlsx", FillSimpleBean.class).withTemplate(template).build();
WriteSheet sheet = EasyExcel.writerSheet().build();
//2,准备数据,1,多数据填充
Map<String,Date> pika1 = new HashMap<>();
pika1.put("date",new Date());
Map<String,Integer> pika2 = new HashMap<>();
pika2.put("increaseCount",30);
pika2.put("totalCount",100);
pika2.put("increaseCountWeek",11);
pika2.put("increaseCountMonth",25);
List<Student> studentList = new ArrayList<>();
Calendar calendar = Calendar.getInstance();
for (int i = 0; i < 30; i++) {
Student data = new Student();
data.setName(UUID.randomUUID().toString().substring(0,5));
data.setId(String.valueOf(i));
calendar.set(1993+i,12-i,28-i+2,0,0,0);
data.setGender(i%2==0?"男":"女");
data.setBirthday(calendar.getTime());
studentList.add(data);
}
//3,数据的填充,多组
writer.fill(pika1,sheet);
writer.fill(pika2,sheet);
writer.fill(studentList,sheet);
//关闭流!!!
writer.finish();
}
填充之后效果: