Getting started with easyexcel
Because Alibaba's easy excel has been used in the project recently, and then go to B
After studying at the station, I read the open class tutorial of dark horse, and then clone the source code of easy excel myself,
The following is the class note sharing of station B, which is basically enough
Corresponding GitHub address
Corresponding station B video
1、 Get to know easyexcel
1. Apache POI
Let's talk about POI first. Students who have experience in report import and export should have heard of or used it.
Apache POI is an open source function library of the Apache Software Foundation, which provides cross platform Java APIs to read and write files in Microsoft Office format. However, there are some problems as follows:
1.1 high learning and use cost
Only those who have a deep understanding of POI know that there is Sax mode (DOM parsing mode) in poi. However, Sax mode is relatively complex. Excel has 03 and 07 versions. The data storage methods of the two versions are different, and the Sax parsing methods are also different.
If you want to understand these two parsing methods clearly, you can write code test. It is estimated that it will take two days. In addition, even after parsing, there is a lot of cumbersome code to convert to your own business model. Overall, it takes at least three days. Due to the complexity of the code, the subsequent maintenance cost is huge.
The Sax mode API of POI can solve some memory overflow problems to a certain extent, but POI still has some defects. For example, the decompression and post decompression storage of Excel version 07 are completed in memory, and the memory consumption is still large. A 3M excel is parsed with the Sax of POI, which still needs about 100m memory.
1.2 the memory consumption of POI is large
Most POI uses its usermodel pattern. The advantage of usermodel is that it is easy to get started and easy to use. Just copy a code and run it. The rest is to write business transformation. Although the transformation also needs to write hundreds of lines of code, it is relatively easy to understand. However, the biggest problem of usermodel mode is that it consumes a lot of memory. A few megabytes of file parsing needs hundreds of megabytes of memory. Many applications now adopt this mode. The reason why it is still normal must be that there is little concurrency. After concurrency, there must be oom or frequent full GC.
Generally speaking, simple writing heavily depends on memory, and the learning cost of complex writing is high.
characteristic
2. EasyExcel
2.1 rewriting POI's analysis of Excel version 07
characteristic
2、 Quick start -- QuickStart
0. Import dependent coordinates
<!-- EasyExcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
<!-- lombok 优雅编程 -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.10</version>
</dependency>
<!-- junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
1. The simplest reading
1.1 requirements and preparations
/**
* 需求：单实体导入
* 导入Excel学员信息到系统。
* 包含如下列：姓名、性别、出生日期
* 模板详见：杭州黑马在线202003班学员信息.xls
*/
// 杭州黑马在线202003班学员信息.xls文件
1.2. Write entities for exporting data
// 基于lombok
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
/**
* 学生姓名
*/
private String name;
/**
* 学生性别
*/
private String gender;
/**
* 学生出生日期
*/
private Date birthday;
/**
* id
*/
private String id;
}
1.3. Read excel file
Call the API of easyexcel to read the test class studentreaddemo of the excel file
package com.itheima.demo;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder;
import com.itheima.domain.Student;
import com.itheima.listener.StudentReadListener;
import java.io.FileNotFoundException;
/**
* @Author Vsunks.v
* @Date 2020/3/11 23:28
* @Description:
*/
public class StudentReadDemo {
public static void main(String[] args) throws FileNotFoundException {
// 读取文件，读取完之后会自动关闭
/*
pathName 文件路径；"d:\\杭州黑马在线202003班学员信息.xls"
head 每行数据对应的实体；Student.class
readListener 读监听器，每读一样就会调用一次该监听器的invoke方法
sheet方法参数： 工作表的顺序号（从0开始）或者工作表的名字，不传默认为0
*/
// 封装工作簿对象
ExcelReaderBuilder workBook = EasyExcel.read
("d:\\杭州黑马在线202003班学员信息.xls",Student.class,new StudentReadListener());
// 封装工作表
ExcelReaderSheetBuilder sheet1 = workBook.sheet();
// 读取
sheet1.doRead();
}
}
The listener for reading excel is used to process the data generated by reading
package com.itheima.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.itheima.domain.Student;
/**
* @Author Vsunks.v
* @Date 2020/3/11 23:12
* @Description:
*/
public class StudentReadListener extends AnalysisEventListener<Student> {
// 每读一样，会调用该invoke方法一次
@Override
public void invoke(Student data,AnalysisContext context) {
System.out.println("data = " + data);
log.info(data + "保存成功");
}
// 全部读完之后，会调用该方法
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// TODO......
}
}
2. The simplest way to write
2.1 requirements and preparations
/**
* 需求：单实体导出
* 导出多个学生对象到Excel表格
* 包含如下列：姓名、性别、出生日期
* 模板详见：杭州黑马在线202003班学员信息.xlsx
*/
2.2. Write entities for exporting data
// 使用lombok
package com.itheima.domain;
@Data
@AllArgsConstructor
@NoArgsConstructor
@ColumnWidth(20)
public class Student {
/**
* id
*/
//@ExcelProperty(value = "编号",index = 3)
@ExcelIgnore
private String id;
/**
* 学生姓名
*/
@ExcelProperty(value = "学生姓名",index = 0)
//@ColumnWidth(30)
private String name;
/**
* 学生性别
*/
@ExcelProperty(value = "学生性别",index = 2)
private String gender;
/**
* 学生出生日期
*/
@ExcelProperty(value = "学生出生日期",index = 1)
//@ColumnWidth(20)
private Date birthday;
}
2.3 prepare data and write to file
package com.itheima.demo;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.itheima.domain.Student;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @Author Vsunks.v
* @Date 2020/3/11 23:27
* @Description:
*/
public class StudentWriteDemo {
public static void main(String[] args) {
List<Student> students = initData();
/*
String pathName 写入文件的路径
Class head 写入文件的对象类型
默认写入到07的xlsx中，如果想要写入xls，可以指定类型（待验证）
*/
ExcelWriterBuilder workBook = EasyExcel.write("d:\\杭州黑马学员表.xlsx",Student.class);
// sheet方法参数： 工作表的顺序号（从0开始）或者工作表的名字
workBook.sheet().doWrite(students);
}
private static List<Student> initData() {
ArrayList<Student> students = new ArrayList<>();
for (int i = 0; i < 10; i++) {
Student data = new Student();
data.setName("杭州黑马学号0" + i);
data.setBirthday(new Date());
data.setGender("男");
students.add(data);
}
return students;
}
}
3. File upload and download
File upload and download based on spring MVC
0. Import dependencies
<!-- EasyExcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.0.5</version>
</dependency>
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.10</version>
<scope>provided</scope>
</dependency>
<!-- junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-nop</artifactId>
<version>1.7.2</version>
</dependency>
<!-- SpringMVC（Spring） -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>5.0.5.RELEASE</version>
</dependency>
<!-- Servlet -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>4.0.1</version>
<scope>provided</scope>
</dependency>
<!-- 文件上传 -->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.4</version>
</dependency>
3.1 file upload
Write the entity class corresponding to each line in Excel
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
/**
* 学生姓名
*/
private String name;
/**
* 学生性别
*/
private String gender;
/**
* 学生出生日期
*/
private Date birthday;
/**
* id
*/
private String id;
}
Write callback listener studentreadlistener
@Component
@Scope("prototype") // 作者要求每次读取都要使用新的Listener
public class StudentReadListener extends AnalysisEventListener<Student> {
@Autowired
private StudentService studentService;
private final int BATCH_SAVE_NUM = 5;
ArrayList<Student> students = new ArrayList<>();
private int count = 0;
// 每读一样，会调用该invoke方法一次
@Override
public void invoke(Student data,AnalysisContext context) {
students.add(data);
if (++count % BATCH_SAVE_NUM == 0) {
studentService.save(students);
students.clear();
}
}
// 全部读完之后，会调用该方法
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// TODO......
}
}
Business code interface studentservice and implementation class studentserviceimpl
public interface StudentService {
void save(ArrayList<Student> students);
}
@Service
public class StudentServiceImpl implements StudentService {
@Override
public void save(ArrayList<Student> students) {
System.out.println("students in service = " + students);
}
}
Spring configuration file
<!-- 组件扫描-->
<context:component-scan base-package="com.itheima"/>
Spring MVC configuration file
<!-- 组件扫描-->
<context:component-scan base-package="com.itheima.demo"/>
<!-- MVC文件上传多部件解析器 -->
<bean class="org.springframework.web.multipart.commons.CommonsMultipartResolver"
id="multipartResolver"/>
Read the uploaded excel file by encoding
@Controller
public class WebUploadAndDownload {
/**
* 文件上传
* 1. 编写excel中每一行对应的实体类
* 2. 由于默认异步读取excel，所以需要逐行读取的回调监听器
* 3. 开始读取Excel
*/
@PostMapping("upload")
@ResponseBody
public String upload(multipartfile file) throws IOException {
ExcelReaderBuilder workBook = EasyExcel.read(file.getInputStream(),studentReadListener);
workBook.sheet().doRead();
return "success";
}
3.2 file download
Write entity classes and create objects to write to tables
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
/**
* id
*/
@ExcelIgnore
private String id;
/**
* 学生姓名
*/
//@ExcelProperty({"学员信息表","学生姓名"})
@ExcelProperty("学生姓名")
private String name;
/**
* 学生性别
*/
//@ExcelProperty({"学员信息表","学生性别"})
@ExcelProperty("学生性别")
private String gender;
/**
* 学生出生日期
*/
//@ExcelProperty({"学员信息表","学生出生日期"})
@ExcelProperty("学生出生日期")
private Date birthday;
}
// 循环生成10个学生对象
private static List<Student> initData() {
ArrayList<Student> students = new ArrayList<>();
for (int i = 0; i < 10; i++) {
Student data = new Student();
data.setName("杭州黑马学号0" + i);
data.setBirthday(new Date());
data.setGender("男");
students.add(data);
}
return students;
}
The encoding writes the data to the response body to realize the download
public class WebUploadAndDownload {
/**
* 文件下载
* 1. 编写实体类并创建对象以便写入表格
* 2. 设置响应参数：文件的ContentType和文件名，同时设置编码避免乱码
* 3. 直接写，内部会调用finish方法自动关闭OutputStream
*/
@GetMapping("download")
public void download(HttpServletResponse response) throws IOException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 防止中文乱码
String fileName = URLEncoder.encode("测试","UTF-8");
response.setHeader("Content-Disposition","attachment; filename*=UTF-8''" + fileName + ".xlsx");
ExcelWriterBuilder workBook = EasyExcel.write(response.getOutputStream(),Student.class);
ExcelWriterSheetBuilder sheet = workBook.sheet("模板");
sheet.doWrite(initData());
}
}
4. Custom cell style
Easyexcel supports adjusting row height, column width, background color, font size and other contents, but the control method is the same as using the native POI, which is cumbersome and not recommended.
However, you can use the template filling method to write data directly to the preset style table, and the original style will be maintained when writing data.
3、 Fill
1.1 preparation of formwork
In Excel table, {} is used to represent the variables to be filled in the package. If there are {,} left and right braces in the cell text, you need to use slash to escape \ {, \} in front of the brackets.
The member variable name of the entity object filled with data in the code or the key of the filled map set must be consistent with the variable name wrapped by {} in Excel.
1.2 packaging data
Write a class that encapsulates and fills data or select map
/**
* 使用实体类封装填充数据
*
* 实体中成员变量名称需要和Excel表各种{}包裹的变量名匹配
*/
@Data
public class FillData {
private String name;
private int age;
}
/**
* 生成多组数据代码
* /
private static List<FillData> initFillData() {
ArrayList<FillData> fillDatas = new ArrayList<FillData>();
for (int i = 0; i < 10; i++) {
FillData fillData = new FillData();
fillData.setName("杭州黑马0" + i);
fillData.setAge(10 + i);
fillDatas.add(fillData);
}
return fillDatas;
}
1.3 filling
Prepare data and populate to file
public static void main(String[] args) {
// 加载模板
InputStream templateFile = FillData.class.getClassLoader().getResourceAsStream(
"fill_data_template1" +
".xlsx");
// 写入文件
String targetFileName = "单组数据填充.xlsx";
// 准备对象数据填充
FillData fillData = new FillData();
fillData.setName("杭州黑马");
fillData.setAge(10);
// 生成工作簿对象
ExcelWriterBuilder workBookWriter = EasyExcel.write(targetFileName).withTemplate(templateFile);
// 获取工作表并填充
//workBookWriter.sheet().doFill(fillData);
// 使用Map数据填充
HashMap<String,String> mapFillData = new HashMap<>();
mapFillData.put("name","杭州黑马Map");
mapFillData.put("age","11");
// 获取第一个工作表填充并自动关闭流
workBookWriter.sheet().doFill(mapFillData);
}
1.4 effect
2. Populate multiple sets of data
2.1 preparation of formwork
Use {.} in Excel tables To represent the variables to be filled in the package. If there are {,} left and right braces in the cell text, you need to use slashes to escape \ {, \} in front of the braces.
The member variable name of the entity object filled with data in the code or the key of the filled map set must be consistent with the variable name wrapped by {} in Excel.
2.2 packaging data
Write a class that encapsulates and fills data or select map
// 同上
2.3 filling
Prepare data and populate to file
public static void main(String[] args) {
// 加载模板
InputStream templateFile = FillData.class.getClassLoader().getResourceAsStream(
"fill_data_template2.xlsx");
// 写入文件
String targetFileName = "多组数据填充.xlsx";
List<FillData> fillDatas = initData();
// 生成工作簿对象
ExcelWriterBuilder workBookWriter =
EasyExcel.write(targetFileName).withTemplate(templateFile);
// 获取第一个工作表填充并自动关闭流
workBookWriter.sheet().doFill(fillDatas);
}
3. Combined filling
3.1 preparation of formwork
There are multiple groups of data filling and single data filling. In order to avoid conflicting coverage of the two data, you need to set line wrapping through fillconfig object when filling multiple groups.
3.2 packaging data
Write a class that encapsulates and fills data or select map
// 同上
3.3 filling
Prepare data and populate to file
public static void main(String[] args) {
// 加载模板
InputStream templateFile = FillData.class.getClassLoader().getResourceAsStream(
"fill_data_template3.xlsx");
// 目标文件
String targetFileName = "组合数据填充.xlsx";
List<FillData> fillDatas = initData();
// 生成工作簿对象
ExcelWriter excelWriter = EasyExcel.write(targetFileName).withTemplate(templateFile).build();
// 生成工作表对象
WriteSheet writeSheet = EasyExcel.writerSheet().build();
// 组合填充时，因为多组填充的数据量不确定，需要在多组填充完之后另起一行
FillConfig fillConfig = FillConfig.builder().forceNewRow(true).build();
// 填充并换行
excelWriter.fill(fillDatas,fillConfig,writeSheet);
HashMap<String,String> otherData = new HashMap<>();
otherData.put("date","2020-03-14");
otherData.put("total","100");
excelWriter.fill(otherData,writeSheet);
// 关闭
excelWriter.finish();
}
4. Horizontal fill
4.1 preparation of formwork
Horizontal filling is the same as multiple groups of filling templates. The difference is that horizontal filling needs to be set through fillconfig object.
4.2 packaging data
Write a class that encapsulates and fills data or select map
// 同上
4.3 filling
Prepare data and populate to file
public static void main(String[] args) {
// 加载模板
InputStream templateFile = FillData.class.getClassLoader().getResourceAsStream(
"fill_data_template4.xlsx");
// 写入文件
String targetFileName = "easyExcelDemo\\水平数据填充.xlsx";
List<FillData> fillDatas = initData();
// 生成工作簿对象
ExcelWriter excelWriter = EasyExcel.write(targetFileName).withTemplate(templateFile).build();
// 生成工作表对象
WriteSheet writeSheet = EasyExcel.writerSheet().build();
// 组合填充时，因为多组填充的数据量不确定，需要在多组填充完之后另起一行
FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
// 填充
excelWriter.fill(fillDatas,writeSheet);
// 关闭
excelWriter.finish();
}
5. Precautions
In order to save memory, the whole document is not organized in memory and then written to the file as a whole. Instead, it is written line by line, which can not delete and move lines, and note writing is not supported. When writing multiple groups of data, if you need to add a new row, you can only add it in the last row, not in the middle.
6. Filling comprehensive exercise
See report_ template. xlsx
/**
* reprot综合练习
*/
@Test
public void test06() {
InputStream templateInputStream = this.getClass().getClassLoader().getResourceAsStream(
"report_template.xlsx");
// 目标文件
String targetFile = "模板写入6-report.xlsx";
// 写入workbook对象
ExcelWriter workBook =
EasyExcel.write(targetFile,FillData.class).withTemplate(templateInputStream).build();
WriteSheet sheet = EasyExcel.writerSheet().build();
// 填充配置，开启组合填充换行
//FillConfig fillConfig = FillConfig.builder().forceNewRow(true).build();
// ****** 准备数据 *******
// 日期
HashMap<String,String> dateMap = new HashMap<String,String>();
dateMap.put("date","2020-03-16");
// 总会员数
HashMap<String,String> totalCountMap = new HashMap<String,String>();
dateMap.put("totalCount","1000");
// 新增员数
HashMap<String,String> increaseCountMap = new HashMap<String,String>();
dateMap.put("increaseCount","100");
// 本周新增会员数
HashMap<String,String> increaseCountWeekMap = new HashMap<String,String>();
dateMap.put("increaseCountWeek","50");
// 本月新增会员数
HashMap<String,String> increaseCountMonthMap = new HashMap<String,String>();
dateMap.put("increaseCountMonth","100");
// 新增会员数据
List<Student> students = initData();
// **** 准备数据结束****
// 写入统计数据
workBook.fill(dateMap,sheet);
workBook.fill(totalCountMap,sheet);
workBook.fill(increaseCountMap,sheet);
workBook.fill(increaseCountWeekMap,sheet);
workBook.fill(increaseCountMonthMap,sheet);
// 写入新增会员
workBook.fill(students,sheet);
workBook.finish();
}
4、 Common APIs and notes
1. Common class
2. Comments on reading
@ExcelProperty
Where to use: the standard works on member variables
Optional properties:
Usage effect: the index attribute can specify which column in Excel corresponds to the current field. It can be matched according to the column name value or not written.
If @ excelproperty annotation is not used, the order of member variables from top to bottom corresponds to the order from left to right in the table;
Usage suggestions: either do not write all, or use index all, or use names to match. Try not to mix the three.
Code demonstration:
// 1. 修改成员变量顺序读取Excel表格
// 2. 修改index属性值读取Excel表格
// 3. 修改value属性值读取Excel表格
@ExcelIgnore
Marked on the member variable, all fields will match excel by default. If this annotation is added, this field will be ignored
Code demonstration:
// 4. 忽略id成员变量值读取Excel表格
@DateTimeFormat
It is marked on the member variable, date conversion, and this annotation will be called when the member variable of string type is used in the code to receive the date format data in Excel. The value inside refers to Java text. SimpleDateFormat
// 5. 按照指定的格式写入Excel内容
@NumberFormat
It is marked on the member variable for number conversion. This annotation will be called when the member variable of string type is used in the code to receive data in Excel number format. The value inside refers to Java text. DecimalFormat
@ExcelIgnoreUnannotated
Label on class.
When this annotation is not marked, all member variables in the default class will participate in reading and writing, regardless of whether the annotation @ excelproperty is added to the member variable.
After the annotation is marked, the member variables in the class will not participate in reading and writing if they are not marked with @ excelproperty annotation.
3. General parameters when reading
Both readworkbook and readsheet have parameters. If it is empty, the parent is used by default.
4. Readworkbook (Workbook object) parameter
5. Readsheet (worksheet object) parameter
6. Annotation interpretation when writing
@ExcelProperty
Where to use: the standard works on member variables
Optional properties:
Usage effect: index specifies the column to write to. If it is not specified, it will be sorted according to the position of member variables;
Value specifies the column header to be written. If not specified, the name of the member variable is used as the column header;
If you want to set a complex header, you can specify multiple values for value.
Code demonstration:
// 5. 为《杭州黑马学员表.xlsx》文件中学生信息设置一个统一的表头“杭州黑马学员信息表”
Other notes:
Basically the same as when reading
7. General parameters on write
Writeworkbook and writesheet all have parameters. If it is blank, the parent will be used by default.