前端可视化展示Excel

Excel可视化

• 后端返回Excel流前端进行展示

前言

需求：有的时候我们需要接收到后端返回的Excel流，前端进行可视化展示时，这个时候按照如下步骤即可

提前说明：前端展示的Excle样式与格式完全由后端控制，比如合并单元格、样式控制…

前端Demo🌰：例子 （有现成写好的接口）

安装依赖库

• 安装依赖库（二选一即可）
  • npm install xlsx xlsx-style less less-loader@5.0.0
  • yarn add xlsx xlsx-style less less-loader@5.0.0
• 拷贝文件夹
  • src/components/x-spreadsheet
  • src/util/xlsx-exchange
  • 戳我下载👆
• 引入依赖：在你想请求展示的.vue文件中

import axios from 'axios'
import Spreadsheet from '@/components/x-spreadsheet';
import XStyle from 'xlsx-style';
import Exchange from '@/util/xlsx-exchange';

• 启动项目：修改报错
  • cpexcel.js (ctrl+p直接搜索文件即可)

(删除)-- var cpt = require('./cpt' + 'able');
(添加)++ var cpt = cptable;

展示

• 定义template

<div class="content">
  <div id="xss-demo" />
</div>

• 定义data

data() {
    return {
      sheet: null,
      flobFileSize: 0,
    }
},

• 初始化xsheet

async mounted() {
  this.instantiateSheet();
},

• xsheet配置

method: {
  // 设置xsheet 数据
  setXsheetData(out) {
    this.sheet.loadData(out);
    if (out.length) {
      let colLen = Object.keys(out[0].rows[0].cells).length || 10;
      // let colLen = this.getTableColLen(out) || 10;
      let rowLen = Object.keys(out[0].rows).length || 10;
      this.sheet.sheet.data.rows.len = rowLen;
      this.sheet.sheet.data.cols.len = colLen;
      this.sheet.reRender();
    }
  },
  // 实例化 xsheet
  instantiateSheet() {
    let queryListHeight = 0;
    if (this.$refs.queryList)
      queryListHeight = this.$refs.queryList.$el.offsetHeight || 0;
    this.sheet = new Spreadsheet(document.getElementById("xss-demo"), {
      mode: "read", // edit | read
      showToolbar: false,
      showGrid: false,
      showContextmenu: false,
      view: {
        height: () =>
          document.documentElement.clientHeight - 180 - queryListHeight,
        width: () => document.documentElement.clientWidth - 300,
      },
      row: {
        height: 25,
        len: 100,
      },
      col: {
        len: 16,
        width: 100,
        indexWidth: 60,
        minWidth: 60,
      },
    });
  },
}

• 发送请求

axios({
  url: 'http://39.102.36.212:3006/excelExport',
  method: 'post',
  responseType: 'blob'
}).then((res) => {
  const self = this
  const result = res.data
  this.flobFileSize = result.size || 0;
  if (result instanceof Blob) {
    var reader = new FileReader();
    reader.onload = function (e) {
      let data = e.target.result;
      console.log(data)
      if (data) {
        let workbook = XStyle.read(data, { type: "binary", cellStyles: true });
        let out = Exchange.stox(workbook);
        self.setXsheetData(out)
      } else {
        self.setXsheetData([])
      }
    };
    reader.readAsBinaryString(result);
  } else {
    self.setXsheetData([])
  }
})

• 展现效果