vue使用exceljs生成表格数据，进行预览，并实现导出与打印

紫幽
19 阅读5分钟

​

参考文章：使用exceljs将excel文件转化为html预览最佳实践（完整源码）

                   Vue使用两种方式打印页面数据

                   ExcelJS的基本操作

一、使用exceljs生成表格数据

 1.安装exceljs

npm install exceljs

2.页面引入

import ExcelJS from "exceljs";

3. 生成excel数据

const wb = new ExcelJS.Workbook();
const ws = wb.addWorksheet("Sheet1");
ws.columns = [ //设置表头
  { header: 'Id', key: 'id', width: 10 },
  { header: 'Name', key: 'name', width: 32 },
  { header: 'D.O.B.', key: 'DOB', width: 10, outlineLevel: 1 }
];
// 单独设置列宽度
let row = ws.getColumn(1).width = 20;
// 如果是规律的表格，直接对照key添加新行
ws.addRow({id: 1, name: 'John Doe', dob: new Date(1970,1,1)});
// 不规律数据直接数组添加
let row1 = ws.addRow([1，'John Doe'，new Date(1970,1,1)]);
row.alignment = {
    horizontal: "center", // 水平对齐
    vertical: "middle", // 垂直对齐
    wrapText: true, // 换行
};
rows.height = 20; // 行高
ws.mergeCells("A1:H1"); // 合并单元格

const cell = ws.getCell("A1"); // 获取单元格
cell.border = { // 边框
    bottom: {
        style: "thin",
        color: {
        argb: "ff000000",
        },
    },
    right: {
        style: "thin",
        color: {
            argb: "ff000000",
        },
    },
};
cell.font = { // 字体
    name: "宋体",
    size: 12 ,
};
cell.fill = { // 背景
    type: "pattern",
    pattern: "solid",
    fgColor: {
        argb: "ffff0000",
    },
};

二、将生成的excel数据转换成h5样式在页面预览

我在参考文章基础上完善了字体、字体颜色、背景色、边框等的读取设置

并把vue3转成了vue2

1.将excel数据转成h5代码

