适用于Excel的Apache POI:将单元格类型设置为“文本"，然后将其设置为“文本".对于整个专栏 [英] Apache POI for Excel: Setting the cell type to "text" for an entire column

问题描述

我需要使用Java库用于电子表格的Apache POI生成.xls(Excel)文件.

I need to generate an .xls (Excel) file, using the Java library Apache POI for spreadsheets.

文件将在A列中包含电话号码列表，格式为"0221 ...."或"+49221 ..."-因此，默认情况下，Excel将其解释为数字单元格.这很不好，因为前导0或+会被修剪.

The file will contain a list of phone numbers in column A, formatted as "0221...." or "+49221..." - so Excel by default interprets them as numeric cells. This is bad, because the leading 0 or + will get trimmed.

要解决此问题，我可以使用cell.setCellType(Cell.CELL_TYPE_STRING)，它工作正常，但仅适用于我为此设置的特定单元格.

To solve the problem, I can use cell.setCellType(Cell.CELL_TYPE_STRING), which works fine, but only for the specific cells I set this for.

如何将此设置应用于整列(即，甚至对于其余所有单元格，用户都将在其中输入其他电话号码)?

How can I apply this setting for the entire column (i.e. even for all the remaining cells, where the user will enter additional phone numbers)?

在Excel中，这是可能的:选择整个列，然后应用单元格类型(此设置在保存/加载文件后仍然有效).

In Excel, this is possible: Selecting the entire column, and apply the cell type (the setting survives saving/loading the file.)

但是我找不到正确的POI方法.

But I can't find the correct method for POI.

• 首先我想，它应该类似于sheet.setDefaultCellType(int colNum).但是我找不到这样的东西(可能我只是盲目的?有很多
• 然后我想:也许只能将其应用于 NamedRange 或类似的东西，但是我还没有弄清楚它是如何工作的...

• First I assumed, it should be something like sheet.setDefaultCellType(int colNum). But I can't find anything like this (probably I'm just blind? There are lots of similar methods in the library for applying text styles like "align center" etc.)
• Then I thought: Maybe it can only be applied to a NamedRange or something similar, but I haven't managed to work out how this works...

推荐答案

将这有帮助吗?

通过使用@符号创建数据格式并在CellStyle中使用它 然后传递给setDefaultColumnStyle()方法的对象，它是 在这种情况下，可以将列的默认数据类型设置为text. 我没有进一步的实验，但怀疑是否有可能做 与其他样式对象类似的东西可以将默认类型设置为 数字甚至是自定义格式(例如货币).

By creating a data format with the @ symbol and using that in a CellStyle object that is then passed to the setDefaultColumnStyle() method, it was possible to set the default data type of the column to, in this case, text. I have not experiemented further but do suspect it would be possible to do something similar with other style objects to set the default type to numeric or even a customised format such as currency.

这篇关于适用于Excel的Apache POI:将单元格类型设置为“文本"，然后将其设置为“文本".对于整个专栏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！