Write entity classes that match data in Excel tables
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class Stu {
// Set the table header name index
@excelProperty (value = "student id ",index = 0)
private int sno;
// Set the table header name index
@excelProperty (value = "student name ",index = 1)
private String sname;
}
Copy the code
Maven introduces dependencies
<dependencies>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
</dependencies>
Copy the code
Write data demo to Excel [encapsulate fake data normally should be query from database]
import com.alibaba.excel.EasyExcel;
import com.atguigu.cmn.test.entity.Stu;
import java.util.ArrayList;
import java.util.List;
public class WriteTest {
public static void main(String[] args) {
String fileName = "E:\11.xlsx";
// File path or input/output stream entity class template name
EasyExcel.write(fileName,Stu.class).sheet("Student Information")
// Data to write
.doWrite(data());
}
// Loop through the data to be added, finally encapsulating it into the list collection
private static List<Stu> data(a) {
List<Stu> list = new ArrayList<Stu>();
for (int i = 0; i < 10; i++) {
Stu data = new Stu();
data.setSno(i);
data.setSname("Zhang"+i);
list.add(data);
}
returnlist; }}Copy the code
Inserted data
Read Excel data demo [need to integrate read listener]
The ExcelReadListener listener is used to read data from Excel
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.atguigu.cmn.test.entity.Stu;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
public class ExcelReadListener extends AnalysisEventListener<Stu> {
// Create a List to hold the read data
List<Stu> list = new ArrayList<>();
// Insert data into the database
@Override
public void invoke(Stu stu, AnalysisContext analysisContext) {
System.out.println("Line read by STu =" + stu);
list.add(stu);
}
// The current method of reading header information is executed before the method of reading information
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
System.out.println("Header information"+headMap);
}
// Execute after reading
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("Reading data completed"); }}Copy the code
Code to read data
import com.alibaba.excel.EasyExcel;
import com.atguigu.cmn.test.entity.Stu;
public class ReadTest {
public static void main(String[] args) {
String fileName = "E:\11.xlsx";
// The parameter file path is inside the entity class listener
EasyExcel.read(fileName, Stu.class, newExcelReadListener()).sheet() .doRead(); }}Copy the code
Data read