Read data from Excel File
To read Excel formula with POI, firstly create a new Excel file where there are
numbers in column A and B, and a formula =A1*10+B1 in column C.
Then run the following commands:
$ wget http://mirrors.tuna.tsinghua.edu.cn/apache/poi/release/bin/poi-bin-3.17-20170915.tar.gz
$ tar zxf poi-bin-3.17-20170915.tar.gz
$ wget http://central.maven.org/maven2/org/apache/xmlbeans/xmlbeans/2.6.0/xmlbeans-2.6.0.jar
$ ls
formularTest.xlsx poi-3.17 xmlbeans-2.6.0.jar
$ ls poi-3.17
docs lib LICENSE NOTICE ooxml-lib poi-3.17.jar poi-examples-3.17.jar poi-excelant-3.17.jar poi-ooxml-3.17.jar poi-ooxml-schemas-3.17.jar poi-scratchpad-3.17.jar
$ export CLASSPATH=./*:poi-3.17/*:poi-3.17/lib/*
$ groovysh
import org.apache.poi.xssf.usermodel.XSSFWorkbook
excelFile = new FileInputStream(new File("formularTest.xlsx"))
workbook = new XSSFWorkbook(excelFile)
dataSheet = workbook.getSheetAt(0)
firRow = dataSheet.getRow(0)
formulaCell = firRow.getCell(2)
formulaCell.rawValue // 21
formulaCell.getCellTypeEnum() // FORMULA
formulaCell.getCellFormula() // A1*10+B1
Ref:
-
https://stackoverflow.com/questions/7608511/java-poi-how-to-read-excel-cell-value-and-not-the-formula-computing-it
-
https://stackoverflow.com/questions/23080945/java-lang-classnotfoundexception-org-apache-xmlbeans-xmlexception