Java POI : How to read Excel cell value and not the formula computing it?

📝 Blog Post: Java POI: How to Read Excel Cell Value and Not the Formula Computing It

👋 Hey there, fellow developers! Are you using Apache POI API to extract values from an Excel file in your Java project? That's awesome! However, you may have encountered a bit of a hiccup when dealing with cells that contain formulas. Instead of getting the actual value of the cell, you're stuck with the formula itself 😕. Don't worry, we've got your back! In this blog post, we'll dive into a common issue faced by developers when working with Apache POI and provide you with easy solutions to overcome it.

The Problem

Let's say you have an Excel file with cells containing formulas, and you want to retrieve the computed values. You might be tempted to use the getStringCellValue() method provided by Apache POI to get the value. Unfortunately, this will only give you the formula itself, not the calculated result. So what can you do to get the desired outcome?

One solution you might come across is using the evaluateFormulaCell() method, which seems promising. However, there's a catch. If you're using Excel's GETPIVOTDATA formula, this method won't work. You'll end up facing a frustrating NotImplementedException 😫.

<pre><code>Exception in thread "main" org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell Landscape!K11 at org.apache.poi.ss.formula.WorkbookEvaluator.addExceptionInfo(WorkbookEvaluator.java:321) at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288) at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:221) at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:320) at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCell(HSSFFormulaEvaluator.java:213) at fromExcelToJava.ExcelSheetReader.unAutreTest(ExcelSheetReader.java:193) at fromExcelToJava.ExcelSheetReader.main(ExcelSheetReader.java:224) Caused by: org.apache.poi.ss.formula.eval.NotImplementedException: GETPIVOTDATA at org.apache.poi.hssf.record.formula.functions.NotImplementedFunction.evaluate(NotImplementedFunction.java:42) </code></pre>

The Solution

Fear not! We have a workaround for you. Instead of using getStringCellValue() or evaluateFormulaCell(), we can utilize the DataFormatter class provided by Apache POI to get the cell value as it appears in Excel.

Here's a code snippet to get you started:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.IOException;

public class ExcelReader {

    public static void main(String[] args) {
        try (FileInputStream file = new FileInputStream("path/to/your/excel/file.xlsx");
             Workbook workbook = new XSSFWorkbook(file)) {

            Sheet sheet = workbook.getSheetAt(0);
            Row row = sheet.getRow(0);
            Cell cell = row.getCell(0);

            DataFormatter dataFormatter = new DataFormatter();
            String cellValue = dataFormatter.formatCellValue(cell);

            System.out.println("Cell Value: " + cellValue);

        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

By using the DataFormatter class, we can ensure that we get the actual value of the cell, regardless of whether it contains a formula or not. This solution works smoothly, even with formulas like GETPIVOTDATA. 🎉

Conclusion

Now you know how to overcome the hurdle of retrieving the computed value from an Excel cell when using Apache POI. By adopting the approach of using DataFormatter, you can easily obtain the desired values instead of getting stuck with the formula itself.

So go ahead and implement this solution in your project, read those Excel values like a boss, and take your Java skills to the next level! 💪

If you found this blog post helpful, we'd love to hear from you. Share your thoughts in the comments section below and let us know if you have any other Java or Apache POI related questions. Happy coding! 😄🚀