Front-end visual display Excel

Excel visualization

• The backend returns to the Excel stream frontend for display

Preface

Requirements: Sometimes we need to receive the Excel when the front end performs 160bc1e1905c1c visual display. this time, follow the steps below.

Description advance: distal displayed Excle style format entirely by the backhaul control , such as merging cells, the control pattern ..

Front-end Demo🌰: example (there are ready-made interfaces)

Install dependent libraries

• Install dependent libraries (choose one of the two)

  • npm install xlsx xlsx-style less less-loader@5.0.0
  • yarn add xlsx xlsx-style less less-loader@5.0.0

• Copy folder

  • src/components/x-spreadsheet
  • src/util/xlsx-exchange
  • poke me to download
• .vue file you want to request to show

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

• Start the project: modify the error

  • cpexcel.js ( ctrl+p can search the file directly)

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

Show off

• Definition template

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

• Definition data

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

• Initialize xsheet

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

• xsheet configuration

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,
      },
    });
  },
}

• send request

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([])
  }
})

• Show effect