前端xlsx-populate导出excel表格

// xlsx免费版功能局限可以尝试使用xlsx-populate
import {fromBlankAsync} from "xlsx-populate"
const data = [
    [],
    [, 5, 6],
    [, 8, 9]
]
    
function getXlsxBlob(data, sheetName) {
    return fromBlankAsync().then(workbook => {
        let maxColumnNumber = 0
        data.forEach(_ => {
            if(_.length > maxColumnNumber) maxColumnNumber = _.length
        })
        // range(startRowNumber, startColumnNameOrNumber, endRowNumber, endColumnNameOrNumber)
        workbook.sheet(0).name(sheetName).range(1, 1, data.length, maxColumnNumber).value(data)
  
        const dataValid = workbook.addSheet('sheet2');
        dataValid.range(1,1,3,1).value([
            [1],
            [4],
            [7]
        ])

        workbook.sheet(0).range(1,1,3,1).dataValidation({
          type: 'list',
          allowBlank: false,
          showInputMessage: false,
          prompt: false,
          promptTitle: 'String',
          showErrorMessage: true,
          error: '其他用户已经限定了可以输入该单元格的数值。',
          errorTitle: '输入值非法。',
          operator: 'String',
          formula1: 'sheet2!$A:$A',//Required
          formula2: 'String'
        })
        
        workbook.sheet(0).range(1,1,1,3).merged(true)
        
        return workbook.outputAsync()
    })
}

function createDownLoadData(data, sheetName, filename) {
  getXlsxBlob(data, sheetName)
      .then(blobData => {
        let url = window.URL.createObjectURL(new Blob([blobData]));
        let link = document.createElement("a");
        link.style.display = "none";
        link.href = url;
        link.setAttribute("download", filename);
        document.body.appendChild(link);
        link.click();
        document.body.removeChild(link);
        window.URL.revokeObjectURL(url);
      })
}

function handleExport() {
  createDownLoadData(data, "前端测试", "前端导出的表格.xlsx")
}