Article catalogue
- 1, Introduction to EasyExcel
- 2, Add function of course classification (back end)
-
-
- 1. Introducing easyexcel dependency
- 2. Use the code generator to generate the course classification code
- 3. Create correspondence between entity class and excel
- 4. Write EduSubjectController class
- 5. Write the EduSubjectServiceImpl class
- 6. Create read Excel listener
- 7. Restart the oss service and upload the test files in Swagger
-
- 3, Add function of course classification (front end)
- 4, Course classification list (back end)
- 5, Course classification list (front end)
1, Introduction to EasyExcel
1. Application scenarios for Excel import and export
- Data import: reduce the input workload
- Data export: statistical information archiving
- Data transmission: data transmission between heterogeneous systems
2. EasyExcel features
The well-known frameworks for parsing and generating Excel in the Java field include Apache poi, jxl, etc. But they all have a serious problem, which is very memory consumption. If your system has a small amount of concurrency, it may be OK, but once concurrency comes up, it will be full gc of OOM or JVM frequently.
EasyExcel is an open source excel processing framework of Alibaba, which is famous for its simple use and memory saving. The main reason why EasyExcel can greatly reduce the memory consumption is that it does not load all the file data into the memory at one time when parsing excel, but reads the data line by line from the disk and parses it one by one.
EasyExcel adopts the line by line parsing mode, and notifies the processing (analysis event listener) of the parsing results of one line in the mode of observer.
3. Case 1: Excel write operation by easyexcel
1. Introducing pom dependency in xml
<dependencies>
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.1</version>
</dependency>
<!-- because EasyExcel The bottom is Poi Therefore, it is necessary to introduce poi Dependence of -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
</dependencies>
2. Create entity class
@Data
public class WriteData {
//Set excel header name
@ExcelProperty("Student number")
private Integer sno;
@ExcelProperty("Student name")
private String sname;
}
3. Implement write operation
public static void testWrite(){
//Write operation of excel
//1. Set write folder address and excel name
String filename = "F:\write.xlsx";
//2. Call the method in easyexcel to realize the write operation
EasyExcel.write(filename, WriteData.class).sheet("Student list").doWrite(getData());
}
public static List <WriteData> getData(){
List <WriteData> list = new ArrayList <>();
for(int i = 0;i < 10;i++){
String sname = "lucy"+i;
list.add(new WriteData(i, sname));
}
return list;
}
4. Case 2:EasyExcel reading operation
1. Create an entity class and mark the corresponding column relationship
@Data
public class ReadData {
//Set excel header name
@ExcelProperty(index = 0)
private Integer sno;
@ExcelProperty(index = 1)
private String sname;
}
2. Create listener to read excel file
public class ExcelListner extends AnalysisEventListener<ReadData> {
List<ReadData> list = new ArrayList<ReadData>();
//Read line by line
@Override
public void invoke(ReadData readData, AnalysisContext analysisContext) {
System.out.println("****"+readData);
list.add(readData);
}
//Read Excel header 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) {
}
}
3. Read operation
public static void testRead(){
String filename = "F:\write.xlsx";
EasyExcel.read(filename,ReadData.class,new ExcelListner()).sheet().doRead();
}
2, Add function of course classification (back end)
Core: EasyExcel reading excel content implementation
1. Introducing easyexcel dependency
2. Use the code generator to generate the course classification code
3. Create correspondence between entity class and excel
@Data
public class SubjectData {
@ExcelProperty(index = 0)
private String oneSujectName;
@ExcelProperty(index = 1)
private String twoSujectName;
}
4. Write EduSubjectController class
@Api(description = "Course classification")
@RestController
@RequestMapping("/eduservice/subject")
@CrossOrigin
public class EduSubjectController {
@Autowired
private EduSubjectService eduSubjectService;
//Add course classification
@ApiOperation(value = "Add course classification")
@PostMapping("addSubjects")
public R addSubjects(MultipartFile file){
boolean flag = eduSubjectService.saveSubjects(file);
if(flag){
return R.ok().message("File imported successfully!");
}else{
return R.error().message("File import failed!");
}
}
}
5. Write the EduSubjectServiceImpl class
@Service
public class EduSubjectServiceImpl extends ServiceImpl<EduSubjectMapper, EduSubject> implements EduSubjectService {
@Autowired
private EduSubjectService eduSubjectService;
//Add course classification
@Override
public boolean saveSubjects(MultipartFile file) {
try {
InputStream inputStream = file.getInputStream();
//Read excel file
EasyExcel.read(inputStream, SubjectData.class, new SubjectExcelListner(eduSubjectService)).sheet().doRead();
return true;
}catch (Exception e){
e.printStackTrace();
return false;
}
}
}
6. Create read Excel listener
public class SubjectExcelListner extends AnalysisEventListener<SubjectData> {
//Because SubjectExcelListner is used in EduSubjectServiceImpl in the form of new every time, SubjectExcelListner cannot be handed over to
//Spring manages, so you can't inject objects with @ Autowird or @ Resource annotations
//However, this class needs to call the methods in the service for database operation. How to use it?
//You can pass parameters through construction methods and use service objects
private EduSubjectService eduSubjectService;
public SubjectExcelListner(EduSubjectService eduSubjectService) {
this.eduSubjectService = eduSubjectService;
}
public SubjectExcelListner() {}
//Read line by line
@Override
public void invoke(SubjectData subjectData, AnalysisContext analysisContext) {
if(subjectData==null){
throw new GuLiException(20001,"Data is empty!");
}
//Add primary classification
EduSubject oneSubject = this.existOneSubject(subjectData.getOneSubjectName());
if(oneSubject==null){//There is no same primary classification
oneSubject = new EduSubject();
oneSubject.setTitle(subjectData.getOneSubjectName());
oneSubject.setParentId("0");
eduSubjectService.save(oneSubject);
}
//Get primary classification id
String pid = oneSubject.getId();
//Add secondary classification
EduSubject twoSubject = this.existTwoSubject(subjectData.getTwoSubjectName(), pid);
if(twoSubject==null){
twoSubject = new EduSubject();
twoSubject.setTitle(subjectData.getTwoSubjectName());
twoSubject.setParentId(pid);
eduSubjectService.save(twoSubject);
}
}
//Read Excel header
@Override
public void invokeHeadMap(Map <Integer, String> headMap, AnalysisContext context) {
System.out.println("Header information :"+headMap);
}
//Operation after reading
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
//Judge whether the primary classification is repeated
private EduSubject existOneSubject(String name){
QueryWrapper <EduSubject> wrapper = new QueryWrapper <>();
wrapper.eq("title", name).eq("parent_id",0);
EduSubject subject = eduSubjectService.getOne(wrapper);
return subject;
}
//Judge whether the secondary classification is repeated
private EduSubject existTwoSubject(String name,String pid){
QueryWrapper <EduSubject> wrapper = new QueryWrapper <>();
wrapper.eq("title", name).eq("parent_id", pid);
EduSubject subject = eduSubjectService.getOne(wrapper);
return subject;
}
}
7. Restart the oss service and upload the test files in Swagger
http://localhost:8001/swagger-ui.html
3, Add function of course classification (front end)
1. Add course classification route
/src/router/index.js:
{
path: '/subject',
component: Layout, //layout
redirect: '/subject/table',
name: 'SubjectSort',
meta: { title: 'Course classification management', icon: 'nested' },
children: [{
path: 'list',
name: 'EduSubjectList',
component: () =>
import ('@/views/edu/subject/list'),
meta: { title: 'Course classification list', icon: 'table' }
},
{
path: 'import',
name: 'EduSubjectImport',
component: () =>
import ('@/views/edu/subject/import'),
meta: { title: 'Import course classification', icon: 'tree' }
}
]
}
2. Create a course classification page and modify the page path corresponding to the route
3. The effect is achieved on the page of adding course classification
Compose page:
<template>
<div class="app-container">
<el-form label-width="120px">
<el-form-item label="Information description">
<el-tag type="info">excel Template description</el-tag>
<el-tag>
<i class="el-icon-download"/>
<a :href="OSS_PATH + '/excel/template.xlsx'">Click download template</a>
</el-tag>
</el-form-item>
<el-form-item label="choice Excel">
<el-upload
ref="upload"
:auto-upload="false"
:on-success="fileUploadSuccess"
:on-error="fileUploadError"
:disabled="importBtnDisabled"
:limit="1"
:action="BASE_API+'/eduservice/subject/addSubjects'"
name="file"
accept="application/vnd.ms-excel">
<el-button slot="trigger" size="small" type="primary">Select file</el-button>
<el-button
:loading="false"
style="margin-left: 10px;"
size="small"
type="success"
@click="submitUpload">{{ fileUploadBtnText }}</el-button>
</el-upload>
</el-form-item>
</el-form>
</div>
</template>
Writing js method:
<script>
export default {
data() {
return {
BASE_API: process.env.BASE_API, // Interface API address
OSS_PATH: process.env.OSS_PATH, // Alibaba cloud OSS address
fileUploadBtnText: 'Upload to server', // Button text
importBtnDisabled: false, // Whether the button is disabled,
loading: false
}
},
created() {
},
methods: {
//Upload successfully called method
fileUploadSuccess(response){
if (response.success === true) {
this.fileUploadBtnText = 'Import succeeded'
this.loading = false
this.$message({
type: 'success',
message: response.message
})
}
//Jump to list page
this.$router.push({path:'/subject/list'})
},
//Method called when upload failed
fileUploadError(response){
if(response.success === false){
this.fileUploadBtnText = 'Import failed'
this.loading = false
this.$message({
type: 'error',
message: response.message
})
}
//Jump to list page
this.$router.push({path:'/subject/list'})
},
//Click the button to upload the file to the interface
submitUpload(){
this.importBtnDisabled = true //Upload button disabled
this.loading = true
this.$refs.upload.submit()
}
}
}
</script>
4. Start service test
4, Course classification list (back end)
1. Create the corresponding entity class according to the returned data
//Primary classification
@Data
public class OneSubject {
private String id;
private String title;
//There are multiple secondary classifications in a primary classification
private List<TwoSubject> children = new ArrayList<>();
}
//Secondary classification
@Data
public class TwoSubject {
private String id;
private String title;
}
The returned data format is:
2. Write Controller class
@ApiOperation(value = "Query all course classifications")
@GetMapping("getAllSubject")
public R getAllSubject(){
List <OneSubject> oneSubjectList = eduSubjectService.getAllSubject();
return R.ok().data("list",oneSubjectList);
}
3. Write Service class
//Query all course categories = = > tree
@Override
public List <OneSubject> getAllSubject() {
//1. Query all primary classification parents_ id=0
QueryWrapper<EduSubject> wrapper = new QueryWrapper<EduSubject>();
wrapper.eq("parent_id",0);
List <EduSubject> oneSubjectList = this.baseMapper.selectList(wrapper);
//2. Query all secondary classification parents_ id!= 0
wrapper = new QueryWrapper<EduSubject>();
wrapper.ne("parent_id",0);
List <EduSubject> twoSubjectList = this.baseMapper.selectList(wrapper);
//Define the final return type
List <OneSubject> finalSubjectList = new ArrayList <OneSubject>();
///3 primary classification of packaging
//Query all the level-1 classification list sets, get each level-1 classification object, and go back to the value of each level-1 classification object,
//Encapsulated in the required list collection list < onesubject > findsubjectlist
//List<EduSubject> ==> List <OneSubject> finalSubjectList
for (EduSubject subject : oneSubjectList) {
//Encapsulate oneSubjectList into finalSubjectList
OneSubject oneSubject = new OneSubject();
//Both of the following methods can be used
BeanUtils.copyProperties(subject,oneSubject);
// oneSubject.setId(subject.getId());
// oneSubject.setTitle(subject.getTitle());
//Loop through and query all the secondary classifications in the primary classification
//Create a list set to encapsulate the secondary classification of each primary classification
List <TwoSubject> children = new ArrayList <>();
for (EduSubject eduSubject : twoSubjectList) {
TwoSubject twoSubject = new TwoSubject();
if (eduSubject.getParentId().equals(subject.getId())){
BeanUtils.copyProperties(eduSubject,twoSubject);
children.add(twoSubject);
}
}
oneSubject.setChildren(children);
finalSubjectList.add(oneSubject);//Add the result set and put the statement after 76 lines. You can also think about why???
}
return finalSubjectList;
}
4. Test with swagger
...
5, Course classification list (front end)
1. Write front-end interface
import request from '@/utils/request'
export default {
//Query all course classifications
getAllSubject() {
return request({
url: `/eduservice/subject/getAllSubject`,
method: 'get'
})
}
}
2. Refer to the tree module to integrate the front end
<template>
<div class="app-container">
<el-input v-model="filterText" placeholder="Filter keyword" style="margin-bottom:30px;" />
<el-tree
ref="tree2"
:data="data2"
:props="defaultProps"
:filter-node-method="filterNode"
class="filter-tree"
default-expand-all
/>
</div>
</template>
3. Front end interface call
<script>
import subject from '@/api/edu/subject'
export default {
data() {
return {
filterText: '',
data2: [],
defaultProps: {
children: 'children',
label: 'title'
}
}
},
watch: {
filterText(val) {
this.$refs.tree2.filter(val)
}
},
created() {
//After the page is loaded, it is called.
this.getSubjectList()
},
methods: {
getSubjectList(){
subject.getAllSubject().then(response=>{
this.data2 = response.data.list;
})
},
filterNode(value, data) {
if (!value) return true
return data.title.toLowerCase().indexOf(value) !== -1 //Search is not case sensitive
}
}
}
</script>
4. Start project service test
