Read US Zip Code from excel using apache poi

Question

I am using the following code to read zip code from my excel file which has value like this:

06785

The excel file cell is formatted as special >> zipcode

File src = new File("C:\\Users\myxl.xlsx");

FileInputStream fis = new FileInputStream(src);
XSSFWorkbook wb = new XSSFWorkbook(fis);
XSSFSheet sh1 = wb.getSheetAt(0); 

System.out.println(sh1.getRow(1).getCell(19).getRichStringCellValue());
//Output = Can not get a text value form a numeric cell

wb.close();
fis.close();

I have tried the followings:

System.out.println(sh1.getRow(1).getCell(19).getStringCellValue());
//Output = Can not get a text value form a numeric cell

System.out.println(sh1.getRow(1).getCell(19).toString());
//Output = 6785.0

System.out.println(sh1.getRow(1).getCell(19).getNumericCellValue());
//Output = 6785.0

System.out.println(sh1.getRow(1).getCell(19).getRawValue());
//Output = 6785

I can not get the value exactly as it appears within excel, which is 06785, it always prints without the 0 or with .0

Would anyone be able to help with this as how I would get an output of the zip code as it exactly appears in excel - 06785?

This might help http://stackoverflow.com/questions/1072561/how-can-i-read-numeric-strings-in-excel-cells-as-string-not-numbers-with-apach — Pragnani, Mar 22 '16 at 19:10
You would need to set the cell type to Cell.CELL_TYPE_STRING before reading the cell value. E.g.: cell.setCellType(Cell.CELL_TYPE_STRING) — Rg90, Mar 22 '16 at 19:12
Take a numeric value from spreadsheet and add the neccessary leading zeros with a `java.util.Formatter`. — blafasel, Mar 22 '16 at 19:13
@Rg90 tried that, `cell.setCellType(Cell.CELL_TYPE_STRING); System.out.println(sh1.getRow(1).getCell(19).getStringCellValue());` The output is still 6785 — Ami, Mar 22 '16 at 19:18
@blafasel would you be able to elaborate a little more please? how would i add that and where please? — Ami, Mar 22 '16 at 19:19

score 0 · Answer 1 · answered Mar 22 '16 at 20:39

0

This seems to solve the issue for now (using DecimalFormat).

If anyone else have better answers please provide your valuable input here.

Thanks so much for all the inputs so far.

String myint = new java.text.DecimalFormat("00000").format(sh1.getRow(1).getCell(19).getNumericCellValue());


    //OR

DecimalFormat df= new DecimalFormat("00000");
String formatted = df.format(sh1.getRow(1).getCell(19).getNumericCellValue());



System.out.println(myint);

System.out.println("******");

System.out.println(formatted);

answered Mar 22 '16 at 20:39

Ami

61
9

Please have a look at http://stackoverflow.com/questions/36033189/java-apache-poi-custom-format/36040687#36040687. There I have shown how to get numeric values from Excel with the number format using `CellGeneralFormatter` or `CellNumberFormatter`. – Axel Richter Mar 23 '16 at 06:21

score 0 · Answer 2 · edited Sep 08 '16 at 19:00

First we need to take the value as string from cell as below..

cell.setCellType(Cell.CELL_TYPE_STRING);
cellValueAsString = zeroPaddingToZipCode(cell.getStringCellValue(););

Then we need to have similar function as below to have padded or non padded zip codes.

private String zeroPaddingToZipCode(String cellValueAsString) {
    if(null != cellValueAsString){
        if(!cellValueAsString.contains("-")){
            if(cellValueAsString.length() == 4 ){
                cellValueAsString = "0"+cellValueAsString;
            }
        } else {
            String[] zipCodes = cellValueAsString.split("-");
            if(zipCodes.length == 2 ) {
                if(zipCodes[0].length() == 4 || zipCodes[0].length() == 2 ) {
                    cellValueAsString = "0"+zipCodes[0];
                } else {
                    cellValueAsString = zipCodes[0];
                }
                cellValueAsString += "-";
                if(zipCodes[1].length() == 4 || zipCodes[1].length() == 2 ) {
                    cellValueAsString += "0"+zipCodes[1];
                } else {
                    cellValueAsString += zipCodes[1];
                }

            }
        }
    }
    return cellValueAsString;
}

Check if it is working

Read US Zip Code from excel using apache poi

2 Answers2