jquery实现导出Excel功能，支持多sheets导出

2021-09-25 阅读：1799 分类：前端标签： jquery

前端项目开发中，我们有时需要将表格数据完整的导出来，而且保留原有样式，这样的如果用后端导出会比较麻烦，我们可以采用jquery来导出。

1、比如有入如下几个表格

 <button type="button" class="layui-btn" onclick="tablesToExcel('#table_1,#table_2,#table_3,#table_4,#table_5', '客户评分汇总.xls');" >导出</button>
<table id="table_1" data-SheetName="表格一" >
  <thead>
    <tr style="background:#008080; color:#fff; font-weight:bold; height:50px;">
      <th width="60px" style="border:1px solid #e6e6e6;" >序号</th>  
      <th width="100px" style="border:1px solid #e6e6e6;">名称</th> 
      <th width="100px" style="border:1px solid #e6e6e6;">时间</th> 
    </tr>
  </thead>
  <tbody>
   <tr>
    <td>1</td>
    <td>PHP</td>
    <td>2021-09-25</td>
  </tr>
   <tr>
    <td>2</td>
    <td>JavaScript</td>
    <td>2021-09-25</td>
  </tr>
  </tbody>
</table>
<table id="table_2" data-SheetName="表格二" >
  <thead>
    <tr style="background:#008080; color:#fff; font-weight:bold; height:50px;">
      <th width="60px" style="border:1px solid #e6e6e6;" >序号</th>  
      <th width="100px" style="border:1px solid #e6e6e6;">名称</th> 
      <th width="100px" style="border:1px solid #e6e6e6;">时间</th> 
    </tr>
  </thead>
  <tbody>
   <tr>
    <td>1</td>
    <td>Java</td>
    <td>2021-09-25</td>
  </tr>
   <tr>
    <td>2</td>
    <td>Python</td>
    <td>2021-09-25</td>
  </tr>
  </tbody>
</table>

2、导出js代码

<script src="https://libs.baidu.com/jquery/2.1.1/jquery.min.js"></script>
<script type="text/javascript">
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=windows-1252

` + 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">
  <style type="text/css">
   table td,table th {
   border: 1px solid #ddd;
   min-height: 40px;
   line-height:40px;
   padding-left:5px;
   padding-right:5px;  
   text-align:center;  
   overflow: hidden;
   font-size:16px;
   padding-top:5px;
   padding-bottom:5px;
   font-family: '微软雅黑';
   width:100%;
  }
  </style>
</head>
<body><table>{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=windows-1252

` + 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: ''
    };
    var tables = jQuery(tables);
    $.each(tables,function(SheetIndex){
      var $table = $(this);
      var SheetName = $table.attr('data-SheetName');
      if($.trim(SheetName) === ''){
        SheetName = 'Sheet' + SheetIndex;
      }
      context_WorkBook.ExcelWorksheets += format(template_ExcelWorksheet, {
        SheetIndex: SheetIndex
      ,  SheetName: SheetName
      });
      context_WorkBook.HTMLWorksheets += format(template_HTMLWorksheet, {
        SheetIndex: SheetIndex
      ,  SheetContent: $table.html()
      });
      context_WorkBook.ListWorksheets += format(template_ListWorksheet, {
        SheetIndex: SheetIndex
      });
    });

    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);
  }
})(jQuery);
</script>

上面就可以把网页中的表格，安装样式导出来，也支持自定义样式，样式在上面的js中编写。