Available in the Full Version
Javascript Excel - Excel Formulas
This sample shows how to create an Excel worksheet with custom formulas using the Infragistics Ignite UI for jQuery JavaScript Excel library.
This sample uses CTP (Community Technical Preview) features. The API and behavior may change when these features are released with full support.
This sample is designed for a larger screen size.
On mobile, try rotating your screen, view full size, or email to another device.
This example uses the AVERAGE function to display the average values for a column of numbers. You can use the Excel library in browsers and in other JavaScript runtimes such as Node.js.
Code View
Copy to Clipboard
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
<style>
.right {
float: right;
}
#exportButton {
float: left;
}
</style>
<!--Required scripts-->
<script src="http://code.jquery.com/jquery-1.11.3.min.js"></script>
<!-- External files for exporting -->
<script src="/js/external/FileSaver.js"></script>
<script src="/js/external/Blob.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/infragistics.core.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/modules/infragistics.ext_core.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/modules/infragistics.ext_collections.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/modules/infragistics.ext_text.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/modules/infragistics.ext_io.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/modules/infragistics.ext_ui.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/modules/infragistics.documents.core_core.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/modules/infragistics.ext_collectionsextended.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/modules/infragistics.excel_core.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/modules/infragistics.ext_threading.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/modules/infragistics.ext_web.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/modules/infragistics.xml.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/modules/infragistics.documents.core_openxml.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/modules/infragistics.excel_serialization_openxml.js"></script>
</head>
<body>
<br />
<button id="exportButton" onclick="createFormulasWorkbook()">Create File</button>
<br />
<img alt="Result in Excel" src="/images/samples/client-side-excel-library/excel-formulas.png" />
<script>
function createFormulasWorkbook() {
var workbook = new $.ig.excel.Workbook($.ig.excel.WorkbookFormat.excel2007);
var sheet = workbook.worksheets().add('Sheet1');
sheet.columns(0).setWidth(180, $.ig.excel.WorksheetColumnWidthUnit.pixel);
sheet.columns(1).setWidth(116, $.ig.excel.WorksheetColumnWidthUnit.pixel);
sheet.columns(2).setWidth(124, $.ig.excel.WorksheetColumnWidthUnit.pixel);
// Create some rows and columns of data
sheet.getCell('A1').value('Project/Client');
sheet.getCell('B1').value('Status');
sheet.getCell('C1').value('Est. Duration (Mos.)');
sheet.getCell('A2').value('Bank of Winniwonka');
sheet.getCell('B2').value('In Progress');
sheet.getCell('C2').value(7);
sheet.getCell('A3').value('Arvalis, Inc.');
sheet.getCell('B3').value('Negotiation');
sheet.getCell('C3').value(3);
sheet.getCell('A4').value('Panamoramic Studios');
sheet.getCell('B4').value('Negotiation');
sheet.getCell('C4').value(15);
sheet.getCell('A5').value('Werkz.me');
sheet.getCell('B5').value('Change Request');
sheet.getCell('C5').value(24);
// Add a formula to average one fo the columns of data
sheet.getCell('C6').applyFormula('=AVERAGE(C2:C5)');
// Alternatively, the formula can be parsed and applied manually, like so:
//var formula = $.ig.excel.Formula.parse('=AVERAGE(C2:C5)', $.ig.excel.CellReferenceMode.a1);
//formula.applyTo(sheet.getCell('C6'));
// Save the workbook
saveWorkbook(workbook, "Formulas.xlsx");
}
function saveWorkbook(workbook, name) {
workbook.save({ type: 'blob' }, function (data) {
saveAs(data, name);
}, function (error) {
alert('Error exporting: : ' + error);
});
}
</script>
</body>
</html>