读取Excel数据时遇Cannot get a STRING value from a NUMERIC cell错误求助

AI 小北

2026-7-27

问题描述

尝试从Excel文件导入数据自动化填充网页表单时，运行抛出错误：Cannot get a STRING value from a NUMERIC cell，原因是Excel内部分单元格为数值类型、部分为文本类型。实现代码如下：

package com.Demo;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.chrome.ChromeOptions;
import org.openqa.selenium.support.ui.Select;

public class SignupformTest {

    public static void main(String[] args) throws IOException {
        System.setProperty("webdriver.chrome.driver", "C:\\Users\\VIRAL FISSION\\Music\\Selenium\\New folder\\chromedriver_win32\\chromedriver.exe");
        ChromeOptions options = new ChromeOptions();
        options.setExperimentalOption("excludeSwitches",new String[] {"enable-automation"});
        options.addArguments("--remote-allow-origins=*");
//        options.addArguments("--disable-web-security");
        WebDriver driver = new ChromeDriver(options);
        driver.get("https://app.viralfission.com/signup");
        
        FileInputStream file = new FileInputStream("C:\\Users\\VIRAL FISSION\\Pictures\\testintakedata.xlsx");
        
     // Create Workbook instance holding reference to .xlsx file
     XSSFWorkbook workbook = new XSSFWorkbook(file);
    
     // Get first/desired sheet from the workbook
     XSSFSheet sheet = workbook.getSheet("Sheet1");
     
     
     int noofcount = sheet.getLastRowNum();
     
     System.out.print("No. of Records in the Excel sheet : " + noofcount);
     
     for(int row = 1; row <= noofcount; row++) {
         XSSFRow currentrow =  sheet.getRow(row);
         DataFormatter formatter = new DataFormatter();
         String cellvalue = formatter.formatCellValue(1,0);

         
         String Referal_Code = currentrow.getCell(0).getStringCellValue();
         String First_Name = currentrow.getCell(1).getStringCellValue(); 
         String Last_Name = currentrow.getCell(2).getStringCellValue(); 
         String Contact_Number = currentrow.getCell(3).getStringCellValue();
         String WhatsApp_Number = currentrow.getCell(4).getStringCellValue(); 
         String Gender = currentrow.getCell(5).getStringCellValue(); 
         String Date_of_Birth = currentrow.getCell(6).getStringCellValue(); 
         String Flat_No = currentrow.getCell(7).getStringCellValue(); 
         String Building_Name = currentrow.getCell(8).getStringCellValue(); 
         String Street_Name = currentrow.getCell(9).getStringCellValue(); 
         String Landmark = currentrow.getCell(10).getStringCellValue(); 
         String State = currentrow.getCell(11).getStringCellValue(); 
         String City = currentrow.getCell(12).getStringCellValue(); 
         String Pincode = currentrow.getCell(13).getStringCellValue(); 
         String Instagram = currentrow.getCell(14).getStringCellValue(); 
         String Twitter = currentrow.getCell(15).getStringCellValue(); 
         
         
//       driver.findElement(By.linkText("https://app.viralfission.com/signup")).click();
         
         driver.findElement(By.name("referral")).sendKeys(Referal_Code);
         driver.findElement(By.name("firstname")).sendKeys(First_Name);
         driver.findElement(By.name("lastname")).sendKeys(Last_Name);
         driver.findElement(By.name("contactNumber")).sendKeys(Contact_Number);
         driver.findElement(By.name("whatsAppNumber")).sendKeys(WhatsApp_Number);
         
         
         Select dropgender = new Select(driver.findElement(By.name("gender")));
         dropgender.selectByVisibleText(Gender);
         Select dropdateofbirth = new Select(driver.findElement(By.name("DOB")));
         dropdateofbirth.selectByVisibleText(Date_of_Birth);
         
         driver.findElement(By.name("flatNumber")).sendKeys(Flat_No);
         driver.findElement(By.name("buildingName")).sendKeys(Building_Name);
         driver.findElement(By.name("streetName")).sendKeys(Street_Name);
         driver.findElement(By.name("landmark")).sendKeys(Landmark);
         
         
         Select dropState = new Select(driver.findElement(By.name("state")));
         dropState.selectByVisibleText(State);
         Select dropCity = new Select(driver.findElement(By.name("city")));
         dropCity.selectByVisibleText(City);
         
         driver.findElement(By.name("pincode")).sendKeys(Pincode);
         driver.findElement(By.name("instagram")).sendKeys(Instagram);
         driver.findElement(By.name("twitter")).sendKeys(Twitter);
         
        driver.findElement(By.xpath("//*[@id=\"cdk-step-content-0-0\"]/form/div[7]/button")).click();
        
        if(driver.getPageSource().contains("'Enter your details to get one step closer'")) {
            System.out.println("Good" + row + "Record");
        }

         
     }
     driver.close();


    }

}

错误原因

getStringCellValue()的局限性：这个方法仅能读取文本类型单元格，一旦遇到数值型单元格（比如手机号、邮编这类常被存为数字的字段），直接调用就会抛出类型不匹配异常。
DataFormatter未正确使用：代码里虽创建了DataFormatter实例，但调用formatCellValue(1,0)是错误用法（参数应为单元格对象），且后续获取所有单元格值时完全未用到这个格式化器，等于没起作用。

