2

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.

3

1 Answer 1

2

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
                    }
                ]
            };
Sign up to request clarification or add additional context in comments.

1 Comment

how can I set specific template to exported excel ?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.