xssf如何获取任何字符串 [英] xssf How to get anything as String

问题描述

我尝试使用apache poi xssf将excel文件解析为XML。
现在有一个单元格而不知道其中的内容我只想从中获取一个String。
但是当我使用

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

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

什么给了我总和（例如4）或对另一个文本的引用

What gives me the sum (e.g. 4) or a reference to another text

= 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?

推荐答案

Excel将一些单元格存储为字符串，但大多数都是应用了特殊格式规则的数字。如果要获取原始值，请使用基于 cell.getCellType（）的switch语句，如其他一些答案所示。

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将显示的相同，基于对单元格+单元格类型应用所有格式规则，那么Apache POI有一个类做到这一点 - DataFormatter

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

您需要做的就是：

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

无论细胞类型是什么，DataFormatter都会尽可能地为您格式化，使用Excel中应用的规则，并在最后返回一个格式良好的字符串。

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.

这篇关于xssf如何获取任何字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！