Import and export is so smooth, do you use EasyPoi?

POI: Technology used to process files such as Excel and Word. EasyPoi: Easy to write Excel import and export functions for people who have not been exposed to POI. To put it bluntly, with EasyPoi, the file import and export becomes so easy!

Official website: easyPoi.mydoc. IO /

1. SpringBoot integrates EasyPoi

Introduction of depend on

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <! --easy-poi--> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-base</artifactId> <version>4.0. 0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>4.0. 0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>4.0. 0</version>
</dependency>
Copy the code

2. Relevant notes

2.1 @ ExcelTarget

Applied to the entity class, indicating that this is an entity class that can be imported and exported using EasyPoi

Common attributes:

Value: unique identifier that cannot be repeated. But you can omit it, like “user”

Usage:

@ExcelTarget("user")
public class User implements Serializable {}Copy the code

2.2 @ Excel

Applied to attributes in the entity class, indicating that this is a column in Excel.

Common attributes:

Name: Column name in an Excel table, for example, name=” Name”
OrderNum: The order of columns in an Excel table, for example orderNum=”1″
Type: export type, 1 text, 2 images, 3 functions, 10 numbers, default is text
Width: indicates the width of the column
Height: The height of the column
SavePath: Saves the image
Exportformat: Time format to be exported in Excel, for example, exportformat=” YYYY-MM-DD HH: MM :ss”.
ImportFormat: Time format of Excel import
Format: Sets the time format of the import and export
IsStatistics: Do you aggregate this column? The default false
NeedMerge: Whether to merge cells vertically. Default: false
Replace: The replace value, for example, replace = {” fem_0 “, “_1”}, indicates that if the value is 1, the ‘male’ is exported, and if the value is 0, the ‘female’ is exported.

Usage:

@ExcelTarget("user")
public class User implements Serializable {

  @excel (name = "name ", width = 30, orderNum = "1")
  private String name;

  @Excel(name = "年龄", width = 10, orderNum = "2",isSta    tistics = true)
  private Integer age;

  @ Excel (name = "gender", width = 10, orderNum = "3", repl ace = {" female _0 ", "male _1"})
  private Integer sex;

  @excel (name = "yyyy ", width = 30, orderNum = "4", form at = "YYYY-MM-DD HH: MM :ss")
  private Date birthday;

  @excel (name = "profile info ", type = 2, width = 20,height = 30,savePath = "D:\ WorkSpace\ easy-poi\ SRC \main\ resources")
  private String head;
}
Copy the code

2.3 @ ExcelEntity

Applied to a class in an entity class, indicating that it is a class in an exported class.

Common attributes:

Name: unique identifier.

Usage:

@ExcelTarget("user")
public class User implements Serializable {

    @ExcelEntity(name = "address")
    private Address address;
}
Copy the code

@ExcelTarget("address")
public class Address implements Serializable {

    @excel (name = "province ", width = 30, orderNum = "6")
    private String province;

    @excel (name = "city ", width = 30, orderNum = "7")
    private String city;

    @excel (name = "county ", width = 30, orderNum = "8")
    private String county;
}
Copy the code

2.4 @ ExcelCollection

It shows that this is a set, a one-to-many relationship

Common attributes:

Name: uniquely identifies the collection name
OrderNum: Order of columns in an Excel table

Usage:

@ExcelTarget("user")
public class User implements Serializable {

  @excelCollection (name = "purchased fruit ", orderNum = "9")
  private List<Fruit> fruits;
  
}
Copy the code

@ExcelTarget("fruit")
public class Fruit implements Serializable {

    @excel (name = "fruit name ")
    private String name;

    @excel (name = "price/yuan ")
    private Double price;

    @excel (name = "quantity ")
    private Integer count;
    
}
Copy the code

2.5 @ ExcelIgnore

Applies to properties and skips this column when exporting

Usage:

@ExcelIgnore 
@excel (name = "honor ", width = 60, orderNum = "5")
private List<String> honors;
Copy the code

3. The export Excel

3.1 Exporting Basic Attributes

Define user class:

/ * * *@DescriptionImport and export entity classes *@AuthorA rebus *@Date2021/5/24 insisting * /
@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("user")
public class User implements Serializable {

    @excel (name = "name ", width = 30, orderNum = "1")
    private String name;

    @excel (name = "age ", width = 10, orderNum = "2")
    private Integer age;

