Use Vue + springboot + easyexcel to integrate import and export data

InfoQ 2022-05-14 14:31:57 阅读数:65

Use VUE+SpringBoot+EasyExcel  Integrate import and export data to create a common maven The project is just

Project directory structure

Use VUE+SpringBoot+EasyExcel  Consolidate import and export data 1  The front end is stored in resources/static  Next



 <!--  export  -->
  export Excel

 <!--  Data presentation  -->
 <el-table :data=&quot;list&quot; stripe style=&quot;width: 100%&quot;>
 <el-table-column prop=&quot;name&quot; label=&quot; full name &quot; width=&quot;180&quot;>
 <el-table-column prop=&quot;birthday&quot; label=&quot; Birthday &quot; width=&quot;180&quot;>
 <el-table-column prop=&quot;salary&quot; label=&quot; Salary &quot;> </el-table-column>
 :page-sizes=&quot;[2, 5, 10, 20]&quot;
 layout=&quot;total, sizes, prev, pager, next, jumper&quot;

</body><script>new Vue({el: '#app',data() {return {dialogVisible: false, // Whether the file upload dialog box displays list: [], //  Dictionary data importUrl: 'http://localhost:8811/api/excel/import',pageNum: 1, //  the number of pages pageSize: 5, //  Number of entries per page total: 1000,}},created() {this.showList()},methods: {showList() {// Use custom configuration const request = axios.create({baseURL: 'http://localhost:8811', //url Prefix timeout: 1000, // Timeout time // headers: { token: 'helen123456' }, // Carry token })request.get('/api/excel/list', {params: {pageNum: this.pageNum,pageSize: this.pageSize,},}).then((res) => { = =})},//  When uploading more than one file fileUploadExceed() {this.$message.warning(' Only one file can be selected ')},//  export exportData() {window.location.href = 'http://localhost:8811/api/excel/export'},

 // Upload successful callback
 fileUploadSuccess(response) {
 if (response.code === 0) {
 this.$message.success(' Data import succeeded ')
 this.dialogVisible = false
 } else {

 // Upload failed callback
 fileUploadError(error) {
 this.$message.error(' Data import failed ')
 *  The number of data displayed on the current page selected by the user
 handleSizeChange(val) {
 console.log(` each page  ${val}  strip `)
 this.pageSize = val
 handleCurrentChange(val) {
 console.log(` The current page : ${val}`)
 this.pageNum = val

</script></html>2  database sql

 varchar(255) DEFAULT NULL COMMENT ' full name ',
 datetime DEFAULT NULL COMMENT ' Birthday ',
 decimal(10,4) DEFAULT NULL COMMENT ' Salary ') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;3  Back end 3.1 contrllerStudentController


@[email protected]@[email protected](&quot;/api/excel/&quot;)public class StudentController {

private StudentMapper studentMapper;

public HashMap<String, Object> list(@RequestParam int pageNum,@RequestParam int pageSize){
 //  Paging query
 Page<Student> page = new Page<>(pageNum, pageSize);
 //  Encapsulated data
 HashMap<String, Object> map = new HashMap<>();
 ArrayList<ExcelStudentDTO> excelDictDTOList = new ArrayList<>();
 //  Conversion data
 page.getRecords().forEach(student -> {
 ExcelStudentDTO studentDTO = new ExcelStudentDTO();
 return map;

 *  Import
 * @param file  File object
@Transactional(rollbackFor = {Exception.class})
public String importData( @RequestParam(&quot;file&quot;) MultipartFile file){
 try {
 //  Read file stream
 (file.getInputStream(),//  Files uploaded from the front end
 ExcelStudentDTO.class,//  Follow excel The corresponding entity class
 new ExcelDictDTOListener(studentMapper))//  Monitor  
 .excelType(ExcelTypeEnum.XLSX)// excel The type of
 .sheet(&quot; Templates &quot;).doRead();;importData finished&quot;);
 } catch (IOException e) {; Failure &quot;);
 return &quot; Upload successful &quot;;

 *  Import
public String exportData(HttpServletResponse response){

 try {
 //  Set response body content

 //  here URLEncoder.encode It can prevent Chinese characters from miscoding   Of course and easyexcel It doesn't matter.
 String fileName = URLEncoder.encode(&quot;myStu&quot;, &quot;UTF-8&quot;).replaceAll(&quot;\\+&quot;, &quot;%20&quot;);
 response.setHeader(&quot;Content-disposition&quot;, &quot;attachment;filename*=utf-8''&quot; + fileName + &quot;.xlsx&quot;);
 } catch (Exception e) {
 return &quot; Upload successful &quot;;



@Mapperpublic interface StudentMapper extends BaseMapper<Student> {void insertBatch(List<ExcelStudentDTO> list);}StudentMapper.xml


<?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?><!DOCTYPE mapper PUBLIC &quot;-// Mapper 3.0//EN&quot; &quot;;><mapper namespace=&quot;look.word.mapper.StudentMapper&quot;><insert id=&quot;insertBatch&quot; >insert into student(name, birthday, salary)values<foreach collection=&quot;list&quot; item=&quot;item&quot; separator=&quot;,&quot;>(#{} ,#{item.birthday} ,#{item.salary})</foreach></insert></mapper>3.3 beanExcelStudentDTO

When importing data   Make sure that excel List names and ExcelStudentDTO Consistent Austria



  • excel The corresponding entity class
  • @author jiejie*/@Datapublic class ExcelStudentDTO {// excel Column name in @ExcelProperty(&quot; full name &quot;)private String name;
  • @ExcelProperty(&quot; Birthday &quot;)private Date birthday;
  • @ExcelProperty(&quot; Salary &quot;)private BigDecimal salary;}Student



  • The entity class corresponding to the database
  • @author jiejie
    /@[email protected](value = &quot;student&quot;)public class Student {/
  • full name */@TableField(value = &quot;name&quot;)private String name;
  • /**
  • Birthday */@TableField(value = &quot;birthday&quot;)private Date birthday;
  • /**
  • Salary */@TableField(value = &quot;salary&quot;)private BigDecimal salary;
  • public static final String COL_NAME = &quot;name&quot;;
  • public static final String COL_BIRTHDAY = &quot;birthday&quot;;
  • public static final String COL_SALARY = &quot;salary&quot;;}3.3 listener Official documents

EasyExcel Reading a file requires




  • monitor
  • While reading the data again   Insert data
  • @author : look-word
  • @date : 2022-05-10 21:35**/@Slf4j//@AllArgsConstructor // Full participation @NoArgsConstructor // No arguments public class ExcelDictDTOListener extends AnalysisEventListener<ExcelStudentDTO> {
  • /**
  • every other 5 Storage database , It can be used in practice 3000 strip , Then clean up list , Convenient for memory recycling */private static final int BATCH_COUNT = 5;List<ExcelStudentDTO> list = new ArrayList<ExcelStudentDTO>();
  • private StudentMapper studentMapper;
  • // Pass in mapper object public ExcelDictDTOListener(StudentMapper studentMapper) {this.studentMapper = studentMapper;}
  • /*** Traverse the records of each row
  • @param data
  • @param context*/@Overridepublic void invoke(ExcelStudentDTO data, AnalysisContext context) {; Resolve to a record : {}&quot;, data);list.add(data);//  achieve BATCH_COUNT 了 , Need to store the database once , Prevent tens of thousands of data in memory , Easy to OOMif (list.size() >= BATCH_COUNT) {saveData();//  Storage complete cleaning  listlist.clear();}}
  • /**
  • All data analysis is done   Will call */@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {//  We also need to save data here , Make sure that the last legacy data is also stored in the database saveData();; All data analysis completed !&quot;);}
  • /**
  • Plus the storage database */private void saveData() {;{} Data , Start storing the database !&quot;, list.size());studentMapper.insertBatch(list); // Batch insert; Storage database success !&quot;);}}3.5 configmybatisPlus Paging plug-ins



@Configurationpublic class MybatisPlusConfig {

 *  New paging plug-in , One is to ease and the other to follow mybatis The rules of ,
 *  Need to set up  MybatisConfiguration#useDeprecatedExecutor = false
 *  Avoid cache problems ( This property will be removed after the old plug-in is removed )
public MybatisPlusInterceptor mybatisPlusInterceptor() {
 MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
 PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
 return interceptor;

public ConfigurationCustomizer configurationCustomizer() {
 return configuration -> configuration.setUseDeprecatedExecutor(false);

}3.6  The configuration file application.yaml


server:port: 8811spring:datasource: # mysql Database connection type: com.zaxxer.hikari.HikariDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://localhost:3306/2022_source_springboot?serverTimezone=GMT%2B8&characterEncoding=utf-8username: rootpassword: 317311mybatis-plus:configuration:# sql journal log-impl: org.apache.ibatis.logging.stdout.StdOutImplmapper-locations:- classpath:mapper/*.xml4  Start test start springboot Oh

Page rendering

Use VUE+SpringBoot+EasyExcel  Integrate import and export data export effect

Use VUE+SpringBoot+EasyExcel  Integrate import and export data. Note

When importing data   Make sure that excel List names and ExcelStudentDTO Consistent Austria

The author of this article : look-word

Link to this article :

If this article helps you , Like it and pay attention to it !!