preface

Blog address: studyidea.cn/easyexcel

Reader H recently sent a private message to Black Brother:

Xiao Hei brother, RECENTLY I am in charge of the development of the company’s report platform, and I need to export the report to Excel. Every time we use POI development, we have to write a long pile of code, and several times we failed to build because we didn’t add null judgment. Would like to ask you if there is a more efficient way to read and write Excel?

If you’ve ever used a POI, you probably know this: each time you write a bunch of code, you end up with something like this:

Is this code stinky and long? When you have a lot of fields, it’s easy to make a mistake. I still remember that WHEN I used POI to export an Excel with more than 20 fields, I kept copying and pasting, and accidentally wrote the wrong line number, which was a sad thing.

Today, little black brother will recommend an Ali open source project “EasyExcel”, take you completely bid farewell to the above long and smelly code, completely solve this problem.

EasyExcel

EasyExcel is an open source project produced by Ali. It can be seen from the name that this project is designed to make it easier for you to operate Excel. In addition, EasyExcel also solved the POI memory overflow problem and fixed some bugs in some concurrent cases.

Github address: github.com/alibaba/eas…

By the time xiao Hei wrote the article, 13.6K STAR data has been available, so it can be seen that this project is still very popular.

Nonsense not to say, we directly into the source of actual combat.

First we need to introduce the EasyExcel POM dependency:

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

It is recommended that you use official version 2.0 and above, and not use older version 1.0. There is a big difference between the two apis. In addition, beta version may have some bugs, please use caution.

The ordinary way

One line of code generates Excel

/ / write 1
String fileName = "temp/" + "test" + System.currentTimeMillis() + ".xlsx";
EasyExcel.write(fileName)
        .head(head())// Set the table header
        .sheet("Template")// Set the name of the sheet
        // Adaptive column width
        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
        .doWrite(dataList());// Write data

Copy the code

Generating Excel code is particularly easy, using chained statements where one line of code directly takes care of the generated code. We don’t have to specify row numbers or column numbers anymore.

The adaptive column width strategy is used in the code above.

Let’s look at how headers and headings are generated.

Create a header

/** * create table headers, you can create complex table headers **@return* /
private static List<List<String>> head() {
    List<List<String>> list = new ArrayList<List<String>>();
    // The first list header
    List<String> head0 = new ArrayList<String>();
    head0.add(First column);
    head0.add("First column, second row.");
    // The second list header
    List<String> head1 = new ArrayList<String>();
    head1.add(First column);
    head1.add("Second column, second row.");
    / / the third column
    List<String> head2 = new ArrayList<String>();
    head2.add(First column);
    head2.add("Third column, second row.");
    list.add(head0);
    list.add(head1);
    list.add(head2);
    return list;
}
Copy the code

Each of the above List

represents a column of data, and each data in the collection will be written sequentially to each row of that column. If the number of rows in each column is the same, the cells are automatically merged. With this rule, we create complex table headers.

Create table header as follows:

Write table body data

private static List dataList(a) {
    List<List<Object>> list = new ArrayList<List<Object>>();
    for (int i = 0; i < 10; i++) {
        List<Object> data = new ArrayList<Object>();
        data.add("Thumb up +" + i);
        // date will install the YYYY-MM-DD HH: MM :ss format
        data.add(new Date());
        data.add(0.56);
        list.add(data);
    }
    return list;
}
Copy the code

List > is then used for the body data, but not the same as for the header.

Each Listrepresents a row of data, and the data will be written to each column in order.

EasyExcel will convert the output of data in the collection to the default format. For example, date data will be formatted as YYYY-MM-DD HH: MM: SS.

If you need to convert the data to other formats, it is recommended to directly format the data into strings and add them to the List instead of converting them through EasyExcel.

The final effect is as follows:

Do you want to experience this right away? Wait, the above method is still a little cumbersome, using EasyExcel can also be faster. We can use annotations instead of manually setting the table header and body.

Annotation way

Excel code generated by annotations is as follows:

String fileName = "temp/annotateWrite" + System.currentTimeMillis() + ".xlsx";
// You need to specify which class to write to, then write to the first sheet, name it template and the stream will automatically close
// If you want to use 03, pass in the excelType argument
EasyExcel
        .write(fileName, DemoData.class)
        .sheet("Annotation mode")
        .registerWriteHandler(createTableStyle())// Excel table style
        .doWrite(data());
Copy the code

This code is basically the same as above, except that you pass in the DemoData data type in the write method. EasyExcel automatically generates the header based on the DemoData type.

Let’s take a look at what DemoData looks like inside.

@ContentRowHeight(30)// Table body row height
@HeadRowHeight(20)// Table header row height
@ColumnWidth(35)/ / column width
@Data
public class DemoData {
    /** * Sets the column width separately */
    @ColumnWidth(50)
    @ExcelProperty("String title")
    private String string;
    /** * year month day hour minute second format */
    @DateTimeFormat("Yyyy year MM month DD day HH MM minute ss second")
    @ExcelProperty(value = "Date heading")
    private Date date;
    /** * Format percentage */
    @NumberFormat("# # # %")
    @ExcelProperty("Number heading")
    private Double doubleData;
    @ExcelProperty(value = "Enumeration class",converter = DemoEnumConvert.class)
    private DemoEnum demoEnum;
    /** * ignore this field */
    @ExcelIgnore
    private String ignore;
}
Copy the code

