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.