xssf 如何以字符串的形式获取任何东西 [英] xssf How to get anything as String

问题描述

我尝试使用 apache poi xssf 将 excel 文件解析为 XML.现在有一个单元格但不知道里面有什么，我只想从中取出一个字符串.但是当我使用

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

可能指代Hans"之类的文本.

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屋！