data() {
    return {
      tableHtml: "",
      themeColors: {
        0: "#FFFFFF", // 白色 √
        1: "#000000", // 黑色 √
        2: "#C9CDD1", // 灰色 √
        3: "#4874CB", // 蓝色 √
        4: "#D9E1F4", // 浅蓝 √
        5: "#F9CBAA", // 橙色 √
        6: "#F2BA02", // 浅橙 √
        7: "#00FF00", // 浅绿 √
        8: "#30C0B4", // 青色 √
        9: "#E54C5E", // 红色 √
        10: "#FFC7CE", // 浅红
        11: "#7030A0", // 紫色
      },
    };
},
methods: {
// 颜色解析
    parseColor(argb) {
      if (!argb) return "000";
      // 8位：AARRGGBB
      if (argb.length === 8) {
        return `${argb.substring(2)}`;
      }
      // 6位：RRGGBB
      if (argb.length === 6) {
        return `${argb}`;
      }
      return "000";
    },
    // pt 转 px
    ptToPx(pt) {
      if (!pt) return "";
      return (pt * 1.333).toFixed(1);
    },
    // 获取单元格颜色
    getCellColor(cell) {
      if (cell.fill && cell.fill.fgColor) {
        if (cell.fill.fgColor.argb) {
          return `#${this.parseColor(cell.fill.fgColor.argb)}`; // ARGB 转 RGB
        }
        if (cell.fill.fgColor.theme !== undefined) {
          return this.themeColors[cell.fill.fgColor.theme] || "#FFFFFF"; // 主题色转换
        }
      }
      return "";
    },
    // 获取单元格字体颜色
    getCellFontColor(cell) {
      if (cell.font && cell.font.color && cell.font.color.argb) {
        return `#${this.parseColor(cell.font.color.argb)}`; // ARGB 转 RGB
      }
      if (cell.font && cell.font.color && cell.font.color.theme !== undefined) {
        return this.themeColors[cell.font.color.theme] || "#000"; // 主题色转换
      }
      return "#000";
    },
    // 获取边框颜色
    getBorderColor(border) {
      if (!border || !border.color) return "#000";
      const c = border.color;
      if (c.argb) return "#" + this.parseColor(c.argb);
      if (c.theme !== undefined) return this.themeColors[c.theme] || "#000";

      return "#000";
    },
    // 获取边框样式
    getBorderStyle(border) {
      if (!border || !border.style) return "";

      const map = {
        hair: "0.5px solid",
        thin: "1px solid",
        medium: "2px solid",
        thick: "3px solid",
        dotted: "1px dotted",
        dashed: "1px dashed",
        double: "3px double",
      };

      return map[border.style] || "1px solid";
    },
    // 生成单元格边框样式
    getCellBorderStyle(cell) {
      if (!cell.border) return "";

      const b = cell.border;
      const styles = [];

      if (b.top) {
        styles.push(
          `border-top:${this.getBorderStyle(b.top)} ${this.getBorderColor(
            b.top
          )}`
        );
      }
      if (b.right) {
        styles.push(
          `border-right:${this.getBorderStyle(b.right)} ${this.getBorderColor(
            b.right
          )}`
        );
      }
      if (b.bottom) {
        styles.push(
          `border-bottom:${this.getBorderStyle(b.bottom)} ${this.getBorderColor(
            b.bottom
          )}`
        );
      }
      if (b.left) {
        styles.push(
          `border-left:${this.getBorderStyle(b.left)} ${this.getBorderColor(
            b.left
          )}`
        );
      }

      return styles.join(";");
    },
    handleStyles(cell) {
      let styles = [];
      // 读取字体颜色
      styles.push(`color: ${this.getCellFontColor(cell)}`);
      // 读取背景色
      styles.push(`-webkit-print-color-adjust: exact;background-color: ${this.getCellColor(cell)}`);
      // 边框样式
      styles.push(this.getCellBorderStyle(cell));
      // 加粗
      if (cell.font && cell.font.bold) {
        styles.push("font-weight: bold");
      }
      // 字体
      if (cell.font?.name) {
        styles.push(`font-family: '${cell.font.name}'`);
      }
      // 字号
      if (cell.font?.size) {
        styles.push(`font-size: ${this.ptToPx(cell.font.size)}px`);
      }
      // 文字对齐
      if (cell.alignment) {
        if (cell.alignment.horizontal) {
          styles.push(`text-align: ${cell.alignment.horizontal}`);
        }
        if (cell.alignment.vertical) {
          styles.push(`vertical-align: ${cell.alignment.vertical}`);
        }
      }
      return styles.join("; ");
    },
    // 处理常规单元格内容
    handleValueSimple(value) {
      if (value && typeof value === "object" && value.richText) {
        return value.richText.reduce((acc, curr) => {
          let colorValue = "#000";
          if (curr.font && curr.font.color) {
            if (curr.font.color.argb) {
              colorValue = `#${curr.font.color.argb.substring(2)}`;
            } else if (curr.font.color.theme !== undefined) {
              colorValue = this.themeColors[curr.font.color.theme] || "#000";
            }
          }
          const family = curr.font?.name ? curr.font.name : "";
          const size = curr.font?.size
            ? `${this.ptToPx(curr.font.size)}px`
            : "";
          return (
            acc +
            `<span style="color:${colorValue};font-family:${family};font-size:${size}">${curr.text}</span>`
          );
        }, "");
      }
      return value || "";
    },
    // 处理合并单元格内容
    handleValue(value) {
      if (value && typeof value === "object" && value.richText) {
        const arr = value.richText.reduce((acc, curr) => {
          let colorValue = "#000";
          if (curr.font && curr.font.color && curr.font.color.argb) {
            colorValue = `#${curr.font.color.argb.substring(2)}`;
          }
          const family = curr.font?.name ? curr.font.name : "";
          const size = curr.font?.size
            ? `${this.ptToPx(curr.font.size)}px`
            : "";
          const parts = curr.text
            .split(/\r/)
            .map(
              (item) =>
                `<p style="color:${colorValue};font-family:${family};font-size:${size}">${item}</p>`
            );
          return acc.concat(parts);
        }, []);
        return arr.join("").replace(/\n/g, "<br />");
      }
      return value || "";
    },
    // 处理 Excel 文件
    async handleFileUpload(file) {
      const html = await this.readExcel(file);
      this.tableHtml = html;
    },
    // 读取 Excel 并转换成 HTML
    async readExcel(file) {
      // const workbook = new ExcelJS.Workbook();
      // const buffer = await file.arrayBuffer();
      // await workbook.xlsx.load(buffer);

      const workbook = file;
      const worksheetIds = workbook.worksheets.map((v) => v.id);

      let allHtml = "";

      workbook.eachSheet((worksheet, sheetId) => {
        const merges = worksheet.model.merges || [];
        const currentSheetIndex = worksheetIds.indexOf(sheetId);
        // 计算列宽百分比
        const colWidths = [];
        let totalWidth = 0;

        worksheet.columns.forEach((col, index) => {
          const w = col.width || 10; // Excel 默认大约是 8~10
          colWidths[index + 1] = w;
          totalWidth += w;
        });

        // 转成百分比
        const colPercents = {};
        for (let i in colWidths) {
          colPercents[i] = ((colWidths[i] / totalWidth) * 100).toFixed(2);
        }

        allHtml +=
          '<table border="1" style="border-collapse: collapse;width:100%;margin-bottom:20px;">';

        worksheet.eachRow({ includeEmpty: true }, (row, rowIndex) => {
          allHtml += "<tr>";

          row.eachCell({ includeEmpty: true }, (cell, colIndex) => {
            let cellValue = cell.value || "";
            let rowspan = 1,
              colspan = 1;
            let isMerged = false;

            for (let merge of merges) {
              const [start, end] = merge.split(":");
              const startCell = worksheet.getCell(start);
              const endCell = worksheet.getCell(end);

              const startRow = startCell.row,
                startCol = startCell.col;
              const endRow = endCell.row,
                endCol = endCell.col;

              if (startRow === rowIndex && startCol === colIndex) {
                rowspan = endRow - startRow + 1;
                colspan = endCol - startCol + 1;
                isMerged = true;
                let styles = this.handleStyles(cell);

                let mergeWidth = 0;
                for (let c = startCol; c <= endCol; c++) {
                  mergeWidth += parseFloat(colPercents[c] || 0);
                }

                allHtml += `<td rowspan="${rowspan}" colspan="${colspan}"
                  style="width:${mergeWidth}%;${styles}">
                  ${this.handleValue(startCell.value)}</td>`;
                // allHtml += `<td rowspan="${rowspan}" colspan="${colspan}" style="${styles}">
                //   ${this.handleValue(startCell.value)}</td>`;
                break;
              }

              if (
                rowIndex >= startRow &&
                rowIndex <= endRow &&
                colIndex >= startCol &&
                colIndex <= endCol
              ) {
                isMerged = true;
                break;
              }
            }

            if (!isMerged) {
              let styles = this.handleStyles(cell);
              const width = colPercents[colIndex] || "";
              allHtml += `<td style="width:${width}%;${styles}">
                ${this.handleValueSimple(cellValue)}</td>`;
              // allHtml += `<td style="${styles}">
              //   ${this.handleValueSimple(cellValue)}</td>`;
            }
          });

          allHtml += "</tr>";
        });

        allHtml += "</table>";
      });

      return allHtml;
    },
},