    @ Excel (name = "gender", width = 10, orderNum = "3", the replace = {" female _0 ", "male _1"})
    private Integer sex;

    @excel (name = "yyyY-MM-DD HH: MM :ss", width = 30, orderNum = "4", format =" YYYY-MM-DD HH: MM: SS ")
    private Date birthday;
}
Copy the code

Test method:

@Test
void exportBasic(a) throws IOException {
    // Create test data
    List<User> userList = new ArrayList<>();
    for (int i = 0; i < 10; i++) {
        User user = new User();
        user.setName("Zhou Zhiruo" + i);
        user.setAge(18 + i);
        user.setBirthday(new Date());
        user.setSex(new Random().nextInt(2));
        userList.add(user);
    }
    / * -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - the following is the core of the derived using EasyPoi method -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - * /
    // ExportParams() has two arguments, one is the title table title and the other is sheetName
    Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("User table"."sheet1"),User.class,userList);
    // Export the file to the specified location
    FileOutputStream outputStream = new FileOutputStream("D: \ \ WorkSpace \ \ easy - poi \ \ SRC \ \ the main \ \ resources \ \ user table. XLS");
    workbook.write(outputStream);
    outputStream.close();
    workbook.close();
}
Copy the code

Execution Result:

3.2 Exporting Images

Define user class (including user avatar) :

/ * * *@DescriptionImport and export entity classes *@AuthorA rebus *@Date2021/5/24 insisting * /
@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("user")
public class User implements Serializable {

    @excel (name = "name ", width = 30, orderNum = "1")
    private String name;

    @excel (name = "age ", width = 10, orderNum = "2")
    private Integer age;

    @ Excel (name = "gender", width = 10, orderNum = "3", the replace = {" female _0 ", "male _1"})
    private Integer sex;

    @excel (name = "yyyY-MM-DD HH: MM :ss", width = 30, orderNum = "4", format =" YYYY-MM-DD HH: MM: SS ")
    private Date birthday;
    
    // Remember that the image type = 2
    @excel (name = "profile info ", type = 2, width = 20,height = 30)
    private String head;
}
Copy the code

Test method:

@Test
void exportBasic(a) throws IOException {
    // Create test data
    List<User> userList = new ArrayList<>();
    for (int i = 0; i < 10; i++) {
        User user = new User();
        user.setName("Zhou Zhiruo" + i);
        user.setAge(18 + i);
        user.setBirthday(new Date());
        user.setSex(new Random().nextInt(2));
        // Specify the location of the image
        user.setHead("D:\\WorkSpace\\easy-poi\\src\\main\\resources\\static\\image\\head.jpeg");
        userList.add(user);
    }
    / * -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - the following is the core of the derived using EasyPoi method -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - * /
    // ExportParams() has two arguments, one is the title table title and the other is sheetName
    Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("User table"."sheet1"),User.class,userList);
    // Export the file to the specified location
    FileOutputStream outputStream = new FileOutputStream("D: \ \ WorkSpace \ \ easy - poi \ \ SRC \ \ the main \ \ resources \ \ user table. XLS");
    workbook.write(outputStream);
    outputStream.close();
    workbook.close();
}
Copy the code

Execution Result:

3.3 Exporting objects in Objects

Define the user class (containing the user address object) :

/ * * *@DescriptionImport and export entity classes *@AuthorA rebus *@Date2021/5/24 insisting * /

@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("user")
public class User implements Serializable {

    @excel (name = "name ", width = 30, orderNum = "1")
    private String name;

    @excel (name = "age ", width = 10, orderNum = "2")
    private Integer age;

    @ Excel (name = "gender", width = 10, orderNum = "3", the replace = {" female _0 ", "male _1"})
    private Integer sex;

    @excel (name = "yyyY-MM-DD HH: MM :ss", width = 30, orderNum = "4", format =" YYYY-MM-DD HH: MM: SS ")
    private Date birthday;

    @ExcelEntity(name = "address")
    private Address address;
}
Copy the code

Address:

/ * * *@DescriptionAddress *@AuthorA rebus *@Date2021/5/24 roar, * /
@Data
@AllArgsConstructor
@ExcelTarget("address")
public class Address implements Serializable {

    @excel (name = "province ", width = 30, orderNum = "5")
    private String province;

    @excel (name = "city ", width = 30, orderNum = "6")
    private String city;

