Excel的Apache POI:将单元类型设置为整个列的“text”

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

我需要生成一个.xls (Excel)文件，使用Java库Apache POI进行电子表格。

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.

该文件将包含一个列中的电话号码列表,格式为“0221 ....”或“+ 49221……”- Excel默认将它们解释为数字单元格。这很糟糕，因为前导0或+将被删除。

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.

为了解决这个问题，我可以使用cell.setCellType(Cell.CELL_TYPE_STRING)，它工作得很好，但只适用于我为其设置的特定单元格。

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

我如何将该设置应用于整个列(即，即使对于所有剩余的单元，用户将在哪里输入额外的电话号码)?

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

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

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

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

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.)
首先我假设，它应该是类似于表单的东西。setDefaultCellType(int colNum)。但我找不到这样的东西(也许我只是瞎了眼?)在库中有许多类似的方法用于应用文本样式，如“align center”等。
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...
然后我想:也许它只能用在名字或类似的东西上，但我还没弄明白它是怎么工作的……

4 个解决方案

#1

Will this help?

这个会有帮助吗?

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.

通过使用@符号创建数据格式，并在CellStyle对象中使用该格式，然后传递给setDefaultColumnStyle()方法，可以将列的默认数据类型设置为text。我还没有进一步体验过，但我怀疑有可能对其他样式对象进行类似的操作，将默认类型设置为数字，甚至是定制格式(如currency)。

#2

Here's some example code inspired by Vlad's answer:

以下是受弗拉德回答启发的一些示例代码:

DataFormat fmt = workbook.createDataFormat();
CellStyle textStyle = workbook.createCellStyle();
textStyle.setDataFormat(fmt.getFormat("@"));
worksheet.setDefaultColumnStyle(0, textStyle);

The above code sets the default style for the first column of worksheet to TEXT.

上面的代码为工作表的第一列的文本设置默认样式。

Thanks, Vlad!

谢谢,弗拉德!

#3

Vlad: This got most of the problem done. I found that all the empty rows were formatted the way I wanted them. However rows where I had initial zeros looked and worked fine; however if I had to change a pre-existing value with another value starting with zero the zeroes disappeared. So I recommend adding code to when you create the value in the cell to reset the the setCellStyle to text. here are some snippets: method public Cell createCell(Row r!, BBjNumber cell, BBjString value!)

弗拉德:这解决了大部分问题。我发现所有空行的格式都是我想要的。然而，初始零的行看起来和工作都很好;但是，如果我必须用另一个从0开始的值来改变先前存在的值，那么0就消失了。因此，我建议在单元格中创建值时添加代码，以将setCellStyle重置为text。这里有一些片段:方法公共单元创建器(行r!， BBjNumber单元格，BBjString值!)

c!=#this!.createCell(r!, cell)
c!.setCellValue(value!)
c!.setCellStyle(#text_format!)

methodret c!

methodret c !

#text_format! = #wb!.createCellStyle()
#text_format!.setDataFormat(#format!.getFormat("@"))

methodend

this may help some other sole searching for initial zeroes. Thank you for posting. Alex

这可能有助于其他一些单独搜索初始0。谢谢你的帖子。亚历克斯

#4

A better way to set it now using POI is to use the BuiltinFormats class.

使用POI设置它的更好方法是使用BuiltinFormats类。

Eg:

例如:

To convert the column type to numeric

将列类型转换为数字

 CellStyle numericStyle = workbook.createCellStyle(); 
 numericStyle.setDataFormat(BuiltinFormats.getBuiltinFormat(2)); // 2 For Number 
 worksheet.setDefaultColumnStyle(colId, numericStyle);

The complete list of BuiltinFormats formats can be seen, here

可以在这里看到构建格式的完整列表

#1