[Ява] Вставка и чтение формул в Excel

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

Установка Способ 1: Скачайте Бесплатный шпиль.XLS для Java и распакуйте его. Затем добавьте Spire.Xls.jar файл в ваш проект как зависимость.

Метод 2: Вы также можете добавить зависимость jar в проект maven, добавив следующие конфигурации в pom.xml .


        
            com.e-iceblue
            e-iceblue
            http://repo.e-iceblue.com/nexus/content/groups/public/
        


    
        e-iceblue
        spire.xls.free
        2.2.0

Вставка формул

import com.spire.xls.*;

public class InsertFormulas {

    public static void main(String[] args) {

        //Create a Workbook object
        Workbook workbook = new Workbook();

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Declare two variables: currentRow、currentFormula
        int currentRow = 1;
        String currentFormula = null;

        //Set the column width
        sheet.setColumnWidth(1, 32);
        sheet.setColumnWidth(2, 16);

        //Write test data into cells
        sheet.getCellRange(currentRow,1).setValue("Test data:");
        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);

        //Write text in cells
        currentRow += 2;
        sheet.getCellRange(currentRow,1).setValue("Formulas:") ; ;
        sheet.getCellRange(currentRow,2).setValue("result:");

        //Format cells
        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);

        //Arithmetic operation
        currentFormula = "=1/2+3*4";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Date function
        currentFormula = "=TODAY()";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);
        sheet.getCellRange(currentRow,2).getStyle().setNumberFormat("YYYY/MM/DD");

        //Time function
        currentFormula = "=NOW()";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);
        sheet.getCellRange(currentRow,2).getStyle().setNumberFormat("H:MM AM/PM");

        //IF function
        currentFormula = "=IF(B1=5,\"Yes\",\"No\")";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //PI function
        currentFormula = "=PI()";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Trigonometric function
        currentFormula = "=SIN(PI()/6)";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Count function
        currentFormula = "=Count(B1:F1)";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Maximum function
        currentFormula = "=MAX(B1:F1)";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Average function
        currentFormula = "=AVERAGE(B1:F1)";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Summation function
        currentFormula = "=SUM(B1:F1)";
        sheet.getCellRange(++currentRow,1).setText(currentFormula);
        sheet.getCellRange(currentRow,2).setFormula(currentFormula);

        //Save to file
        workbook.saveToFile("output/InsertFormulas.xlsx",FileFormat.Version2013);
    }
}

Чтение формул

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

public class ReadFormulas {

    public static void main(String[] args) {

        //Create a Workbook object
        Workbook workbook = new Workbook();

        //Load an Excel file
        workbook.loadFromFile("output/InsertFormulas.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Loop through the cells within B1:B13
        for (Object cell: sheet.getCellRange("B1:B13")
                ) {
            CellRange cellRange = (CellRange)cell;

            //Detect if a cell range has formula
            if (cellRange.hasFormula()){

                //Print out the cell containing a formula and the formula itself
                String certainCell = String.format("Cell[%d, %d] contains a formula: ",cellRange.getRow(),cellRange.getColumn());
                System.out.println(certainCell + cellRange.getFormula());
            }
        }
    }
}

Оригинал: “https://dev.to/jazzzzz/java-insert-and-read-formulas-in-excel-56io”