js根据数据(json格式的数据)和列名(英文名)列别名(中文名)导出Excel(多个sheet)

js根据数据(json格式的数据)和列名(英文名)列别名(中文名)导出Excel(多个sheet)

/**
 * 使用数据和列明列别名,导出excel(包含多个sheet)
 * @param tableDataList 数据集合
 * @param columnsAliasList   数据列表头字段
 * @param columnsList   数据列字段
 * @param wsnames       sheet的名字数组
 * @param wbname        工作簿名字
 */
function tablesToExcels3(tableDataList, columnsAliasList, columnsList, wsnames, wbname) {

    var 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>Axel Richter</Author><Created>{created}</Created></DocumentProperties>'
            + '<Styles>'
            + '<Style ss:ID="Currency"><NumberFormat ss:Format="Currency"></NumberFormat></Style>'
            + '<Style ss:ID="Date"><NumberFormat ss:Format="Medium Date"></NumberFormat></Style>'
            + '</Styles>'
            + '{worksheets}</Workbook>'
        , tmplWorksheetXML = '<Worksheet ss:Name="{nameWS}"><Table>{rows}</Table></Worksheet>'
        , tmplCellXML = '<Cell{attributeStyleID}{attributeFormula}><Data ss:Type="{nameType}">{data}</Data></Cell>'
        , base64 = function (s) {
            return window.btoa(unescape(encodeURIComponent(s)))
        }
        , format = function (s, c) {
            return s.replace(/{(\w+)}/g, function (m, p) {
                return c[p];
            })
        }

    var ctx = "";
    var workbookXML = "";
    var worksheetsXML = "";
    var rowsXML = "";

    for (let i = 0; i < columnsList.length; i++) {

        // 添加表头
        rowsXML += '<Row>';
        for (let j = 0; j < columnsAliasList[i].length; j++) {
            ctx = {
                attributeStyleID: ' ss:StyleID="Currency"',
                nameType: 'String',
                data: columnsAliasList[i][j],
                attributeFormula: ''
            };
            rowsXML += format(tmplCellXML, ctx);
        }
        rowsXML += '</Row>'

        let columns = columnsList[i];
        for (let k = 0; k < tableDataList[i].length; k++) {
            rowsXML += '<Row>';
            for (let j = 0; j < columns.length; j++) {
                ctx = {
                    attributeStyleID: ' ss:StyleID=""',
                    nameType: 'String',
                    data: tableDataList[i][k][columns[j]],
                    attributeFormula: ''
                };
                rowsXML += format(tmplCellXML, ctx);
            }
            rowsXML += '</Row>'
        }

        ctx = {rows: rowsXML, nameWS: wsnames[i] || 'Sheet' + i};
        worksheetsXML += format(tmplWorksheetXML, ctx);
        rowsXML = "";

    }

    ctx = {created: (new Date()).getTime(), worksheets: worksheetsXML};
    workbookXML = format(tmplWorkbookXML, ctx);

    var link = document.createElement("A");
    link.href = uri + base64(workbookXML);
    link.download = wbname || 'Workbook.xls';
    link.target = '_blank';
    document.body.appendChild(link);
    link.click();
    document.body.removeChild(link);

}