Java xssf 如何以字符串的形式获取任何东西
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21538481/
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
xssf How to get anything as String
提问by mxcd
I try to parse an excel file into XML using apache poi xssf. Now having a cell and not knowing what is in it I just want to get a String out of it. But when I use
我尝试使用 apache poi xssf 将 excel 文件解析为 XML。现在有一个单元格但不知道里面有什么，我只想从中取出一个字符串。但是当我使用
cell.getStringCellValue()
it throws an exception, what is not very suprising since it is documented this way. So I build my way around that by checking weather it is a numeric or a text cell. But what to do with formula cells. They may contain numbers like
它抛出一个异常，这并不奇怪，因为它是这样记录的。所以我通过检查天气是数字还是文本单元格来解决这个问题。但是如何处理公式单元格。它们可能包含数字，如
= A2 + B2
What gives me the sum (e.g. 4) or a reference to another text
是什么给了我总和（例如 4）或对其他文本的引用
= C2
what might refer to a text like "Hans".
what 可能指的是像“Hans”这样的文本。
How can I know what is really in my cell and how do I get a String out of it?
我如何知道我的单元格中的真实内容以及如何从中获取字符串？
采纳答案by Gagravarr
Excel stores some cells as strings, but most as numbers with special formatting rules applied to them. If you want to get the raw values, use a switch statement based on cell.getCellType()as some of the other answers have shown.
Excel 将某些单元格存储为字符串，但大多数单元格存储为应用了特殊格式规则的数字。如果您想获取原始值，请使用基于cell.getCellType()其他一些答案所示的 switch 语句。
However, if what you want is a string of the cell, showing the same as what Excel would show, based on applying all the formatting rules on the cell + cell types, then Apache POI has a class to do just that - DataFormatter
但是，如果您想要的是单元格的字符串，显示与 Excel 显示的相同，基于在单元格 + 单元格类型上应用所有格式规则，那么 Apache POI 有一个类可以做到这一点 - DataFormatter
All you need to do is something like:
您需要做的就是：
Workbook wb = WorkbookFactory.create(new File("myfile.xls"));
DataFormatter df = new DataFormatter();
Sheet s = wb.getSheetAt(0);
Row r1 = s.getRow(0);
Cell cA1 = r1.getCell(0);
String asItLooksInExcel = df.formatCellValue(cA1);
Doesn't matter what the cell type is, DataFormatter will format it as best it can for you, using the rules applied in Excel, and giving you back a nicely formatted string at the end.
无论单元格类型是什么，DataFormatter 都会使用 Excel 中应用的规则尽可能为您设置格式，并在最后给您一个格式良好的字符串。
回答by Kick
You can add check on CELL type as below :
您可以添加对 CELL 类型的检查，如下所示：
switch(cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
System.out.print(cell.getBooleanCellValue() + "\t\t");
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + "\t\t");
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + "\t\t");
break;
}
回答by SANN3
Try this one
试试这个
case Cell.CELL_TYPE_FORMULA:
switch (cell.getCachedFormulaResultType()) {
case Cell.CELL_TYPE_STRING:
System.out.println(cell.getRichStringCellValue().getString());
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
System.out.println(cell.getDateCellValue() + "");
} else {
System.out.println(cell.getNumericCellValue());
}
break;
}
break;
回答by toni07
The accepeted answer does not work with formula cells (in the result String you get the formula, not the result of the formula). Here is what worked for me in every case:
接受的答案不适用于公式单元格（在结果字符串中您得到的是公式，而不是公式的结果）。以下是在每种情况下对我有用的方法：
final XSSFWorkbook workbook = new XSSFWorkbook(file);
final DataFormatter dataFormatter = new DataFormatter();
final FormulaEvaluator objFormulaEvaluator = new XSSFFormulaEvaluator(workbook);
final Cell cell = ...;
objFormulaEvaluator.evaluate(cell);
final String cellValue = dataFormatter.formatCellValue(cell, objFormulaEvaluator);
