jquery2excel 导出多个excel

概述

Jquery multiple table to excel
jquery2table 是将当前页面的table导出excel，需求是这样的：当前页面有多个table表，需要导出所有的table到一个excel文件中，多个table分成多个sheet表。该需求可以运用phpexcel技术实现，但是由于当前页面，不涉及分页，所以就想省略对服务器的请求直接当前页面导出

实现过程

1. JS代码

   /**
    * Created by kxk on 2016/9/19.
    */
   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);
       }
   })();
   

2. HTML代码

   <table id="tbl1" class="table2excel">
   	<tr>
   		<td>a</td>
   		<td>b</td>
   		<td>c</td>
   	</tr>
   	<tr>
   		<td>aa</td>
   		<td>bb</td>
   		<td>cc</td>
   	</tr>
   	<tr>
   		<td>aaa</td>
   		<td>bbb</td>
   		<td>ccc</td>
   	</tr>
   	<tr>
   		<td>aaaa</td>
   		<td>bbbb</td>
   		<td>cccc</td>
   	</tr>
   </table>
   <hr>
   <table id="tbl2" class="table2excel">
   	<tr>
   		<td>A</td>
   		<td>B</td>
   		<td>C</td>
   		<td>D</td>
   	</tr>
   	<tr>
   		<td>AA</td>
   		<td>BB</td>
   		<td>CC</td>
   		<td>DD</td>
   	</tr>
   </table>
   <button  onclick="tablesToExcel(['tbl1','tbl2'], ['ProductDay1','ProductDay2'], 'TestBook.xls', 'Excel')">Export to Excel</button>
   

3. 具体调用说明：
   1. tablesToExcel() 通过该JS方法调用
   2. 第一个参数：数组，里面是两个table的id属性名
   3. 第二个参数：数组，数组长度和第一个参数长度相同，里面的元素对应excel中表sheet名
   4. 第三个参数：字符串，导出的excel文件名
   5. 第四个参数：导出的文件格式

参考资料

http://stackoverflow.com/questions/29698796/how-to-convert-html-table-to-excel-with-multiple-sheet