【前端11】excel.js的使用，规定列类型

const workbook = new ExcelJS.Workbook()
      const worksheet = workbook.addWorksheet('Sheet1')
      //添加表头
      //worksheet.addRow(['contract No.', 'Invoice Currency', 'Contract Currency', 'vendor name', 'Com Co.', 'TP', 'Amount', 'Reference', 'Document date', 'Due date', 'Service type', 'Status', 'Document number', 'Vender code', 'vendor name', 'Year', 'Text', 'Block key', 'Terms of payment', 'Baseline payment date', 'Local currency amount', 'Contact person', 'Responsible AP', 'Business Area'])
      worksheet.addRow(['contract No.', 'Currency', 'Contract Currency', 'Vendor Name', 'Company Code', 'Trading partner', 'Total Amount', 'Reference', 'Posting Date', 'Due date for net payment period', 'Service type', 'Status', 'Document Number', 'Vender code', 'Year', 'Text', 'PBk', 'Terms of Payment Key', 'Bline Date', 'Loc.curr.amount', 'Contact person', 'Responsible AP', 'BusA'])


      // 添加数据
      dataToExport.value.forEach((item: any) => {
        worksheet.addRow([item.contractNum, item.invoiceCurrency, item.ContractCurrency, item.vendorName, item.companyCode, item.tradingPartner, item.amount, item.reference, excelDate(item.documentDate), excelDate(item.dueDate), item.invoiceServiceType, item.currentStatus, item.documentNum, item.venderCode, item.year, item.text, item.blockKey, item.termsOfPayment, excelDate(item.baselinePaymentDate), item.
          localCurrencyAmount, item.contactPerson, item.responsibleAP, item.business_area])
      })
      // 设置数值格式和文本格式
      const numFmtColumns = ['G', 'T']; // 列索引，需要设置数值格式的列

      numFmtColumns.forEach((colIndex) => {
        worksheet.getColumn(colIndex).eachCell((cell) => {
          if (!isNaN(cell.value)) { // 只处理数值类型的单元格
            cell.numFmt = '#,##0.00';
          }
        });
      });
      // 将列 m设置为文本格式
      const textColumns = ['M']; // 列索引，需要设置为文本格式的列

      textColumns.forEach((colIndex) => {
        worksheet.getColumn(colIndex).eachCell((cell) => {
          cell.value = cell.value.toString(); // 将值转换为字符串确保是文本类型
          cell.numFmt = '@'; // 设置为文本格式
        });
      });
      // 生成文件
      workbook.xlsx.writeBuffer().then((buffer: any) => {
        const blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' })
        const link = document.createElement('a')
        link.href = URL.createObjectURL(blob)
        link.download = 'invoice_information.xlsx'
        link.click()
        URL.revokeObjectURL(link.href)
      })