Calculate Excel formulas in C#, VB.NET, Java, C++, PHP

Calculate formulas

EasyXLS™ library allows you to calculate Excel formulas by defining formulas with functions or loading template Excel files with predefined formulas. The defined spreadsheet can be optionally exported to Excel.

The library can be used as Excel calculation engine. The engine provides all the functions supported by Excel as far as formulas are concerned. It provides arithmetic, logical and unary operators, formulas with numbers, dates and strings, formulas with cell references, formulas with cell ranges, formulas with names and with arrays.

Source code sample
Excel functions
Export to Excel with formulas calculation
Import Excel file with formulas

Source code sample

The below example shows how to calculate Excel formulas and read the formula result.

The source code samples can be integrated in:

- ASP.NET web pages
- Windows applications
- Windows Forms (WinForms)
- Console applications
- Windows service applications
- ASP.NET MVC web applications
- PHP and ASP web pages
- Java applications

C++

// Create an instance of the class that creates Excel files
EasyXLS::IExcelDocumentPtr workbook;
hr = CoCreateInstance(__uuidof(EasyXLS::ExcelDocument),
NULL,
CLSCTX_ALL,
__uuidof(EasyXLS::IExcelDocument),
(void**) &workbook) ;

// Add a sheet
EasyXLS::IExcelWorksheetPtr xlsWorksheet;
hr = CoCreateInstance(__uuidof(EasyXLS::ExcelWorksheet),
NULL,
CLSCTX_ALL,
__uuidof(EasyXLS::IExcelWorksheet),
(void**) &xlsWorksheet) ;
xlsWorksheet->setSheetName("Formula calculation");
workbook->easy_addWorksheet(xlsWorksheet);

// Get the table of data for the sheet
EasyXLS::IExcelTablePtr xlsTable = xlsWorksheet->easy_getExcelTable();

// Add data in cells and set the formula
xlsTable->easy_getCell_2("A1")->setValue("1");
xlsTable->easy_getCell_2("A2")->setValue("2");
xlsTable->easy_getCell_2("A3")->setValue("3");
xlsTable->easy_getCell_2("A4")->setValue("4");
xlsTable->easy_getCell_2("A6")->setValue("=SUM(A1:A4)");

// Call the method that computes Excel formulas
_bstr_t sError = 
   xlsWorksheet->easy_computeFormulas(_variant_t((IDispatch*)workbook, true), true);
if (strcmp(sError, "") == 0) 
    printf("\nFormulas computed successfully"); 
else
    printf("\nError computing formulas! Error: ", sError);

// Read formula result
printf("\nThe result of the formula entered at position A6 is: %s", 
              (LPCSTR)xlsTable->easy_getCell(5,0)->getFormulaResultValue());

// Export Excel file (optional)
workbook->easy_WriteXLSXFile("C:\\Samples\\Excel calculation.xlsx");


C++.NET

// Create an instance of the class that creates Excel files
ExcelDocument ^workbook = gcnew ExcelDocument();

// Add a sheet
ExcelWorksheet ^xlsWorksheet = gcnew ExcelWorksheet("Formula calculation"); 
workbook->easy_addWorksheet(xlsWorksheet);

// Get the table of data for the sheet
ExcelTable ^xlsTable = xlsWorksheet->easy_getExcelTable();

// Add data in cells and set the formula
xlsTable->easy_getCell("A1")->setValue("1");
xlsTable->easy_getCell("A2")->setValue("2");
xlsTable->easy_getCell("A3")->setValue("3");
xlsTable->easy_getCell("A4")->setValue("4");
xlsTable->easy_getCell("A6")->setValue("=SUM(A1:A4)");

// Call the method that computes Excel formulas
String ^sError = xlsWorksheet->easy_computeFormulas(workbook, true);
if (sError->Equals("")) 
    Console::WriteLine("Formulas computed successfully"); 
else 
    Console::WriteLine(String::Concat("Error computing formulas! Error: ", sError)); 

// Read formula result
Console::WriteLine(String::Concat(
    "The result of the formula entered at position A6 is: ", 
    xlsTable->easy_getCell(5,0)->getFormulaResultValue()));

// Export Excel file (optional)
workbook->easy_WriteXLSXFile("C:\\Samples\\Excel calculation.xlsx");

.NET:

// Create an instance of the class that creates Excel files
$workbook = new COM("EasyXLS.ExcelDocument");

// Add a sheet
$xlsWorksheet = new COM("EasyXLS.ExcelWorksheet");
$xlsWorksheet->setSheetName("Formula calculation");
$workbook->easy_addWorksheet($xlsWorksheet);

// Get the table of data for the sheet
$xlsTable = $xlsWorksheet->easy_getExcelTable();

// Add data in cells and set the formula
$xlsTable->easy_getCell_2("A1")->setValue("1");
$xlsTable->easy_getCell_2("A2")->setValue("2");
$xlsTable->easy_getCell_2("A3")->setValue("3");
$xlsTable->easy_getCell_2("A4")->setValue("4");
$xlsTable->easy_getCell_2("A6")->setValue("=SUM(A1:A4)");

