Calculation/app/public/js/shares/export_excel.js

'use strict';

/**
 *
 *
 * @author Mai
 * @date
 * @version
 */

const SpreadExcelObj = (function() {
    const _createHideSpread = function () {
        const div = document.createElement("div");
        div.setAttribute('id', 'exportExcelSpread');
        //$(div).css("position", "absolute").css('top', 0).css('left', 0).css('height', 300).css('width', 500).css('z-index', 999);
        //document.body.insertBefore(div, null);
        div.style.display = 'none';
        return div;
    };

    const _removeHideSpread = function (div) {
        document.body.removeChild(div);
    };

    const exportSimpleXlsxSheetData = function (sheet, setting, data) {
        SpreadJsObj.beginMassOperation(sheet);
        sheet.options.isProtected = false;
        sheet.setColumnCount(setting.cols.length);
        sheet.setRowCount(setting.headRows + data.length);

        for (let iRow = 0; iRow < setting.headRowHeight.length; iRow++) {
            sheet.setRowHeight(iRow, setting.headRowHeight[iRow]);
        }
        for (let iCol = 0; iCol < setting.cols.length; iCol++) {
            const col = setting.cols[iCol];
            const title = col.title.split('|');
            const colSpan = col.colSpan ? col.colSpan.split('|') : ['1'],
                rowSpan = col.rowSpan ? col.rowSpan.split('|') : ['1'];
            for (let i = 0; i < title.length; i++) {
                const cell = sheet.getCell(i, iCol);
                cell.text(title[i]).wordWrap(true).hAlign(1).vAlign(1).font(setting.headerFont);
                if ((colSpan[i] !== '' && colSpan[i] !== '1') || (rowSpan[i] !== '' && rowSpan[i] !== '1')) {
                    sheet.addSpan(i, iCol, parseInt(rowSpan[i]), parseInt(colSpan[i]));
                }
            }
            sheet.setColumnWidth(iCol, col.width);
            if (col.visible !== undefined && col.visible !== null) {
                sheet.setColumnVisible(iCol, col.visible);
            }
        }

        const autoFit = !!setting.cols.find(x => { return x.wordWrap; });
        for (let iRow = 0; iRow < data.length; iRow++) {
            const curRow = setting.headRows + iRow;
            const d = data[iRow];
            for (let iCol = 0; iCol < setting.cols.length; iCol++) {
                const cell = sheet.getCell(curRow, iCol);
                const col = setting.cols[iCol];
                if (col.field !== '' && d[col.field]) {
                    cell.value(col.getValue ? col.getValue(d) : d[col.field]);
                    if (col.wordWrap) cell.wordWrap(true);
                    if (typeof d[col.field] === 'string') {
                        cell.formatter('@');
                    }
                }
                if (col.font) {
                    cell.font(col.font);
                } else if (setting.font) {
                    cell.font(setting.font);
                }
                if (col.formatter) {
                    cell.formatter(col.formatter);
                    cell.value(cell.text());
                }
                cell.hAlign(col.hAlign).vAlign(1);
            }
            if (autoFit) sheet.autoFitRow(curRow);
            if (d._hidden) sheet.setRowVisible(curRow, false);
        }
        SpreadJsObj.endMassOperation(sheet);
    };

    const exportXlsxSheetGroup = function(sheet, setting, group) {
        if (!group) return;
        for (const g of group) {
            sheet.rowOutlines.group(g.start + (setting.headRows || 0), g.count);
        }
    };

    const exportSimpleXlsxSheet = function (setting, data, file, group) {
        const div = _createHideSpread();

        const spread = SpreadJsObj.createNewSpread(div, true);
        const sheet = spread.getActiveSheet();
        exportSimpleXlsxSheetData(sheet, setting, data);
        exportXlsxSheetGroup(sheet, setting, group);

        const excelIo = new GC.Spread.Excel.IO();
        const sJson = JSON.stringify(spread.toJSON());
        excelIo.save(sJson, function(blob) {
            saveAs(blob, file);
            _removeHideSpread(div);
        });
    };

    const exportSimpleXlsxSheets = function (sheets, file) {
        if (!sheets || sheets.length === 0) return;

        const div = _createHideSpread();
        const spread = new spreadNS.Workbook(div, {sheetCount: sheets.length});

        for (const [i, sheetData] of sheets.entries()) {
            const sheet = spread.getSheet(i);
            sheet.name(sheetData.name);
            exportSimpleXlsxSheetData(sheet, sheetData.setting, sheetData.data);

        }

        const excelIo = new GC.Spread.Excel.IO();
        const sJson = JSON.stringify(spread.toJSON());
        excelIo.save(sJson, function(blob) {
            saveAs(blob, file);
            _removeHideSpread(div);
        });
    };

    const exportSpread2XlsxWithHeader = function (spread, file) {
        spread.getActiveSheet().options.isProtected = false;
        const excelIo = new GC.Spread.Excel.IO();
        const sJson = JSON.stringify(spread.toJSON({columnHeadersAsFrozenRows: true, rowHeadersAsFrozenColumns: true}));
        excelIo.save(sJson, function(blob) {
            saveAs(blob, file);
        });
        spread.getActiveSheet().options.isProtected = true;
    };

    return {exportSimpleXlsxSheet, exportSpread2XlsxWithHeader, exportSimpleXlsxSheets}
})();

const XLSXObj = (function () {
    const transportSheetData = function (setting, data) {
        const headerStyle = {
            font: { sz: 10, bold: true },
            alignment: { horizontal: 'center' },
        };
        const sHeader = setting.header
            .map((v, i) => Object.assign({}, {v: v, s: headerStyle, position: String.fromCharCode(65+i) + 1 }))
            .reduce((prev, next) => Object.assign({}, prev, {[next.position]: {v: next.v, s: next.s}}), {});
        const sData = data
            .map((v, i) => v.map((k, j) => Object.assign({}, {
                v: k ? k : '',
                s: { font: { sz: 10 }, alignment: {horizontal: setting.hAlign[j]}},
                position: String.fromCharCode(65+j) + (i+2) })))
            .reduce((prev, next) => prev.concat(next))
            .reduce((prev, next) => Object.assign({}, prev, {[next.position]: {v: next.v, s: next.s}}), {});
        const output = Object.assign({}, sHeader, sData);
        const outputPos = Object.keys(output);
        const result = Object.assign({}, output,
            {'!ref': outputPos[0] + ':' + outputPos[outputPos.length - 1]},
            {'!cols': setting.width.map((w) => Object.assign({}, {wpx: w}))});
        return result;
    };

    const exportXlsxSheet = function (setting, data, file) {
        const result = transportSheetData(setting, data);
        const xlsxData = {
            SheetNames: ['Sheet1'],
            Sheets: {
                'Sheet1': result
            }
        };
        const blob = xlsxUtils.format2Blob(xlsxData);
        saveAs(blob, file);
    };

    const exportXlsxSheets = function (sheets, file) {
        const xlsxData = { SheetNames: [], Sheets: {} };
        for (const sheet of sheets) {
            const xlsxSheet = transportSheetData(sheet.setting, sheet.data);
            xlsxData.SheetNames.push(sheet.name);
            xlsxData.Sheets[sheet.name] = xlsxSheet;
        }
        const blob = xlsxUtils.format2Blob(xlsxData);
        saveAs(blob, file);
    };

    return { exportXlsxSheet, exportXlsxSheets }
})();