vue2使用xlsx加xlsx-style导出excel，并自定义样式

最新推荐文章于 2024-04-08 14:19:57 发布

一颗胶囊

最新推荐文章于 2024-04-08 14:19:57 发布

阅读量1.1k

点赞数

文章标签： excel

本文链接：https://blog.csdn.net/weixin_43569270/article/details/132669100

版权

1.下载插件

// 下载
npm i xlsx
npm i xlsx-style

2.修改代码，

使用slsx-style修改行高需要修改源代码，在xlsx-style中找到xlsx.js ,搜索write_ws_xml_data替换下面的代码

// 替换代码
  var DEF_PPI = 96, PPI = DEF_PPI;
  function px2pt(px) { return px * 96 / PPI; }
  function pt2px(pt) { return pt * PPI / 96; }
  function write_ws_xml_data(ws, opts, idx, wb) {
    var o = [], r = [], range = safe_decode_range(ws['!ref']), cell = "", ref, rr = "", cols = [], R = 0, C = 0, rows = ws['!rows'];
    var dense = Array.isArray(ws);
    var params = ({ r: rr }), row, height = -1;
    for (C = range.s.c; C <= range.e.c; ++C) cols[C] = encode_col(C);
    for (R = range.s.r; R <= range.e.r; ++R) {
      r = [];
      rr = encode_row(R);
      for (C = range.s.c; C <= range.e.c; ++C) {
        ref = cols[C] + rr;
        var _cell = dense ? (ws[R] || [])[C] : ws[ref];
        if (_cell === undefined) continue;
        if ((cell = write_ws_xml_cell(_cell, ref, ws, opts, idx, wb)) != null) r.push(cell);
      }
      if (r.length > 0 || (rows && rows[R])) {
        params = ({ r: rr });
        if (rows && rows[R]) {
          row = rows[R];
          if (row.hidden) params.hidden = 1;
          height = -1;
          if (row.hpx) height = px2pt(row.hpx);
          else if (row.hpt) height = row.hpt;
          if (height > -1) { params.ht = height; params.customHeight = 1; }
          if (row.level) { params.outlineLevel = row.level; }
        }
        o[o.length] = (writextag('row', r.join(""), params));
      }
    }
    if (rows) for (; R < rows.length; ++R) {
      if (rows && rows[R]) {
        params = ({ r: R + 1 });
        row = rows[R];
        if (row.hidden) params.hidden = 1;
        height = -1;
        if (row.hpx) height = px2pt(row.hpx);
        else if (row.hpt) height = row.hpt;
        if (height > -1) { params.ht = height; params.customHeight = 1; }
        if (row.level) { params.outlineLevel = row.level; }
        o[o.length] = (writextag('row', "", params));
      }
    }
    return o.join("");
  }

3.使用

import * as XLSX from "xlsx"
import XLSXStyle from "xlsx-style";
let _needSetStyles = []
let cols = []
let ifsubhead = false

/**
 * 导出excel表格
 * @param {Array} tableData - 需要的出表格的数组
 * @param {Array} header - 表头
 * @param {Array} headerItem - 子表
 * @param {String} notice - 注意事项
 * @param {Array} needChangeColor - 需要修改颜色的属性
 * @param {Array} clo - 每一项的宽度
 * @param {Array} subhead - 副标题信息
 * @param {string} filename - 导出文件名称
 */
