xlsx导出excel的时候,导出数值类型

4 阅读1分钟

`js

const exportDataHandler = (name: string, tableNameId: string): void => {
  const xlsxParam: XLSX.Table2SheetOpts = {raw: true};
  // 获取el-table的列宽度
  const tables: HTMLElement | null = document.getElementById(tableNameId) as HTMLElement;
  const colWidths: number[] = [];
  if (tables) {
    const ths: NodeListOf<HTMLTableCellElement> | null = tables.querySelectorAll(".el-table__header-wrapper th");
    if (ths) {
      for (let i = 0; i < ths.length; i++) {
        const cellWidth: number = ths[i].getBoundingClientRect().width - 2;
        colWidths.push(cellWidth);
      }
    }
  }
  // 克隆节点
  const tablesClone: HTMLElement | null = tables?.cloneNode(true) as HTMLElement;
  if (tablesClone?.querySelector(".el-table__fixed") !== null) {
    tablesClone.removeChild(tablesClone.querySelector(".el-table__fixed") as HTMLElement);
  }
  // 将克隆得到的表格转换成sheet
  const sheet: XLSX.WorkSheet = XLSX.utils.table_to_sheet(tablesClone, xlsxParam);

  // 根据el-table的列宽度设置列宽
  const headerRange: XLSX.Range = XLSX.utils.decode_range(sheet["!ref"]?.toString() || "");
  for (let i = headerRange.s.c; i <= headerRange.e.c; i++) {
    const colWidth: number = colWidths[i];
    const colWidthPx: number = colWidth / 7; // 将像素转换成字符宽度,7是一个字符的平均宽度
    const colWidthExcel: number = colWidthPx > 255 ? 255 : colWidthPx; // 列宽不能超过255
    sheet["!cols"] = sheet["!cols"] || [];
    sheet["!cols"][i] = {width: colWidthExcel}; // 设置列宽
  }
  // 设置数值类型
  const colCount = sheet["!cols"].length
  const rangeNum = XLSX.utils.decode_range(sheet['!ref']?.toString() || "");
  for(let i = 3; i < colCount; i++) {
    for (let R = rangeNum.s.r+1; R <=rangeNum.e.r; ++R) { //跳过不要的行数据
      const cell_addr = XLSX.utils.encode_cell({ c:i, r: R})
      if(sheet[cell_addr] &&!isNaN(sheet[cell_addr].v)){
        sheet[cell_addr].t = 'n'
      }
    }
  }

  // 将sheet转换成workbook
  const book: XLSX.WorkBook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(book, sheet, "Sheet1");
  // 将workbook写入array buffer,并保存为excel文件
  const arrayBuffer: ArrayBuffer = XLSX.write(book, {bookType: "xlsx", bookSST: true, type: "array"});
  try {
    FileSaver.saveAs(new Blob([arrayBuffer], {type: "application/octet-stream"}), name);
  } catch (error) {
    console.log(error, arrayBuffer);
  }
};

`