js table表格导出到excel 导出一个工作簿多个sheet

大前端

js table表格导出到excel 导出一个工作簿多个sheet

2021-07-12 · 3793

网上找的源码，测试可以正确导出。

var tablesToExcel = (function() {
    var uri = "data:application/vnd.ms-excel;base64,",
        html_start =
        `<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">`,
        template_ExcelWorksheet =
        `<x:ExcelWorksheet><x:Name>{SheetName}</x:Name><x:WorksheetSource HRef="sheet{SheetIndex}.htm"/></x:ExcelWorksheet>`,
        template_ListWorksheet = `<o:File HRef="sheet{SheetIndex}.htm"/>`,
        template_HTMLWorksheet =
        `
------=_NextPart_dummy
Content-Location: sheet{SheetIndex}.htm
Content-Type: text/html; charset=utf-8

` + html_start + `
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <link id="Main-File" rel="Main-File" href="../WorkBook.htm">
    <link rel="File-List" href="filelist.xml">
</head>
<body><table  border="1">{SheetContent}</table></body>
</html>`,
        template_WorkBook =
        `MIME-Version: 1.0
X-Document-Type: Workbook
Content-Type: multipart/related; boundary="----=_NextPart_dummy"

------=_NextPart_dummy
Content-Location: WorkBook.htm
Content-Type: text/html; charset=utf-8

` + html_start + `
<head>
<meta name="Excel Workbook Frameset">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<link rel="File-List" href="filelist.xml">
<!--[if gte mso 9]><xml>
 <x:ExcelWorkbook>
    <x:ExcelWorksheets>{ExcelWorksheets}</x:ExcelWorksheets>
    <x:ActiveSheet>0</x:ActiveSheet>
 </x:ExcelWorkbook>
</xml><![endif]-->
</head>
<frameset>
    <frame src="sheet0.htm" name="frSheet">
    <noframes><body><p>This page uses frames, but your browser does not support them.</p></body></noframes>
</frameset>
</html>
{HTMLWorksheets}
Content-Location: filelist.xml
Content-Type: text/xml; charset="utf-8"

<xml xmlns:o="urn:schemas-microsoft-com:office:office">
    <o:MainFile HRef="../WorkBook.htm"/>
    {ListWorksheets}
    <o:File HRef="filelist.xml"/>
</xml>
------=_NextPart_dummy--
`,
    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, filename) {
        var context_WorkBook = {
            ExcelWorksheets: "",
            HTMLWorksheets: "",
            ListWorksheets: ""
        };
        tables.forEach((item, index) => {
            var SheetName = item.name;
            context_WorkBook.ExcelWorksheets += format(template_ExcelWorksheet, {
                SheetIndex: index,
                SheetName: SheetName
            });
            context_WorkBook.HTMLWorksheets += format(template_HTMLWorksheet, {
                SheetIndex: index,
                SheetContent: item.html
            });
            context_WorkBook.ListWorksheets += format(template_ListWorksheet, {
                SheetIndex: index
            });
        });
        var link = document.createElement("A");
        link.href = uri + base64(format(template_WorkBook, context_WorkBook));
        link.download = filename || "Workbook.xls";
        link.target = "_blank";
        document.body.appendChild(link);
        link.click();
        document.body.removeChild(link);
    };
})();
var sheet_1 = `<thead><tr><th>这是sheet1的表头</th></tr><thead><tbody><tr><td>这是sheet1的表体</td></tr><tbody>`;
var sheet_2 = `<thead><tr><th>这是sheet2的表头</th></tr><thead><tbody><tr><td>这是sheet2的表体</td></tr><tbody>`;
var sheets = [{
        name: "合并详情",
        html: sheet_1
    },
    {
        name: "发票详情",
        html: sheet_2
    }
];
tablesToExcel(sheets, "导出的excel");

注意：不要对此代码做格式化！！！
格式化后就废了。
如果想加入style 就在 head 内加入css代码

...
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <link id="Main-File" rel="Main-File" href="../WorkBook.htm">
    <link rel="File-List" href="filelist.xml">
    <style>/*此处加入css代码*/</style>
</head>
...