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

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

usevuespringbooteasyexcelintegrate
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

index.html

html



 <!--  export  -->
 <el-button
 @click=&quot;exportData&quot;
 type=&quot;primary&quot;
 size=&quot;mini&quot;
 icon=&quot;el-icon-upload2&quot;
 >
  export Excel
 </el-button>

 <!--  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>
 <el-table-column prop=&quot;birthday&quot; label=&quot; Birthday &quot; width=&quot;180&quot;>
 </el-table-column>
 <el-table-column prop=&quot;salary&quot; label=&quot; Salary &quot;> </el-table-column>
 </el-table>
 <div>
 <el-pagination
 @size-change=&quot;handleSizeChange&quot;
 @current-change=&quot;handleCurrentChange&quot;
 :current-page=&quot;pageNum&quot;
 :page-sizes=&quot;[2, 5, 10, 20]&quot;
 :page-size=&quot;pageSize&quot;
 background
 layout=&quot;total, sizes, prev, pager, next, jumper&quot;
 :total=&quot;total&quot;
 >
 </el-pagination>
 </div>
 </div>
 </div>
</div>

</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) => {this.total = res.data.sizethis.list = res.data.listconsole.log(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 {
 this.$message.error(response.message)
 }
 },

 // 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
 this.showList()
 },
 handleCurrentChange(val) {
 console.log(` The current page : ${val}`)
 this.pageNum = val
 this.showList()
 },
 },
})

</script></html>2  database sql

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

java

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

@Resource
private StudentMapper studentMapper;

@GetMapping(&quot;list&quot;)
public HashMap<String, Object> list(@RequestParam int pageNum,@RequestParam int pageSize){
 //  Paging query
 Page<Student> page = new Page<>(pageNum, pageSize);
 studentMapper.selectPage(page,null);
 
 //  Encapsulated data
 HashMap<String, Object> map = new HashMap<>();
 ArrayList<ExcelStudentDTO> excelDictDTOList = new ArrayList<>();
 //  Conversion data
 page.getRecords().forEach(student -> {
 ExcelStudentDTO studentDTO = new ExcelStudentDTO();
 BeanUtils.copyProperties(student,studentDTO);
 excelDictDTOList.add(studentDTO);
 });
 
 map.put(&quot;list&quot;,excelDictDTOList);
 map.put(&quot;size&quot;,page.getTotal());
 return map;
}

/**
 *  Import
 * @param file  File object
 */
@RequestMapping(&quot;import&quot;)
@Transactional(rollbackFor = {Exception.class})
public String importData( @RequestParam(&quot;file&quot;) MultipartFile file){
 try {
 //  Read file stream
 EasyExcel.read
 (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();
 log.info(&quot;importData finished&quot;);
 } catch (IOException e) {
 log.info(&quot; Failure &quot;);
 e.printStackTrace();
 }
 return &quot; Upload successful &quot;;
}

/**
 *  Import
 */
@GetMapping(&quot;export&quot;)
public String exportData(HttpServletResponse response){

 try {
 //  Set response body content
 response.setContentType(&quot;application/vnd.ms-excel&quot;);
 response.setCharacterEncoding(&quot;utf-8&quot;);

 //  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;);
 EasyExcel.write(response.getOutputStream()
 ,ExcelStudentDTO.class).sheet().doWrite(studentMapper.selectList(null));
 } catch (Exception e) {
 e.printStackTrace();
 }
 return &quot; Upload successful &quot;;
}

}3.2mapperStudentMapper

java

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

xml

<?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?><!DOCTYPE mapper PUBLIC &quot;-//mybatis.org//DTD Mapper 3.0//EN&quot; &quot;http://mybatis.org/dtd/mybatis-3-mapper.dtd&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.name} ,#{item.birthday} ,#{item.salary})</foreach></insert></mapper>3.3 beanExcelStudentDTO

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

Java

/**

  • 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

java

/**

  • 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

ExcelDictDTOListener

java

/**

  • 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) {log.info(&quot; 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();log.info(&quot; All data analysis completed !&quot;);}
  • /**
  • Plus the storage database */private void saveData() {log.info(&quot;{} Data , Start storing the database !&quot;, list.size());studentMapper.insertBatch(list); // Batch insert log.info(&quot; Storage database success !&quot;);}}3.5 configmybatisPlus Paging plug-ins

MybatisPlusConfig

java

@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 )
 */
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
 MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
 PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
 paginationInnerInterceptor.setDbType(DbType.MYSQL);
 paginationInnerInterceptor.setOverflow(true);
 interceptor.addInnerInterceptor(paginationInnerInterceptor);
 return interceptor;
}

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

}3.6  The configuration file application.yaml

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 :https://www.cnblogs.com/look-word/p/16269739.html

If this article helps you , Like it and pay attention to it !!
版权声明:本文为[InfoQ]所创,转载请带上原文链接,感谢。 https://qdmana.com/2022/134/202205141424380600.html