SheetJS实现excel预览编辑

一.常见SheetJS操作

1.读取excel文件

// 监听文件的上传, 调用该方法
function readWorkbookFromLocalFile(file, callback) {
    var reader = new FileReader();
    reader.onload = function(e) {
        var data = e.target.result;
        var workbook = XLSX.read(data, {type: 'binary'});
        if(callback) callback(workbook);
    };
    // 读取本地excel文件
    reader.readAsBinaryString(file);
}

2.生成sheet对象

function readWorkbook(workbook) {
    var sheetNames = workbook.SheetNames; // 工作表名称集合
    let worksheets = [];
    sheetNames.forEach(sheetName => {
        let worksheet = workbook.Sheets[sheetName];
        worksheets.push({sheetName,worksheet});
    });
    let sheetObj = {}
    worksheets.forEach(ele => {
        let {sheetName, worksheet} = ele;
        let json = XLSX.utils.sheet_to_json(worksheet);
        sheetObj[sheetName] = json
    })
}

3.json转excel对象

let jsonObj = {};
for (const key in sheetObj) {
    if (Object.hasOwnProperty.call(sheetObj, key)) {
        const element = sheetObj[key];
        jsonObj[key] = XLSX.utils.json_to_sheet(element);
    }
}
let sheetKeys = Object.keys(jsonObj);
let blob = sheet2blob(jsonObj, sheetKeys);

4.sheet对象转文件流

// 将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载
function sheet2blob(sheets={}, sheetNames=[]) {
    sheetNames = sheetNames || ['sheet1'];
    var workbook = {
        SheetNames: [...sheetNames],
        Sheets: {}
    };
    sheetNames.forEach((sheetName) => {
        workbook.Sheets[sheetName] = sheets[sheetName];
    })
    // 生成excel的配置项
    var wopts = {
        bookType: 'xlsx', // 要生成的文件类型
        bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
        type: 'binary'
    };
    var wbout = XLSX.write(workbook, wopts);
    var blob = new Blob([s2ab(wbout)], {type:"application/octet-stream"});
    // 字符串转ArrayBuffer
    function s2ab(s) {
        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;
    }
    return blob;
}