快捷導(dǎo)航

vue導(dǎo)出excel表格并支持樣式及行高修改

更新時(shí)間：2023年06月09日 10:56:21 作者：ZekiHoo

這篇文章主要為大家介紹了vue導(dǎo)出excel表格并支持樣式及行高修改，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪

前言

使用插件xlsx、xlsx-style導(dǎo)出帶有樣式的表格，兩者都可以實(shí)現(xiàn)創(chuàng)建excel，但是xlsx免費(fèi)版僅僅只能做單元格合并，收費(fèi)版才能設(shè)置單元格樣式，所以需要用到xlsx-style插件實(shí)現(xiàn)表格樣式的修改

一、使用步驟

1. 安裝環(huán)境

vue: 2.5.22
xlsx: 0.18.5
xlsx-style: 0.8.13

2. 引入xlsx庫

npm i xlsx xlsx-style -S

3. 修改js文件

這里我們需要兩個(gè)文件：

node_modules/xlsx/dist里的xlsx.extendscript.js

node_modules/xlsx/dist里的xlsx.js

修改xlsx.extendscript.js里所有XLSX變量為XLSX2，避免和xlsx.js里的變量沖突

var XLSX = {};
...

修改xlsx.js里的方法，使導(dǎo)出支持自定義行高功能

// 增加行高設(shè)置
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("");
}

創(chuàng)建自定義的js文件export2Excel.js 內(nèi)容如下

// 將一個(gè)sheet轉(zhuǎn)成最終的excel文件的blob對(duì)象，然后利用URL.createObjectURL下載
function sheet2blob(sheet, sheetName) {
  sheetName = sheetName || 'sheet1';
  var workbook = {
      SheetNames: [sheetName],
      Sheets: {}
  };
  workbook.Sheets[sheetName] = sheet; // 生成excel的配置項(xiàng)
  var wopts = {
      bookType: 'xlsx', // 要生成的文件類型
      bookSST: false, // 是否生成Shared String Table，官方解釋是，如果開啟生成速度會(huì)下降，但在低版本IOS設(shè)備上有更好的兼容性
      type: 'binary'
  };
  var wbout = XLSX.write(workbook, wopts);
  var blob = new Blob([s2ab(wbout)], {
      type: "application/octet-stream"
  }); // 字符串轉(zhuǎn)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) &amp; 0xFF;
      return buf;
  }
  return blob;
}
// 將workbook裝化成blob對(duì)象,可支持多個(gè)sheet
function workbook2blob(workbook) {
  // 生成excel的配置項(xiàng)
  var wopts = {
      // 要生成的文件類型
      bookType: "xlsx",
      // 是否生成Shared String Table，官方解釋是，如果開啟生成速度會(huì)下降，但在低版本IOS設(shè)備上有更好的兼容性
      bookSST: false,
      type: "binary"
  };
  var wbout = XLSX.write(workbook, wopts);
  // 將字符串轉(zhuǎn)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) &amp; 0xff;
      return buf;
  }
  var blob = new Blob([s2ab(wbout)], {
      type: "application/octet-stream"
  });
  return blob;
}
/**
 * 通用的打開下載對(duì)話框方法
 * @param url 下載地址，也可以是一個(gè)blob對(duì)象，必選
 * @param saveName 保持文件名，可選
 */
function openDownloadDialog(url, saveName) {
  if (typeof url == 'object' &amp;&amp; url instanceof Blob) {
      // 創(chuàng)建blob地址
      url = URL.createObjectURL(url);
  }
  var aLink = document.createElement('a');
  aLink.href = url;
  // HTML5新增的屬性，指定保存文件名，可以不要后綴，注意，file:///模式下不會(huì)生效
  aLink.download = saveName || '';
  var 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);
}
// 為合并項(xiàng)添加邊框
function addRangeBorder(range, ws) {
  let arr = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"];
  range.forEach(item =&gt; {
      let startColNumber = Number(item.s.r), endColNumber = Number(item.e.r);
      let startRowNumber = Number(item.s.c), endRowNumber = Number(item.e.c);
      const test = ws[arr[startRowNumber] + (startColNumber + 1)];
      for (let col = startColNumber; col &lt;= endColNumber; col++) {
          for (let row = startRowNumber; row &lt;= endRowNumber; row++) {
              ws[arr[row] + (col + 1)] = test;
          }
      }
  })
  return ws;
}

