关于vue2:vue导出excel表格并支持样式行高修改xlsxxlsxstyle

2次阅读

共计 5666 个字符，预计需要花费 15 分钟才能阅读完成。

前言

应用插件 xlsx、xlsx-style 导出带有款式的表格，两者都能够实现创立 excel，然而 xlsx 免费版仅仅只能做单元格合并，免费版能力设置单元格款式，所以须要用到 xlsx-style 插件实现表格款式的批改

一、应用步骤

1. 装置环境

  • vue: 2.5.22
  • xlsx: 0.18.5
  • xlsx-style: 0.8.13

2. 引入 xlsx 库 

npm i xlsx xlsx-style -S

3. 批改 js 文件

这里咱们须要两个文件：

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

  • node_modules/xlsx/dist 里的 xlsx.js

    批改 xlsx.extendscript.js 里所有 XLSX 变量为 XLSX2，防止和 xlsx.js 里的变量抵触 
    var XLSX = {};
...
    批改 xlsx.js 里的办法，使导出反对自定义行高性能 
    // 减少行高设置
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("");
}
    创立自定义的 js 文件 export2Excel.js 内容如下 
    // 将一个 sheet 转成最终的 excel 文件的 blob 对象，而后利用 URL.createObjectURL 下载
function sheet2blob(sheet, sheetName) {
  sheetName = sheetName || 'sheet1';
  var workbook = {SheetNames: [sheetName],
      Sheets: {}};
  workbook.Sheets[sheetName] = sheet; // 生成 excel 的配置项
 
  var wopts = {
      bookType: 'xlsx', // 要生成的文件类型
      bookSST: false, // 是否生成 Shared String Table，官网解释是，如果开启生成速度会降落，但在低版本 IOS 设施上有更好的兼容性
      type: 'binary'
  };
  var wbout = XLSX.write(workbook, wopts);
  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;
}
 
// 将 workbook 装化成 blob 对象, 可反对多个 sheet
function workbook2blob(workbook) {
  // 生成 excel 的配置项
  var wopts = {
      // 要生成的文件类型
      bookType: "xlsx",
      // 是否生成 Shared String Table，官网解释是，如果开启生成速度会降落，但在低版本 IOS 设施上有更好的兼容性
      bookSST: false,
      type: "binary"
  };
  var wbout = XLSX.write(workbook, wopts);
  // 将字符串转 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;
  }
  var blob = new Blob([s2ab(wbout)], {type: "application/octet-stream"});
  return blob;
}
/**
 * 通用的关上下载对话框办法
 * @param url 下载地址，也能够是一个 blob 对象，必选
 * @param saveName 放弃文件名，可选
 */
function openDownloadDialog(url, saveName) {if (typeof url == 'object' && url instanceof Blob) {
      // 创立 blob 地址
      url = URL.createObjectURL(url);
  }
  var aLink = document.createElement('a');
  aLink.href = url;
  // HTML5 新增的属性，指定保留文件名，能够不要后缀，留神，file:/// 模式下不会失效
  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);
}
// 为合并项增加边框
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 => {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 <= endColNumber; col++) {for (let row = startRowNumber; row <= 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],
                  ['与会者', null, null, null, null, null, null]
                  ['序号', '姓名', '性别', '年龄', '批改人', '批改日期', '提交日期']
              ]
          }
      },
      methods: {exportExcel() {
              const aoa = [...this.excelAoa];
              // 这个就是批改格局的代码
              sheet["A1"].s = {
                  font: {
                      sz: 14,
                      bold: true,
                      color: {rgb: "0000FF"}
                  },
                  fill: {
                      bgColor: {indexed: 64},
                      fgColor: {rgb: "FFFF00"}
                  }
              }; //<==== 设置 xlsx 单元格款式
              const sheet = this.xlsxAddStyle(aoa);
              openDownloadDialog(sheet2blob(sheet), '导出.xlsx');
          },
          // 表格款式设置
          xlsxAddStyle(aoa) {
              // 留神 XLSX2 变量
              const sheet = XLSX2.utils.aoa_to_sheet(aoa);
              // 合并单元格 s 开始坐标  e 完结坐标  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 = []; // 表格每列高度
              // 单元格外侧有框线
              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];
                      if (typeof element === "object") {const index = Number(key.slice(1)) - 1;
                          rowH[index] = {hpx: 30};
                          element.s = {
                              alignment: {
                                  horizontal: "center", // 所有单元格右对齐
                                  vertical: "center", // 所有单元格垂直居中
                                  wrapText: true // 换行
                              },
                              font: {
                                  name: "微软雅黑",
                                  sz: 10,
                                  italic: false,
                                  underline: false,
                              },
                              border: borderAll,
                              fill: {fgColor: { rgb: "FFFFFFFF"},
                              },
                          };
                          // 题目的款式
                          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;
              // 合并项增加边框
              const sheetNew = addRangeBorder(sheet['!merges'], sheet);
              return sheetNew
          }
      }
  }
</script>
正文完
vue2
发表至： vue2
2023-06-08
 0
关于vue2:vue2veevalidate2表单校验
关于vue2:vue2使用sortablejs实现eltable的行拖拽
关于vue2:vue2升级vue3遇到的问题汇总
关于vue2:vue导出excel表格并支持样式行高修改xlsxxlsxstyle

站内搜索

-「」