Maven introduces dependencies

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.6</version>
</dependency>
Copy the code

Export data

The controller layer


@Autowired
private DictService dictService;

@apiOperation (value = "export dictionary data ")
@GetMapping(value = "exportData")
public void exportData(HttpServletResponse response){
    dictService.exportData(response);
}
Copy the code

Service layer: Only the code inside the implementation class is given here

/** * export field data to Excel *@param response
 */
@Override
public void exportData(HttpServletResponse response) {


    try {
        // Set the format of the returned data
        response.setContentType("application/vnd.ms-excel");
        // Set the encoding of the returned data
        response.setCharacterEncoding("utf-8");
        // Here URLEncoder. Encode can prevent Chinese garbled of course and easyExcel has no relationship
        String fileName = URLEncoder.encode("Data dictionary"."UTF-8");
        response.setHeader("Content-disposition"."attachment; filename="+ fileName + ".xlsx");

        // Query the list of dictionary data from the data sky
        Dict represents the mapping entity class for database tables. DictEeVo represents the mapping entity class for Excel data
        You need to change the list of data generics to dictEeVo in order to write data to Excel files
        List<Dict> dictList = baseMapper.selectList(null);

        List<DictEeVo> dictEeVoList = new ArrayList<>(dictList.size());

        for(Dict dict : dictList) {
            DictEeVo dictVo = new DictEeVo();

            // Copy the data of one entity class to another entity class.
            BeanUtils.copyProperties(dict,dictVo);
            dictEeVoList.add(dictVo);
        }
        // Excel write data output stream Excel entity class map export template name
        EasyExcel.write(response.getOutputStream(),DictEeVo.class).sheet("Data dictionary")
                // List of data to export
                .doWrite(dictEeVoList);

    } catch(IOException e) { e.printStackTrace(); }}Copy the code

Mapper layer query data write: export scenario

Import data

DictListener: After reading the data of excel file, insert data row by row into the data table through the listener

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.atguigu.yygh.cmn.mapper.DictMapper;
import com.atguigu.yygh.model.cmn.Dict;
import com.atguigu.yygh.model.cmn.DictEeVo;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

@Component
public class DictListener extends AnalysisEventListener<DictEeVo> {

    @Autowired
    private DictMapper dictMapper;

    // Read the data line by line
    @Override
    public void invoke(DictEeVo dictEeVo, AnalysisContext analysisContext) {
    
        // Dict corresponds to the database table entity class dictEeVo which represents the Excel data mapping entity class
        Dict dict = new Dict();
        // You need to change the obtained Excel entity class data into the database table mapping entity class data to insert into the database
        BeanUtils.copyProperties(dictEeVo,dict);
        // Setting whether to delete the current line is my business
        dict.setIsDeleted(0);
        // Insert data row by row
        dictMapper.insert(dict);
    }

    // Export data to complete the call
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("Data import completed"); }}Copy the code

The service layer code


@Autowired
private DictListener dictListener;


/** * import dictionary data *@param file
 */
@Override
public void importDictData(MultipartFile file) {
    try {
        The Excel entity class listener inserts data into the table
        EasyExcel.read(file.getInputStream(),DictEeVo.class,dictListener).sheet().doRead();
    } catch(IOException e) { e.printStackTrace(); }}Copy the code

The controller layer code

@apiOperation (value = "")
@PostMapping("importData")
public R importData(MultipartFile file){
    dictService.importDictData(file);
    return R.ok();
}
Copy the code