@From 0-1 realization of the front end to read excel table and render to the interface

English | simplified Chinese

This paper aims to solve the need of reading table files, obtaining file content and rendering to the interface without calling the back-end interface

  • My other articles address extension needs:
  • Automatic cell merge is performed after reading the parse table
  • Automatically set cell color according to data comparison and analysis after reading the parse table
  • After reading the parsed table, data analysis (dialysis) is performed to generate echarts diagrams that can meet user-defined requirements
  • Download the table function

instructions

The company usually do background management system more, similar demand is very common, I have also written similar posts, but are only put code never write notes and steps, hey hey, not much to say, this article is a complete record:

The premise

I usually use:

Ant Design + Angular

Element UI + Vue

Ant Design + Vue

For convenience, we use it todayElement UI + Vue

Based on thevue-element-adminJust start

Step 1: Preparation

1. Click on vue-element-admin to download

2. Download and decompress

3. Install dependencies and run them

4. The operation is successful

Step 2: Implement import table parsing

1. Go to the following path:

src\views\dashboard\index.vue

2. Delete unnecessary code and prepare to start.

<template>
 <div class="dashboard-container">
   
 </div>
</template>

<script>
export default {
 name: 'Dashboard'
}
</script>

<style lang="scss" scoped>

</style>

Copy the code

Increased 3.The importbutton

<template>
  <div class="dashboard-container">
    <! Import button -->
    <div class="button_group">
      <a
        href="javascript:;"
        class="button_s my_file el-button button_s el-button--primary el-button--small"
      >
        <input type="file" class="my_input" @change="importExcel" id="upload" />The import</a>
    </div>
    <! Import button -->
  </div>
</template>

<script>
export default {
  name: 'Dashboard'.methods: {
    /** * import table */
     importExcel(e){}}}</script>

<style lang="scss" scoped>// Button style.button_group {
  .button_s {
    width: 78px;
    margin: 5px 10px 5px 5px;
  }
  .button_m {
    width: 100px;
    margin: 5px 10px 5px 5px;
  }
  .my_file {
    position: relative;
    .my_input {
      position: absolute;
      opacity: 0;
      width: 78px;
      height: 30px;
      top: 0;
      left: 0; }}} // Button style</style>
Copy the code

4. Save and refresh.

5. Download XLSX, introduction;

6. WriteThe import formFunction, save refresh;

<script>
import xlsx from "xlsx";
export default {
  name: 'Dashboard'.methods: {
    /** * import table */
     importExcel(e) {
      const files = e.target.files;
      console.log(files);
      if(! files.length) {return ;
      } else if (!/\.(xls|xlsx)$/.test(files[0].name.toLowerCase())) {
        return alert("Upload format is not correct, please upload XLS or XLSX format");
      }
      const fileReader = new FileReader();
      fileReader.onload = ev= > {
        try {
          const data = ev.target.result;
          const XLSX = xlsx;
          const workbook = XLSX.read(data, {
            type: "binary"
          });
          const wsname = workbook.SheetNames[0]; Wb. SheetNames[0] is the name of the first Sheet in Sheets
          const ws = XLSX.utils.sheet_to_json(workbook.Sheets[wsname]); Wb.Sheets[Sheet name] get data from the first Sheet
          const excellist = []; // Clear the received data
          // Edit data
          for (var i = 0; i < ws.length; i++) {
            excellist.push(ws[i]);
          }
          console.log("Read results", excellist); // The result is an array of objects
        } catch (e) {
          return alert("Read failed!");; }}; fileReader.readAsBinaryString(files[0]);
      var input = document.getElementById("upload");
      input.value = "";
    }
  }
}
</script>
Copy the code

7. Prepare the following table forTesting capabilities ；

8. Sometimes, the table title is Chinese, after reading we want to get the English attribute name, soAdd the following code, test again;

