scene

In the work, the scene of exporting Excel often appears, such as managing the function of background export, batch correcting database, etc. in these scenes, the table data often corresponds to a record of the database, and the table format is very simple. However, similar import and export need to be implemented in different code. The product often gives the author an Excel table data to import into the database. It is a waste of time to write a pile of import and export codes with similar functions and processes every time. Here is the encapsulation of Apache POI. Let's have a taste.

Use

Create a table mapping object

@Data
public class Human {

    @Column(title = "Eye color")
    private String eyeColor;

    @Column(title = "Hair color")
    private String hairColor;
}

@Data
@ToString(callSuper = true)
public class Girl extends Human {

    @Column(index = 0, title = "Full name")
    private String name;

    @Column(index = 1, title = "Age")
    private Integer age;

    @Column(index = 2, title = "height")
    private Float height;

    @Column(index = 2, title = "weight")
    private Float weight;
}

Export demonstration

Here, the author uses Junit Mock to export some data:

@Test
public void export1() {
	//Place the exported file in the compiled file directory
    String path = FileUtil.findRealPathByClasspath(this.getClass(), "/");
    path = path + "excel/Girl_" + TimeUtil.getCurrentSeconds() + ".xlsx";
    //mock 2000 rows of data
    List<Girl> girls = this.mockGirls(2000);
    //Export to specified file directory
    byte[] bytes = ExcelHelper.instance(Girl.class).input(girls).doExport();
    NIOUtil.writeFile(path, bytes);
}

private List<Girl> mockGirls(int size) {
    List<Girl> data = new ArrayList<>(size);
    for (int i = 0; i < size; i++) {
        Girl girl = new Girl();
        girl.setName("CY" + i);
        girl.setAge(19);
        girl.setHeight(1.68F + 0.001F * i);
        girl.setWeight(45F + 0.001F * i);
        girl.setHairColor("black");
        girl.setEyeColor("gray");
        data.add(girl);
    }

    return data;
}

Export results:

Import demonstration

The author uses the data just exported to import and print:

@Test
public void import1() {
    InputStream is = this.getClass().getResourceAsStream("/excel/Girl.xlsx");
    List<Girl> list = ExcelHelper.instance(Girl.class).input(is).doImport();
    System.out.println(list);
}

Import results:

From the above demonstration, for this simple Excel report, a few lines of code can be solved. If you think it's good, you can reference it through Maven or github download source code.

Introduce

maven

<dependency>
  <groupId>com.github.rxyor</groupId>
  <artifactId>carp-common-util</artifactId>
  <version>1.0.3</version>
</dependency>

github

https://github.com/rxyor/carp.git (remember star, >