使用spreadJS excel时，压缩ssjson并使用aja

背景

由于spreadJS生成的ssjson是json格式，导致相比excel格式会大出很多，所以需要在前后台交互时做下压缩。

参考大小

注意点

1. 提交数据时的contentType，FormData需要将部分数据放入该值，所以指定为false
2. XHR获取二进制数据只能使用异步模式，不能使用同步模式，即async必须设置为true（默认即为true）
3. 目前jquery并不直接支持获取二进制数据，这里使用了jquery-ajax-native.js，当然这并不是必须的，你也可以使用原生XHR，方法参考底部链接

代码

pako.min.js
jquery-ajax-native.js

<script src="assets/libs/compress/zlib/pako.min.js"></script>
<script src="assets/libs/jquery/jquery-ajax-native.js"></script>

function saveExcelData(spread, sheet) {
    const requestPayload = new FormData();
    requestPayload.append('displayTitleName', $('input[name=titleName]:first').val());
    requestPayload.append('excelData', JSON.stringify(sheet.getDatasource()));
    {
        let fullExcelSSJson = JSON.stringify(spread.toJSON({
            includeBindingSource: true
        }));
        // 压缩ssJson
        let bytes = pako.deflate(new TextEncoder().encode(fullExcelSSJson));
        requestPayload.append('compressSSJson', new Blob([bytes], {type: 'application/octet-stream'}));
    }
    return $.ajax({
        url: '/saveExcelData',
        method: 'POST',
        contentType: false,
        processData: false,
        data: requestPayload,
        async: false
    });
}
function loadCompressSSJson(spread) {
    $.ajax({
        url: '/loadCompressExcelSSJson',
        method: 'GET',
        async: true, // 二进制加载不能同步请求
        dataType: 'native',
        xhrFields: {
            responseType: 'arraybuffer'
        },
        data: {
            batchID: excelDataBatchID
        }
    }, function (data) {
        spread.fromJSON(JSON.parse(new TextDecoder().decode(pako.inflate(new Uint8Array(data)))));
    });
}

@Data
public class ExcelBatchHistory {
    private byte[] compressSsJson;
}

@RestController
@RequestMapping(value = "/")
public class IndexController {
    @PostMapping(value = "/saveExcelData")
    public String saveExcelData(@RequestParam("displayTitleName") String displayTitleName,
                                @RequestParam("excelData") String excelDataRequestJson,
                                @RequestParam("compressSSJson") MultipartFile compressSSJson) throws IOException {
        byte[] compressSSJsonByte = compressSSJson.getBytes();

        MyModel m = new MyModel();
        m.setId(111L);
        m.setCompressSsJson(compressSSJsonByte);

        baseMapper.insert(m);

        return "{}";
    }

    @GetMapping(value = "/loadCompressExcelSSJson", produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)
    public byte[] loadCompressExcelSSJson() throws IOException, IntrospectionException {
        MyModel m = baseMapper.selectById(111L);
        return m.getCompressSsJson();
    }
}

create table excel_batch_history
(
    compress_ss_json mediumblob null comment '压缩过的ssJson数据',
    create_date      datetime   not null,
    history_id       bigint auto_increment
        primary key
);

相关链接

• spreadJS 官网
• decompress gzip and zlib string in javascript
• javascript sending arraybuffer using xmlhttprequest
• JS压缩库pako
• JQuery拓展jquery ajax native
• sending multipart formdata with jquery ajax（使用ajax发送二进制数据）
• converting between strings and arraybuffers(js中转换字符串和byte数组)
• using jquery ajax to download a binary file(使用ajax获取二进制文件)
• issue-为jquery添加二进制数据的原生支持