Importing Excel data in Java 3

Again, it's Apache POI, a continuation of the last time.

Contents of this time

Last time I wrote how to get each cell type, ** CellType.FORMULA ** Method at the time of getCellFormula() Then, I found that the formula itself in the cell can be obtained as a String type. So what if you want the result of that formula? What to do when you say.

What to do specifically

I searched for something that I couldn't do in various ways and referred to this article.

** Formula Evaluator interface ** There was something called.

This evaluates the formula cell. Make it a value. In short, it helps a lot to get the calculated value.

Also, ** CreationHelper interface ** It seems to use also. This is the object that handles the instantiation of the concrete class. I think it's the one that creates various POI objects.

Prepare these two,

  1. Prepare CreationHelper from Workbook object (getCreationHelper ())
  2. Create FormulaEvaluator through CreationHelper (createFormulaEvaluator ())
  3. Calculate the target cell using FormulaEvaluator.evaluateInCell ()

It is a flow like.

FormulaEvaluator.evaluateInCell(cell) A method that puts a cell in the argument, evaluates and calculates the formula of the cell included in the argument, and returns the result

If you use this, you can get the calculated value even with a mathematical formula.

Implementation result

    private static String getCellStringValue(Cell cell) {
        String retStr;

        CellType cellType = cell.getCellType();
        switch (cellType) {
        case STRING:
            retStr = cell.getStringCellValue();
        case NUMERIC:
            retStr = String.valueOf(cell.getNumericCellValue());
        case BOOLEAN:
            retStr = String.valueOf(cell.getBooleanCellValue());
        case FORMULA:

            //Get function result
            Workbook wb = cell.getSheet().getWorkbook();
            CreationHelper ch = wb.getCreationHelper();
            FormulaEvaluator fe = ch.createFormulaEvaluator();
            retStr = String.valueOf(getCellStringValue(fe.evaluateInCell(cell)));

        case ERROR:
            retStr = String.valueOf(cell.getErrorCellValue());
            retStr = "";
        return retStr;




Recommended Posts

Importing Excel data in Java 2
Importing Excel data in Java 3
Import Excel data in Java
Save Java PDF in Excel
Java addition excel data validation
Create variable length binary data in Java
Partization in Java
Changes in Java 11
Rock-paper-scissors in Java
Java creates a pie chart in Excel
Delete blank rows / columns in Java Excel
Pi in Java
FizzBuzz in Java
Add, read, and delete Excel comments in Java
Organized memo in the head (Java --Data type)
[java] sort in list
Read JSON in Java
Interpreter implementation in Java
Make Blackjack in Java
Rock-paper-scissors app in Java
NVL-ish guy in Java
Combine arrays in Java
"Hello World" in Java
Callable Interface in Java
Comments in Java source
[Java] Data type ①-Basic type
Azure functions in java
Format XML in Java
Print Java Excel Worksheet
Boyer-Moore implementation in Java
Hello World in Java
Use OpenCV in Java
webApi memorandum in java
Type determination in Java
Ping commands in Java
Various threads in java
Heapsort implementation (in java)
Zabbix API in Java
ASCII art in Java
Compare Lists in Java
Express failure in Java
Create JSON in Java
Date manipulation in Java 8
What's new in Java 8
Use PreparedStatement in Java
What's new in Java 9,10,11
Parallel execution in Java
Initializing HashMap in Java
Java basic data types
Vectorize and image MNIST handwritten digit image data in Java
How to create a data URI (base64) in Java
Try using RocksDB in Java
Read binary files in Java 1
Avoid Yubaba's error in Java
Java learning memo (data type)
Get EXIF information in Java
[Neta] Sleep Sort in Java
Get weather forecasts from Watson Weather Company Data in simple Java
Edit ini in Java: ini4j
Java history in this world