preface

New companies often have some sort of data export capability in the background, and in many cases it is filtered data (not paginated).

At this point, it’s a bit wasteful for the back end to run through the logic, but it would be perfect if the front end could do it all by itself.

SheetJS(js-xlsx)

Project Address:sheetjs

Because the demand is relatively simple, just import and export, have not encountered any problems at present.

compatibility

use

The official readme.md has been introduced in great detail, so here’s a brief introduction.

Use the CDN

<script src="https://unpkg.com/xlsx/dist/xlsx.full.min.js"></script> 
Copy the code

The use of NPM

npm install xlsx

The official demo

There are many official demos available at Github

Some of the demos may have problems, so it’s best to run them once you watch them.

Simple download example

Aoa_to_sheet and JSON_TO_sheet are demonstrated here.

And table_to_sheet can convert DOM directly to Excel. At present, there is no such appeal, so we have not tried this approach. We guess that there may be some requirements for DOM.

demo

/**
 * JSON转换Excel
 */
function json2Sheet () {
    let json = [
        {
            "Name": "Zhang"."Gender": "Male"."Age": 18
        },
        {
            "Name": "Bill"."Gender": "Female"."Age": 19
        },
        {
            "Name": "Wang Erma"."Gender": "Unknown"."Age": 20}]// instantiate a workbook
    let book = XLSX.utils.book_new()

    // instantiate a Sheet
    let sheet = XLSX.utils.json_to_sheet(json, {
        header: ['name'.'gender'.'age']})// Write the Sheet to the workbook
    XLSX.utils.book_append_sheet(book, sheet, 'Sheet1')

    // Write the file to trigger the browser download directly
    XLSX.writeFile(book, 'json2Sheet.xlsx')}/** * Array conversion Excel */
function array2Sheet () {
    let data = [
        ['name'.'gender'.'age'],
        ['Joe'.'male'.'18'],
        ['bill'.'woman'.'the'],
        ['Wang Erma'.'unknown'.'20']]// instantiate a workbook
    let book = XLSX.utils.book_new()

    // instantiate a Sheet
    let sheet = XLSX.utils.aoa_to_sheet(data)

    // Write the Sheet to the workbook
    XLSX.utils.book_append_sheet(book, sheet, 'Sheet1')

    // Write the file to trigger the browser download directly
    XLSX.writeFile(book, 'array2Sheet.xlsx')}Copy the code

Simple parsing example

Here is the result of the input:file upload after parsing.

Two main methods are used: xlsx.read and xlsx.utils.sheet_to_AOa.

/**
 * JSON转换Excel
 */
function parseExcel (fileDom) {
    let file = fileDom.files[0]
    let reader = new FileReader()
    let rABS = typeofFileReader ! = ="undefined" && (FileReader.prototype||{}).readAsBinaryString
    if (rABS) {
        reader.readAsBinaryString(file)
    } else {
        reader.readAsArrayBuffer(file)
    }
    reader.onload = function(e) {
        let data = e.target.result
        if(! rABS) { data =new Uint8Array(data)
        }
        let workBook = XLSX.read(data, {type: rABS ? 'binary' : 'array'})
        workBook.SheetNames.forEach(name= > {
            let sheet = workBook.Sheets[name]
            let json = XLSX.utils.sheet_to_json(sheet, {
                raw: false.header: 1
            })
            // TODO handles data}}})Copy the code