Preface

Recently, reader H sent a private message to Little Black Gogh:

Developers who have used poi may have had this experience, writing one code at a time, and the final code is as follows:

Is this code stinky and long?When there are many fields, it is easy to write incorrectly if you are not careful.Little Black Gogh remembers that when poi was used to export excel for more than 20 fields, it kept copying and pasting, and the line number was written incorrectly, which was a heartbreak.

Today, Little Black Gogh has come to recommend an open source Ali project, EasyExcel, to take you away from the long and stinky code above and solve this problem thoroughly.

EasyExcel

EasyExcel is an open source project from Ali. You can tell by name that this project is designed to make it easier for you to work with Excel.EasyExcel also solves the poi memory overflow problem and fixes some bug s in concurrent situations.

github address: https://github.com/alibaba/easyexcel

As of the time Montenegro wrote the article, there were 13.6k star t data, so this project is still very popular.

Say nothing more, we go directly to the source actual link.

First we need to introduce EasyExcel pom dependencies:

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

It is recommended that you use more than 2.0 official versions instead of 1.0 older versions, which differ greatly in API usage.In addition, there may be some bug s in the beta version, which you should use with caution.

Common way

One line of code to generate Excel

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

Generating excel code is particularly simple, using a chain statement where one line of code directly generates the code.We don't need to specify line numbers, column numbers anymore in the code.

Let's see how headers and headers are generated.

Create Header

/**
 * Create headers to create complex headers
 *
 * @return
 */
private static List<List<String>> head() {
    List<List<String>> list = new ArrayList<List<String>>();
    // First List Header
    List<String> head0 = new ArrayList<String>();
    head0.add("Column 1");
    head0.add("First column, second row");
    // Second List Header
    List<String> head1 = new ArrayList<String>();
    head1.add("Column 1");
    head1.add("Column 2 Line 2");
    // Column 3
    List<String> head2 = new ArrayList<String>();
    head2.add("Column 1");
    head2.add("Column 3, row 2");
    list.add(head0);
    list.add(head1);
    list.add(head2);
    return list;
}

Each List <String>above represents a column of data in which each data is written sequentially to each row.Cells are automatically merged if the contents of the same number of rows per column are the same.With this rule, we create complex headers.

The final header is created as follows:

Writing table data

private static List dataList() {
    List<List<Object>> list = new ArrayList<List<Object>>();
    for (int i = 0; i < 10; i++) {
        List<Object> data = new ArrayList<Object>();
        data.add("Give the thumbs-up+" + i);
        // date will install yyyy-MM-dd HH:mm:ss formatting
        data.add(new Date());
        data.add(0.56);
        list.add(data);
    }
    return list;
}

Tablebody data is then used with List<List<Object>, but not with the header rules.

Each List <Object>represents one row of data, which is written sequentially to each column.

The data EasyExcel in the collection will convert the output to the default format, such as date type data which will be formatted as yyyy-MM-dd HH:mm:ss.

If you need to convert to another format, it is recommended that you format the data directly into a string and add it to the List instead of using EasyExcel conversion.

The results are as follows:

Do you want to experience this right away?And so on, it's still a bit cumbersome to use, and EasyExcel is faster.We can use annotations instead of setting the header and body manually.

Notes

Notes generate Excel code as follows:

String fileName = "temp/annotateWrite" + System.currentTimeMillis() + ".xlsx";
// Here you need to specify which class to write to, then write to the first sheet, named Template, and the file stream will automatically close
// If you want to use 03 here, pass in the excelType parameter
EasyExcel
        .write(fileName, DemoData.class)
        .sheet("Notes")
        .registerWriteHandler(createTableStyle())// Excel Table Style
        .doWrite(data());

The code here is roughly the same as above, except here you need to pass in the DemoData data type in the write method.EasyExcel automatically generates a header based on the DemoData type.

Let's see what the DemoData class really is inside of.

