@

• This paper aims to solve the need for the front-end to read the table file, obtain the file content and render to the interface without calling the back-end interface
• My other articles can address expansion requirements:
• Perform automatic cell merging after reading the parsed table
• After reading the parsing table, the cell color is automatically set according to the data comparison and analysis
• After reading the analysis table, perform data analysis (dialysis) to generate the eckarts diagram that can meet the user-defined needs
• Download interface table function

explain

The company usually has a lot of background management systems, and similar requirements are very common. I have written similar posts, but they only put code and never write comments and steps. Hey hey, don't say much. This article is a complete record:

premise

I often use:
Ant Design + Angular
Element UI + Vue
Ant Design + Vue
For convenience, today we use Element UI + Vue
Start directly based on Vue element admin

Code warehouse

1.Code cloud address
2.github address

Step 1: preparation

1. Click to enter Vue element admin Download
   
2. Download and unzip
3. Installation and operation
   
4. Run successfully
   

Step 2: import table parsing

1. Enter the following path;

src\views\dashboard\index.vue

1. Delete useless code and prepare to start;

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

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

<style lang="scss" scoped>

</style>

1. Add the import button to save and refresh;

<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" />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>

1. Download xlsx and import;
   

2. Write import form function, save and 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("The upload format is incorrect, please upload xls perhaps 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]; //Take the first table, WB Sheetnames [0] is the name of the first Sheet in the Sheets
          const ws = XLSX.utils.sheet_to_json(workbook.Sheets[wsname]); //Generate json table content, WB Sheets [Sheet name] get the data of the first Sheet
          const excellist = []; //Clear received data
          //Edit data
          for (var i = 0; i < ws.length; i++) {
            excellist.push(ws[i]);
          }
          console.log("Read results", excellist); // At this point, you get an array of objects that need to be processed
        } catch (e) {
          return alert("read failure!");;
        }
      };
      fileReader.readAsBinaryString(files[0]);
      var input = document.getElementById("upload");
      input.value = "";
    }
  }
}
</script>

1. Write the following table to test the function;
   
   

2. Sometimes, the title of the form is Chinese. If we want to get the English attribute name after reading, add the following code and 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 a valid range of worksheets
      let C;
      /* Get cell value 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 there is an empty header, it will be replaced with the default value you want replace with your desired default
        // XLSX.utils.format_cell generates a cell text value
        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("The upload format is incorrect, please upload xls perhaps 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]; //Take the first table, WB Sheetnames [0] is the name of the first Sheet in the Sheets
          const ws = XLSX.utils.sheet_to_json(workbook.Sheets[wsname]); //Generate json table content, WB Sheets [Sheet name] get the data of the first Sheet
          const excellist = []; //Clear received data
          //Edit data
          for (var i = 0; i < ws.length; i++) {
            excellist.push(ws[i]);
          }
          console.log("Read results", excellist); // At this point, you get an array of objects that need to be processed
          // Get header 2-1
          const a = workbook.Sheets[workbook.SheetNames[0]];
          const headers = this.getHeader(a);
          console.log('headers', headers);
          // Get header 2-2
        } catch (e) {
          return alert("read failure!");;
        }
      };
      fileReader.readAsBinaryString(files[0]);
      var input = document.getElementById("upload");
      input.value = "";
    }
  }
}
</script>

We change the form to irregular state, save and open the interface for test

Step 3: realize table rendering

1. Add table components to the interface.

<!-- 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 -->

data() {
    return {
      dataArr: [], // Table content data array
      // countArr: {}, / / analyze the table data and header to get a cross reference array for user-defined merging. This article only writes the basics for the time being, and does not introduce the automatic merging of cells ~ ~ my other articles have written the implementation method of user-defined merging~
      tableColumn: [], // Table header configuration array
      tableLoading: false // Is the form loading
    };
  },

2. Add table rendering method.

Note: some codes in the table rendering method are used to map Chinese and English attribute names. This is a function I added. Sometimes it doesn't need to be used. You can modify the code according to your own needs;

setTable(headers, excellist) {
      const tableTitleData = []; // Store table header data
      const tableMapTitle = {}; // Set the table content 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;
    },

1. Call the table rendering method.

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

1. functional testing
   

epilogue

This code supports irregular data. Those without header can also be rendered to the interface~~
Welcome to point out the error of my code~
If there is a better way to write, you are welcome to put forward it and make common progress~~