How to calculate complex formulas from your application

[dorinratiu1975]

I will show you how to use EasyXLS, a .NET or Java library, as a calculation engine.

EasyXLS supports all mathematical operators like arithmetic, logical and unary operators, and a various set of functions as Financial functions, Logical functions, Lookup and Reference functions, Database and List Management functions, Math and Trigonometry functions, Information functions, Statistical functions, Text functions or Date and Time Functions.
You may find a full list of supported formulas on chapter Formula Support from this document:
www.easyxls.com/docs/EasyXLS_DetailedSpecificationsSheet.pdf

EasyXLS uses a table to store data. You may fill the table with values and formulas that use those values. After filling the values you need to use ExcelWorksheet.easy_computeFormulas method that calculates all existing formulas. The method returns an error string if any problems occurred during the formula processing.

Here is a C# sample code that calculates two formulas, a simple one that uses SUM function and a complex one that uses the financial function FV (the future value of an investment).

using System;
using System.Data;
using EasyXLS;
using EasyXLS.Constants;

public class CalculateFormula
{
    [STAThread]
    static void Main() 
    {
      //Create an instance of the object that generates Excel files 
          ExcelDocument xls = new ExcelDocument();

    //Add one worksheet
        xls.easy_addWorksheet("Formula");

        //Get the table, populate the sheet and set the formulas
        ExcelTable xlsTable = ((ExcelWorksheet)xls.easy_getSheet("Formula")).easy_getExcelTable();
        ExcelWorksheet xlsWorksheet = (ExcelWorksheet) xls.easy_getSheet("Formula");

        //--------------------------------- Simple SUM formula ------------------------
        //Set cells values
        xlsTable.easy_getCell("A1").setValue("1");
        xlsTable.easy_getCell("A2").setValue("2");
        xlsTable.easy_getCell("A3").setValue("3");
        xlsTable.easy_getCell("A4").setValue("4");
          //The sum of the above values
        xlsTable.easy_getCell("A6").setValue("=SUM(A1:A4)");

        //--------------------------------- Complex financial formula -----------------
        //Annual rate
        xlsTable.easy_getCell("A8").setValue("0.25");
        //Number of payments
        xlsTable.easy_getCell("A9").setValue("10"); 
          //Total amount to be payed
        xlsTable.easy_getCell("A10").setValue("-3000");
        //The future value(FV) of an investment based on periodic, constant payments and a constant interest rate
        xlsTable.easy_getCell("A11").setValue("=FV(A8/12, A9, A10)"); 

        //Compute the formulas
        xlsWorksheet.easy_computeFormulas(xls, true);

        //Display the sum
      Console.WriteLine(xlsTable.easy_getCell("A6").getFormulaResultValue());
        //Display the future value of the investment
     Console.WriteLine(xlsTable.easy_getCell("A11").getFormulaResultValue());

        //Dispose memory
        xls.Dispose();

      Console.ReadLine();
    }
}

Furthermore, EasyXLS library can read and write from .NET xlsx, xlsb, xls files.
EasyXLS is a .NET or Java component to read and write Microsoft Excel 97-2010 files.
The library supports XLS, XLSX, XLSB, CSV, TXT, XML and HTML file formats.
EasyXLS also supports any of C#, VB.NET, J#, C++.NET., ASP, PHP, C++, VB6, VBS, Java or Coldfusion programming languages.

You may check for more samples in the online manual:
www.easyxls.com/manual/index.html

For more details about the library check the website:
www.easyxls.com