无法从数字单元格获取文本值“Poi”

Question

无法从数字单元格获取文本值“Poi”

80

我想从Excel电子表格中读取数据，但总是出现错误。我已经尝试将工作表格式设置为文本和数字，但错误仍然存在。

我看到一个人使用了cell.setCellType(Cell.CELL_TYPE_STRING);解决了这个问题，但我不知道在我的代码中应该在哪里加入这行代码。

WebElement searchbox = driver.findElement(By.name("j_username"));
WebElement searchbox2 = driver.findElement(By.name("j_password"));         

try {
    FileInputStream file = new FileInputStream(new File("C:\\paulo.xls")); 
    HSSFWorkbook workbook = new HSSFWorkbook(file);
    HSSFSheet sheet = workbook.getSheetAt(0);

    for (int i=1; i <= sheet.getLastRowNum(); i++){
        String j_username = sheet.getRow(i).getCell(0).getStringCellValue();
        String j_password = sheet.getRow(i).getCell(0).getStringCellValue();

        searchbox.sendKeys(j_username);
        searchbox2.sendKeys(j_password);
        searchbox.submit();  

        driver.manage().timeouts().implicitlyWait(10000, TimeUnit.MILLISECONDS);
    }

    workbook.close();
    file.close();
} catch (FileNotFoundException fnfe) {
    fnfe.printStackTrace();
} catch (IOException ioe) {
    ioe.printStackTrace();

- Paulo Roberto

你能放置完整的堆栈跟踪错误信息吗？ - Anatoly

10

sheet.getRow(i).getCell(0).toString() 是什么意思？ - Abdull

1

@Abdull，你的评论比官方答案还有用 :) 解决了我的问题。 - pouyan021

1

如果您需要将数字类型转换为字符串形式，请尝试使用cell.getRawValue()：https://dev59.com/h8_wz4gBFxS5KdRjRuRo - Criss

14个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- yathin c · Answer 1

这是解决“无法从数字单元格获取文本值”错误的另一种方法：“Poi”

进入Excel工作表。拖动并选择您正在从Excel工作表导入数据的数字。转到“格式”>“数字”>然后选择“纯文本”，然后将其导出为.xlsx文件。现在尝试运行脚本

希望能正常工作...！

Cannot get a text value from a numeric cell “Poi”.img

- virtuvious · Answer 2

如果您正在使用cellIterator处理行，则以下内容适用于我...

  DataFormatter formatter = new DataFormatter();   
  while(cellIterator.hasNext())
  {                         
        cell = cellIterator.next();
        String val = "";            
        switch(cell.getCellType()) 
        {
            case Cell.CELL_TYPE_NUMERIC:
                val = String.valueOf(formatter.formatCellValue(cell));
                break;
            case Cell.CELL_TYPE_STRING:
                val = formatter.formatCellValue(cell);
                break;
        }
    .....
    .....
  }

- user6503911 · Answer 3

这个会起作用：

WebElement searchbox = driver.findElement(By.name("j_username"));
WebElement searchbox2 = driver.findElement(By.name("j_password"));         


try {

      FileInputStream file = new FileInputStream(new File("C:\\paulo.xls")); 
      HSSFWorkbook workbook = new HSSFWorkbook(file);

      HSSFSheet sheet = workbook.getSheetAt(0);

    for (int i=1; i <= sheet.getLastRowNum(); i++){

            HSSFCell j_username = sheet.getRow(i).getCell(0)
            HSSFCell j_password = sheet.getRow(i).getCell(0)

            //Setting the Cell type as String
            j_username.setCellType(j_username.CELL_TYPE_STRING)
            j_password.setCellType(j_password.CELL_TYPE_STRING)

            searchbox.sendKeys(j_username.toString());
            searchbox2.sendKeys(j_password.toString());


            searchbox.submit();       

            driver.manage().timeouts().implicitlyWait(10000, TimeUnit.MILLISECONDS);

    }

      workbook.close();
      file.close();

     } catch (FileNotFoundException fnfe) {
      fnfe.printStackTrace();
     } catch (IOException ioe) {
      ioe.printStackTrace();
     }

- Anany Sharma · Answer 4

