js将数据导出至本地excel文件

June 8, 2020

1791 views

2175 words

废话不多说，直接上代码

// downloadXLSX.js
import XLSX from 'xlsx';
export default (fileName = 'default', data) => {
    let getCharCol = n => {
        let s = "",
            m = 0
        while (n > 0) {
            m = n % 26 + 1
            s = String.fromCharCode(m + 64) + s
            n = (n - m) / 26
        }
        return s
    }

    let string2ArrayBuffer = s => {
        let buf = new ArrayBuffer(s.length)
        let view = new Uint8Array(buf)
        for (let i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF
        return buf
    }

    let saveAs = (obj, fileName) => {
        let tmpa = document.createElement("a")
        tmpa.download = fileName + '.xlsx'
        tmpa.href = URL.createObjectURL(obj)
        tmpa.click()
        setTimeout(() => URL.revokeObjectURL(obj), 100)
    }

    let content = {}

    data.map((r, i) => r.map((j, k) => content[i > 25?getCharCol(i) + (k + 1):String.fromCharCode(65 + i) + (k + 1)] = { v: j }

    //设置区域,比如表格从A1到D10,SheetNames:标题
    let coordinate = Object.keys(content)

    // 表起始位置 一般为A1
    let start = coordinate[0]

    let key = 0

    data.map(r => r.map((j, i) => i + 1 >= key?key = i + 1:key))

    let header = coordinate[coordinate.length - 1].replace(/[0-9]/ig, '')

    // 表结束位置，一般为最后列的字母 + 最长长度
    let end = header + key

    let workBook = {
        SheetNames: ["Sheet1"],
        Sheets: {
            "Sheet1": Object.assign({}, content, { "!ref": start + ":" + end }),
        },
    }

    //这里的数据是用来定义导出的格式类型
    let excelData = XLSX.write(workBook, { bookType: "xlsx", bookSST: false, type: "binary" })

    let blob = new Blob([string2ArrayBuffer(excelData)], { type: "" })

    saveAs(blob, fileName)
}

所需依赖

xlsx

npm i xlsx
yarn add xlsx

使用方法

import donwloadXLSX from 'downloadXLSX'

let fileName = 'demo'

// 其中data为二维数组，数组的子数组，分别为xlsx每一列的数据
let data = [
    ['名字1', 'demo1'],
    ['名字2', 'demo2']
]

donwloadXLSX(fileName, data)

执行后即会下载xlsx文件，文件名为demo.xlsx，以下为表格内容