// Call the method that computes Excel formulas
$sError = $xlsWorksheet->easy_computeFormulas($workbook, true);
if ($sError == "") 
    echo "Formulas computed successfully <br />"; 
else 
    echo "Error computing formulas! Error: " . $sError; 

// Read formula result
echo "The result of the formula entered at position A6 is: " . 
   $xlsTable->easy_getCell(5,0)->getFormulaResultValue();

// Export excel file (optional)
$workbook->easy_WriteXLSXFile("C:\Samples\Excel calculation.xlsx");


Java:

// Create an instance of the class that creates Excel files
$workbook = new java("EasyXLS.ExcelDocument"); 
	
// Add a sheet
$xlsWorksheet = new java("EasyXLS.ExcelWorksheet");
$xlsWorksheet->setSheetName("Formula calculation");
$workbook->easy_addWorksheet($xlsWorksheet);

// Get the table of data for the sheet
$xlsTable = $xlsWorksheet->easy_getExcelTable();

// Add data in cells and set the formula
$xlsTable->easy_getCell("A1")->setValue("1");
$xlsTable->easy_getCell("A2")->setValue("2");
$xlsTable->easy_getCell("A3")->setValue("3");
$xlsTable->easy_getCell("A4")->setValue("4");
$xlsTable->easy_getCell("A6")->setValue("=SUM(A1:A4)");

// Call the method that computes Excel formulas
$sError = $xlsWorksheet->easy_computeFormulas($workbook, true);
if ($sError == "") 
    	echo "Formulas computed successfully <br />"; 
else 
    	echo "Error computing formulas! Error: " . $sError; 

// Read formula result
echo "The result of the formula entered at position A6 is: " . 
    $xlsTable->easy_getCell(5,0)->getFormulaResultValue();

// Export excel file (optional)
$workbook->easy_WriteXLSXFile("C:\Samples\Excel calculation.xlsx");

.NET:

# Create an instance of the class that creates Excel files
workbook = ExcelDocument()

# Add a sheet
xlsWorksheet = ExcelWorksheet("Formula calculation")
workbook.easy_addWorksheet(xlsWorksheet)

# Get the table of data for the sheet
xlsTable = xlsWorksheet.easy_getExcelTable()

# Add data in cells and set the formula
xlsTable.easy_getCell("A1").setValue("1")
xlsTable.easy_getCell("A2").setValue("2")
xlsTable.easy_getCell("A3").setValue("3")
xlsTable.easy_getCell("A4").setValue("4")
xlsTable.easy_getCell("A6").setValue("=SUM(A1:A4)")

# Call the method that computes Excel formulas
sError = xlsWorksheet.easy_computeFormulas(workbook, True)
if len(sError) == 0:
    print("Formulas computed successfully")
else: 
    print("Error computing formulas! Error: " + sError)

# Read formula result
print("The result of the formula entered at position A6 is: " + 
                       xlsTable.easy_getCell(5,0).getFormulaResultValue())

# Export Excel file (optional)
workbook.easy_WriteXLSXFile("C:\\Samples\\Excel calculation.xlsx")


Java:

# Create an instance of the class that creates Excel files
workbook = gateway.jvm.ExcelDocument()

# Add a sheet
xlsWorksheet = gateway.jvm.ExcelWorksheet("Formula calculation")
workbook.easy_addWorksheet(xlsWorksheet)

# Get the table of data for the sheet
xlsTable = xlsWorksheet.easy_getExcelTable()

# Add data in cells and set the formula
xlsTable.easy_getCell("A1").setValue("1")
xlsTable.easy_getCell("A2").setValue("2")
xlsTable.easy_getCell("A3").setValue("3")
xlsTable.easy_getCell("A4").setValue("4")
xlsTable.easy_getCell("A6").setValue("=SUM(A1:A4)")

# Call the method that computes Excel formulas
sError = xlsWorksheet.easy_computeFormulas(workbook, True)
if len(sError) == 0:
    print("Formulas computed successfully")
else: 
    print("Error computing formulas! Error: " + sError)

# Read formula result
print("The result of the formula entered at position A6 is: " + 
                       xlsTable.easy_getCell(5,0).getFormulaResultValue())

# Export Excel file (optional)
workbook.easy_WriteXLSXFile("C:\\Samples\\Excel calculation.xlsx")

Excel functions

The calculation engine provides all the functions supported by Excel. The full list of formulas elements can be found at the following link.

Export to Excel with formulas calculation

EasyXLS allows you to export formulas to Excel file. The above code sample shows how to achieve this goal.

The formula is a cell value that must start with the "=" sign (i.e. =A1+C2).

Import Excel file with formulas

EasyXLS allows you to import an Excel file as a template with predefined formulas with functions, calculate spreadsheet formulas and read the formula result.

Getting started with EasyXLS Excel library

To download the trial version of EasyXLS Excel Library, press the below button:

If you already own a license key, you may login and download EasyXLS from your account.

Available for: Professional, Excel Writer, Excel Reader

EasyXLS Excel libraries:
.NET Excel Library
full .NET version to import, export or convert Excel files
COM+ Excel Library
full COM+ version to import, export or convert Excel files


Java Excel Library
full Java version to import, export or convert Excel files