将表格数据导出 Excel 格式的数据方法（2）

最新推荐文章于 2024-05-16 11:28:07 发布

枫沂

最新推荐文章于 2024-05-16 11:28:07 发布

阅读量233

点赞数 2

文章标签： excel 前端 javascript

本文链接：https://blog.csdn.net/qq_42556623/article/details/136900646

版权

1、在工具类中创建一个脚本文件

/**
 * @param {*} tableValueData
 *      表格数据: []
 * @param {*} tableTitleData
 *      表格标题数据: ["", "", "", ""]
 * @param {*} fileName
 *      文件名
 * @param {*} tableHeader
 *      表格名
 */

// 引入插件
import XLSXS from "xlsx-js-style";
// 暴露方法
export function exportExcel(tableValueData, tableTitleData, fileName, tableHeader) {
  const header = [];
  const body = [];
  let strMaxNum = {};
  tableValueData.forEach((e, i) => {
    let styleArr = [];
    e.forEach((ele, ei) => {
      if (!ele) ele = 0;
      let styleObj = {
        v: ele,
        t: "s",
        s: {
          alignment: {
            vertical: "center",
            horizontal: "center",
          },
          border: {
            top: { style: "thin" },
            left: { style: "thin" },
            bottom: { style: "thin" },
            right: { style: "thin" },
          },
        },
      };
      styleArr.push(styleObj);
      strMaxNum[ei] = strMaxNum[ei]
        ? strMaxNum[ei] > ele.toString().length
          ? strMaxNum[ei]
          : ele.toString().length
        : ele.toString().length;
    });
    body.push(styleArr);
  });
  let cols = [];
  let title1 = [];
  tableTitleData.forEach((item, index) => {
    let obj = {
      v: item,
      t: "s",
      s: {
        alignment: {
          vertical: "center",
          horizontal: "center",
        },
        border: {
          top: { style: "thin" },
          left: { style: "thin" },
          bottom: { style: "thin" },
          right: { style: "thin" },
        },
      },
    };
    title1.push(obj);
    let resultMaxNum = Number(item.length) > strMaxNum[index] ? Number(item.length) : strMaxNum[index];
    let colsObj = { wch: resultMaxNum * 2 + 5 };
    cols.push(colsObj);
    // 设置标题
    if (index == 0) {
      header.push({
        v: tableHeader,
        t: "s",
        s: {
          font: {
            bold: true,
            sz: 14,
            name: "宋体",
          },
          alignment: {
            vertical: "center",
            horizontal: "center",
          },
          border: {
            top: { style: "thin" },
            left: { style: "thin" },
            bottom: { style: "thin" },
            right: { style: "thin" },
          },
        },
      });
    } else {
      header.push({
        v: '',
        t: "s",
        s: {
          font: {
            bold: true,
            sz: 14,
            name: "宋体",
          },
          alignment: {
            vertical: "center",
            horizontal: "center",
          },
          border: {
            top: { style: "thin" },
            left: { style: "thin" },
            bottom: { style: "thin" },
            right: { style: "thin" },
          },
        },
      });
    }
  });
  body.unshift(title1);
  body.unshift(header);
  const sheet = XLSXS.utils.aoa_to_sheet(body);

  sheet["!cols"] = cols;
  const rows = [{ hpx: 20 }, { hpx: 16 }, { hpx: 18 }];
  sheet["!rows"] = rows;

  let mergeNum = tableTitleData.length - 1;
  const merges = [{ s: { r: 0, c: 0 }, e: { r: 0, c: mergeNum } }];
  sheet["!merges"] = merges; // 将merges添加到sheet中

  // 导出Excel文件
  const workbook = XLSXS.utils.book_new(); // 创建虚拟的 workbook
  XLSXS.utils.book_append_sheet(workbook, sheet, tableHeader); // 向 workbook 中添加 sheet
  XLSXS.writeFile(workbook, `${fileName}.xlsx`); // 导出 workbook
}