使用：
this.handleFileUpload(wb);

wb是步骤一的excel数据

页面显示
<div v-html="tableHtml"/>

2.要是想导入excel文件预览，就把函数改一下

h5，用的是element的上传组件

 <el-upload
      action=""
      :auto-upload="false"
      :show-file-list="true"
      :on-change="handleFileUpload"
      accept=".xlsx,.xls"
    >
      <el-button type="primary"> 上传 Excel </el-button>
    </el-upload>

handleFileUpload函数修改后

async handleFileUpload(file) {
      const html = await this.readExcel(file.raw);
      this.tableHtml = html;
},

三、导出excel文件

1.安装file-saver

npm install file-saver --save

2.页面引入

import FileSaver from "file-saver";

3.导出

wb.xlsx.writeBuffer().then((buffer) => {
    let file = new Blob([buffer], {
        type: "application/octet-stream",
    });
    FileSaver.saveAs(
        file,
        new Date(1970,1,1)".xlsx"
    );
});

wb是步骤一的excel数据

四、打印或导出pdf

1.安装 vue-print-nb

npm install vue-print-nb --save

2.在main.js中引入

vue-print-nb直接在组件引入会有问题，所以直接全局引入

import Print from 'vue-print-nb'
Vue.use(Print)

3.打印按钮绑定 handlePrint

<el-button v-print="handlePrint">打印</el-button>

4.在数据data中添加

data() {
    return {
      handlePrint: {
        id: 'print',
        extraHead: '<meta http-equiv="Content-Language"content="zh-cn"/>,打印表格数据<style>#print {width: 100%;}<style>',
        popTitle: "页眉部分",
    },
}

5.被打印的容器，添加id对应上个步骤的id

<div v-html="tableHtml" id="print" />

tableHtml是步骤二的样式代码

6.打印不全，添加样式

<style media="print">
  @media print {
    @page{
      size:  auto;
      margin: 8mm 6mm;
    }
    html {
      /*打印缩放，防止显示不全*/
      zoom: 90%;
    }
    #print table {
      table-layout: auto !important;
    }
    #print .el-table__header-wrapper .el-table__header {
      width: 100% !important;
    }
    #print .el-table__body-wrapper .el-table__body {
      width: 100% !important;
    }
    .el-table--border .el-table__cell, .el-table__body-wrapper td {
      border: solid 1px #f2f2f2;
    }
    td.el-table__cell{
      border: solid 1px #f2f2f2;
    }
  }
</style>

7.vue-print-nb导出pdf没有背景色解决办法

给要打印的背景的元素添加样式 -webkit-print-color-adjust: exact;

我在步骤二的数据处理函数里已经加上了

8.分页

在要进行分页的标签上，添加page-break-after:always;

会在添加标签的后面分页 ​

 9.打印另存为名称修改

vue-print-nb默认名称是网页名称，所以直接修改网页名

可以打印前保存一下网页名，打印完后改回来

this.originalTitle = document.title;
document.title = '报表_用户ID_20250405';
document.title = this.originalTitle;

​ ​

avatar
文章
阅读
粉丝