Home > Article > Web Front-end > Detailed explanation of the use of js-xlsx tool library xlsxUtils

Detailed explanation of the use of js-xlsx tool library xlsxUtils

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

This time I will bring you a detailed explanation of the use of the js-xlsx tool library xlsxUtils. What are the precautions for using the js-xlsx tool library xlsxUtils. The following is a practical case. Let’s take a look. take a look.

Function description:

var xlsxUtils = {
    Binary: {
        fixdata(data) { //文件流转BinaryString
            //......
        },
        s2ab(s) { //字符串转字符流
            //......
        }
    },
    _wb: null,//缓存导入时的Workbook 
    _rABS: false,//设置读取方式
    /**
     * @desc  导入根据文件
     * @param {File} f 文件
     * @param {Function} c 回调
     * @return {Object} 回调值
     */
    import(f, c) {        //......
    },    /**
     * @desc  根据表Sheet名获取数据
     * @param {String} name 
     * @return {Object} 
     */
    getSheetByName(name) {//
        //......
    },    /**
     * @desc  根据表Sheet索引获取数据
     * @param {Number} index 
     * @return {Object}
     */
    getSheetByIndex(index = 0) {        //......
    },    /**
     * @desc 导出
     * @param {Array} data 数据{title1:dataList,title2:dataList....}  
     * @param {String} type 
     * @return {Blob}
     */
    export(data, type) {        //......
    },    /**
     * 从数据数组或对象中根据key生成相同key值的对象
     * @param {Object|Array} data 
     * @return {Object}
     */
    readDataHead(data) {        //......
    },    /**
     * @desc 格式化数据为Sheet格式
     * @param {Array} json 数据
     */
    format2Sheet(json, n) {        //......
    },    /**
     * @desc 格式化数据为Sheet格式
     * @param {Array} sheetData 
     * @param {String} title 
     * @param {Object} wb 
     */
    format2WB(sheetData, title = "mySheet", wb) {        //......
    },    /**
     * @desc 将xlsx Workbook 转为blob
     * @param {Array} wb 
     * @param {String} type 类型
     */
    format2Blob(wb, type) {        //......
    },    /**
     * @desc 匹配单元格对应的标识
     * @param {Number} n 
     */
    getCharCol(n) {       //......
    },
};

Example:

Import example (online example):

<!DOCTYPE html><html><head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <script src="http://oss.sheetjs.com/js-xlsx/xlsx.core.min.js"></script> 
    <script src="./xlsx.utils.js"></script></head><body>
    <input type="file" id="demo" />
    <div id="loadInfo"></div>
    <div>
        <div>
            <label>SheetNames</label>:            <span id="SheetNames"></span>
        </div>
        <div>
            <label id="demoName"></label>(示例):            <span id="demoData"></span>
        </div>
    </div>
    <script>
        var demo = document.getElementById("demo");
        demo.onchange = function () {            let f = this.files[0];            let loadInfo = document.getElementById("loadInfo");            let demoName = document.getElementById("demoName");            let demoData = document.getElementById("demoData");
            loadInfo.innerHTML = "正在读取请骚等";
            demoName.innerHTML = "";
            demoData.innerHTML = "";
            xlsxUtils.import(f, (w) => {                document.getElementById("SheetNames").innerHTML = JSON.stringify(w.SheetNames);
                demoName.innerHTML = w.SheetNames[0];
                demoData.innerHTML = JSON.stringify(xlsxUtils.getSheetByIndex(0));
                loadInfo.innerHTML = "读取完成";
            });
        };    </script></body></html>

Export example (online example)

<!DOCTYPE html><html><head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>demo</title>
    <script src="http://oss.sheetjs.com/js-xlsx/xlsx.core.min.js"></script> 
    <script src="xlsx.utils.min.js"></script>
    <script src="saveAs.min.js"> </script></head><body>
    <script>
        var data = [{ 主页: "111", 名称: "6800", 数量: "6800", 昵称: "广告主网" }, { 主页: "433", 名称: "6800", 数量: "6800", 昵称: "广告主网" }, { 名称: "22", 商家: "6800", 数量: "6800", 昵称: "广告主网", }, { 名称: "43", 商家: "6800", 数量: "6800", 昵称: "广告主网", }, { 店家: "43", 价格: "6800", 数量: "6800", 昵称: "广告主网", }];    </script>
    <button id="down">导出</button>
    <script>
        var down = document.getElementById("down");
        down.onclick = function () {
            data.unshift(xlsxUtils.readDataHead(data));            var blob = xlsxUtils.export({ "Sheet1": data, "Sheet2": [{ "a": "A", "b": "B" }, { "a": 1, "b": "2" }, { "a": 3, "b": 4 }, { "a": 5, "b": 6 }] });
            saveAs(URL.createObjectURL(blob), "aa.xlsx");
        };    </script></body></html>

