Как добавлять и читать формулы Excel на Java

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

Конфигурация среды

Установите пакет jar через репозиторий Maven, и код для настройки файла pom.xml выглядит следующим образом:

<repositories>
        <repository>
            <id>com.e-iceblue</id>
            <name>e-iceblue</name>
            <url>http://repo.e-iceblue.com/nexus/content/groups/public/</url>
        </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls.free</artifactId>
        <version>2.2.0</version>
    </dependency>
</dependencies>


Добавить формулы

import com.spire.xls.*;

public class InsertFormulas {

    public static void main(String[] args) {

        //Создать объект Workbook
        Workbook workbook = new Workbook();

        //Получите первый рабочий лист
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Объявить две переменные
        int currentRow = 1;
        String currentFormula = null;

        //Установить ширину столбца
        sheet.setColumnWidth(1, 32);
        sheet.setColumnWidth(2, 16);

        //Записать данные для тестирования в ячейку
        sheet.getCellRange(currentRow,1).setValue("Данные испытаний:");
        sheet.getCellRange(currentRow,2).setNumberValue(1);
        sheet.getCellRange(currentRow,3).setNumberValue(2);
        sheet.getCellRange(currentRow,4).setNumberValue(3);
        sheet.getCellRange(currentRow,5).setNumberValue(4);
        sheet.getCellRange(currentRow,6).setNumberValue(5);

        //Напишите текст
        currentRow += 2;
        sheet.getCellRange(currentRow,1).setValue("Формулы:") ; ;
        sheet.getCellRange(currentRow,2).setValue("результат:");

        //Формат ячеек
        CellRange range = sheet.getCellRange(currentRow,1,currentRow,2);
        range.getStyle().getFont().isBold(true);
        range.getStyle().setKnownColor(ExcelColors.LightGreen1);
        range.getStyle().setFillPattern(ExcelPatternType.Solid);
        range.getStyle().getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Medium);

        //Арифметические операции
        currentFormula = "=1/2+3*4";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Функция даты
        currentFormula = "=TODAY()";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);
        sheet.getCellRange(currentRow,2).getStyle().setNumberFormat("YYYY/MM/DD");

        //Функция времени
        currentFormula = "=NOW()";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);
        sheet.getCellRange(currentRow,2).getStyle().setNumberFormat("H:MM AM/PM");

        //Функция PI
        currentFormula = "=PI()";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Тригонометрическая функция
        currentFormula = "=SIN(PI()/6)";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Функция подсчета
        currentFormula = "=Count(B1:F1)";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Максимальная функция
        currentFormula = "=MAX(B1:F1)";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Средняя функция
        currentFormula = "=AVERAGE(B1:F1)";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Функция суммы
        currentFormula = "=SUM(B1:F1)";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Сохраните документ
        workbook.saveToFile("Formulas.xlsx",FileFormat.Version2013);
    }
}

image

Прочитать формулы

import com.spire.xls.CellRange;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class ReadFormulas {

    public static void main(String[] args) {

        //Создать объект Workbook
        Workbook workbook = new Workbook();

        //Загрузить документ Excel
        workbook.loadFromFile("Formulas.xlsx");

        //Получите первый рабочий лист
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Пройдите ячейки от B1 до B13
        for (Object cell: sheet.getCellRange("B1:B13")
                ) {
            CellRange cellRange = (CellRange)cell;

            //Определите, содержит ли ячейка формулу
            if (cellRange.hasFormula()){

                //Печать ячеек и формул
                String certainCell = String.format("Ячейка [% d,% d] содержит формулу: ",cellRange.getRow(),cellRange.getColumn());
                System.out.println(certainCell + cellRange.getFormula());
            }
        }
    }
}

image
Tags:
Java, Excel

You can't comment this post because its author is not yet a full member of the community. You will be able to contact the author only after he or she has been invited by someone in the community. Until then, author's username will be hidden by an alias.