前端导出表格的几种方法

前言：
最近工作上有这个需求，然后就上网查了下，搜集一下几种方法

一，单表格下载(xlsx)

安装依赖
1
npm install xlsx file-saver --save
引入

1 2	import FileSaver from 'file-saver' import XLSX from 'xlsx'

download() {
   //此处传入table的DOM节点
  let et = XLSX.utils.table_to_book(document.getElementById("outtable"));
  let etout = XLSX.write(et, {
    bookType: "xlsx",
    bookSST: true,
    type: "array"
  });
  try {
      FileSaver.saveAs(
        new Blob([etout], {
          type: "application/octet-stream"
        }),
        `${this.search_val}.xlsx`
      ); //trade-publish.xlsx 为导出的文件名
  } catch (e) {
    console.log(e, etout);
  }
  return etout;
},

二，单表格下载+样式

var dom1 = document.querySelector('#Table1')
     tableToExcel(dom1, "统计表");
     //替换table数据和worksheet名字
     function format(s, c) {
       return s.replace(/{(\w+)}/g, function(m, p) {
         return c[p];
       });
     }
     function base64(s) {
       return window.btoa(unescape(encodeURIComponent(s)));
     }

     function tableToExcel(tableid, sheetName) {
       var uri = "data:application/vnd.ms-excel;base64,";
       var template =
         '<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]-->" +
         ' <style type="text/css">' +
         "table {" +
         "border: .5pt solid black;" +
         " }" +
         "table td {" +
         "border: .5pt solid black;" +
         "width: 150px;" +
         "height: 25px;" +
         " text-align: center;" +
         "font-size: 13px;" +
         " }" +
         "</style>" +
         '</head><body ><table class="excelTable">{table}</table></body></html>';
       var ctx = {
         worksheet: sheetName || "Worksheet",
         table: tableid.innerHTML
       };
       let a = document.createElement("a");
       //表格名字
       a.download =
         "统计表.xlsx";
       a.href = uri + base64(format(template, ctx));
       a.click();
     }

三，多表格下载（xlsx）

第一种方法：

 var workbook = XLSX.utils.book_new();

/* convert table 'table1' to worksheet named "Sheet1" */
var ws1 = XLSX.utils.table_to_sheet(document.querySelector('#Table1'));
XLSX.utils.book_append_sheet(workbook, ws1, "sheet1");

/* convert table 'table2' to worksheet named "Sheet2" */
var ws2 = XLSX.utils.table_to_sheet(document.querySelector('#Table2'));
XLSX.utils.book_append_sheet(workbook, ws2, "sheet2");

/* get binary string as output */
var wbOut = XLSX.write(workbook, {
  bookType: "xlsx",
  bookSST: true,
  type: "array"
});
try {
  FileSaver.saveAs(
          new Blob([wbOut], { type: "application/octet-stream" }),
          "Demo.xlsx"
  );
} catch (e) {
  if (typeof console !== "undefined") console.log(e, wbOut);
}
return wbout;

第二种方法：

function exp() {
       tablesToExcel(['tabDiv1', 'tabDiv2', 'tabDiv3'], ['sheet1', 'sheet2', 'sheet3'], "testExport.xls", "Excel");
   }
   //导出excel包含多个sheet
   //tables:tableId的数组;wsbames:sheet的名字数组;wbname:工作簿名字;appname:Excel
   function tablesToExcel(tables, wsnames, wbname, appname) {

       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><Style>cell{border: 1px solid red;}</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];
               })
           }

       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");
                   tables[i].setAttribute("border", "1");
                   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);
       //       查看后台的打印输出
       console.log(workbookXML);
       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);

   }