Customized starting column export example (online example)

<!DOCTYPE html><html><head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>demo</title>
    <script src="http://oss.sheetjs.com/js-xlsx/xlsx.core.min.js"></script> 
    <script src="xlsx.utils.min.js"></script>
    <script src="saveAs.min.js"> </script></head><body>
    <script>
        var data = [{ 主页: "111", 名称: "6800", 数量: "6800", 昵称: "广告主网" }, { 主页: "433", 名称: "6800", 数量: "6800", 昵称: "广告主网" }, { 名称: "22", 商家: "6800", 数量: "6800", 昵称: "广告主网", }, { 名称: "43", 商家: "6800", 数量: "6800", 昵称: "广告主网", }, { 店家: "43", 价格: "6800", 数量: "6800", 昵称: "广告主网", }];    </script>
    <button id="down">导出</button>
    <script>
        var down = document.getElementById("down");
        down.onclick = function () {
            data.unshift(xlsxUtils.readDataHead(data)); 
            var tmpdata = xlsxUtils.format2Sheet(data, 2);            var tmpWB = xlsxUtils.format2WB(tmpdata, "Sheet1");
            tmpdata = xlsxUtils.format2Sheet([{ "a": "A", "b": "B" }, { "a": 1, "b": "2" }, { "a": 3, "b": 4 }, { "a": 5, "b": 6 }], 4);
            tmpWB = xlsxUtils.format2WB(tmpdata, "Sheet2", tmpWB);            var blob = xlsxUtils.format2Blob(tmpWB);
            saveAs(URL.createObjectURL(blob), "aa.xlsx");
        };    </script></body></html>

Multiple data merge and export to single table example (online example)

<!DOCTYPE html><html><head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>demo</title>
    <script src="http://oss.sheetjs.com/js-xlsx/xlsx.core.min.js"></script> 
    <script src="xlsx.utils.min.js"></script>
    <script src="saveAs.min.js"> </script></head><body>
    <script>
        var data = [{ 主页: "111", 名称: "6800", 数量: "6800", 昵称: "广告主网" }, { 主页: "433", 名称: "6800", 数量: "6800", 昵称: "广告主网" }, { 名称: "22", 商家: "6800", 数量: "6800", 昵称: "广告主网", }, { 名称: "43", 商家: "6800", 数量: "6800", 昵称: "广告主网", }, { 店家: "43", 价格: "6800", 数量: "6800", 昵称: "广告主网", }];    </script>
    <button id="down">导出</button>
    <script>
        var down = document.getElementById("down");
        down.onclick = function () {            var d1 = xlsxUtils.format2Sheet([{ "a": "A", "b": "B" }, { "a": 1, "b": "2" }, { "a": 3, "b": 4 }, { "a": 5, "b": 6 }]);            var h=xlsxUtils.readDataHead(data);//读取data的列头数据
            data.unshift(h);//将列头追加到最data的前
            var em={};//用于换行
            Object.keys(h).forEach((v)=>{
                em[v]="";
            });            for(var i=0;i<5;i++){//换5行
                data.unshift(em);
            }            var d2 = xlsxUtils.format2Sheet(data, 3);//3代表从D列开始
            d1=Object.assign(d1,d2); 
            var tmpWB = xlsxUtils.format2WB(d1, "Sheet1"); 
            var blob = xlsxUtils.format2Blob(tmpWB);
            saveAs(URL.createObjectURL(blob), "aa.xlsx");
        };    </script></body></html>

I believe you have mastered the method after reading the case in this article, and more How exciting, please pay attention to other related articles on php Chinese website!

Detailed explanation of the use of js-xlsx tool library xlsxUtils

Related articles