export function exportTemplate(tableData, header, headerItem, notice, needSetStyles = [], col = [], subhead = [], filename) {
  // 检查是否有需要修改颜色的属性
  _needSetStyles = needSetStyles.length > 0 ? needSetStyles : [];
  // 检查是否有指定每一列的宽度
  cols = col.length > 0 ? col : [];

  if (tableData.length > 0) {
    // 表头设置
    const aoa = [[notice]];

    console.log(tableData);
    if (subhead.length > 0) {
      aoa.push(subhead);
      ifsubhead = true
    }
    aoa.push(header);

    console.log(headerItem, 'headerItem');
    if (headerItem.length > 0) {
      headerItem.forEach(item => {
        aoa.push(item)
      })
    }

    // 创建 Excel 表格并设置样式
    const sheet = xlsxAddStyle(aoa, notice);

    // 导出文件
    openDownloadDialog(sheet2blob(sheet), `${filename}.xlsx`);
  } else {
    // 表格数据为空，可以进行相应的处理
  }

}
export function openDownloadDialog(url, saveName) {
  let urlA;
  if (typeof url === "object" && url instanceof Blob) {
    urlA = URL.createObjectURL(url); // 创建blob地址
  }
  const aLink = document.createElement("a");
  aLink.href = urlA;
  // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
  aLink.download = saveName || "";
  let event;
  if (window.MouseEvent) event = new MouseEvent("click");
  else {
    event = document.createEvent("MouseEvents");
    event.initMouseEvent(
      "click",
      true,
      false,
      window,
      0,
      0,
      0,
      0,
      0,
      false,
      false,
      false,
      false,
      0,
      null
    );
  }
  aLink.dispatchEvent(event);
}
// 将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载
export function sheet2blob(sheet, sheetName) {
  var sheetNameS = sheetName || "sheet1";
  var workbook = {
    SheetNames: [sheetNameS],
    Sheets: {},
  };
  workbook.Sheets[sheetNameS] = sheet;
  // 生成excel的配置项
  var wopts = {
    bookType: "xlsx", // 要生成的文件类型
    bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
    type: "binary",
  };
  var wbout = XLSXStyle.write(workbook, wopts);
  // XLSXStyle.write(wb, { bookType: bookType, bookSST: false, type: 'binary' });
  var blob = new Blob([s2ab(wbout)], { type: "application/octet-stream" });
  // 字符串转ArrayBuffer
  function s2ab(s) {
    var buf = new ArrayBuffer(s.length);
    var view = new Uint8Array(buf);
    for (var i = 0; i !== s.length; ++i) view[i] = s.charCodeAt(i) & 0xff;
    return buf;
  }
  return blob;
}
// 表格样式的设置
export function xlsxAddStyle(xlsx, filename) {
  // 创建工作簿
  // const workbook = XLSX.utils.book_new();
  const sheet = XLSX.utils.aoa_to_sheet(xlsx);
  console.log(sheet, 'sheet');
  const mergeArr = []; // 合并的单元格
  const rowH = []; // 表格每列高度
  // 单元格外侧有框线
  const borderAll = {
    top: { style: "thin" },
    bottom: { style: "thin" },
    left: { style: "thin" },
    right: { style: "thin" },
  };
  // 单元格外侧无框线
  const noBorder = {
    top: { style: "" },
    bottom: { style: "" },
    left: { style: "" },
    right: { style: "" },
  };

  for (const key in sheet) {
    if (Object.hasOwnProperty.call(sheet, key)) {
      const element = sheet[key];
      console.log(element, 'element');
      if (typeof element === "object") {
        const index = Number(key.slice(1)) - 1;
        rowH[index] = { hpt: 20, hpx: 20 };
        element.s = {
          alignment: {
            horizontal: "center", // 所有单元格右对齐
            vertical: "center", // 所有单元格垂直居中
          },
          font: {
            name: "宋体",
            sz: 10,
            italic: false,
            underline: false,
          },
          // border: borderAll,
          // fill: {
          //   fgColor: { rgb: "FFFFFFFF" },
          // },
        };
        // 指标值表格的样式
        if (key.indexOf("C") > -1) {
          element.s.alignment.horizontal = "center";
        }
        // 标题的样式
        if (index === 1) {
          element.s.font.bold = true;
          element.s.rows = { hpx: 150 }
        }
        // 如果传来副标题，那么之前设置的样式会被副标题顶替掉，需要重新设置样式
        if (ifsubhead) {
          if (index === 1) {
            element.s.font.bold = true;
            // 高
            rowH[1] = { hpt: 20, hpx: 20 }
          }
          if (index === 2) {
            element.s.font.bold = true;
            rowH[2] = { hpt: 30, hpx: 30 }

          }
        }
        // 处理合并单元格数组 s 开始 e 结束  c 列 r行
        if (element.v == filename) {
          mergeArr.push({
            s: { c: 0, r: 0 },
            e: { c: cols.length - 1, r: 0 },
          });
          rowH[0] = { hpt: 110, hpx: 110 }

        }
        if (element.v.includes('职业工种')) {
          mergeArr.push({
            s: { c: 0, r: 1 },
            e: { c: 1, r: 1 },
          });
        }
      }
    }
  }
  // 表头的样式设置
  sheet["A1"].s.alignment.horizontal = "left";
  // sheet["A1"].s.font.underline = true;
  sheet["A1"].s.font.sz = 11;
  sheet["A1"].s.font.color = { rgb: 'FFFFFF' }
  sheet["A1"].s.border = borderAll
  sheet['A1'].s.fill = { fgColor: { rgb: '217346' } }
  // 单元格的列宽
  sheet["!cols"] = cols
  // 设置行高
  sheet['!rows'] = rowH;


  // 自定义样式
  _needSetStyles.forEach(item => {
    sheet[item.name].s = item.style
  })

  sheet["!merges"] = mergeArr;
  return sheet
}

