Apache POI for Excel: Setting the cell type to "text" for an entire column

Question

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

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.

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

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

But I can't find the correct method for 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.)
• 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...

Answer 1

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!

Answer 2

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

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