Storing Phone Numbers in Microsoft Excel XSSF using Apache POI

Question

I have a phone number stored in Excel as a String, the Excel file is created successfully and the data is free from errors but every phone number has a "number stored as text" error next to it.

I have read online that I should use the special Phone Number format included with excel or a custom 000-000-0000 format. I can set these using the excel program but not from my Java code.

---

I have looked around for information on setCellType and DataFormat but I assume CellType must be String and I don't see how I can use DataFormat for anything but dates.

I have also had a look at DataFormatter but I do not understand how to use it for storing data. It looks like it's only meant to help with reading data. http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.html

---

How can I do either of the following?

1) Flag a cell as 'ignore error', to ignore the "number stored as Text" error
2) Make use of the built in Excel cell format "Special > Phone Number"

For 1) it appears there is a flag that persists through saving and closing file, I don't know how to edit or view it with POI. There is a post about it:

Pages 669 and 670 of the excel docs cover FeatFormulaErr2, which is held in FeatRecord shared features, which in theory allows you to store the fact that "number as text" should be ignored for a cell range

We've also got two test files, one with the warnings on, and one with them off - 46136-NoWarnings.xls and 46136-WithWarnings.xls . I didn't create them however!

Nick http://mail-archives.apache.org/mod_mbox/poi-user/201003.mbox/%[email protected]%3E

It appears this can be done in VBA with cell.Errors.Item(xlNumberAsText).Ignore = True but there seems to be no equivalent for POI

Answer 1

I have figured out how to achieve #2) Make use of the built in Excel cell format "Special > Phone Number"

Phone Numbers in Excel are stored as CELL_TYPE_NUMERIC, not CELL_TYPE_STRING

try {
    row.createCell(0).setCellValue(Long.parseLong(aNumericOnlyPhoneNumberString));
} catch (NumberFormatException e) {
    row.createCell(0);
}
CellStyle phoneNumberStyle = wb.createCellStyle();
phoneNumberStyle.setDataFormat(wb.createDataFormat().getFormat("(000) 000-0000"));
row.getCell(0).setCellStyle(phoneNumberStyle);