    @excel (name = "county ", width = 30, orderNum = "7")
    private String county;

}
Copy the code

Test method:

@Test
void exportUserTest(a) throws IOException {
    List<User> userList = new ArrayList<>();
    for (int i = 0; i < 5; i++) {
        User user = new User();
        user.setName("Zhou Zhiruo" + i);
        user.setAge(18 + i);
        user.setBirthday(new Date());
        user.setSex(new Random().nextInt(2));
        // Set the address
        user.setAddress(new Address("Hunan"."Changsha"."Ningxiang"));
        userList.add(user);
    }
    Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("User table"."sheet1"),User.class,userList);
    // Export the file to the specified location
    FileOutputStream outputStream = new FileOutputStream("D: \ \ WorkSpace \ \ easy - poi \ \ SRC \ \ the main \ \ resources \ \ user table. XLS");
    workbook.write(outputStream);
    outputStream.close();
    workbook.close();
}
Copy the code

Execution Result:

3.4 Exporting a List of Objects

Define a user class (containing the honor information earned by the user) :

/ * * *@DescriptionImport and export entity classes *@AuthorA rebus *@Date2021/5/24 insisting * /
@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("user")
public class User implements Serializable {

    @excel (name = "name ", width = 30, orderNum = "1")
    private String name;

    @excel (name = "age ", width = 10, orderNum = "2")
    private Integer age;

    @ Excel (name = "gender", width = 10, orderNum = "3", the replace = {" female _0 ", "male _1"})
    private Integer sex;

    @excel (name = "yyyY-MM-DD HH: MM :ss", width = 30, orderNum = "4", format =" YYYY-MM-DD HH: MM: SS ")
    private Date birthday;

    @excel (name = "honor ", width = 60, orderNum = "5")
    private List<String> honors;
}
Copy the code

Test method:

@Test
void exportUserTest(a) throws IOException {
    List<User> userList = new ArrayList<>();
    String honorList="First prize in eating Contest, second prize in International Sleeping Contest";
    List<String> honors = Arrays.asList(honorList.split("、"));
    for (int i = 0; i < 5; i++) {
        User user = new User();
        user.setName("Zhou Zhiruo" + i);
        user.setAge(18 + i);
        user.setBirthday(new Date());
        user.setSex(0);
        // Set user honors
        user.setHonors(honors);
        userList.add(user);
    }
    Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("User table"."sheet1"),User.class,userList);
    // Export the file to the specified location
    FileOutputStream outputStream = new FileOutputStream("D: \ \ WorkSpace \ \ easy - poi \ \ SRC \ \ the main \ \ resources \ \ user table. XLS");
    workbook.write(outputStream);
    outputStream.close();
    workbook.close();
}
Copy the code

Execution Result:

3.5 Exporting one-to-many Relationships

Define the user class (which contains information about the fruit that the user buys, in this case, more than one fruit that the user buys) :

@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("user")
public class User implements Serializable {

    @excel (name = "name ", width = 30, orderNum = "1",needMerge = true)
    private String name;

    @excel (name = "age ", width = 10, orderNum = "2",needMerge = true)
    private Integer age;

    @excel (name = "gender ", width = 10, orderNum = "3", replace = {" female _0"," male _1"},needMerge = true)
    private Integer sex;

    @excel (name = "yyyY-MM-DD HH: MM :ss", width = 30, orderNum = "4", format =" YYYY-MM-DD HH: MM :ss",needMerge = true)
    private Date birthday;

    @excelCollection (name = "purchased fruit ", orderNum = "5")
    private List<Fruit> fruits;
}
Copy the code

Fruit class:

/ * * *@DescriptionFruit entity *@AuthorA rebus *@Date2021/5/24 departed * /
@Data
@AllArgsConstructor
@ExcelTarget("fruit")
public class Fruit implements Serializable {

    @excel (name = "fruit name ")
    private String name;

    @excel (name = "price/yuan ")
    private Double price;

    @excel (name = "quantity ")
    private Integer count;
}
Copy the code

Test method:

