CODEWITHSUNDEEP
javaexcelapache-poi
avinandan012
avinandan012

Reputation: 153

Read currency & % using Java

I am trying to read an excel file using Apache Poi (v3.11 / XSSFWorkbook ). I am reading a particular sheet {e.x. sheet(0)} which is formed by different macros from around 15 sheets from the same file. The issue is I am able to read the non formatted String data but unable to read formatted data like currency or %.

I do not control the excel worksheet anyway. I will be reading the file from a network share path.

XSSFWorkbook myWorkBook = null
    XSSFSheet mySheet = null;
    Row rowHeader = null;
    Row rowData = null;
    FileInputStream fis = new FileInputStream(
            new File(
                    "inputfile.xlsx"));
    myWorkBook = new XSSFWorkbook(fis);
    mySheet = myWorkBook.getSheetAt(0);
    rowHeader = mySheet.getRow(0);
    rowData = mySheet.getRow(1);



        for (Cell s : rowHeader) {  
                System.out.print("|  "+s.getStringCellValue());
                System.out.println(" -->"+getCellValue(myWorkBook,rowData.getCell(s.getColumnIndex())));
    }

    myWorkBook.close();
}
//   $#,##0.00

private static String getCellValue(XSSFWorkbook wb, Cell cell)
{
   XSSFCell x = (XSSFCell)cell;
   return x.getRawValue();
}
}

I have a excel sheet like below

Name    Amount    Rate
Name1   $1,500  75%

If I read the above sheet using this code

Name --> Name1

Amount --> 1500 ( note the missing $ and , )

Rate --> 74.99999998 (number inacurate and missing % symbol)

Upvotes: 0

Views: 1876

Answers (2)

avinandan012
avinandan012

Reputation: 153

Thanks @Gagravarr

this below code solved the issue

private static String getCellValue(XSSFWorkbook wb, Cell cell)
{
  DataFormatter formatter = new DataFormatter();
  String formattedCellValue = formatter.formatCellValue(cell);
  return formattedCellValue; 
}

Upvotes: 1

Uma Kanth
Uma Kanth

Reputation: 5629

From the documentation,

getRawValue() would return the raw, underlying ooxml value.

Use getStringCellValue() instead as it return the value as a String and nothing is lost.

Upvotes: 0

Related Questions