js表格导出xls格式-纯前端实现

我是一只小猫咪aa
已于 2023-12-21 17:00:33 修改
阅读量427
点赞数 1
文章标签： js
于 2019-08-29 17:07:46 首次发布
本文链接：https://blog.csdn.net/weixin_44523717/article/details/100141972
版权
<!DOCTYPE html>
<html>

<head>
    <meta charset="UTF-8">
    <title></title>
</head>
<style>
    .displayNone {
        /* display: none; */
    }
</style>
<script src="./jquery-1.9.0.min.js"></script>

<body>
    <table id="tbl1" class="table2excel displayNone" border="1">
        <tr>
            <td>源业务系统</td>
            <td>收付方式描述</td>
            <td>汇总项目</td>
            <td>笔数</td>
            <td>金额</td>
        </tr>
    </table>
    <hr>
    <table id="tbl2" class="table2excel displayNone" border="1">
        <tr>
            <td>表头1</td>
            <td>表头2</td>
            <td>表头3</td>
        </tr>
    </table>
    <hr>
    <table id="tbl3" class="table2excel displayNone" border="1"v>
        <tr>
            <td>表头1</td>
            <td>表头2</td>
            <td>表头3</td>
            <td>表头4</td>
        </tr>
        <tr>
            <td>Bred</td>
            <td>55</td>
            <td>32423</td>
            <td>55</td>
        </tr>
        <tr>
            <td>Butter</td>
            <td>52</td>
            <td>135</td>
            <td>16</td>
        </tr>

    </table>
    <!-- <button onclick="tablesToExcel(['tbl1','tbl2','tbl3'], ['表1','表2','表3'], 'TestBook.xls', 'Excel')">导出Excel</button> -->
    <button onclick="tablesToExcel(num1, num2, 'TestBook.xls', 'Excel')">导出Excel</button>


</body>
<script>
    $(function () {
        const value1 = [{
                "a1": "源业务系统1",
                "a2": "收付方式描述1",
                "a3": "汇总项目1",
                "a4": "笔数1",
                "a5": "金额1",
            },
            {
                "a1": "源业务系统2",
                "a2": "收付方式描述2",
                "a3": "汇总项目2",
                "a4": "笔数2",
                "a5": "金额2",
            },
            {
                "a1": "源业务系统3",
                "a2": "收付方式描述3",
                "a3": "汇总项目3",
                "a4": "笔数3",
                "a5": "金额3",
            }
        ]


        const value2 = [{
                "a1": "源业务系统1",
                "a2": "收付方式描述1",
                "a3": "开户银行1",
            },
            {
                "a1": "源业务系统2",
                "a2": "收付方式描述2",
                "a3": "开户银行2",
            },
            {
                "a1": "源业务系统3",
                "a2": "收付方式描述3",
                "a3": "开户银行3",
            }
        ]

        //    表单1
        var dom = '';
        for (i = 0; i < value1.length; i++) {
            dom += '<tr>';
            dom += '<td>' + value1[i].a1 + '</td>';
            dom += '<td>' + value1[i].a2 + '</td>';
            dom += '<td>' + value1[i].a3 + '</td>';
            dom += '<td>' + value1[i].a4 + '</td>';
            dom += '<td>' + value1[i].a5 + '</td>';
            dom += '</tr>';
        }
        $('#tbl1').append(dom);


        //    表单2
        var dom2 = '';
        for (i = 0; i < value2.length; i++) {
            dom2 += '<tr>';
            dom2 += '<td>' + value2[i].a1 + '</td>';
            dom2 += '<td>' + value2[i].a2 + '</td>';
            dom2 += '<td>' + value2[i].a3 + '</td>';
            dom2 += '</tr>';
        }
        $('#tbl2').append(dom2);
    })

    // 权限控制
    var num1 = ['tbl1','tbl2'];
    var num2 = ['表1','表2'];



    // -----------------------------------------------

    var tablesToExcel = (function () {
        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];
                })
            }
        return function (tables, wsnames, wbname, appname) {
            var ctx = "";
            var workbookXML = "";
            var worksheetsXML = "";
            var rowsXML = "";

            for (var i = 0; i < tables.length; i++) {
                if (!tables[i].nodeType) tables[i] = document.getElementById(tables[i]);
                for (var j = 0; j < tables[i].rows.length; j++) {
                    rowsXML += '<Row>'
                    for (var k = 0; k < tables[i].rows[j].cells.length; k++) {
                        var dataType = tables[i].rows[j].cells[k].getAttribute("data-type");
                        var dataStyle = tables[i].rows[j].cells[k].getAttribute("data-style");
                        var dataValue = tables[i].rows[j].cells[k].getAttribute("data-value");
                        dataValue = (dataValue) ? dataValue : tables[i].rows[j].cells[k].innerHTML;
                        var dataFormula = tables[i].rows[j].cells[k].getAttribute("data-formula");
                        dataFormula = (dataFormula) ? dataFormula : (appname == 'Calc' && dataType ==
                            'DateTime') ? dataValue : null;
                        ctx = {
                            attributeStyleID: (dataStyle == 'Currency' || dataStyle == 'Date') ?
                                ' ss:StyleID="' + dataStyle + '"' : '',
                            nameType: (dataType == 'Number' || dataType == 'DateTime' || dataType ==
                                'Boolean' || dataType == 'Error') ? dataType : 'String',
                            data: (dataFormula) ? '' : dataValue,
                            attributeFormula: (dataFormula) ? ' ss:Formula="' + dataFormula + '"' : ''
                        };
                        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);
        }
    })();
</script>

</html>
我是一只小猫咪aa
关注
1
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
js表格导出xls格式-纯前端实现

<!DOCTYPE html><html><head> <meta charset="UTF-8"> <title></title></head><style> .displayNone { /* display: none; */ }<...
复制链接

扫一扫