This is the 17th day of my participation in Gwen Challenge
A list,
1.1 EasyExcel overview
1.1.1 website
Github.com/alibaba/eas…
Quick start: www.yuque.com/easyexcel/d…
1.1.2 EasyExcel characteristics
-
Java domain parsing, Excel generation more well-known frameworks have Apache POI, JXL and so on. But they all have one serious problem: they consume a lot of memory. This may be fine if your system has low concurrency, but if it does, it will be OOM or JVM full gc frequently.
-
EasyExcel is an open source Excel processing framework of Alibaba, known for its simplicity and memory saving. The main reason EasyExcel can greatly reduce the memory footprint is that when parsing Excel, the file data is not loaded into memory all at once. Instead, the data is read from disk row by row and parsed one by one.
-
EasyExcel parses a row by row and notifys the result of the row in observer mode (AnalysisEventListener).
1.2 Application Scenarios
-
Data import: reduce input workload
-
Export data: Archive statistics
-
Data transfer: Data transfer between heterogeneous systems
1.3 Common Notes
-
@excelProperty specifies that the current field corresponds to the column in Excel.
-
ExcelIgnore all fields match excel by default. This annotation will ignore the fields
-
@datetimeFormat Date conversion. This annotation is called to receive excel date format data with a String.
-
NumberFormat number conversion. This annotation is called when a String is used to receive excel NumberFormat data.
-
@excelignoreunannotated if ExcelProperty annotations are not added by default, they will not be added
Second, write Excel
2.1 Creating a Project
1. Create a regular Maven project
Project name: alibaba_easyExcel
2. Introduce XML-related dependencies in POM
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.1.7</version> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-simple</artifactId> <version>1.7.5</version> </dependency> <dependency> <groupId>org.apache.xmlbeans</groupId> <artifactId>xmlbeans</artifactId> <version>3.1.0</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId> <version>1.18.10</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId> The < version > 4.12 < / version > < / dependency > < / dependencies >Copy the code2.2 Easiest to write
Create an entity class
@data Public class ExcelStudentData {@excelProperty (" name ") private String Name; @excelProperty (" birthday ") private Date birthday; @excelProperty (" salary ") private Double salary; @ExcelProperty private String password; }Copy the code2. Test cases
- Excel 2007 and Excel 03 have different writes
- Excel version 03 can write 65536 lines at most at one time
@test public void simpleWrite(){String fileName="E:\\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \\ \ XLSX "; EasyExcel. Write(fileName, excelstudentdata.class).sheet(" student info ").dowrite (data()); } private List<ExcelStudentData> data(){ ArrayList<ExcelStudentData> list = new ArrayList<ExcelStudentData>(); for (int i = 0; i < 10; i++) { ExcelStudentData excelStudentData = new ExcelStudentData(); ExcelStudentData. Elegantly-named setName (" Xiao ray "+ I); excelStudentData.setBirthday(new Date()); ExcelStudentData. SetSalary (10000.90); list.add(excelStudentData); } return list; }}Copy the codeResults:
Tested:
- 1. Existing Excel is cleared and reassigned. Don’t add
- 2. The open file cannot be written
- 3. You need to establish a directory first
2.3 Specify write columns
Configure the index attribute for the column
@data Public class ExcelStudentData {@excelProperty (value = "name ",index = 2) private String Name; @data public class ExcelStudentData {@excelProperty (value =" name ",index = 2) private String Name; @excelProperty (value = "birthday ",index = 5) private Date birthday; @excelProperty (value = "salary ",index = 6) private Double salary; @ExcelIgnore private String password; }Copy the code2.4 Custom format conversion
Configure @dateTimeFormat and @numberFormat
@data Public class ExcelStudentData {@excelProperty (value = "name ") private String Name; @dateTimeFormat (" YYYY ") @excelProperty (value = "Date ") private Date birthday; @numberFormat ("#.##%") @excelProperty (value = "salary ") private Double salary; /** * ignore this field */ @excelignore private String password; }Copy the codeThird, reading Excel
3.1 Reference Documents
www.yuque.com/easyexcel/d…
3.2 Creating listeners
@Slf4j public class ExcelStudentListener extends AnalysisEventListener<ExcelStudentData> { private static final int BATCH_COUNT=2; List<ExcelStudentData> list =new ArrayList<ExcelStudentData>(); @param analysisContext @return void **/ public void invoke(ExcelStudentData o) AnalysisContext AnalysisContext) {log.info(" read a record,{}",o); list.add(o); If (list.size()>=BATCH_COUNT){log.info(" store database "); list.clear(); }} public void doAfterAllAnalysed(AnalysisContext AnalysisContext) {log.info(" Store remaining data to database "); Log.info (" all data parsed "); }}Copy the code3.3 Test Cases
Public class ExcelReadTest {@ Test public void Test () {String fileName = "E: \ \ lei \ \ study \ \. XLSX"; EasyExcel.read(fileName, ExcelStudentData.class,new ExcelStudentListener()) .sheet().doRead(); }}Copy the codeIv. Actual combat of the project
In the project, EasyExcel is used, mainly for the excel assembly application of multi-level directory. Data like this:
There are five categories: PV incoming line number > box transformer number > bus box number > inverter number > PV module package.
A total of 2000 pieces of data, assembled into a multi-level directory.
Read data:
Public void WriteData() {String fileName="E:\ Hulk \\ R&D department \\ Development data \\ Table.xlsx "; EasyExcel.read(fileName, ExcelPhotovoData.class,new ExcelDeviceListener(wiringPointMapper,deviceInfoMapper)) .sheet().doRead(); }Copy the codeListeners pass into the Mapper and finally wrap the directory.
@Slf4j
public class ExcelDeviceListener extends AnalysisEventListener<ExcelPhotovoData> {
private WiringPointMapper wiringPointMapper;
private DeviceInfoMapper deviceInfoMapper;
//定义临界值
private static final int BATCH_COUNT=500;
List<DeviceInfo> xbList=new ArrayList<DeviceInfo>();
public ExcelDeviceListener (){}
public ExcelDeviceListener(DeviceInfoMapper deviceInfoMapper){
this.deviceInfoMapper=deviceInfoMapper;
}
public ExcelDeviceListener(WiringPointMapper wiringPointMapper,DeviceInfoMapper deviceInfoMapper){
this.wiringPointMapper=wiringPointMapper;
this.deviceInfoMapper=deviceInfoMapper;
};
@Override
public void invoke(ExcelPhotovoData excelPhotovoData, AnalysisContext analysisContext) {
log.info("读取到一条记录，{}"+excelPhotovoData);
String level=excelPhotovoData.getJx();
String level2=excelPhotovoData.getXb();
String level3=excelPhotovoData.getHl();
String level4=excelPhotovoData.getNb();
String level5=excelPhotovoData.getGfb();
String level6=excelPhotovoData.getNumber();
Integer parentId=null;
Integer parentId2=null;
Integer parentId3=null;
Integer parentId4=null;
Integer parentId5=null;
Integer parentId6=null;
//查看一级目录是否存在
WiringPoint isExistLevel=this.getByLevel(level);
if(ObjectUtil.isNull(isExistLevel)){
// 组装一级目录
WiringPoint wiringPoint = new WiringPoint();
wiringPoint.setSysId(41040020001L);
wiringPoint.setPointName(level);
wiringPoint.setDeleteFlag(0);
wiringPoint.setCreateTime(DateUtil.date());
wiringPoint.setUpdateTime(DateUtil.date());
wiringPoint.setParentId(0);
wiringPoint.setType("0");
wiringPointMapper.insert(wiringPoint);
parentId=wiringPoint.getId();
}else{
parentId=isExistLevel.getId();
}
// 判断二级节点是否存在
WiringPoint isExistLevel2 = this.getByLevel2(level2,parentId);
if(ObjectUtil.isNull(isExistLevel2)){
// 查找dev_id 和sn_id
QueryWrapper<DeviceInfo> deviceInfoQueryWrapper = new QueryWrapper<>();
deviceInfoQueryWrapper.eq("sys_id",41040020001L);
deviceInfoQueryWrapper.eq("device_type","xb");
deviceInfoQueryWrapper.eq("sn",level2);
DeviceInfo deviceInfo = deviceInfoMapper.selectOne(deviceInfoQueryWrapper);
// 组装二级目录
WiringPoint wiringPoint = new WiringPoint();
wiringPoint.setSysId(41040020001L);
wiringPoint.setPointName(level2);
wiringPoint.setDeleteFlag(0);
wiringPoint.setCreateTime(DateUtil.date());
wiringPoint.setUpdateTime(DateUtil.date());
wiringPoint.setParentId(parentId);
wiringPoint.setSnId(deviceInfo.getId());
wiringPoint.setDevId(deviceInfo.getDevId());
wiringPointMapper.insert(wiringPoint);
parentId2=wiringPoint.getId();
}else{
parentId2=isExistLevel2.getId();
}
// 判断三级节点是否存在
WiringPoint isExistLevel3 = this.getByLevel2(level3,parentId2);
if(ObjectUtil.isNull(isExistLevel3)){
// 查找dev_id 和sn_id
QueryWrapper<DeviceInfo> deviceInfoQueryWrapper = new QueryWrapper<>();
deviceInfoQueryWrapper.eq("sys_id",41040020001L);
deviceInfoQueryWrapper.eq("device_type","hl");
deviceInfoQueryWrapper.eq("sn",level3);
DeviceInfo deviceInfo = deviceInfoMapper.selectOne(deviceInfoQueryWrapper);
// 组装三级目录
WiringPoint wiringPoint = new WiringPoint();
wiringPoint.setSysId(41040020001L);
wiringPoint.setPointName(level3);
wiringPoint.setDeleteFlag(0);
wiringPoint.setCreateTime(DateUtil.date());
wiringPoint.setUpdateTime(DateUtil.date());
wiringPoint.setParentId(parentId2);
wiringPoint.setSnId(deviceInfo.getId());
wiringPoint.setDevId(deviceInfo.getDevId());
wiringPointMapper.insert(wiringPoint);
parentId3=wiringPoint.getId();
}else{
parentId3=isExistLevel3.getId();
}
// 判断四级节点是否存在
WiringPoint isExistLevel4 = this.getByLevel2(level4,parentId3);
if(ObjectUtil.isNull(isExistLevel4)){
// 查找dev_id 和sn_id
QueryWrapper<DeviceInfo> deviceInfoQueryWrapper = new QueryWrapper<>();
deviceInfoQueryWrapper.eq("sys_id",41040020001L);
deviceInfoQueryWrapper.eq("device_type","nb");
deviceInfoQueryWrapper.eq("sn",level4);
DeviceInfo deviceInfo = deviceInfoMapper.selectOne(deviceInfoQueryWrapper);
// 组装三级目录
WiringPoint wiringPoint = new WiringPoint();
wiringPoint.setSysId(41040020001L);
wiringPoint.setPointName(level4);
wiringPoint.setDeleteFlag(0);
wiringPoint.setCreateTime(DateUtil.date());
wiringPoint.setUpdateTime(DateUtil.date());
wiringPoint.setParentId(parentId3);
wiringPoint.setSnId(deviceInfo.getId());
wiringPoint.setDevId(deviceInfo.getDevId());
wiringPointMapper.insert(wiringPoint);
parentId4=wiringPoint.getId();
}else{
parentId4=isExistLevel4.getId();
}
// 判断五级节点是否存在
WiringPoint isExistLevel5 = this.getByLevel2(level5,parentId4);
if(ObjectUtil.isNull(isExistLevel5)){
// 查找dev_id 和sn_id
QueryWrapper<DeviceInfo> deviceInfoQueryWrapper = new QueryWrapper<>();
deviceInfoQueryWrapper.eq("sys_id",41040020001L);
deviceInfoQueryWrapper.eq("device_type","gf");
deviceInfoQueryWrapper.eq("sn",level5);
DeviceInfo deviceInfo = deviceInfoMapper.selectOne(deviceInfoQueryWrapper);
// 组装五级目录
WiringPoint wiringPoint = new WiringPoint();
wiringPoint.setSysId(41040020001L);
wiringPoint.setPointName(level5);
wiringPoint.setDeleteFlag(0);
wiringPoint.setCreateTime(DateUtil.date());
wiringPoint.setUpdateTime(DateUtil.date());
wiringPoint.setParentId(parentId4);
wiringPoint.setSnId(deviceInfo.getId());
wiringPoint.setDevId(deviceInfo.getDevId());
wiringPointMapper.insert(wiringPoint);
parentId5=wiringPoint.getId();
}else{
parentId5=isExistLevel5.getId();
}
}
//根据一级目录查询二级目录是否存在
private WiringPoint getByLevel2(String level2, Integer parentId) {
QueryWrapper<WiringPoint> wrapper = new QueryWrapper<>();
wrapper.eq("point_name",level2);
wrapper.eq("sys_id",41040020001L);
wrapper.eq("parent_id",parentId);
WiringPoint wiringPoint = wiringPointMapper.selectOne(wrapper);
return wiringPoint;
}
//根据一级目录查询是否存在
private WiringPoint getByLevel(String level) {
QueryWrapper<WiringPoint> wrapper = new QueryWrapper<>();
wrapper.eq("point_name",level);
wrapper.eq("sys_id",41040020001L);
wrapper.eq("parent_id",0);
WiringPoint wiringPoint = wiringPointMapper.selectOne(wrapper);
return wiringPoint;
}
}
Copy the code