Back to Repositories

Testing Custom Excel Styling Implementation in alibaba/easyexcel

This test suite demonstrates Excel file writing capabilities with custom styling using EasyExcel library. It focuses on implementing custom cell styles for headers and content, including font sizes and background colors.

Test Coverage Overview

The test coverage focuses on Excel file generation with advanced styling features:

Custom header styling with red background and 20pt font
Content styling with green background and custom font size
Dynamic header generation with timestamps
Data writing with custom formatting

Implementation Analysis

The testing approach utilizes JUnit framework to validate EasyExcel’s writing capabilities. It implements HorizontalCellStyleStrategy for distinct header and content styling, demonstrating separation of concerns in style management.

Key patterns include factory methods for header and data generation, and fluent API usage for Excel writing configuration.

Technical Details

JUnit Jupiter for test execution
Apache POI integration for Excel styling (IndexedColors, FillPatternType)
EasyExcel’s WriteCellStyle and WriteFont for style customization
Custom utility classes for file path management
SLF4J for logging

Best Practices Demonstrated

The test exhibits several testing best practices:

Separation of test data generation from test logic
Clear style configuration separation for headers and content
Proper resource management with automatic file stream closure
Meaningful test data generation with sequential numbering

alibaba/easyexcel

easyexcel-test/src/test/java/com/alibaba/easyexcel/test/temp/WriteV34Test.java

            
package com.alibaba.easyexcel.test.temp;

import java.util.ArrayList;
import java.util.List;

import com.alibaba.easyexcel.test.demo.write.DemoData;
import com.alibaba.easyexcel.test.util.TestFileUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;

import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 临时测试
 *
 * @author Jiaju Zhuang
 **/

public class WriteV34Test {

    private static final Logger LOGGER = LoggerFactory.getLogger(WriteV34Test.class);

    @Test
    public void test() throws Exception {
        String fileName = TestFileUtil.getPath() + "handlerStyleWrite" + System.currentTimeMillis() + ".xlsx";
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为红色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short)20);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 背景绿色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short)20);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
            new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName, DemoData.class).head(head()).registerWriteHandler(horizontalCellStyleStrategy).sheet(
                "模板")
            .doWrite(data(1));
    }

    private List<List<String>> head() {
        List<List<String>> list = new ArrayList<List<String>>();
        List<String> head0 = new ArrayList<String>();
        head0.add("字符串" + System.currentTimeMillis());
        head0.add("再找找");
        List<String> head1 = new ArrayList<String>();
        head1.add("数字" + System.currentTimeMillis());
        List<String> head2 = new ArrayList<String>();
        head2.add("日期" + System.currentTimeMillis());
        List<String> head3 = new ArrayList<String>();
        head3.add("日期" + System.currentTimeMillis());
        list.add(head0);
        list.add(head1);
        list.add(head2);
        list.add(head3);

        return list;
    }

    private List<DemoData> data(int no) {
        List<DemoData> list = new ArrayList<DemoData>();
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + no + "---" + i);
            list.add(data);
        }
        return list;
    }

}