Home > Article > Web Front-end > How to use js-xlsx to merge cells

How to use js-xlsx to merge cells

php中世界最好的语言Original: 2018-03-12 15:02:0011159browse

This time I will show you how to use s-xlsx to merge cells, and what are the precautions for using s-xlsx to merge cells. The following is a practical case, let's take a look.

Import data observation data format

1.1. Let’s first create an xlsx table with merged cells

Let’s take the example of header data merging:

How to use js-xlsx to merge cells

Example

1.2. Write a simple data import function (you can refer to the article in the preface to write it, I will not put the code )

Import xlsx reference data format:

Example

How to use js-xlsx to merge cells

##1.3. Check the official website instructions

How to use js-xlsx to merge cells

Official website example (http://sheetjs.com/demos/modify.html):

Official website example

How to use js-xlsx to merge cells

According to the official website description, we can simply see that the data format of merged cells is:

........
data["!merges"] = [{
                s: {//s为开始
                    c: 1,//开始列
                    r: 0//可以看成开始行,实际是取值范围
                },
                e: {//e结束
                    c: 4,//结束列
                    r: 0//结束行
                }
            }];
........

2. Hands-on experiment

2.1. Write a simple Export demo

<!DOCTYPE html><html><head>
    <meta charset="UTF-8">
    <title></title></head><body>
    <script src="http://oss.sheetjs.com/js-xlsx/xlsx.full.min.js"></script>
    <!--调用FileSaver saveAs函数可以实现文件下载-->
    <!--<script src="http://sheetjs.com/demos/Blob.js"></script>
    <script src="http://sheetjs.com/demos/FileSaver.js"></script>-->
    <script>
        //如果使用 FileSaver.js 就不要同时使用以下函数
        function saveAs(obj, fileName) {//当然可以自定义简单的下载文件实现方式 
            var tmpa = document.createElement("a");
            tmpa.download = fileName || "下载";
            tmpa.href = URL.createObjectURL(obj); //绑定a标签
            tmpa.click(); //模拟点击实现下载
            setTimeout(function () { //延时释放
                URL.revokeObjectURL(obj); //用URL.revokeObjectURL()来释放这个object URL
            }, 100);
        }        var jsono = [{ //测试数据
            "id": 1,//A
            "合并的列头1": "数据11",//B
            "合并的列头2": "数据12",//C
            "合并的列头3": "数据13",//D
            "合并的列头4": "数据14",//E
        }, {            "id": 2,            "合并的列头1": "数据21",            "合并的列头2": "数据22",            "合并的列头3": "数据23",            "合并的列头4": "数据24",
        }];//....
        const wopts = { bookType: &#39;xlsx&#39;, bookSST: true, type: &#39;binary&#39; };//这里的数据是用来定义导出的格式类型 
        function downloadExl(data, type) {            var wb = { SheetNames: [&#39;Sheet1&#39;], Sheets: {}, Props: {} };            //wb.Sheets[&#39;Sheet1&#39;] = XLSX.utils.json_to_sheet(data);//通过json_to_sheet转成单页(Sheet)数据
            data = XLSX.utils.json_to_sheet(data); 
            data["B1"] = { t: "s", v: "asdad" };
            data["!merges"] = [{//合并第一行数据[B1,C1,D1,E1]
                s: {//s为开始
                    c: 1,//开始列
                    r: 0//开始取值范围
                },                e: {//e结束
                    c: 4,//结束列
                    r: 0//结束范围
                }
            }];
            wb.Sheets[&#39;Sheet1&#39;] = data;
            saveAs(new Blob([s2ab(XLSX.write(wb, wopts))], { type: "application/octet-stream"}), "这里是下载的文件名" + &#39;.&#39; + (wopts.bookType == "biff2" ? "xls" : wopts.bookType));
        }        function s2ab(s) {            if (typeof ArrayBuffer !== &#39;undefined&#39;) {                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;
            } else {                var buf = new Array(s.length);                for (var i = 0; i != s.length; ++i) buf[i] = s.charCodeAt(i) & 0xFF;                return buf;
            }
        }    </script>
    <button onclick="downloadExl(jsono)">导出</button></body></html>

I believe you have mastered the method after reading the case in this article. For more exciting information, please pay attention to other related articles on the PHP Chinese website!

How to use js-xlsx to merge cells

Related articles