Java POI: Wie liest man den Excel-Zellenwert und nicht die Formel, die ihn berechnet?

Ich verwende die Apache POI-API, um Werte aus einer Excel-Datei abzurufen. Alles funktioniert hervorragend, außer mit Zellen, die Formeln enthalten. Tatsächlich ist die cell.getStringCellValue() gibt die in der Zelle verwendete Formel und nicht den Wert der Zelle zurück.

Ich habe versucht zu verwenden evaluateFormulaCell() Methode, aber es funktioniert nicht, weil ich die Excel-Formel GETPIVOTDATA verwende und diese Formel nicht in der API implementiert ist:

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)

Für Formelzellen speichert Excel zwei Dinge. Einer ist die Formel selbst, der andere ist der “gecachte” Wert (der letzte Wert, als der die Forumla ausgewertet wurde)

Wenn Sie den letzten zwischengespeicherten Wert abrufen möchten (der möglicherweise nicht mehr korrekt ist, aber solange Excel die Datei gespeichert hat und Sie sie nicht geändert haben, sollte dies der Fall sein), möchten Sie Folgendes:

 for(Cell cell : row) {
     if(cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        System.out.println("Formula is " + cell.getCellFormula());
        switch(cell.getCachedFormulaResultType()) {
            case Cell.CELL_TYPE_NUMERIC:
                System.out.println("Last evaluated as: " + cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                System.out.println("Last evaluated as \"" + cell.getRichStringCellValue() + "\"");
                break;
        }
     }
 }

Früher gepostete Lösungen haben bei mir nicht funktioniert. cell.getRawValue() hat dieselbe Formel wie in der Zelle angegeben zurückgegeben. Folgende Funktion hat bei mir funktioniert:

public void readFormula() throws IOException {
    FileInputStream fis = new FileInputStream("Path of your file");
    Workbook wb = new XSSFWorkbook(fis);
    Sheet sheet = wb.getSheetAt(0);
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

    CellReference cellReference = new CellReference("C2"); // pass the cell which contains the formula
    Row row = sheet.getRow(cellReference.getRow());
    Cell cell = row.getCell(cellReference.getCol());

    CellValue cellValue = evaluator.evaluate(cell);

    switch (cellValue.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            System.out.println(cellValue.getBooleanValue());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            System.out.println(cellValue.getNumberValue());
            break;
        case Cell.CELL_TYPE_STRING:
            System.out.println(cellValue.getStringValue());
            break;
        case Cell.CELL_TYPE_BLANK:
            break;
        case Cell.CELL_TYPE_ERROR:
            break;

        // CELL_TYPE_FORMULA will never happen
        case Cell.CELL_TYPE_FORMULA:
            break;
    }

}

Wenn es darum geht, Werte aus Excel-Tabellen zu lesen und sie dann als Strings zu haben, um sie beispielsweise irgendwo darzustellen oder in Textdateiformaten zu verwenden, dann verwenden Sie DataFormatter wird das beste sein.

DataFormatter ist in der Lage, aus jedem Zellenwert eine Zeichenfolge zu erhalten, unabhängig davon, ob der Zellenwert selbst eine Zeichenfolge, ein boolescher Wert, eine Zahl, ein Fehler oder ein Datum ist. Diese Zeichenfolge sieht dann genauso aus, wie Excel sie in den Zellen seiner GUI anzeigen wird.

Einziges Problem sind Formelzellen. Bis Apache poi 5.1.0 wird ein FormulaEvaluator benötigt, um die Formeln mit DataFormatter auszuwerten. Dies schlägt fehl, wenn apache poi kann die Formel nicht auswerten. Ab 5.2.0 auf der DataFormatter kann so eingestellt werden, dass zwischengespeicherte Werte für Formelzellen verwendet werden. Dann wird keine Formelauswertung benötigt, wenn Excel die Formeln vorher ausgewertet hat.

Vollständiges Beispiel:

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("./ExcelExample.xlsx"));
  
  // up to apache poi 5.1.0 a FormulaEvaluator is needed to evaluate the formulas while using DataFormatter
  FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();  
  
  DataFormatter dataFormatter = new DataFormatter(new java.util.Locale("en", "US"));
  // from 5.2.0 on the DataFormatter can set to use cached values for formula cells
  dataFormatter.setUseCachedValuesForFormulaCells(true);
  
  Sheet sheet = workbook.getSheetAt(0);
  for (Row row : sheet) {
   for (Cell cell : row) {
    //String value = dataFormatter.formatCellValue(cell, evaluator); // up to apache poi 5.1.0
    String value = dataFormatter.formatCellValue(cell); // from apache poi 5.2.0 on 
    System.out.println(value);
   }
  }
  workbook.close();
 }
}

