Back to Repositories

Testing POI Excel Format Handling Implementation in EasyExcel

This test suite evaluates Apache POI functionality within the EasyExcel library, focusing on Excel workbook and cell formatting operations. It includes tests for both SXSSF and XSSF implementations, verifying row counting and cell value formatting capabilities.

Test Coverage Overview

The test suite covers essential Excel manipulation operations using Apache POI.

Key areas tested include:

Last row number retrieval for SXSSF workbooks
Sheet manipulation and row creation
XSSF workbook sheet counting and cell formatting
Data formatting with locale-specific settings

Implementation Analysis

The testing approach employs JUnit Jupiter for unit testing POI operations. Tests utilize both streaming (SXSSF) and standard (XSSF) workbook implementations to verify Excel file handling.

Notable patterns include:

Direct file system interaction for Excel files
Logging of operation results using SLF4J
Separate test methods for SXSSF and XSSF implementations

Technical Details

Testing tools and configuration:

JUnit Jupiter test framework
Apache POI library for Excel operations
SLF4J logging framework
SXSSF for memory-efficient streaming
XSSF for XML-based Excel 2007+ files
DataFormatter for locale-specific cell formatting

Best Practices Demonstrated

The test suite exemplifies several testing best practices for Excel file operations.

Notable practices include:

Separate test methods for different POI implementations
Proper resource handling with Excel workbooks
Structured logging for debugging and verification
Locale-aware testing for international compatibility

alibaba/easyexcel

easyexcel-test/src/test/java/com/alibaba/easyexcel/test/temp/poi/PoiFormatTest.java

            
package com.alibaba.easyexcel.test.temp.poi;

import java.io.IOException;
import java.util.Locale;

import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 测试poi
 *
 * @author Jiaju Zhuang
 **/

public class PoiFormatTest {
    private static final Logger LOGGER = LoggerFactory.getLogger(PoiFormatTest.class);

    @Test
    public void lastRowNum() throws IOException {
        String file = "D:\\test\\原文件.xlsx";
        SXSSFWorkbook xssfWorkbook = new SXSSFWorkbook(new XSSFWorkbook(file));
        SXSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
        LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
        SXSSFRow row = xssfSheet.getRow(0);
        LOGGER.info("第一行数据:{}", row);
        xssfSheet.createRow(20);
        LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
    }

    @Test
    public void lastRowNumXSSF() throws IOException {
        String file = "/Users/zhuangjiaju/Downloads/测试格式.xlsx";
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
        LOGGER.info("一共:{}个sheet", xssfWorkbook.getNumberOfSheets());
        XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
        LOGGER.info("一共行数:{}", xssfSheet.getLastRowNum());
        XSSFRow row = xssfSheet.getRow(1);
        XSSFCell xssfCell = row.getCell(0);
        DataFormatter d = new DataFormatter(Locale.CHINA);
        LOGGER.info("fo:{}", d.formatCellValue(xssfCell));

    }
}