Back to Repositories

Testing Cell Data Operations in Multiple Excel Formats in alibaba/easyexcel

This test suite validates cell data handling in EasyExcel, focusing on reading and writing different Excel file formats (XLSX, XLS, CSV) with various data types including dates, integers, and formulas.

Test Coverage Overview

The test suite provides comprehensive coverage for cell data operations across multiple file formats:

Tests XLSX (Excel 2007+), XLS (Excel 2003), and CSV file formats
Validates date formatting and parsing
Tests integer cell data with explicit type casting
Verifies formula handling and evaluation

Implementation Analysis

The testing approach utilizes JUnit Jupiter for structured test execution with ordered test methods. Each test case follows a consistent pattern of writing data to a file and then reading it back to verify data integrity.

The implementation leverages EasyExcel’s WriteCellData class for type-safe cell data handling and employs the CellDataTypeEnum for explicit type definition.

Technical Details

JUnit Jupiter test framework with @TestMethodOrder annotation
EasyExcel API for Excel operations
Custom utility class TestFileUtil for file handling
WriteCellData for type-safe cell data management
FormulaData class for Excel formula processing

Best Practices Demonstrated

The test suite exemplifies several testing best practices:

Separation of concerns with separate read/write data classes
Centralized test data generation
Reusable test methods for different file formats
Proper test initialization with @BeforeAll
Clear test method naming convention

alibaba/easyexcel

easyexcel-test/src/test/java/com/alibaba/easyexcel/test/core/celldata/CellDataDataTest.java

            
package com.alibaba.easyexcel.test.core.celldata;

import java.io.File;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import com.alibaba.easyexcel.test.util.TestFileUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.FormulaData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.util.DateUtils;

import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.MethodOrderer;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.TestMethodOrder;

/**
 * @author Jiaju Zhuang
 */
@TestMethodOrder(MethodOrderer.MethodName.class)
public class CellDataDataTest {

    private static File file07;
    private static File file03;
    private static File fileCsv;

    @BeforeAll
    public static void init() {
        file07 = TestFileUtil.createNewFile("cellData07.xlsx");
        file03 = TestFileUtil.createNewFile("cellData03.xls");
        fileCsv = TestFileUtil.createNewFile("cellDataCsv.csv");
    }

    @Test
    public void t01ReadAndWrite07() throws Exception {
        readAndWrite(file07);
    }

    @Test
    public void t02ReadAndWrite03() throws Exception {
        readAndWrite(file03);
    }

    @Test
    public void t03ReadAndWriteCsv() throws Exception {
        readAndWrite(fileCsv);
    }

    private void readAndWrite(File file) throws Exception {
        EasyExcel.write(file, CellDataWriteData.class).sheet().doWrite(data());
        EasyExcel.read(file, CellDataReadData.class, new CellDataDataListener()).sheet().doRead();
    }

    private List<CellDataWriteData> data() throws Exception {
        List<CellDataWriteData> list = new ArrayList<>();
        CellDataWriteData cellDataData = new CellDataWriteData();
        cellDataData.setDate(new WriteCellData<>(DateUtils.parseDate("2020-01-01 01:01:01")));
        WriteCellData<Integer> integer1 = new WriteCellData<>();
        integer1.setType(CellDataTypeEnum.NUMBER);
        integer1.setNumberValue(BigDecimal.valueOf(2L));
        cellDataData.setInteger1(integer1);
        cellDataData.setInteger2(2);
        WriteCellData<?> formulaValue = new WriteCellData<>();
        FormulaData formulaData = new FormulaData();
        formulaValue.setFormulaData(formulaData);
        formulaData.setFormulaValue("B2+C2");
        cellDataData.setFormulaValue(formulaValue);
        list.add(cellDataData);
        return list;
    }
}