@ContentRowHeight(30)// Surface row height
@HeadRowHeight(20)// Header row height
@ColumnWidth(35)// Column Width
@Data
public class DemoData {
    /**
     * Set the column width individually
     */
    @ColumnWidth(50)
    @ExcelProperty("String Title")
    private String string;
    /**
     * Year, month, day, hour, second format
     */
    @DateTimeFormat("yyyy year MM month dd day HH time mm branch ss second")
    @ExcelProperty(value = "Date Title")
    private Date date;
    /**
     * Formatting Percentage
     */
    @NumberFormat("#.##%")
    @ExcelProperty("Number Title")
    private Double doubleData;
    @ExcelProperty(value = "Enum Class",converter = DemoEnumConvert.class)
    private DemoEnum demoEnum;
    /**
     * Ignore this field
     */
    @ExcelIgnore
    private String ignore;
}

DemoData is a common POJO class that uses the ExayExcel annotations above, and ExayExcel will read the field types and understand them through reflection, then generate Excel directly.

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

• @ExcelProperty specifies which column in excel corresponds to the current field, and the internal value property specifies the name of the header column
• @ExcelIgnore defaults to all fields matching excel, adding this comment will ignore the field
• @ContentRowHeight specifies the height of the table row
• @HeadRowHeight specifies header row height
• @ColumnWidth Specifies the width of the column

ExayExcel also provides several notes, custom dates, and formatting conversions of numbers.

• @DateTimeFormat
• @NumberFormat

In addition, we can customize the formatting conversion scheme, which requires the implementation of Converter class related methods.

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

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

    /**
     * excel Convert to java type, excel will be called when reading
     * @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 will be called when writing
     * @param value
     * @param contentProperty
     * @param globalConfiguration
     * @return
     * @throws Exception
     */
    @Override
    public CellData convertToExcelData(DemoEnum value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return new CellData(value.getDesc());
    }
}

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

Use it as follows:

@ExcelProperty(value = "Enum Class",converter = DemoEnumConvert.class)
private DemoEnum demoEnum;

Finally, let's run it to see how Excel actually works:

Well, the effect is OK.

Yes, the default stylesheet style is different, because we set a custom style in the registerWriteHandler method with the following code:

/***
 * Style excel
 * @return
 */
private static WriteHandler createTableStyle() {
    // Header Policy
    WriteCellStyle headWriteCellStyle = new WriteCellStyle();
    // Set background to red
    headWriteCellStyle.setFillForegroundColor(IndexedColors.PINK.getIndex());
    // Set Font
    WriteFont headWriteFont = new WriteFont();
    headWriteFont.setFontHeightInPoints((short) 20);
    headWriteCellStyle.setWriteFont(headWriteFont);
    // Content Policy
    WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
    // Here you need to specify FillPatternType as FillPatternType.SOLID_FOREGROUND or you will not be able to 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 style of the border
    contentWriteCellStyle.setBorderBottom(BorderStyle.DASHED);
    contentWriteCellStyle.setBorderLeft(BorderStyle.DASHED);
    contentWriteCellStyle.setBorderRight(BorderStyle.DASHED);
    contentWriteCellStyle.setBorderTop(BorderStyle.DASHED);

    // This strategy is that the header-to-header style content is the style of the content Other strategies can be implemented by themselves
    HorizontalCellStyleStrategy horizontalCellStyleStrategy =
            new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    return horizontalCellStyleStrategy;
}

Use Points of Attention

poi conflict problem

In theory, the current easyexcel compatibility supports all newer versions of poi 3.17, 4.0.1, 4.1.0, but if older versions of poi were used before the project, some classes have been deleted due to poi internal code tuning, which makes direct runtime very likely to throw the following exceptions:

• NoSuchMethodException
• ClassNotFoundException
• NoClassDefFoundError

Therefore, attention must be paid to the version of poi in the unified project during use.

Non-annotated custom row height and column width

Customizing row heights and column widths in a non-annotated way is cumbersome, and no direct entry has been found for the time being.Looking through github related issue s, the developer responded that he needed to implement the WriteHandler interface and customize the table style.

summary

This article mainly introduces two ways of generating excel in EasyExcel, and demonstrates the related sample code.In addition to writing, EasyExcel also supports the ability to read Excel quickly, which is not described in detail here.There are many examples of related documents on Github, so you can refer to them for yourself.

Github Document Address: https://alibaba-easyexcel.github.io/index.html

Reference

1. https://github.com/alibaba/easyexcel
2. https://alibaba-easyexcel.github.io/index.html
3. https://cloud.tencent.com/developer/article/1431888