“This is the 29th day of my participation in the November Gwen Challenge. See details of the event: The Last Gwen Challenge 2021”.

What is EaasyPOI?

Easypoi is able to let a person who has not seen or touched POI can easily write Excel import, Excel export, through simple annotations and template language (familiar expression grammar), complete the previous complex writing method, although not as customized as POI but can simplify a lot of our code

@ Excel annotations

The @excel annotation, which is a mandatory annotation, is placed above the entity Bean and applied to filed, which is a description of the Excel column. If the requirements are simple, just use this annotation, which covers common Excel requirements, and you need to know some functions: basic (data import and export), image processing, time processing, merger processing, etc. You should know that when meeting functional requirements, you can use EasyPOI to quickly get started.

Plug-in and YML configuration

XML configuration plug-in, easyPOI-spring-boot-starter plug-in is sprinboot integration easyPOI plug-in.

<! - excel operation springboot -- easypoi -- -- >
<dependency>
 <groupId>cn.afterturn</groupId>
 <artifactId>easypoi-spring-boot-starter</artifactId>
 <version>4.1.2</version>
</dependency>
Copy the code

Configure application.yml to override entity beans for import security. The bean discovered later overrides the previous bean with the same name.

spring:
  main:
   allow-bean-definition-overriding: true
Copy the code

The entity Bean

Add the @excel annotation to the declared property.

@Data
public class User implements Serializable {
 @Excel(name = "Serial number")
 private Integer id;
 @Excel(name = "Name")
 private String name;
 @Excel(name = "Age")
 private Integer age;
 @Excel(name = "Note")
 private String remark;
 
}
Copy the code

Import the export utility class ExcelUtils

Customizing the ExcelUtils template is a process of customizing the ExcelUtils template in an Excel table, retrieving the data set that needs to be passed in, and exporting it using the export() method.

Defining excel templates requires using custom tags in Excel, which is not so much here.

Example of Import Function

Get the list of imported data: The following uses the user table as an example to get the list of imported data, according to the custom ExcelUtils template, and finally calls the import database method.

public String uploadExcel(@RequestParam("file")MultipartFile file){ long startTime=System.currentTimeMillis(); try { List<User> list= ExcelUtils.importExcel(file,User.class); for(int i=0; i<list.size(); i++){ System.out.println(list.get(i)); } } catch (IOException e) { e.printStackTrace(); } long endTime=System.currentTimeMillis(); System.out.println(" time: "+(endtime-startTime)); return "/index"; }Copy the code

Example Export Function

Here is a simple demo. Get the data into the collection and export it to the specified path through the output stream

public  void exportExcel(User user){
    List<User> list=new ArrayList<>();
        list.add(u);
    ExcelUtils.exportExcel();
}
Copy the code

if (! savefile.exists()) { savefile.mkdirs(); } FileOutputStream fos = new FileOutputStream("D:/excel/exportTemp_image.xls"); book.write(fos); fos.close();Copy the code

Copy the code