Calculate Formulas

Mathematic Functions:

Calculate symbol : Calculate Data:

Logic Function:

Calculate symbol : Calculate Data:

Simple Expression:

Calculate symbol : Calculate Data:

MID Functions:

Text : Start Number:
Number Charts:

Option:

Excel Version:
downloads
  • Demo
  • Java
  • C# source
This demo shows you how to calculate formulas and export data to datatable with calculating formulas.
import com.spire.xls.*;

public class CalculateFormulaDemo {
    public void CalculateFormulas(String resultFile){
        Workbook workbook = new Workbook();
        Worksheet sheet = workbook.getWorksheets().get(0);
        Calculate(workbook, sheet);
        workbook.saveToFile(resultFile, ExcelVersion.Version2010);
    }

    public void Calculate(Workbook workbook, Worksheet worksheet){
        int currentRow = 1;
        String currentFormula = null;
        Object formulaResult = null;
        String value = null;

        // Set width respectively of Column A ,Column B,Column C
        worksheet.setColumnWidth(1,32);
        worksheet.setColumnWidth(2,16);
        worksheet.setColumnWidth(3,16);

        //Set the value of Cell A1
        worksheet.getRange().get(currentRow++, 1).setValue("Examples of formulas :");

        // Set the value of Cell A2
        worksheet.getRange().get(++currentRow, 1).setValue("Test data:");

        // Set the style of Cell A1
        CellRange range = worksheet.getRange().get("A1");
        range.getStyle().getFont().isBold(true);
        range.getStyle().setFillPattern(ExcelPatternType.Solid);
        range.getStyle().setKnownColor(ExcelColors.LightGreen1);
        range.getStyle().getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Medium);

        // Additive operation of mutiple cells

        worksheet.getRange().get(currentRow, 2).setNumberValue(7.3);
        worksheet.getRange().get(currentRow, 3).setNumberValue(5);
        worksheet.getRange().get(currentRow, 4).setNumberValue(8.2);
        worksheet.getRange().get(currentRow, 5).setNumberValue(4);
        worksheet.getRange().get(currentRow, 6).setNumberValue(3);
        worksheet.getRange().get(currentRow, 7).setNumberValue(11.3);

        // Create arithmetic expression string about cells
        currentFormula = "=Sheet1!$B$3 + Sheet1!$C$3+Sheet1!$D$3+Sheet1!$E$3+Sheet1!$F$3+Sheet1!$G$3";

        //Caculate arithmetic expression  about cells
        formulaResult = workbook.calculateFormulaValue(currentFormula);
        value = formulaResult.toString();
        worksheet.getRange().get(currentRow,2).setValue(value);

        // Set the value and format of two head cell
        worksheet.getRange().get(currentRow,1).setValue("Formulas");
        worksheet.getRange().get(currentRow,2).setValue("Results");
        worksheet.getRange().get(currentRow,2).setHorizontalAlignment(HorizontalAlignType.Right);
        range = worksheet.getRange().get(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);

        // Expression caculation
        // Create arithmetic tables enclosed type string
        currentFormula = "=33*3/4-2+10";
        worksheet.getRange().get(++currentRow,1).setText(currentFormula);

        // Caculate arithmetic expression
        formulaResult = workbook.calculateFormulaValue(currentFormula);

        value = formulaResult.toString();

        worksheet.getRange().get(currentRow,2).setValue(value);

        //Absolute value function
        // Create abosolute value function string
        currentFormula = "=ABS(-1.21)";
        worksheet.getRange().get(++currentRow,1).setText(currentFormula);

        // Caculate abosulte value function
        formulaResult = workbook.calculateFormulaValue(currentFormula);
        value = formulaResult.toString();
        worksheet.getRange().get(currentRow,2).setValue(value);

        // Sum function
        // Create sum function string
        currentFormula = "=SUM(18,29)";
        worksheet.getRange().get(++currentRow,1).setText(currentFormula);

        // Caculate sum function
        formulaResult = workbook.calculateFormulaValue(currentFormula);
        value = formulaResult.toString();
        worksheet.getRange().get(currentRow,2).setValue(value);

        //NOT function
        // Create NOT function string
        currentFormula = "=NOT(true)";
        worksheet.getRange().get(++currentRow,1).setText(currentFormula);

        //Caculate NOT function
        formulaResult = workbook.calculateFormulaValue(currentFormula);
        value = formulaResult.toString();
        worksheet.getRange().get(currentRow,2).setValue(value);
        worksheet.getRange().get(currentRow,2).setHorizontalAlignment(HorizontalAlignType.Right);

        //String Manipulation function
        //Get the substring
        // Build substring function
        currentFormula = "=MID(\"world\",4,2)";
        worksheet.getRange().get(++currentRow,1).setText(currentFormula);

        //Caculate substring function
        formulaResult = workbook.calculateFormulaValue(currentFormula);
        value = formulaResult.toString();
        worksheet.getRange().get(currentRow,2).setValue(value);

        worksheet.getRange().get(currentRow,2).setHorizontalAlignment(HorizontalAlignType.Right);

        // Random function

        // Create random function string.
        currentFormula = "=RAND()";
        worksheet.getRange().get(++currentRow,1).setText(currentFormula);

        //Caculate random function
        formulaResult = workbook.calculateFormulaValue(currentFormula);
        value = formulaResult.toString();
        worksheet.getRange().get(currentRow,2).setValue(value);


    }
}
No Matter How Big or Small Your Project is,
Any technical question related to our product, contact us at support@e-iceblue.com.
Any question related to the purchase of product, contact us at sales@e-iceblue.com.
If you don't find the function you want, please request a free demo from us.