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.


Result in Excel

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/2024.2/latest/js/infragistics.core.js"></script>

    <script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2024.2/latest/js/modules/infragistics.ext_core.js"></script>
    <script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2024.2/latest/js/modules/infragistics.ext_collections.js"></script>
    <script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2024.2/latest/js/modules/infragistics.ext_text.js"></script>
    <script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2024.2/latest/js/modules/infragistics.ext_io.js"></script>
    <script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2024.2/latest/js/modules/infragistics.ext_ui.js"></script>
    <script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2024.2/latest/js/modules/infragistics.documents.core_core.js"></script>
    <script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2024.2/latest/js/modules/infragistics.ext_collectionsextended.js"></script>
    <script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2024.2/latest/js/modules/infragistics.excel_core.js"></script>
    <script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2024.2/latest/js/modules/infragistics.ext_threading.js"></script>
    <script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2024.2/latest/js/modules/infragistics.ext_web.js"></script>
    <script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2024.2/latest/js/modules/infragistics.xml.js"></script>
    <script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2024.2/latest/js/modules/infragistics.documents.core_openxml.js"></script>
    <script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2024.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>