Reputation: 153
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
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
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