xssf如何获得任何字符串形式

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".

什么可以指像“汉斯”这样的文本?

How can I know what is really in my cell and how do I get a String out of it?

我怎么知道我的单元格里到底是什么?我怎么才能从中得到一条线?

4 个解决方案

#1

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中应用的规则尽可能地格式化它，并在末尾返回一个格式良好的字符串。

#2

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);

#3

You can add check on CELL type as below :

您可以添加以下单元格类型检查:

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;
            }

#4

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;

#1