AngularJS: How to export ui-grid data to excel?

Question

i'm new in angularJS and i want to export my ui-grid data to excel file, with each value in each cell. It is possible? How? any examples?

Thanks in advance.

Answer 1

I can achive this using js-xlsx and FileSaver

http://plnkr.co/edit/DVhIUgGD04yDXtjp7ReW

This code block is exporter service.

(function () {
    'use strict';

    angular
        .module('app.utils')
        .factory('exportUiGridService', exportUiGridService);

    /* @ngInject */
    function exportUiGridService(uiGridExporterService) {
        var service = {
            exportToExcel: exportToExcel
        };

        return service;

        function Workbook() {
            if (!(this instanceof Workbook)) return new Workbook();
            this.SheetNames = [];
            this.Sheets = {};
        }

        function exportToExcel(sheetName, gridApi, rowTypes, colTypes) {
            var columns = gridApi.grid.options.showHeader ? uiGridExporterService.getColumnHeaders(gridApi.grid, colTypes) : [];
            var data = uiGridExporterService.getData(gridApi.grid, rowTypes, colTypes);
            var fileName = gridApi.grid.options.exporterExcelFilename ? gridApi.grid.options.exporterExcelFilename : 'dokuman';
            fileName += '.xlsx';
            var wb = new Workbook(),
                ws = sheetFromArrayUiGrid(data, columns);
            wb.SheetNames.push(sheetName);
            wb.Sheets[sheetName] = ws;
            var wbout = XLSX.write(wb, {
                bookType: 'xlsx',
                bookSST: true,
                type: 'binary'
            });
            saveAs(new Blob([s2ab(wbout)], {
                type: 'application/octet-stream'
            }), fileName);
        }

        function sheetFromArrayUiGrid(data, columns) {
            var ws = {};
            var range = {
                s: {
                    c: 10000000,
                    r: 10000000
                },
                e: {
                    c: 0,
                    r: 0
                }
            };
            var C = 0;
            columns.forEach(function (c) {
                var v = c.displayName || c.name;
                addCell(range, v, 0, C, ws);
                C++;
            }, this);
            var R = 1;
            data.forEach(function (ds) {
                C = 0;
                ds.forEach(function (d) {
                    var v = d.value;
                    addCell(range, v, R, C, ws);
                    C++;
                });
                R++;
            }, this);
            if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
            return ws;
        }

        function datenum(v, date1904) {
            if (date1904) v += 1462;
            var epoch = Date.parse(v);
            return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
        }

        function s2ab(s) {
            var buf = new ArrayBuffer(s.length);
            var view = new Uint8Array(buf);
            for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
            return buf;
        }

        function addCell(range, value, row, col, ws) {
            if (range.s.r > row) range.s.r = row;
            if (range.s.c > col) range.s.c = col;
            if (range.e.r < row) range.e.r = row;
            if (range.e.c < col) range.e.c = col;
            var cell = {
                v: value
            };
            if (cell.v == null) cell.v = '-';
            var cell_ref = XLSX.utils.encode_cell({
                c: col,
                r: row
            });

            if (typeof cell.v === 'number') cell.t = 'n';
            else if (typeof cell.v === 'boolean') cell.t = 'b';
            else if (cell.v instanceof Date) {
                cell.t = 'n';
                cell.z = XLSX.SSF._table[14];
                cell.v = datenum(cell.v);
            } else cell.t = 's';

            ws[cell_ref] = cell;
        }
    }
})();

This is Grid Options for actions in grid menu.

    ctrl.gridOptions = {
                appScopeProvider: ctrl,
                enableFiltering: true,
                enableColumnResizing: true,
                enableGridMenu: true,
                showGridFooter: true,
                showColumnFooter: true,
                exporterMenuCsv: false,
                columnDefs: [
                  .
                  .
                  .
                ],
                onRegisterApi: function (gridApi) {
                    ctrl.gridApi = gridApi;
                },
                gridMenuCustomItems: [{
                        title: 'Export all data as EXCEL',
                        action: function ($event) {
                            exportUiGridService.exportToExcel('Sheet 1', ctrl.gridApi, 'all', 'all');
                        },
                        order: 110
                    },
                    {
                        title: 'Export visible data as EXCEL',
                        action: function ($event) {
                            exportUiGridService.exportToExcel('Sheet 1', ctrl.gridApi, 'visible', 'visible');
                        },
                        order: 111
                    }
                ]
            };