DemoData is a normal POJO class that uses ExayExcel annotations. ExayExcel will read the field types and annotations through reflection and then generate Excel directly.

ExayExcel provides annotation classes that directly define Excel’s data model:

  • @ExcelPropertySpecifies the column in Excel that the current field corresponds to, and the internal value property specifies the name of the table header column
  • @ExcelIgnoreBy default, all fields will match excel. This annotation will ignore the fields
  • @ContentRowHeightSpecifies the table body row height
  • @HeadRowHeightSpecifies the header row height
  • @ColumnWidthSpecifies the width of the column

In addition, ExayExcel provides several annotations, custom dates, and number formatting transformations.

  • @DateTimeFormat
  • @NumberFormat

In addition, we can customize the format conversion scheme, need to implement the Converter class related methods.

public class DemoEnumConvert implements Converter<DemoEnum> {
    @Override
    public Class supportJavaTypeKey(a) {
        return DemoEnum.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey(a) {
        return CellDataTypeEnum.STRING;
    }

    /** * Excel converted to Java type, excel read will be called *@param cellData
     * @param contentProperty
     * @param globalConfiguration
     * @return
     * @throws Exception
     */
    @Override
    public DemoEnum convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return null;
    }

    /** * Java type to Excel type, excel writing will be called *@param value
     * @param contentProperty
     * @param globalConfiguration
     * @return
     * @throws Exception
     */
    @Override
    public CellData convertToExcelData(DemoEnum value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return newCellData(value.getDesc()); }}Copy the code

Finally, we need to specify a custom format conversion scheme using Converter on the @ExcelProperty annotation.

The usage is as follows:

@ExcelProperty(value = "Enumeration class",converter = DemoEnumConvert.class)
private DemoEnum demoEnum;
Copy the code

Finally, let’s run it to see how Excel works in practice:

Well, I guess it worked out just fine.

By the way, the default stylesheet style is not like this. This is because we set the custom style in the registerWriteHandler method as follows:

/*** * Set excel styles *@return* /
private static WriteHandler createTableStyle(a) {
    // The header's strategy
    WriteCellStyle headWriteCellStyle = new WriteCellStyle();
    // Set the background to red
    headWriteCellStyle.setFillForegroundColor(IndexedColors.PINK.getIndex());
    // Set the font
    WriteFont headWriteFont = new WriteFont();
    headWriteFont.setFontHeightInPoints((short) 20);
    headWriteCellStyle.setWriteFont(headWriteFont);
    // Content strategy
    WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
    // Specify FillPatternType as FillPatternType.SOLID_FOREGROUND otherwise cannot display the background color. The header defaults to FillPatternType so you can leave it unspecified
    contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
    // Background green
    contentWriteCellStyle.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());

    WriteFont contentWriteFont = new WriteFont();
    // Font size
    contentWriteFont.setFontHeightInPoints((short) 20);
    contentWriteCellStyle.setWriteFont(contentWriteFont);
    // Set the border style
    contentWriteCellStyle.setBorderBottom(BorderStyle.DASHED);
    contentWriteCellStyle.setBorderLeft(BorderStyle.DASHED);
    contentWriteCellStyle.setBorderRight(BorderStyle.DASHED);
    contentWriteCellStyle.setBorderTop(BorderStyle.DASHED);

    // This strategy is header is header style content is content style other strategies can be implemented themselves
    HorizontalCellStyleStrategy horizontalCellStyleStrategy =
            new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    return horizontalCellStyleStrategy;
}
Copy the code

Use caution points

Poi conflict issues

In theory, easyExcel is compatible with all the newer versions of POI (3.17,4.0.1,4.1.0), but if the project previously used an older version of POI, some classes have been removed due to internal code adjustment of POI, so running directly will most likely throw the following exception:

  • NoSuchMethodException
  • ClassNotFoundException
  • NoClassDefFoundError

Therefore, it is important to pay attention to the version of the POI in the unified project.

Customize row height and column width in non-annotation mode

It is troublesome to customize row height and column width in non-annotation mode. After checking the github issue, the developer replied that they need to implement WriteHandler interface and customize the table style.

conclusion

This article mainly gives you small friends amway EasyExcel powerful functions, introduces EasyExcel two ways to generate Excel, and demonstrate the relevant example code. EasyExcel in addition to write, of course, also supports the function of fast reading Excel, here will not be introduced in detail. Github is rich in documentation examples for your own reference.

Making documents address: alibaba – easyexcel. Making. IO/index. The HTML

Reference

  1. Github.com/alibaba/eas…
  2. alibaba-easyexcel.github.io/index.html
  3. Cloud.tencent.com/developer/a…

Welcome to pay attention to my public account: procedures to get daily dry goods push. If you are interested in my topics, you can also follow my blog: studyidea.cn