Storing Phone Numbers in Microsoft Excel XSSF using Apache POI

5
votes

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

4
javaexcelapache-poiopenxmlvba
do you want it numerical or in this format 000-000-0000? – Gaurav Varma
Ideally the standard excel Phone Number format: "(###) ###-####". Numeric doesn't seem to be the way to go here since a phone number is really a String (never algebraically manipulated) – Ron
agree. But making it as numeric makes sure that you are only going to write numbers in phone field and not alphabets – Gaurav Varma
I can test that in my Java code, I'd rather a letter show up in a mistyped number than have the whole number destroyed by changing a String Cell to a Numeric Cell – Ron
Wrt your 1) option: This seems to be requested regularly from Apache POI but isn't yet supported, afaict. See this answer for more information. – peterh

4 Answers

2
votes

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);
0
votes

I am not sure if you are doing a datatype conversion before writing to the excel. May be you can use something like this :

if(cellData.getCellType() == 1)
    cell.setCellValue((String)cellData.getData());
else if(cellData.getCellType() == 0)
    cell.setCellValue(((Integer)cellData.getData()).intValue());
-1
votes

Hey Try code given below-

@Test
public void writeToExcel() {
    //Blank workbook
    XSSFWorkbook workbook = new XSSFWorkbook(); 

    //Create a blank sheet
    XSSFSheet sheet = workbook.createSheet("Employee Data");

    //This data needs to be written (Object[])
    Map<String, Object[]> data = new TreeMap<String, Object[]>();
    data.put("1", new Object[] {"ID", "NAME", "PHONE"});
    data.put("2", new Object[] {1, "Amit", "9865321425"});
    data.put("3", new Object[] {2, "Lokesh","9562264578"});
    data.put("4", new Object[] {3, "John", "9458262145"});


    //Iterate over data and write to sheet
    Set<String> keyset = data.keySet();
    int rownum = 0;
    for (String key : keyset)
    {
        Row row = sheet.createRow(rownum++);
        Object [] objArr = data.get(key);
        int cellnum = 0;
        for (Object obj : objArr)
        {
           Cell cell = row.createCell(cellnum++);
           if(obj instanceof String)
                cell.setCellValue((String)obj);
            else if(obj instanceof Integer)
                cell.setCellValue((Integer)obj);
        }
    }
    try
    {
        //Write the workbook in file system
        FileOutputStream out = new FileOutputStream(new File("D:/writeTest.xlsx"));
        workbook.write(out);
        out.close();
        System.out.println("writeTest.xlsx written successfully on disk.");
    } 
    catch (Exception e) 
    {
        e.printStackTrace();
    }
}

By default cell type for excel is String. If you want to store phone number in numeric format then you have to set cell type to numeric.

           if(obj instanceof String)
                cell.setCellValue((String)obj);
            else if(obj instanceof Integer){
               // set cell format to numeric
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                cell.setCellValue((Integer)obj);
            }

While reading excel file remember one thing that you are reading String type cell data or Numeric type cell data.

To read String type cell data use code as-

cell.getStringCellValue();

and to read numeric cell type data--

cell.getNumericCellValue();
-1
votes

try this its work perfectly. this is function for read excel xlsx format and store all data in array list..

     public ArrayList Xreadexcel(String file) {        
    boolean f = false;
    ArrayList arraycontainer = new ArrayList();
    try {
        FileInputStream myInput = new FileInputStream(file);
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(0);
        int rowStart = sheet.getFirstRowNum();
        int rowEnd = sheet.getLastRowNum() + 1;
        int count = workbook.getNumberOfSheets();
        if (count > 1) {
            System.out.println("Only one Sheet Allowed");
        } else {
            for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
                Row row = sheet.getRow(rowNum);
                int lastColumn = row.getLastCellNum();
                ArrayList arraylist = new ArrayList();
                int cn = 0;
                for (cn = 0; cn < lastColumn + 1; cn++) {
                    Cell cell = row.getCell(cn, Row.RETURN_NULL_AND_BLANK);
                    if ((cell == null) || (cell.equals("")) || (cell.getCellType() == cell.CELL_TYPE_BLANK)) {
                        arraylist.add("");
                    } else {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        arraylist.add(cell);
                    }
                }
                arraycontainer.add(arraylist);
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return arraycontainer;
}