Ali's open source library makes Excel export no longer complex (both able to write and good-looking)

Keywords: Java

This picture was created by birgl stay Pixabay Publish on

Hello, I'm looking at the mountain.

Above After talking about the content export of EasyExcel, this paper mainly talks about the formatting of export files, including worksheet / cell style and content formatting. After all, sometimes it still depends on the face.

There are many contents. Only the key codes will be listed in the article. If you want a complete source code, you can pay attention to the public name "mountain viewing cabin" and reply to "easyexcel".

Annotation format

Defining format through annotation is an advanced function encapsulated by EasyExcel, which allows us to define format conveniently.

Format content

First define an entity class that uses annotations to format content:

@Data
public class FormatContentItem {
    @ExcelProperty(value = "String title", converter = TitleFormatConverter.class)
    private String string;
    @DateTimeFormat("yyyy year MM month dd day HH Time mm branch ss second")
    @ExcelProperty(value = "Date title")
    private Date date;
    @NumberFormat("0.000%")
    @ExcelProperty("Digital title")
    private Double doubleData;
}

DateTimeFormat and NumberFormat are self-contained annotations used to format time and numbers.

The DateTimeFormat annotation has two attributes. One attribute is value, which is used to define the time format. You can refer to java.text.SimpleDateFormat; Another attribute is use1904 windowing, which indicates whether the usage time uses the 1904 time system or the 1900 time system. By default, whether to use the 1904 time system or the 1900 time system.

The NumberFormat annotation has two attributes. One attribute is value, which is used to define the number format. You can refer to java.text.DecimalFormat; The other attribute is roundingMode, which is used to define the way to retain decimals. java.math.RoundingMode enumeration is used.

To format a string, you can use the Converter property of ExcelProperty, which is passed into the class that implements the Converter. For example, the code of TitleFormatConverter in the example is as follows:

public class TitleFormatConverter implements Converter<String> {
    @Override
    public Class<?> supportJavaTypeKey() {
        return String.class;
    }

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

    @Override
    public WriteCellData<?> convertToExcelData(String value, ExcelContentProperty contentProperty,
            GlobalConfiguration globalConfiguration) {
        return new WriteCellData<>(String.format("title:%s(Custom)", value));
    }

}

The result is:

Define row height and column width

If you use the annotation to define the line height, you can use HeadRowHeight to define the header height and ContentRowHeight to define the table body height. After this annotation is defined, all table body heights are the same. The column width can be defined using the ColumnWidth annotation. This annotation can be defined on the class, indicating that the columns of the whole table are the same width, or on the defined attribute, indicating the width of the specified column.

@Data
@HeadRowHeight(20)
@ContentRowHeight(10)
@ColumnWidth(25)
public class FormatCellItem {
    @ExcelProperty("String title")
    private String string;
    @ExcelProperty("Date title")
    private Date date;
    @ColumnWidth(50)
    @ExcelProperty("Digital title")
    private Double doubleData;
}

The result is:

Cell definition style

The control cell style has four annotations: HeadStyle, HeadFontStyle, ContentStyle and ContentFontStyle. These four annotations can be defined on the class as the style of the global table or on the field as the style of the current column. Here are the configurations commonly used in these annotations.

*Style: divided into HeadStyle and ContentStyle, which define header and table body styles respectively
- dataFormat: header format, short format. It is the subscript of the defined format in the org.apache.poi.ss.usermodel.BuiltinFormats class
- Border *: These are four attributes: borderLeft, borderRight, borderTop and borderBottom. The type is com.alibaba.excel.enums.poi.BorderStyleEnum enumeration, which is used to define the border style of header cells. The color of the border can also be defined by using, * BorderColor.
- fillPatternType: fill type. The type is com.alibaba.excel.enums.poi.FillPatternTypeEnum enumeration. If you want to fill the background color, this property needs to be set to SOLID_FOREGROUND.
- Fillforeroundcolor: foreground color. The type is short, but the value is the idx value of the org.apache.poi.ss.usermodel.IndexedColors enumeration. However, the two types are inconsistent. One is short and the other is int. there is no way to directly reference it. It can be seen that there are still many pits between dependencies in java.
- fillBackgroundColor: background color, the same as fillforeroundcolor.
- Rotation: content rotation angle
*FontStyle: there are HeadFontStyle and ContentFontStyle, which define the font styles of header and table body respectively.
- fontName: defines the font name and type string
- Fonthightinpoints: font size, type is short
- italic: whether it is italicized. The type is com.alibaba.excel.enums.boolean enum
- Bold: bold. The type is com.alibaba.excel.enums.boolean enum
- Strike out: whether to use strikeout (this word originally means strike out, which should be related to baseball)
- Color: text color. The value is org.apache.poi.ss.usermodel.IndexedColors. There are still inconsistent types
- Underline: underline. The type is byte. Font. U can be used directly_ NONE,Font.U_SINGLE,Font.U_DOUBLE,Font.U_SINGLE_ACCOUNTING,Font.U_DOUBLE_ACCOUNTING.