public static List<SupplierSubmittedDataMapperModel> convertExcelToList(String NAME){

    List<SupplierSubmittedDataMapperModel> list = new ArrayList<>();
    try {
        FileInputStream file = new FileInputStream(new File(NAME));
        DataFormatter dataFormatter = new DataFormatter();
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(2);
        
        int rowNumber = 2;
          Iterator<Row> iterator = sheet.iterator();
          while(iterator.hasNext()) {
              Row row=iterator.next();
              
              
              Iterator<Cell> cells = row.iterator();
              int cid = 0;
              SupplierSubmittedDataMapperModel sp = new SupplierSubmittedDataMapperModel();
             
              while(cells.hasNext()) {
                  Cell cell = cells.next();
                  String cellValue = dataFormatter.formatCellValue(cell.getRow().getCell(cid));
                  
                  switch(cid) {
                  case 0:
                      sp.setSectorType(cellValue);
                      break;
                  case 1:
                      sp.setPlatformFamily(cellValue);
                      break;
                  case 2:
                      sp.setT1Supplier(cellValue);
                      break;
                  case 3:
                      sp.setT2Supplier(cellValue);
                      break;
                  case 4:
                      sp.setDistribotor(cellValue);
                      break;
                  case 5:
                      sp.setT2PartCodeAtT1(cellValue);
                      break;
                  case 6:
                      sp.setT2PartNumber(cellValue);
                      break;
                  case 7:
                      sp.setLoA(cellValue);
                      break;
                  case 8:
                      sp.setMultiSource(cellValue);
                      break;
                  case 9:
                      sp.setQtyUsage(cellValue);
                      break;
                  case 10:
                      sp.setDataType(cellValue);
                      break;
                  case 11:
                      sp.setWeek22(cellValue);
                      break;
                  case 12:
                      sp.setWeek23(cellValue);
                      break;
                  case 13:
                      sp.setWeek23(cellValue);
                      break;
                  case 14:
                      sp.setWeek24(cellValue);
                      break;
                  case 15:
                      sp.setWeek25(cellValue);
                      break;
                  case 16:
                      sp.setWeek26(cellValue);
                      break;
                  case 17:
                      sp.setWeek27(cellValue);
                      break;
                  case 18:
                      sp.setWeek28(cellValue);
                      break;
                  case 19:
                      sp.setWeek29(cellValue);
                      break;
                  case 20:
                      sp.setWeek30(cellValue);
                      break;
                  case 21:
                      sp.setWeek31(cellValue);
                      break;
                  case 22:
                      sp.setWeek32(cellValue);
                      break;
                  case 23:
                      sp.setWeek33(cellValue);
                      break;
                  case 24:
                      sp.setWeek34(cellValue);
                      break;
                  case 25:
                      sp.setWeek35(cellValue);
                      break;
                  case 26:
                      sp.setWeek36(cellValue);
                      break;
                  case 27:
                      sp.setWeek37(cellValue);
                      break;
                  case 28:
                      sp.setWeek38(cellValue);
                      break;
                  case 29:
                      sp.setWeek39(cellValue);
                      break;
                  case 30:
                      sp.setWeek40(cellValue);
                      break;
                  case 31:
                      sp.setWeek41(cellValue);
                      break;
                  case 32:
                      sp.setWeek42(cellValue);
                      break;
                  case 33:
                      sp.setWeek43(cellValue);
                      break;
                  case 34:
                      sp.setWeek44(cellValue);
                      break;
                  case 35:
                      sp.setWeek45(cellValue);
                      break;
                  case 36:
                      sp.setWeek46(cellValue);
                      break;
                  case 37:
                      sp.setWeek47(cellValue);
                      break;
                  case 38:
                      sp.setWeek48(cellValue);
                      break;
                  case 39:
                      sp.setWeek49(cellValue);
                      break;
                  case 40:
                      sp.setWeek50(cellValue);
                      break;
                  case 41:
                      sp.setWeek51(cellValue);
                      break;
                  case 42:
                      sp.setWeek52(cellValue);
                      break;
                  case 43:
                      sp.setYear2023(cellValue);
                      break;
                  case 44:
                      sp.setYear2024(cellValue);
                      break;
                  case 45:
                      sp.setYear2059(cellValue);
                      break;
                  default :
                      break;
                      
                  
                  }
                  
                  cid++;
                  
              }
              list.add(sp);
             System.out.println(sp);
               }
          
          workbook.close();
        }catch(Exception e) {
        
        e.printStackTrace();
        }   
    return list;

}