How to get cell value from the cell which consists of formula in excel xls file

Question

I am writing java code to read excel file and to create an object from that but one column consists of formula when I read cell formula is coming in value field instead of value

I tried to get from cell index
I tried to get value from iterator(hasnext()) method :

like:

public static void CheckDuplicate(File file) throws Exception {
        Set newReordsList = new LinkedHashSet();
        LinkedHashMap<String, DataCleansingObject> lhm = new LinkedHashMap<String, DataCleansingObject>();
        FileInputStream fis = new FileInputStream(file);
        HSSFWorkbook workbook = new HSSFWorkbook(fis);
        HSSFSheet spreadsheet = workbook.getSheetAt(0);
        HSSFRow row;
        Iterator< Row> rowIterator = spreadsheet.iterator();
        while (rowIterator.hasNext()) {
            row = (HSSFRow) rowIterator.next();
            if(row.getRowNum() == 0) {
                continue;
            }
            DataCleansingObject dataObj = new DataCleansingObject();

            Iterator< Cell> cellIterator = row.cellIterator();

             String key = "";
            if (cellIterator.hasNext()) {

                dataObj.setDocType(row.getCell(1).toString());
                dataObj.setDocNo(row.getCell(2).toString());
                dataObj.setDocVer(Long.parseLong(row.getCell(3).toString()));
                dataObj.setDocPart(row.getCell(4).toString());
                dataObj.setDocLang(row.getCell(5).toString());
                dataObj.setDocRev(row.getCell(6).toString());
                dataObj.setDocStatus(row.getCell(7).toString());
                dataObj.setDocSD(row.getCell(8).toString());
                dataObj.setDocLD(row.getCell(9).toString());
                dataObj.setDocChangeNumber(row.getCell(10).toString());

                key = row.getCell(1).toString() + "_" + row.getCell(2).toString() + "_" + row.getCell(3).toString() + "_" + row.getCell(4).toString();
            }

            lhm.put(key, dataObj);

        }
        fis.close();
        copyInExcel(newReordsList);

    }

When ever I call below line

Long.parseLong(row.getCell(3).toString())

I need to get long value not formula in that cell

Possible duplicate of [Get Cell Value from Excel Sheet with Apache Poi](https://stackoverflow.com/questions/5578535/get-cell-value-from-excel-sheet-with-apache-poi) — jannis, May 27 '19 at 10:44
[Java POI : How to read Excel cell value and not the formula computing it?](https://stackoverflow.com/questions/7608511/java-poi-how-to-read-excel-cell-value-and-not-the-formula-computing-it) — jannis, May 27 '19 at 10:45
@jannis i tried with the answers given in the above poi but its not working in my condition — vishnukumar hosalli, May 27 '19 at 11:01
What is not working? Do you get errors? Exceptions? Are you expecting something else? Please update the question. — jannis, May 27 '19 at 11:13
Try `cell.getNumericCellValue()` which is in the docs referenced by @jannis — PJ Fanning, May 27 '19 at 21:12
@jannis i am getting this error org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell Sheet1!D2 — vishnukumar hosalli, May 28 '19 at 03:36
looks like the function is not supported for your poi version. what is the function in the cell you are getting this error for? — jannis, May 28 '19 at 04:58

How to get cell value from the cell which consists of formula in excel xls file

0 Answers0