easypoi import verification
1, Basic verification of import
Now the product needs to verify the imported Excel. Illegal Excel is not allowed to be warehoused. You need to return specific error information to the front end and prompt the user. The error information needs to include line number and corresponding error.
Because EasyPOI supports Hibernate Validator, you can use it directly. Because you want to return error information and error line number, you need to use the advanced usage of EasyPOI to implement the interface between IExcelDataModel and IExcelModel. IExcelDataModel is responsible for setting line number and IExcelModel is responsible for setting error information
If the @ Pattern annotation is used, the field type must be String, otherwise an exception will be thrown
In this article, the original Integer type gender is changed to String type genderStr, and the record field is also changed to String type recordStr, etc
Similarly, if you verify the Date type field, first change the type to String, and refer to the following for the regular expression. That is, the original Integer type
Note that if the replace attribute is set in the @ Excel annotation, Hibernate Validator verifies the replaced value
Entity class when exporting
@Data
public class TalentUserInputEntity{
@Excel(name = "full name*")
private String name;
@Excel(name = "Gender*")
private Integer gender;
@Excel(name = "cell-phone number*")
private String phone;
@Excel(name = "Start working time*")
private Date workTime;
@Excel(name = "nation*")
private String national;
@Excel(name = "language proficiency*")
private String languageProficiency;
@Excel(name = "date of birth*")
private Date birth;
@Excel(name = "position*")
private String jobsName;
@Excel(name = "Position type*")
private String categoryName;
@Excel(name = "salary*")
private Integer salary;
@Excel(name = "Duty station*")
private String workArea;
@ExcelCollection(name = "work experience*")
private List<ExperienceInputEntity> experienceList;
@ExcelCollection(name = "Educational experience*")
private List<EducationInputEntity> educationList;
@ExcelCollection(name = "Awards")
private List<AwardsInputEntity> awardList;
@ExcelCollection(name = "Skill certificate")
private List<PunishmentInputEntity> punishmentList;
@Excel(name = "Specialty")
private String specialty;
}
Entity class at import time
@Data
public class TalentUserInputEntity implements IExcelDataModel, IExcelModel {
// Regular time format verification
public static final String DATE_REGEXP = "(Mon|Tue|Wed|Thu|Fri|Sat|Sun)( )(Dec|Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov)( )\\d{2}( )(00:00:00)( )(CST)( )\\d{4}";
/**
* Line number
*/
private int rowNum;
/**
* Error message
*/
private String errorMsg;
@Excel(name = "full name*")
@NotBlank(message = "[full name]Cannot be empty")
private String name;
@Excel(name = "Gender*", replace = {"male_0", "female_1"})
@Pattern(regexp = "[01]", message = "Gender error")
private String genderStr;
@Excel(name = "cell-phone number*")
private String phone;
@Excel(name = "Start working time*")
@Pattern(regexp = DATE_REGEXP, message = "[Start working time]Time format error")
private String workTimeStr;
@Excel(name = "nation*")
@NotBlank(message = "[nation]Cannot be empty")
private String national;
@Excel(name = "language proficiency*")
@NotBlank(message = "[language proficiency]Cannot be empty")
private String languageProficiency;
@Excel(name = "date of birth*")
@Pattern(regexp = DATE_REGEXP, message = "[date of birth]Time format error")
private String birthStr;
@Excel(name = "position*")
@NotBlank(message = "[position]Cannot be empty")
private String jobsName;
@Excel(name = "Position type*")
@NotBlank(message = "[Position type]Cannot be empty")
private String categoryName;
@Excel(name = "salary*", replace = {"3K following_1", "3K-5K_2", "5K-10K_3", "10K-20K_4", "20K-50K_5", "50K above_6"})
@Pattern(regexp = "[123456]", message = "Salary information error")
private String salaryStr;
@Excel(name = "Duty station*")
@NotBlank(message = "[Duty station]Cannot be empty")
private String workArea;
@ExcelCollection(name = "work experience*")
private List<ExperienceInputEntity> experienceList;
@ExcelCollection(name = "Educational experience*")
private List<EducationInputEntity> educationList;
@ExcelCollection(name = "Awards")
private List<AwardsInputEntity> awardList;
@ExcelCollection(name = "Skill certificate")
private List<PunishmentInputEntity> punishmentList;
@Excel(name = "Specialty")
private String specialty;
@Override
public String getErrorMsg() {
return errorMsg;
}
@Override
public void setErrorMsg(String errorMsg) {
this.errorMsg = errorMsg;
}
@Override
public Integer getRowNum() {
return rowNum;
}
@Override
public void setRowNum(Integer rowNum) {
this.rowNum = rowNum;
}
// work experience
@Data
public class ExperienceInputEntity {
@Excel(name = "corporate name*")
private String companyName;
@Excel(name = "Industry*")
private String industry;
@Excel(name = "start time*")
@Pattern(regexp = DATE_REGEXP, message = "[work experience][start time]Time format error")
private String beginTimeStr;
@Excel(name = "End time*")
@Pattern(regexp = DATE_REGEXP, message = "[work experience][End time]Time format error")
private String finishTimeStr;
@Excel(name = "Job title*")
private String jobTitle;
@Excel(name = "Department*")
private String department;
@Excel(name = "job content*")
private String description;
}
// Educational experience
@Data
public class EducationInputEntity {
@Excel(name = "school*")
private String schoolName;
@Excel(name = "education*", replace = {"Junior high school and below_1", "secondary specialized school_2", "high school_3", "junior college_4", "undergraduate_5", "master_6", "doctor_7"})
@Pattern(regexp = "[1234567]", message = "Education information error")
private String recordStr;
@Excel(name = "Start year*")
@Pattern(regexp = DATE_REGEXP, message = "[Educational experience][Start year]Time format error")
private String beginTimeStr;
@Excel(name = "Graduation Year *")
@Pattern(regexp = DATE_REGEXP, message = "[Educational experience][Graduation Year ]Time format error")
private String finishTimeStr;
@Excel(name = "major*")
private String profession;
}
}
2, Duplicate value verification of import value custom verification
The above verifications are only some basic verifications, such as repeated row verification in Excel, repeated data verification with database in Excel, etc. This verification cannot be completed through Hibernate Validator, and only code can be written to implement the verification logic.
Start with simple Excel data and database value double check. In order to facilitate the demonstration, we will not introduce the database. We will directly Mock some data to judge whether it is repeated.
@Service
public class MockTalentDataService {
private static List<TalentUser> talentUsers = new ArrayList<>();
static {
TalentUser u1 = new TalentUser(1L, "ride on the wind", "18311342567");
TalentUser u2 = new TalentUser(2L, "Zhang San", "18512343567");
TalentUser u3 = new TalentUser(3L, "Li Si", "18902343267");
talentUsers.add(u1);
talentUsers.add(u2);
talentUsers.add(u3);
}
/**
* Check whether it is repeated
*/
public boolean checkForDuplicates(String name, String phone) {
// If the number of names is equal to the number of mobile phone numbers, but not equal to 0, it is a duplicate
return talentUsers.stream().anyMatch(e -> e.getName().equals(name) && e.getPhone().equals(phone));
}
}
The data with ID 1 in Mock data is duplicate with the data in example Excel2.
EasyPOI provides a verification interface, which requires us to write a class for verification. In this class, each row of data during import can be verified. The framework judges whether the verification passes through through the ExcelVerifyHandlerResult object. If the verification fails, ErrorMsg needs to be passed.
@Component
public class TalentImportVerifyHandler implements IExcelVerifyHandler<TalentUserInputEntity> {
@Resource
private MockTalentDataService mockTalentDataService;
@Override
public ExcelVerifyHandlerResult verifyHandler(TalentUserInputEntity inputEntity) {
StringJoiner joiner = new StringJoiner(",");
// Judge whether the data is repeated according to the name and mobile phone number
String name = inputEntity.getName();
String phone = inputEntity.getPhone();
// mock database
boolean duplicates = mockTalentDataService.checkForDuplicates(name, phone);
if (duplicates) {
joiner.add("Duplicate data with database data");
}
if (joiner.length() != 0) {
return new ExcelVerifyHandlerResult(false, joiner.toString());
}
return new ExcelVerifyHandlerResult(true);
}
}
Modify the verification code and set the verification class object.
@Resource
private TalentImportVerifyHandler talentImportVerifyHandler;
@PostMapping("/upload")
public Boolean upload(@RequestParam("file") MultipartFile multipartFile) throws Exception {
ImportParams params = new ImportParams();
// The header is set to 2 rows
params.setHeadRows(2);
// The title line is set to 0. The default is 0. It can not be set
params.setTitleRows(0);
// Enable Excel verification
params.setNeedVerfiy(true);
params.setVerifyHandler(talentImportVerifyHandler);
ExcelImportResult<TalentUserInputEntity> result = ExcelImportUtil.importExcelMore(multipartFile.getInputStream(),
TalentUserInputEntity.class, params);
System.out.println("Check failed: " + result.isVerfiyFail());
System.out.println("Collection with failed validation:" + JSONObject.toJSONString(result.getFailList()));
System.out.println("Set passed verification:" + JSONObject.toJSONString(result.getList()));
for (TalentUserInputEntity entity : result.getFailList()) {
int line = entity.getRowNum() + 1;
String msg = "The first" + line + "The error in line is:" + entity.getErrorMsg();
System.out.println(msg);
}
return true;
}
Upload the sample Excel2 file and output the test results:
The data in the seventh row is exactly the same as the data in Mock.
3, Collection object verification of custom verification of import value
Another problem to be solved above is that Hibernate Validator annotation verification is added to the objects in the Collection, but it does not take effect. Let's solve it now. In the previous step, the verification class of the imported object is implemented. The verification class will verify every piece of data in Excel. Can I directly verify the objects in the Collection in the verification class? Practice has proved that it doesn't work, because the verifyHandler method of this verification class will only be called once, so there is only one record in the Collection. Since it doesn't work here, you can only verify the import results.
Because the data in the Collection cannot be verified by EasyPOI, the problematic data may also be put into result.getList() instead of result.getFailList(). In order to verify, the two collections need to be combined into one Collection, and the object in the Collection needs to be verified by using the tool class PoiValidationUtil provided by EasyPOI.
@Resource
private TalentImportVerifyHandler talentImportVerifyHandler;
@PostMapping("/upload")
public Boolean upload(@RequestParam("file") MultipartFile multipartFile) throws Exception {
ImportParams params = new ImportParams();
// The header is set to 2 rows
params.setHeadRows(2);
// The title line is set to 0. The default is 0. It can not be set
params.setTitleRows(0);
// Enable Excel verification
params.setNeedVerfiy(true);
params.setVerifyHandler(talentImportVerifyHandler);
ExcelImportResult<TalentUserInputEntity> result = ExcelImportUtil.importExcelMore(multipartFile.getInputStream(),
TalentUserInputEntity.class, params);
System.out.println("Check failed: " + result.isVerfiyFail());
System.out.println("Collection with failed validation:" + JSONObject.toJSONString(result.getFailList()));
System.out.println("Set passed verification:" + JSONObject.toJSONString(result.getList()));
// Merge result set
List<TalentUserInputEntity> resultList = new ArrayList<>();
resultList.addAll(result.getFailList());
resultList.addAll(result.getList());
for (TalentUserInputEntity inputEntity : resultList) {
StringJoiner joiner = new StringJoiner(",");
joiner.add(inputEntity.getErrorMsg());
// Verifying elements of a Collection
inputEntity.getExperienceList().forEach(e -> verify(joiner, e));
inputEntity.getEducationList().forEach(e -> verify(joiner, e));
inputEntity.getAwardList().forEach(e -> verify(joiner, e));
inputEntity.getPunishmentList().forEach(e -> verify(joiner, e));
inputEntity.setErrorMsg(joiner.toString());
}
for (TalentUserInputEntity entity : result.getFailList()) {
int line = entity.getRowNum() + 1;
String msg = "The first" + line + "The error in line is:" + entity.getErrorMsg();
System.out.println(msg);
}
return true;
}
private void verify(StringJoiner joiner, Object object) {
String validationMsg = PoiValidationUtil.validation(object, null);
if (StringUtils.isNotEmpty(validationMsg)) {
joiner.add(validationMsg);
}
}
Upload the sample Excel2, and the results are as follows:
4, Excel duplicate line verification for custom verification of imported values
In the above, the data in Excel and database data are double checked. Some requirements require that the database needs to check the duplicate rows of Excel before warehousing. This needs to be done in the verification class, but there are not all rows of data in the verification class. How to implement it? The blogger's approach is to put the imported data into ThreadLocal for temporary storage, so as to verify the duplicate rows of Excel in the verification class. After using ThreadLocal, be sure to clean it in time!
First, define what is a duplicate line. Two identical lines are duplicate lines. In this paper, set the duplicate lines with the same behavior as name and phone. Since we only need to compare these two fields, we need to rewrite the equals and hashCode methods of the imported object.
@Data
public class TalentUserInputEntity implements IExcelDataModel, IExcelModel {
// Regular time format verification
public static final String DATE_REGEXP = "(Mon|Tue|Wed|Thu|Fri|Sat|Sun)( )(Dec|Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov)( )\\d{2}( )(00:00:00)( )(CST)( )\\d{4}";
/**
* Line number
*/
private int rowNum;
/**
* Error message
*/
private String errorMsg;
@Excel(name = "full name*")
@NotBlank(message = "[full name]Cannot be empty")
private String name;
@Excel(name = "Gender*", replace = {"male_0", "female_1"})
@Pattern(regexp = "[01]", message = "Gender error")
private String genderStr;
@Excel(name = "cell-phone number*")
@Pattern(regexp = "[0-9]{11}", message = "Incorrect mobile phone number")
private String phone;
@Excel(name = "Start working time*")
@Pattern(regexp = DATE_REGEXP, message = "[Start working time]Time format error")
private String workTimeStr;
@Excel(name = "nation*")
@NotBlank(message = "[nation]Cannot be empty")
private String national;
@Excel(name = "language proficiency*")
@NotBlank(message = "[language proficiency]Cannot be empty")
private String languageProficiency;
@Excel(name = "date of birth*")
@Pattern(regexp = DATE_REGEXP, message = "[date of birth]Time format error")
private String birthStr;
@Excel(name = "position*")
@NotBlank(message = "[position]Cannot be empty")
private String jobsName;
@Excel(name = "Position type*")
@NotBlank(message = "[Position type]Cannot be empty")
private String categoryName;
@Excel(name = "salary*", replace = {"3K following_1", "3K-5K_2", "5K-10K_3", "10K-20K_4", "20K-50K_5", "50K above_6"})
@Pattern(regexp = "[123456]", message = "Salary information error")
private String salaryStr;
@Excel(name = "Duty station*")
@NotBlank(message = "[Duty station]Cannot be empty")
private String workArea;
@ExcelCollection(name = "work experience*")
private List<ExperienceInputEntity> experienceList;
@ExcelCollection(name = "Educational experience*")
private List<EducationInputEntity> educationList;
@ExcelCollection(name = "Awards")
private List<AwardsInputEntity> awardList;
@ExcelCollection(name = "Skill certificate")
private List<PunishmentInputEntity> punishmentList;
@Excel(name = "Specialty")
private String specialty;
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
TalentUserInputEntity that = (TalentUserInputEntity) o;
return Objects.equals(name, that.name) &&
Objects.equals(phone, that.phone);
}
@Override
public int hashCode() {
return Objects.hash(name, phone);
}
@Override
public String getErrorMsg() {
return errorMsg;
}
@Override
public void setErrorMsg(String errorMsg) {
this.errorMsg = errorMsg;
}
@Override
public Integer getRowNum() {
return rowNum;
}
@Override
public void setRowNum(Integer rowNum) {
this.rowNum = rowNum;
}
}
Modify the verification class code to realize the verification logic of repeated lines
@Component
public class TalentImportVerifyHandler implements IExcelVerifyHandler<TalentUserInputEntity> {
private final ThreadLocal<List<TalentUserInputEntity>> threadLocal = new ThreadLocal<>();
@Resource
private MockTalentDataService mockTalentDataService;
@Override
public ExcelVerifyHandlerResult verifyHandler(TalentUserInputEntity inputEntity) {
StringJoiner joiner = new StringJoiner(",");
// Judge whether the data is repeated according to the name and mobile phone number
String name = inputEntity.getName();
String phone = inputEntity.getPhone();
// mock database
boolean duplicates = mockTalentDataService.checkForDuplicates(name, phone);
if (duplicates) {
joiner.add("Duplicate data with database data");
}
List<TalentUserInputEntity> threadLocalVal = threadLocal.get();
if (threadLocalVal == null) {
threadLocalVal = new ArrayList<>();
}
threadLocalVal.forEach(e -> {
if (e.equals(inputEntity)) {
int lineNumber = e.getRowNum() + 1;
joiner.add("Data and section" + lineNumber + "Row repeat");
}
});
// Add row data object to ThreadLocal
threadLocalVal.add(inputEntity);
threadLocal.set(threadLocalVal);
if (joiner.length() != 0) {
return new ExcelVerifyHandlerResult(false, joiner.toString());
}
return new ExcelVerifyHandlerResult(true);
}
public ThreadLocal<List<TalentUserInputEntity>> getThreadLocal() {
return threadLocal;
}
}
Because ThreadLocal is used in the verification class, it needs to be released in time to modify the code at the import.
@Resource
private TalentImportVerifyHandler talentImportVerifyHandler;
@PostMapping("/upload")
public Boolean upload(@RequestParam("file") MultipartFile multipartFile) throws Exception {
ExcelImportResult<TalentUserInputEntity> result;
try {
ImportParams params = new ImportParams();
// The header is set to 2 rows
params.setHeadRows(2);
// The title line is set to 0. The default is 0. It can not be set
params.setTitleRows(0);
// Enable Excel verification
params.setNeedVerfiy(true);
params.setVerifyHandler(talentImportVerifyHandler);
result = ExcelImportUtil.importExcelMore(multipartFile.getInputStream(),
TalentUserInputEntity.class, params);
} finally {
// Clear threadLocal to prevent memory leaks
ThreadLocal<List<TalentUserInputEntity>> threadLocal = talentImportVerifyHandler.getThreadLocal();
if (threadLocal != null) {
threadLocal.remove();
}
}
System.out.println("Check failed: " + result.isVerfiyFail());
System.out.println("Collection with failed validation:" + JSONObject.toJSONString(result.getFailList()));
System.out.println("Set passed verification:" + JSONObject.toJSONString(result.getList()));
// Merge result set
List<TalentUserInputEntity> resultList = new ArrayList<>();
resultList.addAll(result.getFailList());
resultList.addAll(result.getList());
for (TalentUserInputEntity inputEntity : resultList) {
StringJoiner joiner = new StringJoiner(",");
joiner.add(inputEntity.getErrorMsg());
// Verifying elements of a Collection
inputEntity.getExperienceList().forEach(e -> verify(joiner, e));
inputEntity.getEducationList().forEach(e -> verify(joiner, e));
inputEntity.getAwardList().forEach(e -> verify(joiner, e));
inputEntity.getPunishmentList().forEach(e -> verify(joiner, e));
inputEntity.setErrorMsg(joiner.toString());
}
for (TalentUserInputEntity entity : result.getFailList()) {
int line = entity.getRowNum() + 1;
String msg = "The first" + line + "The error in line is:" + entity.getErrorMsg();
System.out.println(msg);
}
return true;
}
private void verify(StringJoiner joiner, Object object) {
String validationMsg = PoiValidationUtil.validation(object, null);
if (StringUtils.isNotEmpty(validationMsg)) {
joiner.add(validationMsg);
}
}
Import the sample Excel2, and the results are as follows:
5, Case
Entity class
CourseEntity.java
package com.mye.hl11easypoi.api.pojo;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import cn.afterturn.easypoi.excel.annotation.ExcelEntity;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import cn.afterturn.easypoi.handler.inter.IExcelDataModel;
import cn.afterturn.easypoi.handler.inter.IExcelModel;
import lombok.Data;
import java.util.List;
@Data
@ExcelTarget("courseEntity")
public class CourseEntity implements java.io.Serializable, IExcelModel, IExcelDataModel {
/**
* Primary key
*/
private String id;
/**
* Course name
* needMerge Whether to merge cells vertically (for multiple rows created by list)
*/
@Excel(name = "Course name", orderNum = "0", width = 25, needMerge = true)
private String name;
/**
* Teacher primary key
*/
// @ExcelEntity(id = "major")
private TeacherEntity chineseTeacher;
/**
* Teacher primary key
*/
@ExcelEntity(id = "absent")
private TeacherEntity mathTeacher;
@ExcelCollection(name = "student", orderNum = "3")
private List<StudentEntity> students;
private String errorMsg; //Customize an errorMsg to accept the get and setErrorMsg methods that override the IExcelModel interface below.
private Integer rowNum; //Customize a rowNum to accept the get and setRowNum methods that override the IExcelModel interface below.
@Override
public String getErrorMsg() {
return errorMsg;
}
@Override
public void setErrorMsg(String errorMsg) {
this.errorMsg = errorMsg;
}
@Override
public Integer getRowNum() {
return rowNum;
}
@Override
public void setRowNum(Integer rowNum) {
this.rowNum = rowNum;
}
}
StudentEntity.java
package com.mye.hl11easypoi.api.pojo;
import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;
import java.util.Date;
@Data
public class StudentEntity implements java.io.Serializable {
/**
* id
*/
private String id;
/**
* Student name
*/
@Excel(name = "Student name", height = 20, width = 30, isImportField = "true")
private String name;
/**
* Student gender
*/
@Excel(name = "Student gender", replace = {"male_1", "female_2"}, suffix = "living", isImportField = "true")
private int sex;
@Excel(name = "date of birth", exportFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd", isImportField = "true", width = 20)
private Date birthday;
@Excel(name = "Admission date", exportFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd")
private Date registrationDate;
}
TeacherEntity.java
package com.mye.hl11easypoi.api.pojo;
import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;
@Data
public class TeacherEntity {
/**
* Teacher name
* isImportField When importing excel, verify the fields in Excel. If there is no such field, the import fails
*/
@Excel(name = "Teacher name", width = 30, orderNum = "1" ,isImportField = "true")
private String name;
/**
* Teacher gender
* replace Replace the value, ` replace = {"male _1", "female _2"} ` replace the value of 1 with male
* suffix Text suffix
*/
@Excel(name = "Teacher gender", replace = {"male_1", "female_2"}, suffix = "living", isImportField = "true",orderNum = "2")
private int sex;
}
Custom verification class
package com.mye.hl11easypoi.api.verifyHandler;
import cn.afterturn.easypoi.excel.entity.result.ExcelVerifyHandlerResult;
import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;
import com.mye.hl11easypoi.api.pojo.CourseEntity;
public class MyVerifyHandler implements IExcelVerifyHandler<CourseEntity> {
@Override
public ExcelVerifyHandlerResult verifyHandler(CourseEntity courseEntity) {
ExcelVerifyHandlerResult result = new ExcelVerifyHandlerResult();
//Suppose we want to add users,
//Now go to the database to query getName. If it exists, it means that the verification fails.
//Suppose there is a getName test course in the database
if ("Test course".equals(courseEntity.getName())) {
result.setMsg("The course already exists");
result.setSuccess(false);
return result;
}
result.setSuccess(true);
return result;
}
}
Test class
package com.mye.hl11easypoi;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.excel.imports.ExcelImportService;
import cn.hutool.json.JSONUtil;
import com.mye.hl11easypoi.api.pojo.*;
import com.mye.hl11easypoi.api.verifyHandler.MyVerifyHandler;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.*;
@SpringBootTest(classes = Hl11EasypoiApplication.class)
@RunWith(SpringRunner.class)
public class TestPOI {
@Test
public void testExportExcel() throws Exception {
List<CourseEntity> courseEntityList = new ArrayList<>();
CourseEntity courseEntity = new CourseEntity();
courseEntity.setId("1");
courseEntity.setName("Test course");
// the second
CourseEntity courseEntity1 = new CourseEntity();
courseEntity1.setId("2");
courseEntity1.setName("mathematics");
TeacherEntity teacherEntity1 = new TeacherEntity();
teacherEntity1.setSex(1);
teacherEntity1.setName("Miss Li");
TeacherEntity teacherEntity = new TeacherEntity();
teacherEntity.setName("Miss Zhang");
teacherEntity.setSex(1);
courseEntity.setMathTeacher(teacherEntity);
courseEntity1.setMathTeacher(teacherEntity1);
List<StudentEntity> studentEntities = new ArrayList<>();
for (int i = 1; i <= 2; i++) {
StudentEntity studentEntity = new StudentEntity();
studentEntity.setName("student" + i);
studentEntity.setSex(i);
studentEntity.setBirthday(new Date());
studentEntities.add(studentEntity);
}
courseEntity.setStudents(studentEntities);
courseEntity1.setStudents(studentEntities);
courseEntityList.add(courseEntity);
courseEntityList.add(courseEntity1);
System.out.println(courseEntityList+"11111111111111");
Date start = new Date();
Workbook workbook = ExcelExportUtil.exportExcel( new ExportParams("Export test",
null, "test"),
CourseEntity.class, courseEntityList);
System.out.println(new Date().getTime() - start.getTime());
File savefile = new File("E:/desktop/excel/");
if (!savefile.exists()) {
savefile.mkdirs();
}
FileOutputStream fos = new FileOutputStream("E:/desktop/excel/Teacher course student export test.xls");
workbook.write(fos);
fos.close();
}
@Test
public void testImport2() throws Exception {
// Parameter 1: import excel file stream parameter 2: import type parameter 3: imported configuration object
ImportParams importParams = new ImportParams();
importParams.setTitleRows(1); // How many rows does the title column occupy
importParams.setHeadRows(2); // Set the number of lines occupied by the field name, i.e. header
importParams.setNeedVerify(true);//Turn on verification
importParams.setVerifyHandler(new MyVerifyHandler());
importParams.setStartSheetIndex(0); // Set the number of tables to read from. Here 0 represents the first table. It is read from the first table by default
BufferedInputStream bis = new BufferedInputStream(new FileInputStream(new File("E:/desktop/excel/Teacher course student export test.xls")));
ExcelImportResult result = new ExcelImportService().importExcelByIs(bis, CourseEntity.class, importParams, true);
//This is imported correctly
List<CourseEntity> list = result.getList();
System.out.println("Successfully imported collection:"+JSONUtil.toJsonStr(list));
List<CourseEntity> failList = result.getFailList();
System.out.println("Failed to import collection"+JSONUtil.toJsonStr(failList));
for (CourseEntity courseEntity : failList) {
int line = courseEntity.getRowNum();
String msg = "The first" + line + "Line error is:" + courseEntity.getErrorMsg();
System.out.println(msg);
}
//Return the error excel information to the client
ExportParams exportParams = new ExportParams();
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, CourseEntity.class, failList);
// HttpServletResponse response = null;
// response.setHeader("content-Type", "application/vnd.ms-excel");
// Response.setheader ("content disposition", "attachment; filename =" + urlencoder.encode ("user data table", "UTF-8") + ".xls");
// response.setCharacterEncoding("UTF-8");
// workbook.write(response.getOutputStream());
FileOutputStream fos = new FileOutputStream("E:/desktop/excel/User data sheet.xls");
workbook.write(fos);
fos.close();
}
}
Export results
Import results
