The project requires that the course data in excel file should be transferred to the database, and the data in excel should be classified.
0. Preface
Introduction to the whole process
Now in the controller, the name path is uploaded, and the excel file is uploaded by calling the method saveSubject(). In saveSubject() created in service, this method is implemented in serviceImpl, and read method in EasyExcel is invoked in saveSubject.
, this method must implement the listener SubjectExcelListener, which reads excel data line by line. According to the characteristics of the file, the names of the first category and the second category cannot be repeated. If the incoming name is empty, it will be added, and if it is not empty, it will be rejected. Therefore, set a method to solve this problem. For example, set the front end as the first category and vue as the second category. Each name has an id and a parent_id, the parent of the first category_ id is 0, P of the second category_ id is the id of the first category, through the passed in name and p_id=0 through subjectservice getOne(wrapper); This method determines whether the first classification is empty, and the same method is used to determine the second classification. When adding, call these two methods to judge whether the first classification and the second classification are added.
In addition, because SubjectExcelListener cannot be managed by spring, it needs its own new and cannot inject other objects, so it needs to pass in serviceimpl by itself. Specifically, it has been written in the implementation code
1. java file generator and code required by the folder
Specifically in this article How to use code generator
The entity class corresponding to the database is also created with the code generator
Note: auto fill mechanism for time use
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("edu_subject")
@ApiModel(value="Subject object", description="Course subjects")
public class Subject implements Serializable {
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "Course category ID")
@TableId(value = "id", type = IdType.ID_WORKER_STR)
private String id;
@ApiModelProperty(value = "Category name")
private String title;
@ApiModelProperty(value = "father ID")
private String parentId;
@ApiModelProperty(value = "sort field")
private Integer sort;
@ApiModelProperty(value = "Creation time")
@TableField(fill = FieldFill.INSERT)
private Date gmtCreate;
@ApiModelProperty(value = "Update time")
@TableField(fill = FieldFill.INSERT_UPDATE)
private Date gmtModfied;
}
2. Create controller
@RestController
@CrossOrigin//Cross domain problem
@RequestMapping("/eduservice/subject")
public class SubjectController {
@Autowired
private SubjectService subjectService;
//Add course classification
//Get the uploaded file and read out the file content
@PostMapping("addSubject")
public UnResult addSubject(MultipartFile file){
//Upload excel file
subjectService.saveSubject(file,subjectService);
return UnResult.ok();
}
}
3. Create entity class corresponding to excel table
index=0 is the first column and index=1 is the second column
/**
* @author ZhangTao
* @date 2021/4/22 15:31
* @note:Create entity class corresponding to excel
*
* It is the first level classification of the header in excel and the columns in the second level classification
*/
@Data
public class SubjectData {
@ExcelProperty(index = 0)
private String oneSubjectName;
@ExcelProperty(index = 1)
private String twoSubjectName;
}4. Write the interface in service and the implementation method in serviceimpl
public interface SubjectService extends IService<Subject> {
//Add course classification
void saveSubject(MultipartFile file,SubjectService subjectService);
}Because the listener is used to read the file in EasyExcel, the fourth step is to write the listener
@Service
public class SubjectServiceImpl extends ServiceImpl<SubjectMapper, Subject> implements SubjectService {
@Override
public void saveSubject(MultipartFile file,SubjectService subjectService) {
try {
//File input stream
InputStream in=file.getInputStream();
//Call method to read
//Let's inject the service directly so that it can be used later
//Because the service cannot be injected into the listener, in this serviceiimpl, the service is injected through the listener, so that the sending method save in the service can be used in the listener/
EasyExcel.read(in, SubjectData.class,new SubjectExcelListener(subjectService)).sheet().doRead();
}catch(Exception e){
e.printStackTrace();
}
}
}5. Write listener
/**
* @author ZhangTao
* @date 2021/4/22 15:42
* @note:monitor
*
* /Because SubjectExcelListener cannot be managed by spring, it needs its own new and cannot inject other objects
* The operation of database cannot be realized
*
*Previously, the service has been injected into the listener,
*/
public class SubjectExcelListener extends AnalysisEventListener<SubjectData> {
public SubjectService subjectService;
//Create a parameterless constructor so that you can add it later,
public SubjectExcelListener(SubjectService subjectService) {
this.subjectService = subjectService;
}
public SubjectExcelListener(){
}
@Override
public void invoke(SubjectData subjectData, AnalysisContext analysisContext) {
if(subjectData==null){
throw new GuliException(20001,"File data is empty");
}
//Read from the second line
//Read line by line. Two values are read each time. The first value is the primary classification and the second is the secondary classification
//Judge whether the first level classification is empty
Subject existOneSubject = this.existOneSubject(subjectService,subjectData.getOneSubjectName());
//If it is blank, there is no same level-1 classification, then add it
if(existOneSubject==null){//There is no same primary classification
existOneSubject=new Subject();
existOneSubject.setParentId("0");
existOneSubject.setTitle(subjectData.getOneSubjectName());//The first level classification name just passed in
subjectService.save(existOneSubject);//A lot has been done before so that the save method can be called by subjectService,
}
//Get the id value of a section classification
//pareat of secondary classification - id is the id value of primary classification
String pid=existOneSubject.getId();
//Add secondary classification
//Judge whether the secondary classification is repeated
Subject existTwoSubject = this.existTwoSubject(subjectService,subjectData.getTwoSubjectName(),pid);
if(existTwoSubject==null){//There is no same primary classification
existTwoSubject=new Subject();
existTwoSubject.setParentId(pid);
existTwoSubject.setTitle(subjectData.getTwoSubjectName());//First level classification name
subjectService.save(existTwoSubject);
}
}
//Judge that the primary classification cannot be added repeatedly
//Make it possible to judge whether there are values in the table according to the passed name and 0 (representing the first level classification) / / Yes
//null if there is no value
private Subject existOneSubject(SubjectService subjectService,String name){
QueryWrapper<Subject> wrapper=new QueryWrapper<>();
wrapper.eq("title",name);
wrapper.eq("parent_id",0);
Subject oneSubject=subjectService.getOne(wrapper);//getOne queries a record according to the Wrapper
return oneSubject;
}
// //Judge that the secondary classification cannot be added repeatedly
private Subject existTwoSubject(SubjectService subjectService,String name,String pid){
QueryWrapper<Subject> wrapper=new QueryWrapper<>();
wrapper.eq("title",name);
wrapper.eq("parent_id",pid);
Subject twoSubject=subjectService.getOne(wrapper);
return twoSubject;
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
test
swagger is used to test