<script>
import xlsx from "xlsx";
export default {
  name: 'Dashboard'.methods: {
    getHeader(sheet) {
      const XLSX = xlsx;
      const headers = [];
      const range = XLSX.utils.decode_range(sheet[! "" ref"]); // worksheet['! Ref '] is the valid range of the worksheet
      let C;
      Start in the first row */
      const R = range.s.r; // row // column C
      let i = 0;
      for (C = range.s.c; C <= range.e.c; ++C) {
        var cell =
          sheet[
            XLSX.utils.encode_cell({ c: C, r: R })
          ]; Find the cell in the first row */
        var hdr = "UNKNOWN" + C; // If the header is empty, replace with your desired default
        // xlsx.utils.format_cell generates cell text values
        if (cell && cell.t) hdr = XLSX.utils.format_cell(cell);
        if(hdr.indexOf('UNKNOWN') > -1) {if(! i) { hdr ='__EMPTY';
          }else {
            hdr = '__EMPTY_' + i;
          }
          i++;
        }
        headers.push(hdr);
      }
      return headers;
    },
    /** * import table */
     importExcel(e) {
      const files = e.target.files;
      console.log(files);
      if(! files.length) {return ;
      } else if (!/\.(xls|xlsx)$/.test(files[0].name.toLowerCase())) {
        return alert("Upload format is not correct, please upload XLS or XLSX format");
      }
      const fileReader = new FileReader();
      fileReader.onload = ev= > {
        try {
          const data = ev.target.result;
          const XLSX = xlsx;
          const workbook = XLSX.read(data, {
            type: "binary"
          });
          const wsname = workbook.SheetNames[0]; Wb. SheetNames[0] is the name of the first Sheet in Sheets
          const ws = XLSX.utils.sheet_to_json(workbook.Sheets[wsname]); Wb.Sheets[Sheet name] get data from the first Sheet
          const excellist = []; // Clear the received data
          // Edit data
          for (var i = 0; i < ws.length; i++) {
            excellist.push(ws[i]);
          }
          console.log("Read results", excellist); // The result is an array of objects
          // get the header 2-1
          const a = workbook.Sheets[workbook.SheetNames[0]].const headers = this.getHeader(a);
          console.log('headers', headers);
          // get the header 2-2
        } catch (e) {
          return alert("Read failed!");; }}; fileReader.readAsBinaryString(files[0]);
      var input = document.getElementById("upload");
      input.value = "";
    }
  }
}
</script>
Copy the code

We changed the table to an irregular state, saved it, and opened the interface to test it

Step 3: Implement table rendering

1. The interface is addedForm components 。

<! -- Table component --><div class="myTable">
      <el-table
        max-height="600"
        :data="dataArr"
        v-loading="tableLoading"
        :span-method="objectSpanMethod"
        border
        style="width: 100%"
      >
        <el-table-column
          :prop="item.prop"
          :label="item.label"
          :width="item.width"
          v-for="(item, i) in tableColumn"
          :key="i"
        ></el-table-column>
      </el-table>
    </div><! -- Table component -->Copy the code
data() {
    return {
      dataArr: [].// Table content data array
      CountArr: {}, countArr: {}, countArr: {}, countArr: {}, countArr: {}, countArr: {}, countArr: {
      tableColumn: [].// Table header configuration array
      tableLoading: false // Whether the table is loaded
    };
  },
Copy the code

2. AddTable rendering method 。

Note: part of the code in the table rendering method is used to map the Chinese and English attribute names, which is a function I added, sometimes it is not necessary to use, you can modify the code according to your own needs;

setTable(headers, excellist) {
      const tableTitleData = []; // Store table header data
      const tableMapTitle = {}; // Set the table contents in Both Chinese and English
      headers.forEach((_, i) = > {
        tableMapTitle[_] = "prop" + i;
        tableTitleData.push({
          prop: "prop" + i,
          label: _.width: 100
        });
      });
      console.log("tableTitleData", tableTitleData);
      // The mapping table content attribute name is English
      const newTableData = [];
      excellist.forEach(_= > {
        const newObj = {};
        Object.keys(_).forEach(key= > {
          newObj[tableMapTitle[key]] = _[key];
        });
        newTableData.push(newObj);
      });
      console.log('newTableData',newTableData);
      this.tableColumn = tableTitleData;
      this.dataArr = newTableData;
    },
Copy the code

3. CallTable rendering method 。

		// Add the following code to the importExcel(e) method
          // Render table 1-1
          this.setTable(headers, excellist);
          // Render table 1-2
Copy the code

  1. A functional test

conclusion

This code supports irregular data, no table header can also render to the interface oh ~~ welcome to point out my code error ~ if there is a better way to write, welcome to put forward, common progress yo ~~