x-data-spreadsheet 在线编辑excel文件，支持导入/导出/上传/读取网络 excel，合并单元格（vue版本）

1. 环境

vue：2.6.10
x-data-spreadsheet：1.1.8
less：4.0.0
less-loader：7.1.0
xlsx：0.15.1
axios：0.18.1

2. 优点

1. 可以导入本地excel文件，读取文件数据及合并单元格信息，呈现在线上excel中。
2. 可以将线上的excel导出成excel文件，并保留合并单元格信息
3. 可直接上传在线的excel文件
4. 可直接读取网络上的excel文件

3. 缺点

1. 不能读取本地excel文件中的样式信息
2. 不能导出线上excel文件样式信息

3. 代码

3.1 前端代码

<template>
    <div>
        <div class="mb-md">
            <input type="file" @change="getWorkbook">
            <button @click="exportJson">导出JSON</button>
            <button @click="exportExcel">导出xlsx</button>
            <button @click="uploadExcel">上传xlsx</button>
            <button @click="downloadExcel">下载xlsx</button>
        </div>
        <!--web spreadsheet组件-->
        <div id="x-spreadsheet-demo"></div>
    </div>
</template>

<script>
//引入依赖包
import zhCN from 'x-data-spreadsheet/src/locale/zh-cn';
import Spreadsheet from 'x-data-spreadsheet';
import XLSX from 'xlsx'
import axios from 'axios'
//设置中文
Spreadsheet.locale('zh-cn',zhCN);
export default {
    name: "xspreadsheet-demo",
    data() {
        return {
            xs: null,
            jsondata: {
                type: '',
                label: ''
            },
        };
    },
    mounted() {
        this.init()
    },
    methods: {
        init() {
            this.xs = new Spreadsheet('#x-spreadsheet-demo', {showToolbar: true, showGrid: true})
                .loadData([]).change((cdata) => {
                    // console.log(cdata);
                    console.log('>>>', this.xs.getData());
                });

            this.xs.on('cell-selected', (cell, ri, ci) => {
                console.log('cell:', cell, ', ri:', ri, ', ci:', ci);
            }).on('cell-edited', (text, ri, ci) => {
                console.log('text:', text, ', ri: ', ri, ', ci:', ci);
            });

            setTimeout(() => {
                // xs.loadData([{ rows }]);
                // xs.cellText(14, 3, 'cell-text').reRender();
                // console.log('cell(8, 8):', this.xs.cell(8, 8));
                // console.log('cellStyle(8, 8):', this.xs.cellStyle(8, 8));
            }, 5000);
        },
        loadExcelFile(fileSelected) {
            var workbook_object = this.getWorkbook(fileSelected)
            this.xs.loadData(this.stox(workbook_object));
        },
        /** 导出excel */
        exportExcel(){
            var new_wb = this.xtos(this.xs.getData());
            /* generate download */
            XLSX.writeFile(new_wb, "SheetJS.xlsx");
        },
        exportJson(){
            console.log(this.xs.getData())
        },
        /** 下载并读取excel */
        downloadExcel(){
            axios.get("http://localhost:8088/api/v1/test/test1",{ responseType: 'arraybuffer'}).then(res=>{
                if(res.status == 200) {
                    var data = res.data
                    console.log('data',data)
                    var data = new Uint8Array(data)
                    var workbook = XLSX.read(data, {type: 'array'});
                    this.xs.loadData(this.stox(workbook));
                }
            })
        },
        /** 上传excel */
        uploadExcel(){
            var new_wb = this.xtos(this.xs.getData());
            var wbout = XLSX.write(new_wb, {type: 'binary'})
            console.log('new_wb',new_wb)
            var file = new Blob([this.s2ab(wbout)]);
            var forms = new FormData()
            var configs = {
                headers:{'Content-Type':'multipart/form-data'}
            };
            forms.append('file',file)
            forms.append('token','231231')
                        
            axios.post("http://localhost:8088/api/v1/test/test",forms ,configs).then(res=>{
                console.log(res)
            })
        },
        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;
        },
        /** 将x-data-spreadsheet中的数据格式转为xlsx中的workbook */
        xtos(sdata) {
            console.log(sdata)
            var out = XLSX.utils.book_new();
            sdata.forEach(function(xws) {
                var aoa = [[]];
                var rowobj = xws.rows;
                for(var ri = 0; ri < rowobj.len; ++ri) {
                    var row = rowobj[ri];
                    if(!row) continue;
                    aoa[ri] = [];
                    Object.keys(row.cells).forEach(function(k) {
                        var idx = +k;
                        if(isNaN(idx)) return;
                        aoa[ri][idx] = row.cells[k].text;
                    });
                }
                var ws = XLSX.utils.aoa_to_sheet(aoa);

                /** 读取在线中的合并单元格，并写入导出的数据中
                 * merges: Array(19)
                    0: "A16:P16"
                    1: "A17:P17"
                    2: "O2:P2"
                    3: "F2:G2"
                 */
                ws['!merges'] = []
                xws.merges.forEach(merge =>{
                    ws['!merges'].push(XLSX.utils.decode_range(merge))
                })

                XLSX.utils.book_append_sheet(out, ws, xws.name);
            });
            return out;
        },
        stox(wb) {
            var out = [];
            wb.SheetNames.forEach(function (name) {
                var o = {name: name, rows: {} ,merges:[]};
                var ws = wb.Sheets[name];
                var aoa = XLSX.utils.sheet_to_json(ws, {raw: false, header: 1});
                aoa.forEach(function (r, i) {
                    var cells = {};
                    r.forEach(function (c, j) {
                        cells[j] = ({text: c});
                    });
                    o.rows[i] = {cells: cells};
                })
                // 设置合并单元格
                ws['!merges'].forEach(merge => {
                    /** merge = {
                     *  s: {c: 0, r: 15}
                     *  e: {c: 15, r: 15} 
                     * }
                     */
                    // 修改 cell 中 merge [合并行数,合并列数]
                    let cell = o.rows[merge.s.r].cells[merge.s.c]

                    //无内容单元格处理
                    if (!cell){
                        cell = {text: ""}
                    }
                    cell.merge = [merge.e.r-merge.s.r,merge.e.c-merge.s.c]
                    o.rows[merge.s.r].cells[merge.s.c] = cell

                    // 修改 merges
                    o.merges.push(XLSX.utils.encode_range(merge))
                })
                out.push(o);
            });
            return out;
        },
        /**
         * 获取文件
         * @param fileSelected
         */
        getWorkbook(fileSelected) {
            console.log('fileSelected',fileSelected)
            let file = fileSelected.target.files[0]
            let reader = new FileReader()
            reader.onload = e => {
                let data = e.target.result,
                    fixedData = this.fixData(data),
                    workbook = XLSX.read(btoa(fixedData), {type: 'base64'})
                this.xs.loadData(this.stox(workbook));
                // console.log("workbook",workbook)
                console.log("fixedData",fixedData)
                // console.log("this.stox(workbook)",this.stox(workbook))
            }
            reader.readAsArrayBuffer(file)
            // return workbook
        },
        fixData(data) {
            var o = "", l = 0, w = 10240
            for (; l < data.byteLength / w; ++l) o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w, l * w + w)))
            o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w)))
            return o
        },
    }
}
</script>
<style scoped>
</style>

3.2 后端php代码

public function test(){
    $file=fopen('xxxxxx', 'r');
    $filesize=filesize('xxxxx');
    header("Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    header("Accept-Ranges: bytes");
    header("Content-Length: $filesize");
    Header("Content-Disposition: attachment; filename=" . basename('xxxxx'));
    ob_clean();
    flush();
    $data = fread($file, $filesize);
    echo $data;
}