|
User Guide EasyXLS Basics Addressing cells Calculate formulas
|
|
|
Calculate formulas
|
Concept
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.
Concept in action
|
Source code sample
|
|
|
|
|
|
|
// Create an instance of the class that creates Excel files
ExcelDocument workbook = new ExcelDocument();
// Add a sheet
ExcelWorksheet xlsWorksheet = new 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.Length == 0)
Console.WriteLine("Formulas computed successfully");
else
Console.WriteLine("Error computing formulas! Error: " + sError);
// Read formula result
Console.WriteLine("The result of the formula entered at position A6 is: " +
xlsTable.easy_getCell(5,0).getFormulaResultValue());
// Export Excel file (optional)
workbook.easy_WriteXLSFile("C:\\Samples\\Excel calculation.xls");
|
|
' Create an instance of the class that creates Excel files
Dim workbook As New ExcelDocument
' Add a sheet
Dim xlsWorksheet = New ExcelWorksheet("Formula calculation")
workbook.easy_addWorksheet(xlsWorksheet)
' Get the table of data for the sheet
Dim 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
Dim sError As String = xlsWorksheet.easy_computeFormulas(workbook, True)
If (sError.Equals("")) Then
Console.WriteLine(vbCrLf & "Formulas computed successfully")
Else
Console.WriteLine(vbCrLf & "Error computing formulas! Error: " & sError)
End If
' Read formula result
Console.WriteLine("The result of the formula entered at position A6 is: " _
& xlsTable.easy_getCell(5, 0).getFormulaResultValue())
' Export Excel file (optional)
workbook.easy_WriteXLSFile("C:\Samples\Excel calculation.xls")
|
|
// Create an instance of the class that creates Excel files
ExcelDocument *workbook = new ExcelDocument();
// Add a sheet
ExcelWorksheet *xlsWorksheet = new 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_WriteXLSFile("C:\\Samples\\Excel calculation.xls");
|
|
// Create an instance of the class that creates Excel files
ExcelDocument workbook = new ExcelDocument();
// Add a sheet
ExcelWorksheet xlsWorksheet = new 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.Length == 0)
Console.WriteLine("Formulas computed successfully");
else
Console.WriteLine("Error computing formulas! Error: " + sError);
// Read formula result
Console.WriteLine("The result of the formula entered at position A6 is: " +
xlsTable.easy_getCell(5,0).getFormulaResultValue());
// Export Excel file (optional)
workbook.easy_WriteXLSFile("C:\\Samples\\Excel calculation.xls");
|
|
// Create an instance of the class that creates Excel files
ExcelDocument workbook = new ExcelDocument();
// Add a sheet
ExcelWorksheet xlsWorksheet = new 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.Length == 0)
Console.WriteLine("Formulas computed successfully");
else
Console.WriteLine("Error computing formulas! Error: " + sError);
// Read formula result
Console.WriteLine("The result of the formula entered at position A6 is: " +
xlsTable.easy_getCell(5,0).getFormulaResultValue());
// Export Excel file (optional)
workbook.easy_WriteXLSFile("C:\\Samples\\Excel calculation.xls");
|
|
// 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_WriteXLSFile("C:\Samples\Excel calculation.xls");
|
|
' Create an instance of the class that creates Excel files
set workbook = Server.CreateObject("EasyXLS.ExcelDocument")
' Add a sheet
set xlsWorksheet = Server.CreateObject("EasyXLS.ExcelWorksheet")
xlsWorksheet.setSheetName("Formula calculation")
workbook.easy_addWorksheet(xlsWorksheet)
' Get the table of data for the sheet
set 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 = "" then
response.write("Formulas computed successfully <br />")
else
response.write("Error computing formulas! Error: " + sError)
end if
' Read formula result
response.write("The result of the formula entered at position A6 is: " + _
xlsTable.easy_getCell(5,0).getFormulaResultValue())
' Export Excel file (optional)
workbook.easy_WriteXLSFile ("C:\Samples\Excel calculation.xls")
|
|
// 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_WriteXLSFile("C:\\Samples\\Excel calculation.xls");
|
|
' Create an instance of the class that creates Excel files
Set workbook = CreateObject("EasyXLS.ExcelDocument")
' Add a sheet
Set xlsWorksheet = CreateObject("EasyXLS.ExcelWorksheet")
xlsWorksheet.setSheetName ("Formula calculation")
workbook.easy_addWorksheet (xlsWorksheet)
' Get the table of data for the sheet
Set 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 = "" Then
Me.Label1.Caption = Me.Label1.Caption & vbCrLf & "Formulas computed successfully"
Else
Me.Label1.Caption = Me.Label1.Caption & vbCrLf & _
"Error computing formulas! Error: " & sError
End If
' Read formula result
Me.Label1.Caption = Me.Label1.Caption & vbCrLf & _
"The result of the formula entered at position A6 is: " & _
xlsTable.easy_getCell(5, 0).getFormulaResultValue()
' Export Excel file (optional)
workbook.easy_WriteXLSFile ("C:\Samples\Excel calculation.xls")
|
|
' Create an instance of the class that creates Excel files
Set workbook = CreateObject("EasyXLS.ExcelDocument")
' Add a sheet
Set xlsWorksheet = CreateObject("EasyXLS.ExcelWorksheet")
xlsWorksheet.setSheetName ("Formula calculation")
workbook.easy_addWorksheet (xlsWorksheet)
' Get the table of data for the sheet
Set 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 = "" Then
WScript.StdOut.Write(vbcrlf & "Formulas computed successfully")
Else
WScript.StdOut.Write(vbcrlf & "Error computing formulas! Error: " & sError)
End If
' Read formula result
WScript.StdOut.Write(vbcrlf & "The result of the formula entered at position A6 is: " _
& xlsTable.easy_getCell(5, 0).getFormulaResultValue())
' Export Excel file (optional)
workbook.easy_WriteXLSFile ("C:\Samples\Excel calculation.xls")
|
|
<!-- Create an instance of the class that creates Excel files -->
<cfobject type="java" class="EasyXLS.ExcelDocument" name="workbook" action="CREATE">
<!-- Add a sheet -->
<cfobject type="java" class="EasyXLS.ExcelWorksheet"
name="xlsWorksheet" action="CREATE">
<cfset xlsWorksheet.setSheetName("Formula calculation")>
<cfset workbook.easy_addWorksheet(xlsWorksheet)>
<!-- Get the table of data for the sheet -->
<cfset xlsTable = xlsWorksheet.easy_getExcelTable()>
<!-- Add data in cells and set the formula -->
<cfset xlsTable.easy_getCell("A1").setValue("1")>
<cfset xlsTable.easy_getCell("A2").setValue("2")>
<cfset xlsTable.easy_getCell("A3").setValue("3")>
<cfset xlsTable.easy_getCell("A4").setValue("4")>
<cfset xlsTable.easy_getCell("A6").setValue("=SUM(A1:A4)")>
<!-- Call the method that computes Excel formulas -->
<cfset sError = xlsWorksheet.easy_computeFormulas(workbook, true)>
<CFIF (sError IS "")>
<cfoutput>
Formulas computed successfully <br />
</cfoutput>
<CFELSE>
<cfoutput>
Error computing formulas! Error: #sError#
</cfoutput>
</CFIF>
<!-- Read formula result -->
<cfoutput>
The result of the formula entered at position A6 is:
#xlsTable.easy_getCell(5,0).getFormulaResultValue()#
</cfoutput>
<!-- Export Excel file (optional) -->
<cfset ret = workbook.easy_WriteXLSFile("C:\Samples\Excel calculation.xls")>
|
|
|
|
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
|