@Test
void exportUserTest(a) throws IOException {
    List<User> userList = new ArrayList<>();
    for (int i = 0; i < 5; i++) {
        User user = new User();
        user.setName("Zhou Zhiruo" + i);
        user.setAge(18 + i);
        user.setBirthday(new Date());
        user.setSex(0);
        // Set the user to buy the fruit information
        user.setFruits(Arrays.asList(new Fruit(The word "apple".23.5.11),new Fruit("Banana".4.98.21)));
        userList.add(user);
    }
    Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("User table"."sheet1"),User.class,userList);
    // Export the file to the specified location
    FileOutputStream outputStream = new FileOutputStream("D: \ \ WorkSpace \ \ easy - poi \ \ SRC \ \ the main \ \ resources \ \ user table. XLS");
    workbook.write(outputStream);
    outputStream.close();
    workbook.close();
}
Copy the code

Execution Result:

4. Import Excel

4.1 Importing Basic Attributes

Document preparation:

Define user class:

@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("user")
public class User implements Serializable {

    @excel (name = "name ", width = 30, orderNum = "1")
    private String name;

    @excel (name = "age ", width = 10, orderNum = "2")
    private Integer age;

    @ Excel (name = "gender", width = 10, orderNum = "3", the replace = {" female _0 ", "male _1"})
    private Integer sex;

    @excel (name = "yyyY-MM-DD HH: MM :ss", width = 30, orderNum = "4", format =" YYYY-MM-DD HH: MM: SS ")
    private Date birthday;
}
Copy the code

Test method:

@Test
void importUserTest(a) {
    ImportParams importParams = new ImportParams();
    // Table header row number, default 0
    importParams.setTitleRows(1);
    // The number of rows in the table header, default 1
    importParams.setHeadRows(1);
    // Export data parameters 1: excel file parameters 2: object type parameters 3: import parameter configuration
    List<User> userList = ExcelImportUtil.importExcel(new File("D: \ \ WorkSpace \ \ easy - poi \ \ SRC \ \ the main \ \ resources \ \ user table. XLS"), User.class,importParams);
   if(! CollectionUtils.isEmpty(userList)) userList.forEach(user->{ System.out.println(user); }); }Copy the code

Execution Result:

4.2 Importing Images

Document preparation:

Define the user class (including the user profile picture, savePath to specify the location of the user profile picture) :

@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("user")
public class User implements Serializable {

    @excel (name = "name ", width = 30, orderNum = "1")
    private String name;

    @excel (name = "age ", width = 10, orderNum = "2")
    private Integer age;

    @ Excel (name = "gender", width = 10, orderNum = "3", the replace = {" female _0 ", "male _1"})
    private Integer sex;

    @excel (name = "yyyY-MM-DD HH: MM :ss", width = 30, orderNum = "4", format =" YYYY-MM-DD HH: MM: SS ")
    private Date birthday;

    @excel (name = "profile info ", type = 2, width = 20,height = 30,savePath = "D: WorkSpace\ easy-poi\ SRC \main\ resources\ head")
    private String head;
}
Copy the code

Test method:

@Test
void importUserTest(a) {
    ImportParams importParams = new ImportParams();
    // Table header row number, default 0
    importParams.setTitleRows(1);
    // The number of rows in the table header, default 1
    importParams.setHeadRows(1);
    // Export data parameters 1: excel file parameters 2: object type parameters 3: import parameter configuration
    List<User> userList = ExcelImportUtil.importExcel(new File("D: \ \ WorkSpace \ \ easy - poi \ \ SRC \ \ the main \ \ resources \ \ user table. XLS"), User.class,importParams);
   if(! CollectionUtils.isEmpty(userList)) userList.forEach(user->{ System.out.println(user); }); }Copy the code

Execution Result:Save position of your avatar:

5. Precautions

When exporting Excel files, remember to close the original exported Excel file first, otherwise an error will be reported.

Wechat official account: Eclipse programming. Focus on programming technology sharing, adhere to lifelong learning.

Import and export is so smooth, do you use EasyPoi?

1. SpringBoot integrates EasyPoi

2. Relevant notes

2.1 @ ExcelTarget

2.2 @ Excel

2.3 @ ExcelEntity

2.4 @ ExcelCollection

2.5 @ ExcelIgnore

3. The export Excel

3.1 Exporting Basic Attributes

3.2 Exporting Images

3.3 Exporting objects in Objects

3.4 Exporting a List of Objects

3.5 Exporting one-to-many Relationships

4. Import Excel

4.1 Importing Basic Attributes

4.2 Importing Images

5. Precautions

Related Posts

Publish your open source software to Ubuntu PPA

How does Java simulate true concurrent requests?

Kafka SpringBoot integration