We can define it this way:

@Data
// Set the header background to red IndexedColors.RED.getIndex()
@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 10)
// The header font is set to 20
@HeadFontStyle(fontHeightInPoints = 20)
// Set the background of the content to green IndexedColors.GREEN.getIndex()
@ContentStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 17)
// The content font is set to 20
@ContentFontStyle(fontHeightInPoints = 20)
public class FormatStyleCellItem {
    // Set the header background of the string to pink IndexedColors.PINK.getIndex()
    @HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 14)
    // The header font of the string is set to 20
    @HeadFontStyle(fontHeightInPoints = 30)
    // The background of the contents of the string is set to sky blue IndexedColors.SKY_BLUE.getIndex()
    @ContentStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)
    // The content font of the string is set to 20
    @ContentFontStyle(fontHeightInPoints = 30)
    @ExcelProperty("String title")
    private String string;
    @ExcelProperty("Date title")
    private Date date;
    @ExcelProperty("Digital title")
    private Double doubleData;
}

The result is:

Class object definition format

This method can be said to be a pure manual assembly of data. The com.alibaba.excel.metadata.data.WriteCellData class is used. This class is equivalent to the definition of cells. The current cell format can be indicated by setting the type attribute of com.alibaba.excel.enums.CellDataTypeEnum enumeration type.

The law of conservation always exists. This method is highly flexible and can be refined to specific cell formats, but the complexity is also increased.

Hyperlinks

The hyperlink uses the com.alibaba.excel.metadata.data.HyperlinkData class. You need to set the address and hyperlink type (com.alibaba.excel.metadata.data.HyperlinkData.HyperlinkType enumeration), and then write the value to the hyperlinkData attribute of the WriteCellData object.

// Set hyperlink
HyperlinkData hyperlinkData = new HyperlinkData();
hyperlinkData.setAddress("https://www.howardliu.cn");
hyperlinkData.setHyperlinkType(HyperlinkType.URL);
WriteCellData<String> hyperlink = new WriteCellData<>("website");
hyperlink.setHyperlinkData(hyperlinkData);

remarks

The notes use the com.alibaba.excel.metadata.data.CommentData class. You need to set the author and note content (com.alibaba.excel.metadata.data.RichTextStringData type), because the default size of the notes is the cell size. If it feels too small, you can also set the relative height and width.

// Set notes
CommentData commentData = new CommentData();
commentData.setAuthor("Howard Liu");
commentData.setRichTextStringData(new RichTextStringData("This is a note"));
// The default size of the note is based on the size of the cell. Here, you want to adjust it to the size of four cells, so one cell is occupied backward and downward
commentData.setRelativeLastColumnIndex(1);
commentData.setRelativeLastRowIndex(1);
WriteCellData<String> comment = new WriteCellData<>("Cell information for comments");
comment.setCommentData(commentData);

formula

The formula uses the com.alibaba.excel.metadata.data.FormulaData class, which can directly set the formulaValue formula, but the formula is not recommended by the official.

// Set formula
FormulaData formulaData = new FormulaData();
// Replace the first number in 123456789 with 2
// This is just an example. If the formula can be calculated in memory, try not to use it
formulaData.setFormulaValue("REPLACE(123456789,1,1,2)");
WriteCellData<String> formula = new WriteCellData<>();
formula.setFormulaData(formulaData);

