In the work, there are two solutions to the request to export the page table to Excel. One is to export the TableDOM to Excel by traversing the tableDOM, but to organize the table data export. Now the second solution is recorded:

1. Organize the JSON data to be exported

const jsonData = [
    { name: '001'.id: '621699190001011231' },
    { name: '002'.id: '52069919000101547X' },
    { name: '003'.id: '423699190103015469' },
    { name: '004'.id: '341655190105011749'}]Copy the code

2. Convert the JSON data to table format before exporting

/ / column headings
const str = '<tr><td>name</td><td>id</td></tr>'
// Iterate over specific values
jsonData.forEach((e,i) = > {
    str += '<tr>'
    for (let item in jsonData[i]) {
      var cellvalue = jsonData[i][item]
      str += `<td style="mso-number-format:'\@';" >${cellvalue}</td>`
      // str+=`<td>${cellvalue}</td>`;
    }
    str += '</tr>'
 })
 const worksheet = 'Export result'
 const uri = 'data:application/vnd.ms-excel; base64,'
Copy the code

Note: If the exported table has long grey display method or other formats, the following methods can be used to solve the problem

  1. Tr in itstyle="mso-number-format:'\@';"
  2. Change it to string
const reg = / ^ [0-9] +.? [0-9] * $/;
if ((cellvalue.length>15) && (reg.test(cellvalue))){
    cellvalue = '=" + cellvalue + '"';
 }
Copy the code

3. Downloaded table template data

const 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]--> </head><body><table>${str}</table></body></html>`Copy the code

4. Download the template

function base64(s) {
    return window.btoa(unescape(encodeURIComponent(s)))
  }
window.location.href = uri + base64(template)
Copy the code