前端vue3实现导出xlsx、txt格式文件

热爱跑步的小前端

于 2025-03-26 15:35:50 发布

阅读量325

点赞数 6

文章标签：前端 vue.js

本文链接：https://blog.youkuaiyun.com/ThreeKims/article/details/146531171

版权

1.灵感

一般来说由后端写导出的格式文件，但是有的时候数据不是很多，所以前端完成小数据的导出功能会很方便，今天给大家介绍一款 xlsx 库，用于导出不同格式文件

2.逻辑代码

（1）安装

pnpm install xlsx-js-style
pnpm install file-saver

（2）导入

import * as XLSX from "xlsx-js-style"; // 导入 xlsx 库
import { saveAs } from "file-saver"; //导入生成.txt 库

（3）导出Excel表逻辑

// 模拟表数据
const printData = ref([
  {
    Ksh: 20250101,
    ks_Zkz: "20250101ZK001",
    BMH: "420101199001010001",
    XM: "张三",
    XQ: "武汉市",
    kspc: "2025-1-01",
    ksz: "A1",
    kcDm: "KC01",
    kcZb: "01",
    xh: 1,
    ErType: "数学",
    ErOneScore: 85.5,
    OneScoreTime: "2025-03-01 09:00:00",
    ErSecScore: 90.0,
    SecScoreTime: "2025-03-05 09:00:00",
    ErThrScore: 92.5,
    ThrScoreTime: "2025-03-10 09:00:00"
  },
  {
    Ksh: 20250102,
    ks_Zkz: "20250102ZK002",
    BMH: "420102199101010002",
    XM: "李四",
    XQ: "黄石市",
    kspc: "2025-1-02",
    ksz: "B2",
    kcDm: "KC02",
    kcZb: "02",
    xh: 2,
    ErType: "英语",
    ErOneScore: 88.0,
    OneScoreTime: "2025-03-02 09:00:00",
    ErSecScore: 86.5,
    SecScoreTime: "2025-03-06 09:00:00",
    ErThrScore: 87.0,
    ThrScoreTime: "2025-03-11 09:00:00"
  },
  {
    Ksh: 20250103,
    ks_Zkz: "20250103ZK003",
    BMH: "420103199201010003",
    XM: "王五",
    XQ: "荆州市",
    kspc: "2025-1-03",
    ksz: "C3",
    kcDm: "KC03",
    kcZb: "03",
    xh: 3,
    ErType: "物理",
    ErOneScore: 78.0,
    OneScoreTime: "2025-03-03 09:00:00",
    ErSecScore: 82.5,
    SecScoreTime: "2025-03-07 09:00:00",
    ErThrScore: 80.0,
    ThrScoreTime: "2025-03-12 09:00:00"
  },
  {
    Ksh: 20250104,
    ks_Zkz: "20250104ZK004",
    BMH: "420104199301010004",
    XM: "赵六",
    XQ: "宜昌市",
    kspc: "2025-1-04",
    ksz: "D4",
    kcDm: "KC04",
    kcZb: "04",
    xh: 4,
    ErType: "化学",
    ErOneScore: 90.0,
    OneScoreTime: "2025-03-04 09:00:00",
    ErSecScore: 91.0,
    SecScoreTime: "2025-03-08 09:00:00",
    ErThrScore: 92.0,
    ThrScoreTime: "2025-03-13 09:00:00"
  },
  {
    Ksh: 20250105,
    ks_Zkz: "20250105ZK005",
    BMH: "420105199401010005",
    XM: "钱七",
    XQ: "襄阳市",
    kspc: "2025-1-05",
    ksz: "E5",
    kcDm: "KC05",
    kcZb: "05",
    xh: 5,
    ErType: "生物",
    ErOneScore: 82.0,
    OneScoreTime: "2025-03-05 09:00:00",
    ErSecScore: 84.0,
    SecScoreTime: "2025-03-09 09:00:00",
    ErThrScore: 85.0,
    ThrScoreTime: "2025-03-14 09:00:00"
  }
]);

// 导出xlsx

