4

I wanted to get the value of a Numeric cell as a simple string.

Suppose there the type of cell is numeric with value 90%. Now I cannot use cell.getStringCellValue() as it will throw exception. I also cannot use cell.getNumericCellValue() as it will return me .9 and not 90%.

I want to store in db which is of type varchar2, so I want the value in string only.

I cannot change the cell type in xls as its the end user job, I have to handle this in code itself.

Also formatter does't work well as there could be different cell types in the xls...dd:mm,dd:mm:ss,formula etc.

All I want is that whatever the cell type is I need to get its value as simple String.

Gagravarr
  • 47,320
  • 10
  • 111
  • 156
Dhananjay
  • 734
  • 1
  • 9
  • 14

3 Answers3

4

You can force the value to be returned as a String using the methods below

HSSFDataFormatter hdf = new HSSFDataFormatter();
System.out.println (hdf.formatCellValue(mycell));

will return "90%"

The API for this method is at http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.html#formatCellValue%28org.apache.poi.ss.usermodel.Cell%29

This works directly even with an HSSFCell

it worked for me even when my Cell is an HSSFCell

i've also tried this cast - which works.

HSSFCell cell1 = (HSSFCell) row1.getCell(2);

HSSFDataFormatter hdf = new HSSFDataFormatter();
System.out.println ("formatted "+ hdf.formatCellValue(cell1));
JoseK
  • 31,141
  • 14
  • 104
  • 131
  • HSSFDataFormatter hdf = new HSSFDataFormatter(); System.out.println (hdf.formatCellValue(mycell)); In my case the "myCell" argument is of type HSSFCell but hdf.formatCellValue(mycell) take "mycell" as a type of Cell. I tried typecasting it to Cell but it throws an exception:java.lang.ClassCastException.....actually my code is written using HSSF API... – Dhananjay Sep 29 '10 at 17:17
  • I tried with HSSF cell ...It works but when i run as a standalone java class with java 1.6....I have my application on portal 6.1 where i have to use java 1.5...It throws exception there.. – Dhananjay Oct 01 '10 at 09:19
  • @user371238: I've tried this with standalone 1.5 and 1.6. do you have the latest poi libraries in server web-inf/lib? – JoseK Oct 01 '10 at 09:33
  • tried 3.6...still getting the same error=java.lang.IncompatibleClassChangeError – Dhananjay Oct 01 '10 at 12:09
  • can you post the whole stacktrace as an Edit to your original question? – JoseK Oct 01 '10 at 12:23
  • i can run on standalone java class with 1.5 also ..but its not running on portal server 6.1 which has 1.5...with 3.6 jar – Dhananjay Oct 01 '10 at 13:07
  • Does not work. `1578390016427` is returns something like : `1.57839E+12` – Pranjal Choladhara Jan 31 '21 at 09:27
  • @PranjalCholadhara: your query is resolved at https://stackoverflow.com/questions/46431561/apache-poi-dataformatter-returns-scientific-notation – JoseK Jan 31 '21 at 17:26
1

Try 

cell.getRichStringCellValue ().getString();

Have a look at this example
Here is Doc

jmj
  • 237,923
  • 42
  • 401
  • 438
  • cell.getRichStringCellValue ().getString(); It Didn,t work...Error:-lang.NoSuchMethodError I am using poi 3.6 jar – Dhananjay Sep 29 '10 at 10:31
  • @user371238 Have you checked the example's code , Try with older version of jar. document shows the method and it is there. – jmj Sep 29 '10 at 10:32
0

The following code is using current apache poi versions of 2021. Now DataFormatter can be used for XSSF (Office Open XML *.xlsx) as well as for HSSF (BIFF *.xls) formats. It should be used together with FormulaEvaluator to get values from formula cells too.

import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;

class ReadExcel {

 public static void main(String[] args) throws Exception {

  Workbook workbook = WorkbookFactory.create(new FileInputStream("Excel.xlsx"));
  //Workbook workbook = WorkbookFactory.create(new FileInputStream("Excel.xls"));

  DataFormatter dataFormatter = new DataFormatter(java.util.Locale.US);
  FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();

  String cellValue = "";

  for (Sheet sheet: workbook) {
   System.out.println(sheet.getSheetName());
   for (Row row : sheet) {
    for (Cell cell : row) {

     cellValue = dataFormatter.formatCellValue(cell, formulaEvaluator);
     System.out.println(cell.getAddress() + ":" + cellValue);
     // do something with cellValue

    }
   }
  }
  workbook.close();
 }
}
Axel Richter
  • 56,077
  • 6
  • 60
  • 87