JS（十四）前端导出表格和echats图形到excel

太过爱你忘了你带给我的痛 2022-06-10 03:38 728阅读 0赞

import $ from 'jquery';
    import echarts from 'echarts';
    import store from '../../store';
    import moment from 'moment';
    import { getQueryString, NumberFormat, convertThousands } from '../../utils/commonLib';
    
    export const exportExcel = (fName) => {
        let $container = document.querySelectorAll('.echarts-for-react');
        saveFile($container, fName);
    }
    
    const findS3 = (tData, index, s3) => {
        let res = '';
        if (!s3 && s3 !== 0) {
            for (let j = index, l = index + 28; j < l; ++j) {
                if (tData[j].v && tData[j].v.S3) {
                    res = tData[j].v.S3;
                    break;
                }
            }
        }
        return res;
    }
    
    const saveFile = function ($el, fileName) {
        let urlObject = window.URL || window.webkitURL || window;
        let exportBlob = new Blob([excelTemplate($el, fileName)]);
        let saveLink = document.createElementNS("http://www.w3.org/1999/xhtml", "a");
        saveLink.href = urlObject.createObjectURL(exportBlob);
        saveLink.download = fileName.toUpperCase() + '.xls';
        let ev = document.createEvent("MouseEvents");
        ev.initMouseEvent("click", true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
        saveLink.dispatchEvent(ev);
        urlObject.revokeObjectURL(saveLink.href);
    }
    
    const excelTemplate = (tagArr, fileName) => {
        let sheetsInfo = "",
            sheetsDate = "",
            picsDate = "";
        Array.from(tagArr).map((value, index) => {
            let table, sheetname;
            if (value.tagName === 'TABLE') {
                // if ($('.w-tabs-tabpane-active').length == 0) {
                //     sheetname = $('.w-table-title').text();
                //     if (!sheetname) {
                //         sheetname = fileName;
                //     }
                // } else {
                //     sheetname = $('.w-tabs-tab-active span').text()
                // }
                // table = value.outerHTML.replace(/=/g, '=3D').replace(/null/g, '');
    
            } else {
                const option = getChartData(value, fileName);
                const canvas = value.getElementsByTagName('canvas')[0];
                table = getChartTable(option, index).outerHTML.replace(/=/g, '=3D').replace(/null/g, '');
                option.title = fileName;
                if (option.title) {
                    sheetname = option.title;
                } else {
                    sheetname = document.getElementsByClassName('w-card-head-title')[0].textContent + index;
                }
                let picInfo = value.getElementsByTagName('canvas')[0].toDataURL(['image/png', 1]).slice(22);
                let picDate = '\r\n' +
                    '\r\n------WIND----' +
                    '\r\nContent-Transfer-Encoding: base64' +
                    '\r\nContent-Location: file:///C:/MimeExcel/picture' + index +
                    '\r\nContent-Type: image/png' +
                    '\r\n' +
                    '\r\n' + picInfo;
                picsDate = picsDate + picDate;
            }
            // 单页头信息
            let sheetInfo = '\r\n<x:ExcelWorksheet>' +
                '\r\n<x:Name>' + sheetname + '</x:Name>' +
                '\r\n<x:WorksheetSource HRef=3D"MimeExcel/sheet' + index + '"/>' +
                '\r\n</x:ExcelWorksheet>';
    
            //单页数据信息
            let sheetDate = '\r\n' +
                '\r\n------WIND----' +
                '\r\nContent-Location: file:///C:/MimeExcel/sheet' + index +
                '\r\nContent-Transfer-Encoding: quoted-printable' +
                '\r\nContent-Type: text/html; charset="utf-8"' +
                '\r\n' +
                '\r\n<html xmlns:o=3D"urn:schemas-microsoft-com:office:office"xmlns:x=3D"urn:schemas-microsoft-com:office:excel"xmlns=3D"http://www.w3.org/TR/REC-html40">' +
                '\r\n' +
                '\r\n<head>' +
                '\r\n <meta http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8">' +
                '\r\n<xml>' +
                '\r\n<x:WorksheetOptions>' +
                '\r\n<x:ProtectContents>False</x:ProtectContents>' +
                '\r\n<x:ProtectObjects>False</x:ProtectObjects>' +
                '\r\n<x:ProtectScenarios>False</x:ProtectScenarios>' +
                '\r\n</x:WorksheetOptions>' +
                '\r\n</xml>' +
                '\r\n</head>' +
                '\r\n<body>' +
                '\r\n<table style=3D"width: 1000px">' +
                table +
                '\r\n</table>' +
                '\r\n</body>' +
                '\r\n</html>';
            //组合成多页
            sheetsInfo = sheetsInfo + sheetInfo;
            sheetsDate = sheetsDate + sheetDate;
        });
        //MIME框架模板
        let template = 'MIME-Version: 1.0' +
            '\r\nX-Document-Type: Workbook' +
            '\r\nContent-Type: multipart/related; boundary="----WIND----"' +
            '\r\n' +
            '\r\n------WIND----' +
            '\r\nContent-Location: file:///C:/MimeExcel.xml' +
            '\r\nContent-Transfer-Encoding: quoted-printable' +
            '\r\nContent-Type: text/html; charset="utf-8"' +
            '\r\n' +
            '\r\n<html xmlns:o=3D"urn:schemas-microsoft-com:office:office"xmlns:x=3D"urn:schemas-microsoft-com:office:excel"xmlns=3D"http://www.w3.org/TR/REC-html40">' +
            '\r\n' +
            '\r\n<head>' +
            '\r\n <meta http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8">' +
            '\r\n  <xml>' +
            '\r\n   <x:ExcelWorkbook>' +
            '\r\n    <x:ExcelWorksheets>' +
            sheetsInfo +
            '\r\n    </x:ExcelWorksheets>' +
            '\r\n   </x:ExcelWorkbook>' +
            '\r\n  </xml>' +
            '\r\n</head>' +
            '\r\n</html>' +
            sheetsDate +
            picsDate +
            '\r\n' +
            '\r\n------WIND------';
        return template;
    }
    /**
     * 获取echarts配置
     * @param {element} chartsDiv echarts所在DIV
     */
    const getChartData = (chartsDiv, fileName) => {
        let option = {};
        let myChart = echarts.init(chartsDiv);
        let chartOption = myChart.getOption();
        if (chartOption) {
            option.title = fileName;
            option.xAxisData = chartOption.xAxis[0].data;
            option.series = [];
            chartOption.series.map((value) => {
                let seriesData = {};
                seriesData.name = value.name;
                seriesData.data = value.data;
                option.series.push(seriesData);
            });
        } else {
            option = null;
        }
        return option;
    };
    
    
    const getChartTable = (option, picIndex) => {
        const tableData = store.getState().mindMap.echartsData.contents;
        const mindmapConfig = store.getState().mindMap.echartsConfig.contents[0].jsonDef;
        let tableOption = mindmapConfig.tableOption;
        let table = document.createElement('table');
        table.innerHTML = `<thead><tr style="height:450px"><td colspan="20"><img src="picture${picIndex}"/></td></tr><tr class="series"><th>日期</th></tr></thead><tbody></tbody>`;  
        for(let item in tableOption){
            let th = document.createElement('th');
            th.innerText = tableOption[item].title;
            table.getElementsByClassName('series')[0].appendChild(th);
        }
        let t = document.createElement('tbody');
        let dataParam = mindmapConfig.dataParam;
    
        // let tempUrl = this.props.location.search;
        // let frequency = getQueryString(tempUrl, "frequency");
        let frequency = 'day';
        tableData.sort((a, b) => {
            return b.d.toString() - a.d.toString();
        });
        //change frequency...
        let _findS3 = (tdata, index, value) => {
            let _s3 = value;
            let monthDay = moment(tableData[index].d.toString().substring(0, 6), "YYYYMM").daysInMonth();
            if (!_s3) {
                for (let k = index; k < index + monthDay; ++k) {
                    if (tableData[k].v.S3) {
                        _s3 = tableData[k].v.S3;
                        break;
                    }
                }
            }
            return _s3;
        }
    
        let _getDateFormat = (datestr, frequency) => {
            let res = '';
            switch (frequency) {
                case 'month' || 'quarter':
                    res = moment(datestr).format("YYYY/MM");
                    break;
                case 'week' || 'day':
                    res = moment(datestr).format("YYYY/MM/DD");
                    break;
                default:
                    res = moment(datestr).format("YYYY/MM/DD");
            }
            return res;
        }
    
        if (tableData.length > 0) {
            let date = '',
                s3 = 0,
                len = tableData.length,
                cday = moment().format("YYYYYMMDD");
            let _getPrevious = (passtableData) => {
                let j = 0,
                    previous = 0;
                for (j = len - 1; j >= 0; --j) {
                    if (passtableData[j].d == cday || passtableData[j].d > cday) {
                        break;
                    }
                }
                return j;
            }
            let it = [];
            let _saveTableData = (P1, cloudFlag, P2, P3) => {
                if (!P2) {
                    if (cloudFlag) { //
                        let j = 0,
                            previous = 0;
                        j = _getPrevious(tableData);
                        previous = tableData[j + 1].v.S1;
                        if(P3){
                            for (let i = 0; i < j; ++i) {
                                it = tableData[i].v;
                                if (it[P1]) {
                                    s3 = _findS3(tableData, i, it.S3);
                                    let tr = document.createElement('tr');
                                    tr.innerHTML = `<td style="text-align:center" >${moment(tableData[i].d.toString()).format('YYYY/MM/DD')}</td><td>${convertThousands(it[P1])}</td><td>${t.S2 - tableData[i + 1]}</td><td>${convertThousands(s3)}</td>`;
                                    t.appendChild(tr);
                                }
                            }
                            let tr = document.createElement('tr');
                            tr.innerHTML = `<td style="text-align:center" >${moment(tableData[j].d.toString()).format('YYYY/MM/DD')}</td><td>${convertThousands(tableData[j].v[P1])}</td><td>${tableData[j].v[P1] - previous}</td><td>${convertThousands(s3)}</td>`;
                            t.appendChild(tr);
                        }else{
                            for (let i = 0; i < j; ++i) {
                                it = tableData[i].v;
                                if (it[P1]) {
                                    s3 = _findS3(tableData, i, it.S3);
                                    let tr = document.createElement('tr');
                                    tr.innerHTML = `<td style="text-align:center" >${moment(tableData[i].d.toString()).format('YYYY/MM/DD')}</td><td>${convertThousands(it[P1])}</td><td>${NumberFormat((it.S2 - tableData[i + 1]) / tableData[i + 1] * 100, 2)}</td><td>${convertThousands(s3)}</td>`;
                                    t.appendChild(tr);
                                }
                            }
                            let tr = document.createElement('tr');
                            tr.innerHTML = `<td style="text-align:center" >${moment(tableData[j].d.toString()).format('YYYY/MM/DD')}</td><td>${convertThousands(tableData[j].v[P1])}</td><td>${NumberFormat((tableData[j].v[P1] - previous) / previous * 100, 2)}</td><td>${convertThousands(s3)}</td>`;
                            t.appendChild(tr);
                        }
                    } else {
                        tableData.map((items, index) => {
                            it = items.v;
                            if (it[P1]) {
                                s3 = _findS3(tableData, index, it.S3);
                                date = _getDateFormat(items.d.toString(), frequency);
                                let tr = document.createElement('tr');
                                tr.innerHTML = `<td style="text-align:center" >${moment(items.d.toString()).format('YYYY/MM/DD')}</td><td>${convertThousands(it[P1])}</td><td>${it.S2}</td><td>${convertThousands(s3)}</td>`;
                                t.appendChild(tr);
                            }
                        });
                    }
                } else {
                    let j = 0,
                        previous = 0;
                    j = _getPrevious(tableData);
                    previous = tableData[j + 1].v.S1;
                    let netHolding = 0;
                    let previousNetHolding = 0;
                    for (let i = 0; i < j; ++i) {
                        it = tableData[i].v;
                        if (it[P1]) {
                            s3 = _findS3(tableData, i, it.S3);
                            date = _getDateFormat(tableData[i].d.toString(), frequency);
                            netHolding = it[P1] - it[P2];
                            previousNetHolding = tableData[i + 1].v[P1] - tableData[i + 1].v[P2];
                            let tr = document.createElement('tr');
                            tr.innerHTML = `<td style="text-align:center" >${date}</td><td>${convertThousands(netHolding)}</td><td>${NumberFormat((netHolding - previousNetHolding) / previousNetHolding * 100, 2)}</td><td>${convertThousands(s3)}</td>`;
                            t.appendChild(tr);
                        }
                    }
                    netHolding = tableData[j].v[P1] - tableData[j].v[P2];
                    previousNetHolding = tableData[j + 1].v[P1] - tableData[j + 1].v[P2];
                    let tr = document.createElement('tr');
                    tr.innerHTML = `<td style="text-align:center">${tableData[j].d}</td><td>${convertThousands(netHolding)}</td><td>${NumberFormat((netHolding - previousNetHolding) / previousNetHolding * 100, 2)}</td><td>${convertThousands(s3)}</td>`;
                    t.appendChild(tr);
                }
            };
    
            let dataParams = mindmapConfig.dataParam;
            if (dataParams.edb && dataParams.cloud) { //cloud & edb request
                let cid = dataParams.cloud[0].id;
                if (cid === 'f.cf.pp2.nh') { //前五大机构持仓
                    _saveTableData("S1._moreHolding", true, "S1._emptyHolding");
                } else if (cid === 'f.cf.pp.sd') { //投机度
                    _saveTableData("S1._speculation_degree", true, '');
                } else if (cid === 'f.cf.pp.ha') { //持仓额
                    _saveTableData("S1._holding_amount", true, '');
                } else {
                    throw new Error("错误请求cloud id=" + cid);
                }
            } else {
                _saveTableData("S1", false, '');
            }
        }
        let arr = Array.from(t.querySelectorAll('tr'));
        arr.map((v, i) => {
            table.querySelectorAll('tbody')[0].appendChild(v);
        });
        return table;
    };

注意：如果发现导出的字符000463变为463，需要在在获得表格时加上：

$(table.find('td')).each(function (index, element) {
                var text = $(this).text();
                $(this).attr('x:str', '\'' + text).attr('style', 'text-align:center').empty().append(text);
            });