introduce

Recently, a function of importing and exporting Excel has been implemented in the project. After looking up some plug-ins, we found the JS xlsx plug-in, so we tried to use it. Here, we will simply record the use methods and problems encountered.

Sheetjs JS xlsx is a plug-in that can read and write various forms. The browser supports well and can be used on multiple language platforms. At present, there are 14k star s on github.
Plug in address: https://github.com/SheetJS/js...

Use

1. Installation dependency

Enter the project folder and install xlsx

yarn add xlsx

2. Introduce in the project

import * as XLSX from 'xlsx'

Export Excel function implementation

1. Define the methods to be used when exporting

export default function download(json,fileName){
    const type = 'xlsx'//Define the format of the exported file
    var tmpDown;//Exported content
    var tmpdata = json[0];
    json.unshift({});
    var keyMap = []; //Get keys
    for (var k in tmpdata) {
        keyMap.push(k);
        json[0][k] = k;
    }
    var tmpdata = [];//Used to save the converted json 
    
    json.map((v, i) => keyMap.map((k, j) => Object.assign({}, {
        v: v[k],
        position: (j > 25 ? getCharCol(j) : String.fromCharCode(65 + j)) + (i + 1)
    }))).reduce((prev, next) => prev.concat(next)).forEach((v, i) => tmpdata[v.position] = {
        v: v.v
    });
    var outputPos = Object.keys(tmpdata); //Set area, such as table from A1 to D10
    var tmpWB = {
        SheetNames: ['mySheet'], //Saved table title
        Sheets: {
            'mySheet': Object.assign({},
                tmpdata, //content
                {
                    '!ref': outputPos[0] + ':' + outputPos[outputPos.length - 1] //Set filled area
                })
        }
    };
    tmpDown = new Blob([s2ab(XLSX.write(tmpWB, 
        {bookType: (type == undefined ? 'xlsx':type),bookSST: false, type: 'binary'}//The data here is used to define the format type of the export
        ))], {
        type: ""
    }); //Create binary object write converted byte stream
    saveAs(tmpDown,fileName);
}

function saveAs(obj, fileName){//Export function implementation
    var tmpa = document.createElement("a");
    tmpa.download = fileName || "download";
    tmpa.href = URL.createObjectURL(obj); //Bind a tag
    tmpa.click(); //Analog Click to download
    setTimeout(function () { //Delayed release
        URL.revokeObjectURL(obj); //Use URL.revokeObjectURL() to release the object URL
    }, 100);
}

function s2ab(s){ //String to character stream
    var buf = new ArrayBuffer(s.length);
    var view = new Uint8Array(buf);
    for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
    return buf;
}

function getCharCol(n){
    let temCol = '',
    s = '',
    m = 0
    while (n > 0) {
        m = n % 26 + 1
        s = String.fromCharCode(m + 64) + s
        n = (n - m) / 26
    }
    return s
}

2. Export method used in the project

//Export excel
//Export excel
downloadExl = () => {
    const { results } = this.props //json data to be exported
    let datas = _.clone(results)//In order not to affect the use of project data, the deep clone method in lodash is adopted here
    let json = datas.map(item=> { //Replace the key name of json data with the one required for export
        return {
            'personnel ID' :item.id,
            'Full name' : item.name,
            'Document type': this.findIdType(item.idType),//Convert type code to Chinese character
            'Identification Number': item.credentialsId,
            'Fixed telephone': item.tel,
            'Mobile phone': item.mobile
        }
    })
    download(json,'Personnel information.xlsx')//Exported file name
}

3. Binding event

<Button onClick={this.downloadExl}>export Excel</Button>

In this way, the export function of Excel can be easily realized

Import Excel function implementation

1. Define the methods to be used when importing

//Import excel
onImportExcel = file => {
    // Get uploaded file object
    const { files } = file.target;
    // Read file through FileReader object
    const fileReader = new FileReader();
    fileReader.onload = event => {
        try {
            const { result } = event.target;
            // Reading the whole excel table object by binary stream
            const workbook = XLSX.read(result, { type: 'binary' });
            // Store acquired data
            let data = [];
            // Traverse each sheet for reading (only the first sheet is read by default here)
            for (const sheet in workbook.Sheets) {
            // esline-disable-next-line
            if (workbook.Sheets.hasOwnProperty(sheet)) {
                // Using the sheet to json method to convert excel to json data
                data = data.concat(XLSX.utils.sheet_to_json(workbook.Sheets[sheet]));
                // break; / / uncomment this row if only the first table is taken
            }
        }
        // The json data finally obtained and formatted
        const uploadData = data.map(item=> {
            return {
                id : Number(item['personnel ID']),
                name : item['Full name'],
                idType: this.findIdType(item['Document type'],'string'),
                credentialsId: item['Identification Number'],
                tel: item['Fixed telephone'],
                mobile: item['Mobile phone']
            }
        })
        console.log(uploadData)//Here we get the json data needed by the back end. Just transfer the calling interface to the back end
        message.success('Upload succeeded!') //The message component in antd is used here
        } catch (e) {
            // Here you can throw a prompt about incorrect file type
            message.error('Incorrect file type!');
        }
    };
    // Open file in binary mode
    fileReader.readAsBinaryString(files[0]);
}

2. Binding event

<Button className={Styles.upload_wrap}>
    Import Excel
    <input className={Styles.file_uploader} type='file' accept='.xlsx, .xls' onChange={this.onImportExcel} /> 
    //Here, the original input style is modified. The accept attribute defines the type of upload file support. The importExcel method in onChange operation defines the operation to be performed when the file is uploaded. 
</Button>

3. Modify style

.upload_wrap {
  display: inline-block;
  position: relative;
  width: 94px;
  padding: 3px 5px;
  overflow: hidden;
}

.file_uploader {
  position: absolute;
  width: 100%;
  height: 100%;
  top: 0;
  left: 0;
  outline: none;
  opacity: 0;
  background-color: transparent;
}

.upload_text {
  display: inline-block;
  margin-left: 5px;
}

.upload_tip {
  display: inline-block;
  margin-left: 10px;
  color: #999;
}

4. Make corresponding prompts for file upload and read results respectively (here use the message component in ant design)

5. json data obtained

The import and export function is implemented in this way, isn't it very simple.