4. index.html引入

<script src="xlsx.js"></script>
<script src="export2Excel.js"></script>
<script src="xlsx.extendscript.js"></script>

5. vue代碼

<template>
  <div class="config-form">
      <el-button @click="exportExcel">下載文檔</el-button>
  </div>
</template>
<script>
  export default {
      data() {
          return {
              value: [20, 50],
              excelAoa: [
                  ['XXXXXXXXXX記錄表', null, null, null, null, null, null],
                  ['與會(huì)者', null, null, null, null, null, null]
                  ['序號(hào)', '姓名', '性別', '年齡', '修改人', '修改日期', '提交日期']
              ]
          }
      },
      methods: {
          exportExcel() {
              const aoa = [
                  ...this.excelAoa
              ];
              //這個(gè)就是修改格式的代碼
              sheet["A1"].s = {
                  font: {
                      sz: 14,
                      bold: true,
                      color: {
                          rgb: "0000FF"
                      }
                  },
                  fill: {
                      bgColor: {
                          indexed: 64
                      },
                      fgColor: {
                          rgb: "FFFF00"
                      }
                  }
              }; //<====設(shè)置xlsx單元格樣式
              const sheet = this.xlsxAddStyle(aoa);
              openDownloadDialog(sheet2blob(sheet), '導(dǎo)出.xlsx');
          },
          // 表格樣式設(shè)置
          xlsxAddStyle(aoa) {
              // 注意XLSX2變量
              const sheet = XLSX2.utils.aoa_to_sheet(aoa);
              // 合并單元格 s 開始坐標(biāo)  e結(jié)束坐標(biāo)  c-x軸  r-y軸
              const mergeArr = [
                  {s: {c: 0, r: 0}, e: {c: 6, r: 0}},
                  {s: {c: 1, r: 1}, e: {c: 6, r: 1}}
              ]
              const rowH = []; // 表格每列高度
              // 單元格外側(cè)有框線
              const borderAll = {
                  top: { style: "thin" },
                  bottom: { style: "thin" },
                  left: { style: "thin" },
                  right: { style: "thin" },
              };
              // 單元格外側(cè)無框線
              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];
                      if (typeof element === "object") {
                          const index = Number(key.slice(1)) - 1;
                          rowH[index] = { hpx: 30 };
                          element.s = {
                              alignment: {
                                  horizontal: "center", // 所有單元格右對(duì)齊
                                  vertical: "center", // 所有單元格垂直居中
                                  wrapText: true // 換行
                              },
                              font: {
                                  name: "微軟雅黑",
                                  sz: 10,
                                  italic: false,
                                  underline: false,
                              },
                              border: borderAll,
                              fill: {
                                  fgColor: { rgb: "FFFFFFFF" },
                              },
                          };
                          // 標(biāo)題的樣式
                          if (['A1'].includes(key)) {
                              element.s.alignment.horizontal = 'center';
                              element.s.font.bold = true;
                              element.s.font.sz = 15;
                              element.s.fill.border = noBorder;
                          }
                      }
                  }
              }
              // 單元格的列寬
              sheet["!cols"] = [
                  { wpx: 60 }, 
                  { wpx: 100 },
                  { wpx: 120 }, 
                  { wpx: 120 }, 
                  { wpx: 100 }, 
                  { wpx: 100 },
                  { wpx: 100 }
              ];
              sheet["!rows"] = rowH;
              sheet["!merges"] = mergeArr;
              // 合并項(xiàng)添加邊框
              const sheetNew = addRangeBorder(sheet['!merges'], sheet);
              return sheetNew
          }
      }
  }
</script>

以上就是vue導(dǎo)出excel表格并支持樣式及行高修改的詳細(xì)內(nèi)容，更多關(guān)于vue導(dǎo)出excel表格的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: