Ali open source this library, so that Excel export is not complicated (use guide to fill templates)

“This is the 16th day of my participation in the First Challenge 2022. For details: First Challenge 2022”

Hello, I’m looking at the mountains.

The write operations mentioned above allow you to export simple lists and define styles. Sometimes, we also need to export a large form, or form + list form, in this case, we need to fill the function.

Content is more, the article will only list the key code, want to complete the source code, you can pay attention to the public number “see the mountain hut” reply “easyExcel” to obtain.

In EasyExcel, write operations do most of the work. The advantage of padding is that you can implement custom styles, as long as you set the style in the template, the filled data can be styled.

Let’s write a form

Since you are using templates, write to define a template.

In EasyExcel’s template fill definition, use {} to indicate the variable you want to use. If you already have “{“,”}” special characters, you need to escape them and replace them with “{“,”}”.

Write the object

Since we’re writing objects, let’s define the structure of the object.

@Data
public class Item {
    private String name;
    private double number;
}
Copy the code

Then start filling:

private static void fillUseObject(a) {
    String fileName = defaultFileName("fillUseObject");
    String templateFile = getPath() + File.separator + "template_fill_sample.xlsx";

    Item item = new Item();
    item.setName("The Outlaw John.");
    item.setNumber(89757);
    EasyExcelFactory.write(fileName)
            .withTemplate(templateFile)
            .sheet()
            .doFill(item);
}
Copy the code

We have also used the template write list for write operations. Here we fill the template in the same way: Com. Alibaba. Excel. Write. Builder. ExcelWriterBuilder# withTemplate (Java. Lang. String) specified template file path, repeat here. The withTemplate method has several overloaded implementations:

1. Specifies the path to the template fileExcelWriterBuilder#withTemplate(java.lang.String)
2. Specifies the template file objectExcelWriterBuilder#withTemplate(java.io.File)
3. Specifies the template file input streamExcelWriterBuilder#withTemplate(java.io.InputStream)

Specify that both template files and template file objects operate on files and require file information.

Specifying the template file input stream is as long as the file stream, the maneuverability space is relatively large. For example, if a template file is mutable, we can initialize the template file using a fill write, based on a template file with variables, and then write to the list using a template write.

The result is:

Write a Map

We can do the same thing with a Map without having to create a class.

private static void fillUseMap(a) {
    String fileName = defaultFileName("fillUseMap");
    String templateFile = getPath() + File.separator + "template_fill_sample.xlsx";

    Map<String, Object> data = new HashMap<>();
    data.put("name"."The Outlaw John.");
    data.put("number".89757);
    EasyExcelFactory.write(fileName)
            .withTemplate(templateFile)
            .sheet()
            .doFill(data);
}
Copy the code

Although maps can function identically, it is recommended to define specific classes. Because classes are checkable, maps are weak detection mechanisms that rely solely on convention or testing and are not very secure.

The result is:

In effect, the result is the same.

Let’s make another list

Define the template first:

As you can see, the parameter definition of the fill list is slightly different from that of the fill object. There is an extra dot in the {.} template.

For tabular scenarios, there are generally small amounts of data and large amounts of data. For small amounts of data, you can operate directly in memory. For large amounts of data, batch writes can be used to save memory by means of file caching.

A small amount of writing

The code:

private static void fillListInMemory(a) {
    String fileName = defaultFileName("fillListInMemory");
    String templateFile = getPath() + File.separator + "template_fill_list.xlsx";

    EasyExcelFactory.write(fileName)
            .withTemplate(templateFile)
            .sheet()
            .doFill(sampleItems());
}
Copy the code

It can be seen that there is no big difference in code implementation between filling list and writing file operation mentioned above, which is also the strong design of EasyExcel architecture. Through the fluent writing method of builder mode, the long-winded writing is shielded, and the differences of different business implementation parameters are also shielded. Only in doFill, different logic is realized according to different parameters.

The result is:

A lot of writing

What follows is a lot of data padding. The difference with the need to manually create ExcelWriter and WriteSheet object, and then use the com. Alibaba. Excel. ExcelWriter. The fill method write data for many times.

The fill method supports writing directly to the list and using lambda functions (fill, not doFill). DoFill calls the Finish method to automatically close the stream. The fill method only does data filling, requiring the stream to be closed manually.

The code is:

private static void fillListSegment(a) {
    String fileName = defaultFileName("fillListSegment");
    String templateFile = getPath() + File.separator + "template_fill_list.xlsx";

    final ExcelWriter excelWriter = EasyExcelFactory.write(fileName).withTemplate(templateFile).build();
    try {
        final WriteSheet writeSheet = EasyExcelFactory.writerSheet().build();
        excelWriter.fill(BaseFill::sampleItems, writeSheet);
        excelWriter.fill(sampleItems(), writeSheet);
    } finally {
        if(excelWriter ! =null) { excelWriter.finish(); }}}Copy the code

The result is:

Finally, write a form + list

Finally, a form and a list. For example, sales statistics, the table header needs to fill in parameter information, such as store information, time, etc., and then sales records, and finally need to add information like total.

In this case, there are many ways to implement this, here is the implementation of fixed lists, and in the tips section we will introduce a dynamic list implementation.

Populate the object + list

Define the template first:

As you can see from the template, there is time information at the beginning, statistics at the end, and a list in the middle.

The code:

/** * populates the object + list, because there is a field after the list, so we need to put {@linkFillConfigBuilder#forceNewRow(Boolean)} Set this parameter to TRUE. * <p> * This has a side effect: all data will be in memory, which is particularly memory intensive when there is a large amount of data. * <p> * there are two ways to solve this problem: * * <ul> * <li>@linkFillConfigBuilder#forceNewRow(Boolean)} Set the value to FALSE</li> * <li>list Write data manually </li> * </ul> */
private static void fillObjectAndListInMemory(a) {
    String fileName = defaultFileName("fillObjectAndListInMemory");
    String templateFile = getPath() + File.separator + "template_fill_object_and_list.xlsx";

    final ExcelWriter excelWriter = EasyExcelFactory.write(fileName).withTemplate(templateFile).build();
    try {
        final WriteSheet writeSheet = EasyExcelFactory.writerSheet().build();

        Map<String, Object> map = new HashMap<>();
        map.put("date", DateTimeFormatter.ISO_LOCAL_DATE_TIME.format(LocalDateTime.now()));
        map.put("total", System.currentTimeMillis());
        excelWriter.fill(map, writeSheet);

        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();

        excelWriter.fill(BaseFill::sampleItems, fillConfig, writeSheet);
        excelWriter.fill(sampleItems(), fillConfig, writeSheet);
    } finally {
        if(excelWriter ! =null) { excelWriter.finish(); }}}Copy the code

There is a new configuration class: FillConfig FillConfig = fillconfig.builder ().forcenewrow (boolea.true).build(), which defines the configuration information for writing. If set to true, a row is created when a list is written, regardless of whether there are empty rows below it, and the data below it is moved back. If not defined or set to false, the statistics on the last row are overwritten.

However, as long as it is set to true, the entire fill operation will be in memory, which is expensive.

The result is:

Fill object + list (large amount of data)

If the list data is large and still in memory, it is easier to run out of memory. So special operations are required:

1. There is no form to fill after the list, which is the easiest to implement.
2. You can only write data manually after a list

The code is as follows:

private static void fillObjectAndListManual(a) {
    String fileName = defaultFileName("fillObjectAndListManual");
    String templateFile = getPath() + File.separator + "template_fill_object_and_list_manual.xlsx";

    final ExcelWriter excelWriter = EasyExcelFactory.write(fileName).withTemplate(templateFile).build();
    try {
        final WriteSheet writeSheet = EasyExcelFactory.writerSheet().build();

        Map<String, Object> map = new HashMap<>();
        map.put("date", DateTimeFormatter.ISO_LOCAL_DATE_TIME.format(LocalDateTime.now()));
        excelWriter.fill(map, writeSheet);

        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();

        excelWriter.fill(BaseFill::sampleItems, fillConfig, writeSheet);
        excelWriter.fill(sampleItems(), fillConfig, writeSheet);

        // Write data manually
        List<List<String>> totalListList = new ArrayList<>();
        List<String> totalList = new ArrayList<>();
        totalListList.add(totalList);
        totalList.add(null);
        totalList.add("Statistics: 1000");
        excelWriter.write(totalListList, writeSheet);
    } finally {
        if(excelWriter ! =null) { excelWriter.finish(); }}}Copy the code

The result is:

Horizontal fill data

Define the template first:

private static void fillObjectAndListHorizontal(a) {
    String fileName = defaultFileName("fillObjectAndListHorizontal");
    String templateFile = getPath() + File.separator + "template_fill_list_horizontal.xlsx";

    final ExcelWriter excelWriter = EasyExcelFactory.write(fileName).withTemplate(templateFile).build();
    try {
        final WriteSheet writeSheet = EasyExcelFactory.writerSheet().build();

        Map<String, Object> map = new HashMap<>();
        map.put("date", DateTimeFormatter.ISO_LOCAL_DATE_TIME.format(LocalDateTime.now()));
        excelWriter.fill(map, writeSheet);

        FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();

        excelWriter.fill(BaseFill::sampleItems, fillConfig, writeSheet);
        excelWriter.fill(sampleItems(), fillConfig, writeSheet);
    } finally {
        if(excelWriter ! =null) { excelWriter.finish(); }}}Copy the code

This configuration FillConfig FillConfig = FillConfig. Builder () direction (WriteDirectionEnum. HORIZONTAL). The build (), is used to define writing direction.

The result is:

Populate multiple tables

Like the write operation, the population operation can also write to multiple tables.

Populate multiple tables

For multi-table writes, templates must be defined with the {prefix. }.

private static void fillMultiList(a) {
    String fileName = defaultFileName("fillMultiList");
    String templateFile = getPath() + File.separator + "template_fill_multi_list.xlsx";

    final ExcelWriter excelWriter = EasyExcelFactory.write(fileName).withTemplate(templateFile).build();
    try {
        final WriteSheet writeSheet = EasyExcelFactory.writerSheet().build();

        Map<String, Object> map = new HashMap<>();
        map.put("date", DateTimeFormatter.ISO_LOCAL_DATE_TIME.format(LocalDateTime.now()));
        excelWriter.fill(map, writeSheet);

        FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();

        excelWriter.fill(new FillWrapper("data1", sampleItems()), fillConfig, writeSheet);
        // data2 is written in batches
        excelWriter.fill(new FillWrapper("data2", sampleItems()), writeSheet);
        excelWriter.fill(new FillWrapper("data3", sampleItems()), writeSheet);
    } finally {
        if(excelWriter ! =null) { excelWriter.finish(); }}}Copy the code

FillWrapper is used to wrap the prefix.

The result is:

At this point, write and fill operations are described.

At the end of the article to summarize

This article from the actual combat point of view said about EasyExcel if the implementation of template to export table, template filling logic, plus write logic, we will have more play, the next will say about these fun SAO operation.

Green hills never change, green waters always flow. See you next time.

Recommended reading

• This article describes 24 operations for Java8 Stream Collectors
• Java8 Optional 6 kinds of operations
• Use Lambda expressions to achieve super sorting functions
• Java8 Time Library (1) : Describes the time class and common apis in Java8
• Java8 time library (2) : Convert Date to LocalDate or LocalDateTime
• Java8 Time Library (3) : Start using Java8 time classes
• Java8 time library (4) : check if the date string is valid
• New features in Java8
• New features in Java9
• New features in Java10
• Optimization of access control based on nested relationships in Java11
• New features for Java11
• New features in Java12

---

Hello, I’m looking at the mountains. Swim in the code, play to enjoy life. If this article is helpful to you, please like, bookmark, follow.