Selenium读取Excel日期显示为数字的解决方法及代码优化咨询

Hey there, let's sort out that frustrating date reading issue you're facing with Excel in your Selenium project! The problem you're seeing—dates showing up as numbers like 43070 or 43095—happens because Excel stores dates as serial numbers (counting days since January 1, 1900 by default). When you force the cell type to string directly, you're just converting that raw serial number to text, instead of interpreting it as a date.

The Solution: Handle Cell Types Dynamically #

Instead of forcing all cells to string type upfront, you need to check the cell's type first, then process it accordingly. This way, you can correctly parse dates while preserving string values as-is. Here are two reliable approaches:

Approach 1: Manual Type Checking with Date Formatting

This gives you full control over how dates are formatted. First, initialize a date formatter for your desired output (e.g., dd-MM-yyyy), then handle each cell type in your loop:

// Initialize date formatter outside your loops to avoid redundant object creation
SimpleDateFormat dateFormatter = new SimpleDateFormat("dd-MM-yyyy");

for (int i = 0; i <= TcRow; i++) { 
    for (int j = 0; j < TcCol; j++) { 
        Cell cell = TcSheet.getRow(i).getCell(j);
        String cellValue = "";

        if (cell != null) { // Handle empty cells to avoid null pointer exceptions
            switch (cell.getCellType()) {
                case STRING:
                    // Directly get string values
                    cellValue = cell.getStringCellValue();
                    break;
                case NUMERIC:
                    // Check if the numeric cell is formatted as a date
                    if (DateUtil.isCellDateFormatted(cell)) {
                        Date dateValue = cell.getDateCellValue();
                        cellValue = dateFormatter.format(dateValue);
                    } else {
                        // For regular numbers, convert to string (preserves formatting if needed)
                        cellValue = String.valueOf(cell.getNumericCellValue());
                        // Alternatively, use DataFormatter to keep Excel's display format:
                        // cellValue = new DataFormatter().formatCellValue(cell);
                    }
                    break;
                case BOOLEAN:
                    cellValue = String.valueOf(cell.getBooleanCellValue());
                    break;
                case FORMULA:
                    // Handle formula cells by getting their computed value
                    cellValue = new DataFormatter().formatCellValue(cell);
                    break;
                default:
                    cellValue = "";
            }
        }
        // Assign the processed value to your data array
        data[i][j] = cellValue;
    }
}

Approach 2: Use DataFormatter for Simplified Handling

Apache POI's DataFormatter class automatically converts cell values to the same string representation you see in Excel. This means it will return formatted dates instead of serial numbers, and preserve string values without extra work. It's a great "set it and forget it" option:

// Initialize DataFormatter once outside your loops
DataFormatter dataFormatter = new DataFormatter();

for (int i = 0; i <= TcRow; i++) { 
    for (int j = 0; j < TcCol; j++) { 
        Cell cell = TcSheet.getRow(i).getCell(j);
        // Get the cell value as it appears in Excel, or empty string if cell is null
        String cellValue = cell != null ? dataFormatter.formatCellValue(cell) : "";
        data[i][j] = cellValue;
    }
}

Key Notes #

• Date Formatting: If you use the manual approach, adjust SimpleDateFormat to match your required output (e.g., MM-dd-yyyy for month-day-year).
• Null Cells: Always check if a cell is null to avoid NullPointerException—empty rows or columns might have null cells.
• Time Zones: If your dates include time or need timezone adjustments, add a timezone to your SimpleDateFormat (e.g., dateFormatter.setTimeZone(TimeZone.getTimeZone("UTC"))).

Either approach will fix your date-to-number issue while keeping string values intact. The DataFormatter method is simpler for most cases, while manual type checking gives you more control over edge cases like custom numeric formatting.

内容的提问来源于stack exchange，提问作者Prasanna Ananthakrishnan