const exportExcel = () => {
  // 列配置，第一行的表头
  const adjustedColumns = [
    { label: "考试号", prop: "ksh" },
    { label: "准考证号", prop: "ksZkz" },
    { label: "身份证号", prop: "bmh" },
    { label: "姓名", prop: "xm" },
    { label: "县区", prop: "xq" },
    { label: "考试批次", prop: "kspc" },
    { label: "考试组", prop: "ksz" },
    { label: "考场", prop: "kcDm" },
    { label: "考场组", prop: "kcZb" },
    { label: "序号", prop: "xh" },
    { label: "考试类型", prop: "erType" },
    { label: "第一次成绩", prop: "erOneScore" },
    { label: "第一次成绩时间", prop: "oneScoreTime" },
    { label: "第二次成绩", prop: "erSecScore" },
    { label: "第二次成绩时间", prop: "secScoreTime" },
    { label: "第三次成绩", prop: "erThrScore" },
    { label: "第三次成绩时间", prop: "thrScoreTime" }
  ];

  const allData: any[] = []; // 储存打印数据
  allData.push(adjustedColumns.map(col => col.label));

  printData.value.forEach(parentItem => {
    // 数据顺序
    // 将 null 转换为空字符串
    const dataRow = adjustedColumns.map(col => {
      const value = parentItem[col.prop];
      return value !== null && value !== undefined ? value : "";
    });

    allData.push(dataRow);
  });

  // 创建并优化工作表
  const ws = XLSX.utils.aoa_to_sheet(allData);
  // 定义统一边框样式
  const fullBorder = {
    top: { style: "thin", color: { rgb: "000000" } },
    left: { style: "thin", color: { rgb: "000000" } },
    bottom: { style: "thin", color: { rgb: "000000" } },
    right: { style: "thin", color: { rgb: "000000" } }
  };
  // 创建样式对象
  const styles = {
    title: {
      font: { bold: true, sz: 12 },
      alignment: { horizontal: "center", vertical: "center" },
      border: fullBorder // 共用边框样式
    },

    data: {
      alignment: { horizontal: "center", vertical: "center" },
      border: fullBorder // 共用边框样式
    }
  };


  // 设置数据行样式（带边框）
  for (let rowIndex = 0; rowIndex < allData.length; rowIndex++) {
    adjustedColumns.forEach((_, colIndex) => {
      const cellAddress = XLSX.utils.encode_cell({ r: rowIndex, c: colIndex });

      // 设置表头行样式（第一行）
      if (rowIndex === 0 && ws[cellAddress]) {
        ws[cellAddress].s = styles.title; // 应用标题样式
      }
      // 设置数据行样式
      else if (ws[cellAddress]) {
        ws[cellAddress].s = styles.data; // 应用数据行样式
      }
    });
  }
  // 设置列宽
  ws["!cols"] = adjustedColumns.map(col => {
    if (col.label === "身份证号") return { width: 30 };
    return { width: 25 };
  });
  const wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, ws, "学生信息明细");
  XLSX.writeFile(wb, "信息报表.xlsx");
};

至此，一个完整的Excel表就完成了

（4）导出txt文件逻辑

// TXT导出逻辑
const exportTXT = (data: any[]) => {
  // 定义列宽，每列的宽度根据最大数据长度确定
  const columnWidths = [16, 20, 25, 14, 12, 12, 12, 12, 12, 12, 12, 15, 25, 15, 25, 15, 25];
  const columnWidthsTwo = [15, 20, 25, 10, 15, 18, 18, 18, 18, 25, 15, 20, 30, 20, 35, 22, 25];
  // 生成表头并填充空格确保列宽一致
  const headers = [
    "考试号",
    "准考证号",
    "身份证号",
    "姓名",
    "县区",
    "考试批次",
    "考试组",
    "考场",
    "考场组",
    "序号",
    "考试类型",
    "第一次成绩",
    "第一次成绩时间",
    "第二次成绩",
    "第二次成绩时间",
    "第三次成绩",
    "第三次成绩时间"
  ]
    .map((header, index) => header.padEnd(columnWidths[index])) // 使用 padEnd 填充空格
    .join("");

  // 生成数据行
  const rows = data.map(item =>
    [
      item.ksh,
      item.ksZkz,
      item.bmh,
      item.xm,
      item.xq,
      item.kspc,
      item.ksz,
      item.kcDm,
      item.kcZb,
      item.xh,
      item.erType,
      item.erOneScore,
      item.oneScoreTime,
      item.erSecScore,
      item.secScoreTime,
      item.erThrScore,
      item.thrScoreTime
    ]
      .map((value, index) => {
        // 判断值是否为 null，如果是则替换为空字符串
        const cellValue = value === null || value === undefined ? "" : String(value);
        return cellValue.padEnd(columnWidthsTwo[index]); // 对每个数据值进行填充
      })
      .join("")
  );

  // 合并内容
  const content = [headers, ...rows].join("\n");

  // 创建文件
  const blob = new Blob([content], {
    type: "text/plain;charset=utf-8"
  });
  saveAs(blob, `考生信息_${new Date().toISOString().slice(0, 10)}.txt`);
};