解决方法

用DataFormatter统一处理所有单元格，它会自动识别单元格类型，将数值、日期等格式转为字符串，完美适配表单的字符串输入需求。具体修改如下：

将DataFormatter实例化移到循环外，避免重复创建对象浪费资源。
所有获取单元格值的地方，替换为formatter.formatCellValue(单元格对象)，同时增加空单元格判断防止空指针。

修改后的完整代码：

package com.Demo;

import java.io.FileInputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.chrome.ChromeOptions;
import org.openqa.selenium.support.ui.Select;

public class SignupformTest {

    public static void main(String[] args) throws IOException {
        System.setProperty("webdriver.chrome.driver", "C:\\Users\\VIRAL FISSION\\Music\\Selenium\\New folder\\chromedriver_win32\\chromedriver.exe");
        ChromeOptions options = new ChromeOptions();
        options.setExperimentalOption("excludeSwitches", new String[]{"enable-automation"});
        options.addArguments("--remote-allow-origins=*");
        WebDriver driver = new ChromeDriver(options);
        driver.get("https://app.viralfission.com/signup");

        FileInputStream file = new FileInputStream("C:\\Users\\VIRAL FISSION\\Pictures\\testintakedata.xlsx");
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheet("Sheet1");

        int noofcount = sheet.getLastRowNum();
        System.out.print("No. of Records in the Excel sheet : " + noofcount);

        // 把DataFormatter实例化移到循环外
        DataFormatter formatter = new DataFormatter();

        for (int row = 1; row <= noofcount; row++) {
            XSSFRow currentrow = sheet.getRow(row);
            if (currentrow == null) continue; // 跳过空行

            // 用formatter统一获取字符串值，自动处理数值/文本类型
            String Referal_Code = getCellValue(currentrow.getCell(0), formatter);
            String First_Name = getCellValue(currentrow.getCell(1), formatter);
            String Last_Name = getCellValue(currentrow.getCell(2), formatter);
            String Contact_Number = getCellValue(currentrow.getCell(3), formatter);
            String WhatsApp_Number = getCellValue(currentrow.getCell(4), formatter);
            String Gender = getCellValue(currentrow.getCell(5), formatter);
            String Date_of_Birth = getCellValue(currentrow.getCell(6), formatter);
            String Flat_No = getCellValue(currentrow.getCell(7), formatter);
            String Building_Name = getCellValue(currentrow.getCell(8), formatter);
            String Street_Name = getCellValue(currentrow.getCell(9), formatter);
            String Landmark = getCellValue(currentrow.getCell(10), formatter);
            String State = getCellValue(currentrow.getCell(11), formatter);
            String City = getCellValue(currentrow.getCell(12), formatter);
            String Pincode = getCellValue(currentrow.getCell(13), formatter);
            String Instagram = getCellValue(currentrow.getCell(14), formatter);
            String Twitter = getCellValue(currentrow.getCell(15), formatter);

            driver.findElement(By.name("referral")).sendKeys(Referal_Code);
            driver.findElement(By.name("firstname")).sendKeys(First_Name);
            driver.findElement(By.name("lastname")).sendKeys(Last_Name);
            driver.findElement(By.name("contactNumber")).sendKeys(Contact_Number);
            driver.findElement(By.name("whatsAppNumber")).sendKeys(WhatsApp_Number);

            Select dropgender = new Select(driver.findElement(By.name("gender")));
            dropgender.selectByVisibleText(Gender);
            Select dropdateofbirth = new Select(driver.findElement(By.name("DOB")));
            dropdateofbirth.selectByVisibleText(Date_of_Birth);

            driver.findElement(By.name("flatNumber")).sendKeys(Flat_No);
            driver.findElement(By.name("buildingName")).sendKeys(Building_Name);
            driver.findElement(By.name("streetName")).sendKeys(Street_Name);
            driver.findElement(By.name("landmark")).sendKeys(Landmark);

            Select dropState = new Select(driver.findElement(By.name("state")));
            dropState.selectByVisibleText(State);
            Select dropCity = new Select(driver.findElement(By.name("city")));
            dropCity.selectByVisibleText(City);

            driver.findElement(By.name("pincode")).sendKeys(Pincode);
            driver.findElement(By.name("instagram")).sendKeys(Instagram);
            driver.findElement(By.name("twitter")).sendKeys(Twitter);

            driver.findElement(By.xpath("//*[@id=\"cdk-step-content-0-0\"]/form/div[7]/button")).click();

            if (driver.getPageSource().contains("'Enter your details to get one step closer'")) {
                System.out.println("Good " + row + " Record");
            }
        }
        driver.close();
        workbook.close(); // 关闭workbook释放资源
        file.close(); // 关闭文件流
    }

    // 封装工具方法，处理空单元格
    private static String getCellValue(org.apache.poi.ss.usermodel.Cell cell, DataFormatter formatter) {
        if (cell == null) {
            return "";
        }
        return formatter.formatCellValue(cell);
    }
}

额外优化点：

增加空行、空单元格判断，避免空指针异常。
关闭workbook和file流，释放资源。
格式化输出日志字符串，提升可读性。

内容的提问来源于stack exchange，提问作者Abhishekh Shukla