How to get percentage value of type from excel sheet using poi

Question

I am trying to read excel value sheets using Apache POI. One of my sheets contains the percentage type as the cell type. When I read this cell using the POI using cell.getNumbericValue (), it returns me a double value. If the cell contains 11.24%, it returns me 0.1124. My problem is that I want to read the% symbol also from the cell, that is, I want to read the exact data, since it is in the cell as 11.24%. So, I tried this with cell.toString (), even then it returns the same value to me as above. Can anyone suggest me how to get rid of this problem. thanks in advance Nandu

Answer 1

You can determine if a cell is formatted as a percentage by testing the cell data format string as follows:

if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { if (cell.getCellStyle().getDataFormatString().contains("%")) { // Detect Percent Values Double value = cell.getNumericCellValue() * 100; System.out.println("Percent value found = " + value.toString() +"%"); } else { Double value = cell.getNumericCellValue(); System.out.println("Non percent value found = " + value.toString()); } } 

This should allow you to distinguish between percentage formatted values ​​and regular numeric values.

Answer 2

Try this String typeCell = cell.getCellStyle (). GetDataFormatString (); He gives

• Common value for unreinforced cells,
• "0%" for percentage formats
• dd / mm / yy for date, locale dependent

I manage to analyze all types in cells (Numbers, Percentage, Date, Text, Currency). I just need to check what value for the cell type it will return to me. Since I also have big problems with HSSFCell.CELL_TYPE_STRING or HSSFCell.CELL_TYPE_NUMERIC:

Answer 3

11.24% is 0.1124, so what is stored in the file and what you get when you ask for a numerical value

The POI provides a utility for formatting numeric strings based on format rules that apply to a cell that has a DataFormatter . If you use this to format a cell, you will return a string with the contents of the cell in many ways, as shown in Excel.