Saving phone numbers in Microsoft Excel XSSF using Apache POI

Question

I have a phone number stored in Excel as a String, the Excel file was created successfully, and the data is error free, but each phone number has a “number saved as text” error next to it.

I read online that I have to use a special phone number format, which is included in excel or in a special format 000-000-0000. I can install them using excel, but not from my Java code.

---

I looked at the information about setCellType and DataFormat, but I assume CellType should be String, and I don’t see how I can use DataFormat for anything but dates.

I also looked at DataFormatter, but I don’t understand how to use it to store data. It appears to be read-only. http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.html

---

How can I do one of the following?

1) Mark the cell as "ignore error" to ignore the error "number saved as text"
2) Use the built-in Excel cell format "Special phone number"

For 1) a flag appears that is saved through the save and close file, I don’t know how to edit or view it using the POI. There is a message about this:

669 670 Excel FeatFormulaErr2, FeatRecord, , " "

: off - 46136-NoWarnings.xls 46136-WithWarnings.xls. !

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

, VBA cell.Errors.Item(xlNumberAsText).Ignore = True, , , POI

Answer 1

, # 2) Excel " "

Excel CELL_TYPE_NUMERIC, 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("[phone]"));
row.getCell(0).setCellStyle(phoneNumberStyle);

Answer 2

, , Excel. , - :

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

Answer 3

, -

@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();
    }
}

excel - String. , .

           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);
            }

excel , String Numeric.

String -

cell.getStringCellValue();

-

cell.getNumericCellValue();

Answer 4

try this job perfectly. it is a function for reading excel xlsx format and storing all data in an 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;
}