This is the 22nd day of my participation in the August More Text Challenge

Daily development, export and import scenarios are very many, especially for the background management is a list of export, if from the export business out of reuse code, focus on logic development, it is very important for developers.

1. Preparation

Is the preparation to read the documentation to learn about EasyExcel? No, let’s get straight to it! I found that the most simple example of the recent business has been dealt with! So preparation naturally only need to import EasyExcel JAR package, here we are due to the Springboot project, so directly use Maven. Go straight to the latest version! Pom.xml adds:

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

2. The export Excel

Now that the preparation is complete, the export begins with a Bean class that has the same fields as the Excel file. @data uses Lombok, and @ExcelProperty contains the name of the first line of Excel and the location of the field, starting at 0 and not repeated.

@Data
public class ExportVo {
  @excelProperty (value = "name ", index = 0)
  private String name;

  @excelProperty (value = "time ", index = 1)
  private Date time;

  @NumberFormat("#.##%")
  @excelProperty (value = "completion ", index = 2)
  private Float rate;

}
Copy the code

Next comes the logical implementation:

@PostMapping("/export")
public void export(@RequestBody ExportDto dto, HttpServletResponse response)
    throws IOException {
    String fileName = "Statistical table";
    ExcelUtil.download(response, fileName, ExportVo.class,getExportVoList());
}

private List getExportVoList(a) {
    List
       
       
      
    1The exported list should be limited by time (such as the last month). Avoid exporting Excel too large, which is generally exported in separate sheets (especially XLS files)65535The maximum number of files that can be exported is 5w.2Considering database query and memory pressure, and the possibility that the paging plug-in has a maximum number of pages, you must do paging query when there are many items, and then combine the List object.3Generally, the data directly retrieved from the database does not meet the requirements of exporting fields. Therefore, field conversion is required if necessary. }Copy the code

The exported handling class ExcelUtil:

public class ExcelUtil {

    public static void download(HttpServletResponse response, String fileName, Class cls, List dataList) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fname = URLEncoder.encode(fileName, "utf-8");
        response.setHeader("Content-disposition"."attachment; filename=" + fname + ExcelTypeEnum.XLSX.getValue());

        LongestMatchColumnWidthStyleStrategy longestMatchColumnWidthStyleStrategy = new LongestMatchColumnWidthStyleStrategy();
        EasyExcel.write(response.getOutputStream(), cls)
            .sheet("sheet1") .registerWriteHandler(longestMatchColumnWidthStyleStrategy) .doWrite(dataList); response.flushBuffer(); }}Copy the code

Send and Download: Send and Download: Send and Download: Send and Download: Send and Download: Send and Download: Send and Download: Send and Download: Send and Download: Send and Download: Send and Download: Send and Download: Send and Download: Send and Download: Send and Download: Send and Download: Send and Download: Send and Download: Send and Download

3. Read the Excel

Import is to reduce the manual input of a large amount of data trouble, good.

@Autowired
private ImportService importService;

@PostMapping("/import")
public void import( @RequestParam(value = "file") MultipartFile file,  @Min(1) @RequestParam("type") int type) throws IOException {
    ImportQueryDto dto = new ImportQueryDto();
    dto.setType(type);
    // ImportDto is an import class, UploadDataListener is a processing class, and the logical processing service needs to be passed in as a parameter
    EasyExcel.read(file.getInputStream(), ImportDto.class,
                   new UploadDataListener(importService)).sheet().doRead();
}
Copy the code

Import the corresponding Excel class. ExcelProperty corresponds to the head of the imported field.

@Data
public class ImportDto {
  @ ExcelProperty (" name ")
  private String name;
  @ ExcelProperty (" number ")
  private Integer num;
}
Copy the code

Import processing:

public class UploadDataListener extends AnalysisEventListener<ImportDto> {
    // Import as many as you want, avoid importing too many
    private static final int BATCH_COUNT = 50;
    // Store the import list
    List<ImportDto> list = new ArrayList<>();

    private ImportService importService;

    public UploadDataListener(ImportService importService) {
        this.importService = importService;
    }

    @Override
    public void invoke(ImportDto importDto, AnalysisContext analysisContext) {
        list.add(importDto);
        if(list.size() >= BATCH_COUNT) { saveData(); list.clear(); }}@Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();
    }

    private void saveData(a) {
        List<ImportModel> importModelList = new ArrayList<>();
        for (ImportDto dto : list) {
            ImportModel model = newImportModel(); model.setName(dto.getName()); model.setNum(dto.getNum()) importModelList.add(model); } importService.saveBatch(importModelList); }}Copy the code

In fact, is the content of the import file need to check, check the data format, need to ask the product whether to ignore the error data, import only normal data, and then how friendly the content of the errors, by returning a contains the error message Excel or prompt box, further prompt, where a grid is prompt.