3.创建模板

    // 创建模板
    createTemplate(templateList) {
      let header = ['考试准考证号', '考试姓名', '总分']
      let subhead = ['职业工种等级：保健按摩师/脊柱按摩师/四级', '占位', '考试名称：保健按摩师脊柱按摩师四级实操考试20230708'];
      let notice = [
        `注意事项:
        1、底纹为红色的列是必填项；底纹为白色的列为非必填项，可为空；
        2、请不要修改模板结构，否则导入失败；
        3、总分填大于等于0代表正常考试，填-1代表考生缺考，填-2代表考试作弊；综合评审科目不录入具体成绩：填-3代表考试合格，填-4代表考试不合格。`
      ]
      let headerItem = templateList.map(item => {
        return [item.examNumber, item.name, '0']
      })

      const clos = [
        { wpx: 210 }, { wpx: 210 }, { wpx: 420 }
      ];
      // 字体样式
      const fontColorStyle = {
        name: "宋体",
        sz: 10,
        italic: false,
        underline: false,
        color: { rgb: 'FFFFFF' },
      };
      // 单元格外侧边框
      const borderStyle = {
        top: { style: "thin" },
        bottom: { style: "thin" },
        left: { style: "thin" },
        right: { style: "thin" },
      };

      const setStyles = [
        {
          name: 'A3',
          style: {
            font: fontColorStyle,
            fill: {
              fgColor: { rgb: 'FF4949' } // 设置背景颜色
            },
            alignment: {
              horizontal: "center", // 所有单元格右对齐
              vertical: "center", // 所有单元格垂直居中
            },
            border: borderStyle
          }
        },
        {
          name: 'B3',
          style: {
            font: fontColorStyle,
            fill: {
              fgColor: { rgb: 'FF4949' } // 设置背景颜色
            },
            alignment: {
              horizontal: "center", // 所有单元格右对齐
              vertical: "center", // 所有单元格垂直居中
            },
            border: borderStyle
          }
        },
        {
          name: 'C3',
          style: {
            font:
              fontColorStyle,
            fill: {
              fgColor: { rgb: 'FF4949' } // 设置背景颜色
            },
            alignment: {
              horizontal: "center", // 所有单元格右对齐
              vertical: "center", // 所有单元格垂直居中
            },
            border: borderStyle
          }
        }
      ]
      exportTemplate(templateList, header, headerItem, notice, setStyles, clos, subhead, '实操成绩导出模板')
    },

4.导出效果