xlsx导出 自适应宽度

前提

npm i xlsx

// 导出列表
const exportPreviewIp = () => {
  const result = state.data.map(item => ({
    实例ID: item.instanceId,
    uuid: item.uuid,
    地区: item.region,
    创建时间: item.createdTime,
    关机时间: item.terminatedTime,
  }));
  const worksheet = XLSX.utils.json_to_sheet(result);

  // 表头的样式
  worksheet["A1"].s = {
    font: {
      bold: true,
    },
    alignment: {
      horizontal: "center",
      vertical: "center",
    },
  };



  // 合并单元格     s: 起始位置,   e: 结束位置,   r: 行,   c: 列
  // data["!merges"] = [{ s: { r: 0, c: 0 }, e: { r: 0, c: 10 } }];
  //  设置列宽
  // data["!cols"] = [{ wch: 50 }, { wch: 20 }, { wch: 40 }];

  // 1.所有表头的宽度
  const headsWidth = Object.keys(state.data[0]).map((value) => {
    if (/.*[\u4e00-\u9fa5]+.*$/.test(value)) {
      return parseFloat(value.toString().length * 2.1);
    } else {
      return parseFloat(value.toString().length * 1.1)
    }
  });
  // console.log("所有表头的宽度：", headsWidth);

  // 2.所有表体值的宽度
  const rowsWidth = state.data.map((item) => {
    // 每行数据中值的宽度
    const maxValue = Object.values(item).map((value, index) => {
      let valueWidth;
      if (/.*[\u4e00-\u9fa5]+.*$/.test(value)) {
        valueWidth = parseFloat(value.toString().length * 2.1);
      } else {
        valueWidth = parseFloat(value.toString().length * 1.1);
      }
      // console.log("每行数据中值的宽度：", valueWidth);

      // 对比出表头和表体值的最大数
      return Math.max(valueWidth, headsWidth[index]);
    });
    // console.log("本行值中最大宽度：", maxValue);
    return maxValue;
  })
  // console.log("每行数据对比出的最大宽度：", rowsWidth);

  // 3.对比每列最大值
  let aotuWidth = []
  rowsWidth.map((row, index) => {
    let maxWidth = [];
    row.map((value, i) => {
      if (index === 0) {
        maxWidth.push({
          wch: value
        });
      } else {
        maxWidth.push({
          wch: Math.max(value, aotuWidth[i].wch)
        })
      }
    })
    aotuWidth = maxWidth;
  });
  // console.log("每列最大宽度：", aotuWidth);

  // 4.给excel设置自适应宽度
  worksheet["!cols"] = aotuWidth;


  const workbook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');
  const binaryWorkbook = XLSX.write(workbook, { type: 'binary', bookType: 'xlsx' });
  const blob = new Blob([s2ab(binaryWorkbook)], { type: 'application/octet-stream' });
  const link = document.createElement('a');
  link.href = window.URL.createObjectURL(blob);
  link.download = '实例列表.xlsx';
  document.body.appendChild(link);
  link.click();
  document.body.removeChild(link);
};

const s2ab = (binaryString) => {
  const buffer = new ArrayBuffer(binaryString.length);
  const view = new Uint8Array(buffer);
  for (let i = 0; i < binaryString.length; i++) {
    view[i] = binaryString.charCodeAt(i) & 0xFF;
  }
  return buffer;
};