Properly Extracting String Values from Excel Cells Using Apache POI DataFormatter

Problem Background and Error Analysis

When working with Apache POI library for Excel file processing, developers frequently encounter the Cannot get a text value from a numeric cell error. The root cause of this issue lies in the mismatch between Excel cell data types and the methods called in code. When a cell is formatted as numeric type, directly calling getStringCellValue() method will throw an exception.

In the original code, the developer attempted to extract string values from cells that might contain numeric data:

String j_username = sheet.getRow(i).getCell(0).getStringCellValue();
String j_password = sheet.getRow(i).getCell(0).getStringCellValue();

The limitation of this approach is that it assumes all cells are text type, while actual Excel files often contain mixed data types including numbers, dates, and boolean values.

DataFormatter Solution

Apache POI provides the DataFormatter class as the standard solution for handling mixed data type cells. This class intelligently recognizes the actual data type of cells and returns formatted values as strings.

The core implementation code is as follows:

import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

FileInputStream fis = new FileInputStream("C:\\paulo.xls");
Workbook workbook = WorkbookFactory.create(fis);
DataFormatter formatter = new DataFormatter();

for (int i = 1; i <= sheet.getLastRowNum(); i++) {
    String j_username = formatter.formatCellValue(sheet.getRow(i).getCell(0));
    String j_password = formatter.formatCellValue(sheet.getRow(i).getCell(1));
    
    searchbox.sendKeys(j_username);
    searchbox2.sendKeys(j_password);
    searchbox.submit();
}

The DataFormatter.formatCellValue() method automatically handles various data types: for numeric cells, it preserves original precision; for date cells, it converts according to Excel's display format; for boolean values, it returns "TRUE" or "FALSE" strings.

Limitations of setCellType Method

Although some developers use cell.setCellType(Cell.CELL_TYPE_STRING) to force cell type conversion, this approach has serious issues. According to Apache POI official documentation, this method damages the integrity of original cell data, particularly for numeric and date type data.

Main problems include:

• Loss of numeric precision: forced conversion may truncate decimal parts
• Date format errors: date values may be converted to meaningless numeric strings
• Performance overhead: frequent type conversions increase processing time
• Data consistency risks: modifying original cell types may affect subsequent processing

Complete Best Practice Implementation

Combined with web automation testing scenarios, the complete optimized implementation should include error handling and resource management:

try (FileInputStream file = new FileInputStream(new File("C:\\paulo.xls"))) {
    Workbook workbook = WorkbookFactory.create(file);
    Sheet sheet = workbook.getSheetAt(0);
    DataFormatter formatter = new DataFormatter();
    
    for (int i = 1; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);
        if (row != null) {
            String j_username = formatter.formatCellValue(row.getCell(0));
            String j_password = formatter.formatCellValue(row.getCell(1));
            
            // Handle possible null values
            if (j_username != null && j_password != null) {
                searchbox.clear();
                searchbox2.clear();
                searchbox.sendKeys(j_username);
                searchbox2.sendKeys(j_password);
                searchbox.submit();
                
                driver.manage().timeouts().implicitlyWait(10, TimeUnit.SECONDS);
            }
        }
    }
} catch (IOException e) {
    e.printStackTrace();
}

Technical Deep Dive

The working principle of DataFormatter is based on Excel's formatting rules. It doesn't just perform simple type conversion, but simulates Excel's display logic:

• For numeric cells, it applies number format patterns (like thousand separators, decimal places)
• For date cells, it uses date format strings for conversion
• Supports localization processing, adjusting output formats according to different locale settings
• Maintains consistency with Excel interface display

This approach ensures accuracy and consistency in data extraction, particularly in scenarios where Excel data needs to be used for report generation or user interface presentation.

Performance Optimization Recommendations

When processing large Excel files, consider the following optimization strategies:

• Reuse DataFormatter instances to avoid repeated creation
• Use try-with-resources statements to ensure proper resource release
• For read-only operations, consider event-driven parsing mode
• Process data in batches to reduce I/O operation frequency

By adopting the correct usage of DataFormatter, developers can build more robust and reliable Excel data processing applications, avoiding runtime errors caused by data type mismatches.