Format cells

Defining cell formats through classes is essentially the same as defining cell formats through annotations. Therefore, corresponding to the annotation HeadStyle, HeadFontStyle, ContentStyle and ContentFontStyle, the class for setting cell format is WriteCellStyle and the class for setting font is WriteFont. The properties of these classes are also similar to those of annotations. I won't repeat them too much. I'll go to the example directly. (in fact, I don't think there are many scenarios using class definition format. If you really encounter it, just look at the class definition.)

// Set the style of a single cell. Of course, if there are many styles, you can also use annotations and other methods.
WriteCellStyle writeCellStyleData = new WriteCellStyle();
// Here, you need to specify FillPatternType as FillPatternType.SOLID_FOREGROUND otherwise, the background color cannot be displayed.
writeCellStyleData.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// Background green
writeCellStyleData.setFillForegroundColor(IndexedColors.GREEN.getIndex());
WriteCellData<String> writeCellStyle = new WriteCellData<>("cell style ");
writeCellStyle.setWriteCellStyle(writeCellStyleData);
writeCellStyle.setType(CellDataTypeEnum.STRING);

// Set multiple styles for a single cell
RichTextStringData richTextStringData = new RichTextStringData();
richTextStringData.setTextString("Red green default");
// The first two words are red
WriteFont writeFont = new WriteFont();
writeFont.setColor(IndexedColors.RED.getIndex());
richTextStringData.applyFont(0, 2, writeFont);
// The next two words are green
writeFont = new WriteFont();
writeFont.setColor(IndexedColors.GREEN.getIndex());
richTextStringData.applyFont(2, 4, writeFont);
WriteCellData<String> richTest = new WriteCellData<>();
richTest.setType(CellDataTypeEnum.RICH_TEXT_STRING);
richTest.setRichTextStringDataValue(richTextStringData);

The result is:

Interceptor definition format

In addition to directly using the class to define the format, we can also implement it with the help of interceptors. (there will be some ambiguity in the name here. The class objects used are named xxxStrategy, which translates to xxx strategy, but it is officially named interceptor)

Existing interceptor

In the previous example, the cell style can be realized by using WriteCellStyle and WriteFont. If you want to realize that the whole row of data is in the same format, you can use the com.alibaba.excel.write.style.HorizontalCellStyleStrategy interceptor.

/**
 * Use existing policies to implement custom styles
 *
 * <ul>
 *     <li>HorizontalCellStyleStrategy The style of each line is the same or interlaced</li>
 *     <li>AbstractVerticalCellStyleStrategy The style of each column is the same, and you need to call back each page yourself</li>
 * </ul>
 */
private static void writeByCellStyleStrategy() {
    String fileName = defaultFileName("writeByCellStyleStrategy");

    // Header policy
    WriteCellStyle headWriteCellStyle = new WriteCellStyle();
    // The background is set to red
    headWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
    WriteFont headWriteFont = new WriteFont();
    headWriteFont.setFontHeightInPoints((short) 40);
    headWriteCellStyle.setWriteFont(headWriteFont);

    // Table body policy
    WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
    // Here, you need to specify FillPatternType as FillPatternType.SOLID_FOREGROUND otherwise, the background color cannot be displayed. The header defaults to fillpattertype, so it can not be specified
    contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
    // Background green
    contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
    WriteFont contentWriteFont = new WriteFont();
    // font size
    contentWriteFont.setFontHeightInPoints((short) 20);
    contentWriteCellStyle.setWriteFont(contentWriteFont);

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

    // Here, you need to specify which class to write, and then write to the first sheet with the name of template, and then the file stream will be closed automatically
    EasyExcelFactory.write(fileName)
            .head(Item.class)
            .registerWriteHandler(horizontalCellStyleStrategy)
            .sheet()
            .doWrite(sampleItems());
}

The result is:

As shown in the above results, if the data of a cell is long, there may be occlusion. At this time, we can use com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy to realize automatic column width adjustment. But this is not accurate, but it is better than nothing.

private static void writeUseLongestMatchColumnWidthStyleStrategy() {
    String fileName = defaultFileName("writeUseLongestMatchColumnWidthStyleStrategy");
    EasyExcelFactory.write(fileName)
            .head(Item.class)
            .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
            .sheet()
            .doWrite(sampleItems());
}

The result is:

As you can see, it's not accurate enough.

custom interceptor

The interceptors shown above implement the com.alibaba.excel.write.handler.WriteHandler interface, and then register in the write function using the com.alibaba.excel.write.builder.AbstractExcelWriterParameterBuilder.registerWriteHandler method. Therefore, we may also define our own interceptors as needed.

This custom interceptor is a low-level function and needs to understand many underlying designs and API s. In view of the length, this article can't cover it. Only examples are given here. If necessary, you can leave a message for communication.

For example, we need to set data validation in some cells. The display form is a drop-down menu. We can write as follows:

public class ColumnValidationWriteHandler implements SheetWriteHandler {
    @Override
    public void afterSheetCreate(SheetWriteHandlerContext context) {
        // The interval sets the data of the first column, the first row and the second row. Since the first row is the header, the data of the first and second rows are actually the second and third rows
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 2, 0, 0);
        DataValidationHelper helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper();
        DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[] {"Test 1", "Test 2"});
        DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
        context.getWriteSheetHolder().getSheet().addValidationData(dataValidation);
    }
}

