This is the second day of my participation in Gwen Challenge, for more details: Gwen Challenge!

Introduction to 👽

It’s not an impossible task to parse an Excel file with a small program. With SheetJs, it’s easy to do, but there are a few things worth noting and sharing along the way.

This practical demonstration takes UNIAPP development environment as an example.

👽 SheetJs introduction

SheetJs is a framework-independent Excel library that can read, write, create, and export Excel files in web or Node environments. SheetJs is the core tool for this practice.

👽 module introduction

👻 Import tool files

Find the build file in the Dist directory in Github’s SheetJS. There are a number of versions available:

Xlsx.full.min. js: version containing all features and functions; Xlsx.core.min. js: version with only core functions;Copy the code

Because this project only involves the parsing of Excel files, and the small program package has a size requirement, so the simplified version of Xlsx.mini-min.js is selected.

Download the xlsx.min.js file and put it in the utils folder of the project for later use.

👻 Obtain the Excel file

Since the applet itself only supports uploading media files such as images, we chose to request an Excel file from the server.

<script>
import XLSX from '.. /.. /utils/xlsx.mini.min.js';/ / import SheetJS
export default {
  data() {
    return {
      fileLocation:'https://xxx.xxx.com/tagetFile.xlsx'.// File address
     fileData:null// Store parsed data}},created() {
    this.getFileData();
  },  
  methods: {
    getFileData() {
      uni.request({
        url: this.fileLocation,
        data: {
          v: parseInt(Math.random() * 1000000000),// This is used to prevent server cache
        },
        method: 'GET'.responseType: 'arraybuffer'.// Define the response type
        success:res= > {
          console.log(res.data)// This is the obtained binary data
          /*----- here are the following explanations: 1. We generally do not change the original data after parsing, but freeze it to optimize performance; 2. 2. {type: 'array'}: defines the parsed data format as array. 3. We only care about the Sheets workbook, so we get it directly. -- -- -- -- - * /
          this.fileData = Object.freeze(
              XLSX.read(new Uint8Array(res.data), { type: 'array' }).Sheets
          )
          console.log('File retrieval failed'.this.fileData);
        },
        fail:res= > {
          console.log('File retrieval failed', res); }}); }}},Copy the code

👻 Organize cell data

After the operation and processing in the previous step, we have successfully obtained the data in the Excel table, but at the same time, we can clearly find that these data are scattered in an array with cells as units, and these data often need further processing before they can be used.



<script>...export default{...data(){
    return {
      cookedSheetAData:null// Used to store SheetA data after processing}},methods: {/* Primitive table data processing function param:{sheetName,// columnSortMap,// mapping structure between column and field names target,// target to be filled} */
    processData(param) {
      let tableData = this.fileData[param.sheetName];

      let uselessKeys = Object.keys(tableData).filter(key= > key[0] = ='! '); // Filter non-data attributes
      uselessKeys.forEach(key= > delete tableData[key]); // Delete non-data attributes

      let usefulKeys = Object.keys(tableData);

      let titleRow = [1.2]; // Title line number
      let dataLength = usefulKeys.filter(key= > key[0] = ='A').length - titleRow.length; // Define the length of the content area array

      let emptyItem = {};
      Object.values(param.columnSortMap).forEach(value= > {
        emptyItem[value] = null;
      });
      this[param.target] = JSON.parse(JSON.stringify(Array(dataLength).fill(emptyItem))); // Create template data

      usefulKeys.forEach(key= > {
        // Resolve cell coordinates as 'A' and '3'
        // Because the number of columns in this table is small, the case of AZ31 coordinates is not described here. Use your own brains
        let columSort = key.match(/[A-Z]/g).join(' '); //'A'
        let columIndex = key.match(/\d/g).join(' '); / / '3'
        if(! titleRow.includes(+columIndex)) {// Assign the parsed value to data
          this[param.target][columIndex - titleRow.length - 1][param.columnSortMap[columSort]] =
            param.columnSortMap[columSort] == 'loanDate'? tableData[key].w : tableData[key].v; }});Object.freeze(this[param.target]);// freeze as usual
    },
    
    // Call a handler to process the corresponding workbook
    processSheetA() {
      let target = 'cookedSheetAData';// Data is the field used to store the corresponding data
      let sheetName = 'Primary Channel Lending List';// Workbook name
      let columnSortMap = {
        A: 'region'.B: 'rank'.C: 'name'.D: 'channelQuality2nd'.E: 'loanAmount'.F: 'finishedCount'.G: 'unfinishedCount'.H: 'refusedCount'.I: 'passingRate'.J: 'loanDate'.K: 'incrementRate'};// The mapping structure between column coordinates and entity fields (i.e. the data under column A corresponds to the Region field in the cookedSheetAData array)
      this.processData({ target, sheetName, columnSortMap }); }}},Copy the code

The next step is to call the corresponding method to process the data and display it on the page. And you’re done!

👽 epilogue

While the code development phase is important for Excel parsing, the definition of Excel content structure is even more important. A reasonable clear Excel spreadsheet can save a lot of things for the development work, you remember!! 🙊 🙊 🙊