Java 无法从数字单元格“Poi”中获取文本值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30125465/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Cannot get a text value from a numeric cell “Poi”
提问by Paulo Roberto
I'm trying to consume data from a spreadsheet in Excel, but always of this error, already tried formatting the worksheet to text and number and still the error persists.
我正在尝试使用 Excel 中电子表格中的数据，但总是出现此错误，已经尝试将工作表格式化为文本和数字，但错误仍然存在。
I saw a person using it resolved cell.setCellType ( Cell.CELL_TYPE_STRING ) ;but I do not know where I fit this passage in my code.
我看到有人使用它解决了问题，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();
采纳答案by Manish
Formatter will work fine in this case.
在这种情况下，格式化程序将正常工作。
import org.apache.poi.ss.usermodel.DataFormatter;
FileInputStream fis = new FileInputStream(workbookName);
Workbook workbook = WorkbookFactory.create(fis);
Sheet sheet = workbook.getSheet(sheetName);
DataFormatter formatter = new DataFormatter();
String val = formatter.formatCellValue(sheet.getRow(row).getCell(col));
list.add(val); //Adding value to list
回答by Anatoly
Cell cell = sheet.getRow(i).getCell(0);
cell.setCellType ( Cell.CELL_TYPE_STRING );
String j_username = cell.getStringCellValue();
Cell cell = sheet.getRow(i).getCell(0);
cell.setCellType ( Cell.CELL_TYPE_STRING );
String j_username = cell.getStringCellValue();
UPDATE
更新
Ok, as have been said in comments, despite this works it isn't correct method of retrieving data from an Excel's cell.
好的，正如评论中所说，尽管这样做有效，但它不是从 Excel 单元格中检索数据的正确方法。
According to the manual here:
根据这里的手册：
If what you want to do is get a String value for your numeric cell, stop!. This is not the way to do it. Instead, for fetching the string value of a numeric or boolean or date cell, use DataFormatter instead.
如果您想要做的是为您的数字单元格获取字符串值，请停止！。这不是这样做的方法。相反，要获取数字或布尔值或日期单元格的字符串值，请改用 DataFormatter。
And according to the DataFormatter API
DataFormatter contains methods for formatting the value stored in an Cell. This can be useful for reports and GUI presentations when you need to display data exactly as it appears in Excel.Supported formats include currency, SSN, percentages, decimals, dates, phone numbers, zip codes, etc.
DataFormatter 包含格式化存储在单元格中的值的方法。当您需要完全按照 Excel 中显示的方式显示数据时，这对于报表和 GUI 演示非常有用。支持的格式包括货币、SSN、百分比、小数、日期、电话号码、邮政编码等。
So, right way to show numeric cell's value is as following:
因此，显示数字单元格值的正确方法如下：
DataFormatter formatter = new DataFormatter(); //creating formatter using the default locale
Cell cell = sheet.getRow(i).getCell(0);
String j_username = formatter.formatCellValue(cell); //Returns the formatted value of a cell as a String regardless of the cell type.
回答by Gagravarr
As explained in the Apache POI Javadocs, you should notuse cell.setCellType(Cell.CELL_TYPE_STRING)to get the string value of a numeric cell, as you'll loose all the formatting
正如Apache POI Javadocs 中所述，您不应该使用cell.setCellType(Cell.CELL_TYPE_STRING)获取数字单元格的字符串值，因为您会丢失所有格式
Instead, as the javadocs explain, you should use DataFormatter
相反，正如javadocs 解释的那样，您应该使用DataFormatter
What DataFormatter does is take the floating point value representing the cell is stored in the file, along with the formatting rules applied to it, and returns you a string that look like it the cell does in Excel.
DataFormatter 所做的是获取表示单元格存储在文件中的浮点值，以及应用于它的格式规则，并返回一个字符串，它看起来像单元格在 Excel 中所做的那样。
So, if you're after a String of the cell, looking much as you had it looking in Excel, just do:
所以，如果你在寻找一个单元格的字符串，看起来就像你在 Excel 中看到的那样，只需执行以下操作：
// Create a formatter, do this once
DataFormatter formatter = new DataFormatter(Locale.US);
.....
for (int i=1; i <= sheet.getLastRowNum(); i++) {
Row r = sheet.getRow(i);
if (r == null) {
// empty row, skip
} else {
String j_username = formatter.formatCellValue(row.getCell(0));
String j_password = formatter.formatCellValue(row.getCell(1));
// Use these
}
}
The formatter will return String cells as-is, and for Numeric cells will apply the formatting rules on the style to the number of the cell
格式化程序将按原样返回字符串单元格，对于数字单元格，会将样式上的格式规则应用于单元格的编号
回答by Harshil Kulkarni
Use that code it definitely works and I modified it.
使用该代码它肯定有效，我对其进行了修改。
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
//import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.*;
public class TestApp {
public static void main(String[] args) throws Exception {
try {
Class forName = Class.forName("com.mysql.jdbc.Driver");
Connection con = null;
con = DriverManager.getConnection("jdbc:mysql://localhost/tables", "root", "root");
con.setAutoCommit(false);
PreparedStatement pstm = null;
FileInputStream input = new FileInputStream("C:\Users\Desktop\a1.xls");
POIFSFileSystem fs = new POIFSFileSystem(input);
Workbook workbook;
workbook = WorkbookFactory.create(fs);
Sheet sheet = workbook.getSheetAt(0);
Row row;
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
row = (Row) sheet.getRow(i);
String name = row.getCell(0).getStringCellValue();
String add = row.getCell(1).getStringCellValue();
int contact = (int) row.getCell(2).getNumericCellValue();
String email = row.getCell(3).getStringCellValue();
String sql = "INSERT INTO employee (name, address, contactNo, email) VALUES('" + name + "','" + add + "'," + contact + ",'" + email + "')";
pstm = (PreparedStatement) con.prepareStatement(sql);
pstm.execute();
System.out.println("Import rows " + i);
}
con.commit();
pstm.close();
con.close();
input.close();
System.out.println("Success import excel to mysql table");
} catch (IOException e) {
}
}
}
回答by user6503911
This will work:
这将起作用：
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();
}
回答by Coder
Using the DataFormatterthis issue is resolved. Thanks to "Gagravarr" for the initial post.
使用DataFormatter此问题已解决。感谢“Gagravarr”的初始帖子。
DataFormatter formatter = new DataFormatter();
String empno = formatter.formatCellValue(cell0);
回答by ZoranS
CellType cell = row.getCell(j).getCellTypeEnum();
switch(cell) {
case NUMERIC:
intVal = row.getCell(j).getNumericCellValue();
System.out.print(intVal);
break;
case STRING:
stringVal = row.getCell(j).getStringCellValue();
System.out.print(stringVal);
break;
}
回答by Kiran Antony
use the code cell.setCellType(Cell.CELL_TYPE_STRING);
before reading the string value, Which can help you.
I am using POI version 3.17 Beta1 version,
sure the version compatibility also..
cell.setCellType(Cell.CELL_TYPE_STRING);
在读取字符串值之前使用代码，它可以帮助您。
我正在使用 POI 版本 3.17 Beta1 版本，确保版本兼容性也..
回答by yathin c
This is one of the other method to solve the Error: "Cannot get a text value from a numeric cell “Poi”"
这是解决错误的另一种方法：“无法从数字单元格“Poi”中获取文本值”
Go to the Excel Sheet. Drag and Select the Numerics which you are importing Data from the Excel sheet. Go to Format > Number > Then Select "Plain Text" Then Export as .xlsx. Now Try to Run the Script
转到 Excel 工作表。从 Excel 工作表中拖动并选择要导入数据的数字。转到格式 > 数字 > 然后选择“纯文本”，然后导出为 .xlsx。现在尝试运行脚本
Hope works Fine...!
希望工作正常......！
回答by virtuvious
If you are processing in rows with cellIterator....then this worked for me ....
如果您正在使用 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;
}
.....
.....
}