If we need to format a cell as a hyperlink, we can also use the Interceptor:

public class CellStyleWriteHandler implements CellWriteHandler {
    @Override
    public void afterCellDispose(CellWriteHandlerContext context) {
        Cell cell = context.getCell();
        // You can perform any operation on the cell here
        if (BooleanUtils.isTrue(context.getHead()) && cell.getColumnIndex() == 0) {
            CreationHelper createHelper = context.getWriteSheetHolder().getSheet().getWorkbook().getCreationHelper();
            Hyperlink hyperlink = createHelper.createHyperlink(HyperlinkType.URL);
            hyperlink.setAddress("https://www.howardliu.cn");
            cell.setHyperlink(hyperlink);
        }
    }
}

The result is:

merge cell

The cell merging function provided by EasyExcel is relatively simple. There are two ways: annotation based merging and Interceptor Based merging.

annotation

Annotation based merged cells provide two annotations:

The OnceAbsoluteMerge annotation implements the merge at the specified location
ContentLoopMerge is a circular merge of contents, specifying that a column is merged every few rows.

// Merge columns 2-3 in rows 6-7 into one cell
@OnceAbsoluteMerge(firstRowIndex = 5, lastRowIndex = 6, firstColumnIndex = 1, lastColumnIndex = 2)
@Data
public class MergeCellItem {
    @ContentLoopMerge(eachRow = 2)
    @ExcelProperty("String title")
    private String string;
    @ExcelProperty("Date title")
    private Date date;
    @ExcelProperty("Digital title")
    private Double doubleData;
}

The result is:

Interceptor

There are also two types of interceptor merging, corresponding to the annotation:

OnceAbsoluteMergeStrategy, relative position merging
LoopMergeStrategy loop merge

private static void writeMergeCellCustom() {
    String fileName = defaultFileName("writeMergeCellCustom");
    // Merge every 2 rows
    // Set eachColumn to 3, which is the length of our data, so the first column will be merged. Of course, other merge strategies can also be written by yourself
    LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 0);
    EasyExcelFactory.write(fileName)
            .head(Item.class)
            .registerWriteHandler(loopMergeStrategy)
            .sheet()
            .doWrite(sampleItems());
}

The result is:

Summary at the end of the paper

From the perspective of actual combat, this paper explains that if EasyExcel realizes to write good-looking tables, EasyExcel provides a lot of annotations and interceptors for formatting, which can be formatted and output by hourly salary. If there are more personalized format requirements, it can also customize the interceptor. Next, let's talk about how to fill the template.

Programmer Group

Ali's open source library makes Excel export no longer complex (both able to write and good-looking)

Annotation format

Format content

Define row height and column width

Cell definition style

Class object definition format

Hyperlinks

remarks

formula

Format cells

Interceptor definition format

Existing interceptor

custom interceptor

merge cell

annotation

Interceptor

Summary at the end of the paper

Recommended reading

Hot Keywords