封装笔记：JS 导出 Excel 文件

TIP

基于 xlsx 做了封装，更易于使用。

typing.ts

import type { WritingOptions } from "xlsx";

export interface ExcelData<T = any> {
  header: string[];
  results: T[];
  meta: { sheetName: string };
}

export interface AoAToSheet<T = any> {
  data: T[][];
  header?: T[];
  filename?: string;
  sheetName?: string;
  write2excelOpts?: WritingOptions;
}

export interface AoaToMultipleSheet<T = any> {
  sheetList: AoAToSheet<T>[];
  filename?: string;
  write2excelOpts?: WritingOptions;
}

Export2Excel.ts

import type { WorkBook } from "xlsx";
import * as xlsx from "xlsx";
import type { AoAToSheet } from "./typing";
import { AoaToMultipleSheet } from "./typing";

const { utils, writeFile } = xlsx;

const DEF_FILE_NAME = "excel-list.xlsx";
const DEF_SHEET_NAME = "sheet";

export function aoaToSheetXlsx<T = any>({ data, header, filename = DEF_FILE_NAME, write2excelOpts = { bookType: "xlsx" } }: AoAToSheet<T>) {
  const arrData = [...data];
  if (header) {
    arrData.unshift(header);
  }

  const worksheet = utils.aoa_to_sheet(arrData);

  /* add worksheet to workbook */
  const workbook: WorkBook = {
    SheetNames: [filename],
    Sheets: {
      [filename]: worksheet,
    },
  };
  /* output format determined by filename */
  writeFile(workbook, filename, write2excelOpts);
  /* at this point, out.xlsb will have been downloaded */
}

/**
 * aoa导出多Sheet的Xlsx
 * @param sheetList 多sheet配置
 * @param filename 文件名(包含后缀)
 * @param write2excelOpts 文件配置
 */
export function aoaToMultipleSheetXlsx<T = any>({
  sheetList,
  filename = DEF_FILE_NAME,
  write2excelOpts = { bookType: "xlsx" },
}: AoaToMultipleSheet<T>) {
  const workbook: WorkBook = {
    SheetNames: [],
    Sheets: {},
  };
  sheetList.forEach((p, index) => {
    const arrData = [...p.data];
    if (p.header) {
      arrData.unshift(p.header);
    }
    const worksheet = utils.aoa_to_sheet(arrData);

    p.sheetName = p.sheetName || `${DEF_SHEET_NAME}${index}`;
    workbook.SheetNames.push(p.sheetName);
    workbook.Sheets[p.sheetName] = worksheet;
  });
  /* output format determined by filename */
  writeFile(workbook, filename, write2excelOpts);
  /* at this point, out.xlsb will have been downloaded */
}

调用示例

import PageEditor from "@/components/PageEditor/PageEditor.vue";
import { aoaToSheetXlsx, aoaToMultipleSheetXlsx } from "@/components/Excel/Export2Excel";

function doExport() {
  // aoaToSheetXlsx({
  //   data: [["数据1", "数据2"]],
  //   header: ["表头1", "表头2"],
  // });
  aoaToMultipleSheetXlsx({
    sheetList: [
      {
        data: [["数据1", "数据2"]],
        header: ["表头1", "表头2"],
        sheetName: "111",
      },
      {
        data: [["22222数据1", "222222数据2"]],
        header: ["表头1", "表头2"],
        sheetName: "222",
      },
    ],
    filename: "测试多sheet导出.xlsx",
  });
}