Считайте значение ячейки Excel, а не формулу с Apache POI

1. Введение

При чтении файла Excel на Java мы обычно хотим прочитать значения ячеек, чтобы выполнить некоторые вычисления или создать отчет. Однако мы можем столкнуться с одной или несколькими ячейками, содержащими формулы, а не необработанные значения данных. Итак, как нам получить фактические значения данных этих ячеек?

В этом руководстве мы рассмотрим различные способы чтения значений ячеек Excel, а не формулу, которая вычисляет значения ячеек, с помощью Java-библиотеки Apache POI .

Есть два способа решить эту проблему:

• Получить последнее кешированное значение для ячейки
• Оцените формулу во время выполнения, чтобы получить значение ячейки

2. Зависимость от Maven

Нам нужно добавить следующую зависимость в наш файл pom.xml для Apache POI:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.0</version>
</dependency>

Последнюю версию poi-ooxml можно загрузить с Maven Central.

3. Получить последнее кешированное значение

Excel сохраняет два объекта для ячейки, когда формула вычисляет ее значение. Одна — это сама формула, а вторая — кэшированное значение. Кэшированное значение содержит последнее значение, оцененное по формуле .

Итак, идея здесь в том, что мы можем получить последнее кешированное значение и рассматривать его как значение ячейки. Не всегда верно то, что последнее кэшированное значение является правильным значением ячейки. Однако, когда мы работаем с сохраненным файлом Excel и в файле нет недавних изменений, последним кэшированным значением должно быть значение ячейки.

Давайте посмотрим, как получить последнее кешированное значение для ячейки:

FileInputStream inputStream = new FileInputStream(new File("temp.xlsx"));
Workbook workbook = new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);

CellAddress cellAddress = new CellAddress("C2");
Row row = sheet.getRow(cellAddress.getRow());
Cell cell = row.getCell(cellAddress.getColumn());

if (cell.getCellType() == CellType.FORMULA) {
    switch (cell.getCachedFormulaResultType()) {
        case BOOLEAN:
            System.out.println(cell.getBooleanCellValue());
            break;
        case NUMERIC:
            System.out.println(cell.getNumericCellValue());
            break;
        case STRING:
            System.out.println(cell.getRichStringCellValue());
            break;
    }
}

4. Оцените формулу, чтобы получить значение ячейки

Apache POI предоставляет класс FormulaEvaluator , который позволяет нам вычислять результаты формул на листах Excel.

Таким образом, мы можем использовать FormulaEvaluator для прямого вычисления значения ячейки во время выполнения. Класс FormulaEvaluator предоставляет метод, называемый оценкойFormulaCell , который оценивает значение ячейки для заданного объекта Cell и возвращает объект CellType , представляющий тип данных значения ячейки.

Давайте посмотрим на этот подход в действии:

// existing Workbook setup

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

// existing Sheet, Row, and Cell setup

if (cell.getCellType() == CellType.FORMULA) {
    switch (evaluator.evaluateFormulaCell(cell)) {
        case BOOLEAN:
            System.out.println(cell.getBooleanCellValue());
            break;
        case NUMERIC:
            System.out.println(cell.getNumericCellValue());
            break;
        case STRING:
            System.out.println(cell.getStringCellValue());
            break;
    }
}

5. Какой подход выбрать

Простая разница между двумя подходами здесь заключается в том, что первый метод использует последнее кэшированное значение, а второй метод оценивает формулу во время выполнения.

Если мы работаем с файлом Excel, который уже сохранен, и мы не собираемся вносить изменения в эту электронную таблицу во время выполнения, то подход с кэшированными значениями предпочтительнее, поскольку нам не нужно оценивать формулу.

Однако, если мы знаем, что будем вносить частые изменения во время выполнения, то лучше оценить формулу во время выполнения, чтобы получить значение ячейки.

6. Заключение

В этой быстрой статье мы увидели два способа получить значение ячейки Excel, а не формулу, которая его вычисляет.

Полный исходный код этой статьи доступен на GitHub .