Read currency & % using Java

Question

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)

the macro generates $ & %. I can see these values if I open the sheet with Ms-excel. And yes I do need $ & %. Objective is to get the cell values exactly seen in the cell. — avinandan012
– avinandan012, Commented Oct 26, 2015 at 15:21
no, what i mean. we read those values. you need those values only or with symbol — Kumaresan Perumal
– Kumaresan Perumal, Commented Oct 26, 2015 at 15:23
What happens if you use DataFormatter to render the cell to a String? — Gagravarr
– Gagravarr, Commented Oct 26, 2015 at 17:47

avinandan012 · Accepted Answer · 2015-10-27 04:16:39Z

1

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

answered Oct 27, 2015 at 4:16

avinandan012

1531 gold badge1 silver badge14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Uma Kanth · Accepted Answer · 2015-10-26 15:36:25Z

0

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.

answered Oct 26, 2015 at 15:36

Uma Kanth

5,6292 gold badges22 silver badges43 bronze badges

1 Comment

avinandan012 Over a year ago

As I stated getStringCellValue() works if the cell is of Type 'Text'. For numbers(Type : Currency / Number /Percentage) you will get "java.lang.IllegalStateException: Cannot get a text value from a numeric cell". Even if you write a logic using getCellType() to avoid this you loose the formatting information

Collectives™ on Stack Overflow

Read currency & % using Java

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related