js导出复杂表头(多级表头)的excel

<!DOCTYPE html><html lang="en"><head> <meta charset="UTF-8"> <title>Document</title></head><body> <table border> <tr style="text-align: center;"> <th rowspan="2">姓名</th> <th colspan="2">一月</th> <th colspan="2">二月</th> </tr> <tr style="text-align: center;"> <th>收入</th> <th>支出</th> <th>收入</th> <th>支出</th> </tr> <tr style="text-align: center;"> <td>张三</td> <td>10元</td> <td>20元</td> <td>15元</td> <td>25元</td> </tr> <tr style="text-align: center;"> <td>李四</td> <td>100元</td> <td>200元</td> <td>150元</td> <td>250元</td> </tr> <table> <button onclick="tableToExcel()"">导出excel</button> <script type="text/javascript"> function tableToExcel(){ //要导出的数据 var exportData = [ { name: 张三, month1: { income: 10元, outlay: 20元 }, month2: { income: 15元, outlay: 25元 } }, { name: 李四, month1: { income: 100元, outlay: 200元 }, month2: { income: 150元, outlay: 250元 } } ] // 自定义的表格 var tableStr = ` <tr style="text-align: center;"> <th rowspan="2">姓名</th> <th colspan="2">一月</th> <th colspan="2">二月</th> </tr> <tr style="text-align: center;"> <th>收入</th> <th>支出</th> <th>收入</th> <th>支出</th> </tr>`; for(let item of exportData) { tableStr += `<tr style="text-align: center;"> <td>${item.name}</td> <td>${item.month1.income}</td> <td>${item.month1.outlay}</td> <td>${item.month2.income}</td> <td>${item.month2.outlay}</td> </tr>`; } //Worksheet名 var worksheet = Sheet1 var uri = data:application/vnd.ms-excel;base64,; // 真正要导出(下载)的HTML模板 var exportTemplate = `<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"> <head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet> <x:Name>${worksheet}</x:Name> <x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet> </x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--> </head> <body> <table syle="table-layout: fixed;word-wrap: break-word; word-break: break-all;">${tableStr}</table> </body> </html>`; //下载模板 window.location.href = uri + base64(exportTemplate) }; //输出base64编码 function base64 (s) { return window.btoa(unescape(encodeURIComponent(s))) }; </script></body></html>

ps:要想修改导出的excel表中表格的格式,可直接在上面的模板中给相应的table、tr、td、th等标签添加css样式即可。

相关文章