Es gibt einen alternativen Befehl, mit dem Sie den Rohwert einer Zelle abrufen können, in der die Formel angelegt ist. Der Rückgabetyp ist String. Verwenden:

cell.getRawValue();

Wenn Sie einen Rohwert aus einer HSSF-Zelle extrahieren möchten, können Sie so etwas wie dieses Codefragment verwenden:

CellBase base = (CellBase) cell;
CellType cellType = cell.getCellType();
base.setCellType(CellType.STRING);
String result = cell.getStringCellValue();
base.setCellType(cellType);

Zumindest für Zeichenfolgen, die vollständig aus Ziffern bestehen (und von Excel automatisch in Zahlen umgewandelt werden), gibt dies die ursprüngliche Zeichenfolge zurück (z "12345") anstelle eines Bruchwerts (z "12345.0"). Beachten Sie, dass setCellType ist in der Schnittstelle verfügbar Cell(seit v. 4.1), aber veraltet und angekündigt, in v 5.x eliminiert zu werden, obwohl diese Methode immer noch im Unterricht verfügbar ist CellBase. Natürlich wäre es schöner, beides zu haben getRawValue in dem Cell Schnittstelle oder zumindest nutzen zu können getStringCellValue auf Nicht-STRING-Zelltypen. Leider sind alle Ersetzungen von setCellType die in der Beschreibung erwähnt werden, deckt diesen Anwendungsfall nicht ab (möglicherweise liest ein Mitglied des POI-Entwicklerteams diese Antwort).

Die Antwort von SelThroughJava war sehr hilfreich. Ich musste meinen Code ein wenig ändern, damit er funktioniert. ich benutzte https://mvnrepository.com/artifact/org.apache.poi/poi und https://mvnrepository.com/artifact/org.testng/testng als Abhängigkeiten. Der vollständige Code ist unten mit genauen Importen angegeben.

 import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.IOException;

    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.util.CellReference;
    import org.apache.poi.sl.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellType;
    import org.apache.poi.ss.usermodel.CellValue;
    import org.apache.poi.ss.usermodel.FormulaEvaluator;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.usermodel.WorkbookFactory;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;


    public class ReadExcelFormulaValue {

        private static final CellType NUMERIC = null;
        public static void main(String[] args) {
            try {
                readFormula();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        public static void readFormula() throws IOException {
            FileInputStream fis = new FileInputStream("C:eclipse-workspace\\sam-webdbriver-diaries\\resources\\tUser_WS.xls");
            org.apache.poi.ss.usermodel.Workbook workbook = WorkbookFactory.create(fis);
            org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(0);

            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

            CellReference cellReference = new CellReference("G2"); // pass the cell which contains the formula
            Row row = sheet.getRow(cellReference.getRow());
            Cell cell = row.getCell(cellReference.getCol());

            CellValue cellValue = evaluator.evaluate(cell);
            System.out.println("Cell type month  is "+cellValue.getCellTypeEnum());
            System.out.println("getNumberValue month is  "+cellValue.getNumberValue());     
          //  System.out.println("getStringValue "+cellValue.getStringValue());


            cellReference = new CellReference("H2"); // pass the cell which contains the formula
             row = sheet.getRow(cellReference.getRow());
             cell = row.getCell(cellReference.getCol());

            cellValue = evaluator.evaluate(cell);
            System.out.println("getNumberValue DAY is  "+cellValue.getNumberValue());    


        }

    }