platform-bi-web/src/utils/exportor.js

/**
 * Created by zhuth on 2019/07/08.
 */
let Base64 = require('./base64.js');
;exports = module.exports = (function () {
    // "use strict";
    let uri = 'data:application/vnd.ms-excel;base64,',
    tmplWorkbookXML = '<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?>'
        + '<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">'
            + '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">'
                + '<Author>{author}</Author>'
                + '<Created>{created}</Created>'
            +'</DocumentProperties>'
            + '{styles}'
            + '{worksheets}'
        + '</Workbook>',
    tmpStyleXML = '<Styles>'
        + '<Style ss:ID="Bold"><Font ss:Bold="1"/></Style>'
        + '<Style ss:ID="Currency"><NumberFormat ss:Format="Currency"></NumberFormat></Style>'
        + '<Style ss:ID="Date"><NumberFormat ss:Format="yyyy-mm-dd"></NumberFormat></Style>'
        + '<Style ss:ID="Center"><Alignment ss:Horizontal="Center" ss:Vertical="Center"/></Style>'
    + '</Styles>',
    tmplWorksheetXML = '<Worksheet ss:Name="{nameWS}"><Table>{columnWidth}{rows}</Table></Worksheet>',
    tmplColumnWidthXML = '<Column ss:Width="{width}"/>',
    tmplTableHeaderXML = '<Row><Cell ss:StyleID="Center" ss:MergeAcross="{columnCount}"><Data ss:Type="String">{header}</Data></Cell></Row>',
    tmplRowXML = '<Row>{cells}</Row>',
    tmplCellXML = '<Cell{attributeStyleID}{attributeFormula}><Data ss:Type="{nameType}">{data}</Data></Cell>',
    _defaultStyleIDs = {
        DateTime: 'Date',
    },
    _format = function(s, c) { return s.replace(/{(\w+)}/g, function(m, p) { return c[p]; }) },
    _export = function(workbookXML, wbname) {
        let link = document.createElement("A");
        link.href = uri + Base64.encode(workbookXML);
        link.download = wbname || 'Workbook.xls';
        link.target = '_blank';
        document.body.appendChild(link);
        link.click();
        document.body.removeChild(link);
 
    };

    function Exportor(json, wbname) {
        this.init(json, wbname);
        this.workbookXML = this.generateWorkbookXML();
    }

    Exportor.prototype.init = function (json, wbname) {
        this.json = json;
        this.wbname = wbname;
        this.workbookXML = '';
    };

    Exportor.prototype.generateWorkbookXML = function() {
        let ctx = {
            worksheets: this.generateWorksheetsXML(),
            styles: tmpStyleXML,
            author: 'zhuth',
            created: 'zhuth'
        };
        return _format(tmplWorkbookXML, ctx);
    };

    Exportor.prototype.generateWorksheetsXML = function() {
        let str = '',
            sheets = this.json.sheets;

        for(let i = 0; i < sheets.length; i++) {
            str += this.generateWorksheetXML(sheets[i]);
        }
        return str;
    };

    Exportor.prototype.generateWorksheetXML = function(sheet) {
        const { name, header, columns, rows } = sheet;
        let widths = columns.map(c => c.width);
        let ctx = {
            nameWS: name,
            columnWidth: this.generateColumnWidth(widths),
            rows: this.generateTableHeaderXML(header, columns.length) + this.generateHeaderRowsXML(columns) + this.generateRowsXML(columns, rows)
        };
        let str = _format(tmplWorksheetXML, ctx);

        return str;
    };

    Exportor.prototype.generateTableHeaderXML = function(header, columnCount) {
        let str = '';
        if(!!header) {
            let ctx = {
                columnCount: columnCount - 1,
                header
            }
            str = _format(tmplTableHeaderXML, ctx);
        }
        return str
    };

    Exportor.prototype.generateColumnWidth = function(widths) {
        let str = '';
        for(let i = 0; i < widths.length; i++) {
            let ctx = {
                width: widths[i] || 100
            };
            str += _format(tmplColumnWidthXML, ctx);
        }
        return str;
    };

    Exportor.prototype.generateHeaderRowsXML = function(columns) {
        let cells = columns.map(c => ({ type: 'String', value: c.name, styleID: 'Bold' }));
        let ctx = {
            cells: this.generateCellsXML(cells)
        };
        let str = _format(tmplRowXML, ctx);
        return str;
    };

    Exportor.prototype.generateRowsXML = function(columns, rows) {
        let cols = columns.map(c => ({ type: c.type, name: c.name, styleID: c.styleID }));
        let str = '';
        for(let i = 0; i < rows.length; i++) {
            let r = rows[i];
            let cells = [];
            for(let j = 0; j < cols.length; j++) {
                cells.push({
                    type: cols[j].type,
                    value: r[j],
                    styleID: cols[j].styleID || _defaultStyleIDs[cols[j].type]
                });
            }
            let ctx = {
                cells: this.generateCellsXML(cells)
            };
            str += _format(tmplRowXML, ctx);
        }
        return str;
    };

    Exportor.prototype.generateCellsXML = function(cells) {
        let str = '';
        for(let i = 0; i < cells.length; i++) {
            let ctx = {
                attributeStyleID: cells[i].styleID ? _format(' ss:StyleID="{styleID}"', {
                    styleID: cells[i].styleID
                }) : '',
                attributeFormula: '',
                nameType: cells[i].type || 'String',
                data: cells[i].value || ''
            };
            str += _format(tmplCellXML, ctx);
        }
        return str;
    };

    Exportor.prototype.export = function() {
        _export(this.workbookXML, this.wbname);
    }

    return Exportor;

})();