过了个春节,我就忘记了,也不知道从哪抄来的SheetJs解析的例子,先保留下

例1:

<html>
<head>

    <title>JS-XLSX Live Demo</title>

    <style>
        #drop {
            border: 2px dashed #bbb;
            -moz-border-radius: 5px;
            -webkit-border-radius: 5px;
            border-radius: 5px;
            padding: 25px;
            text-align: center;
            font: 20pt bold,"Vollkorn";
            color: #bbb
        }

        #b64data {
            width: 100%;
        }

        a {
            text-decoration: none
        }

        button {
        }
    </style>

    <script type="text/javascript" src="dist/xlsx.full.min.js"></script>
    <script type="text/javascript" src="dist/xlsx.core.min.js"></script>

    <script type="text/javascript" src="dist/jquery.min.js"></script>

</head>

<body>
<pre>
<input type="file" name="xlfile" id="xlf" onchange="importf(this)">
</pre>
<pre id="out"></pre>
    <br>
    <div id="demo">


      </div>
    <div>
        <textarea id='debug' rows="30" cols="100"></textarea>

    </div>




    <script>
            /*
                  FileReader共有4种读取方法:
                  1.readAsArrayBuffer(file):将文件读取为ArrayBuffer。
                  2.readAsBinaryString(file):将文件读取为二进制字符串
                  3.readAsDataURL(file):将文件读取为Data URL
                  4.readAsText(file, [encoding]):将文件读取为文本,encoding缺省值为'UTF-8'
                               */
            var workbook;//读取完成的数据
            var rABS = false; //是否将文件读取为二进制字符串

            



        function importf(obj) {//导入


            const headers = ["A", "B", "C", "D", "E","F"]; //针对G0107中的表样,做的6列
            const ranges = 4;                              //针对G0107中的表样,跳过的表头


                if (!obj.files) {
                    return;
                }
                var f = obj.files[0];
                var reader = new FileReader();
                reader.onload = function (e) {
                    var data = e.target.result;
                    if (rABS) {
                        workbook = XLSX.read(btoa(fixdata(data)), {//手动转化
                            type: 'base64'
                        });
                    } else {
                        workbook = XLSX.read(data, {
                            type: 'binary'
                        });
                    }
                    var out_json;

                    for (var sheetNum = 0; sheetNum < Number(workbook.SheetNames.length); sheetNum++) {

                        var first_sheet_name = workbook.SheetNames[sheetNum];

                        //var json = XLSX.utils.sheet_to_json(workbook.Sheets[first_sheet_name]);
                        //var json = XLSX.utils.sheet_to_json(workbook.Sheets[first_sheet_name],
                        //    {  header: headers, raw: true, defval:''}
                        //);

                        var json = XLSX.utils.sheet_to_html(workbook.Sheets[first_sheet_name]);

                        debugger;
                        //console.log(json);

                        out_json = getOutput(json, 'json');
                        $('#debug').val(out_json);


                    }
                    
                };
                if (rABS) {
                    reader.readAsArrayBuffer(f);
                } else {
                    reader.readAsBinaryString(f);
                }
            }

            function fixdata(data) { //文件流转BinaryString
                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;
        }


        function getOutput(obj, type) {
            switch (type) {
                case 'json':
                    return JSON.stringify(obj);
                    break;
                case 'obj':
                    return obj;
                    break;
                default:
                    return JSON.stringify(obj);
            }
        }

    </script>


</body>
</html>

  

 

例2:

 

<!DOCTYPE html>

<html lang="en" xmlns="http://www.w3.org/1999/xhtml">
<head>
    <meta charset="utf-8" />
    <title></title>

    <script type="text/javascript" src="dist/xlsx.full.min.js"></script>
    <script type="text/javascript" src="dist/xlsx.core.min.js"></script>

    <script type="text/javascript" src="dist/jquery.min.js"></script>

</head>
<body>


    <div>
        <input type='file' onchange='readFile(event)'>
    </div>
    <div>
        <textarea id='debug' rows="30" cols="100"></textarea>
    </div>


    <script>
        var excel = new Object();
        var sheet = new Object();
        var row = new Object();
        var cell = new Object();
        var x;
        var wb;
        var output;
        var filename;
        var rows_a, rows_b;
        var cols_a, cols_b;
        var range = new Object();

        var readFile = function (event) {
            var read = new FileReader();
            var file = event.target.files[0];
            read.readAsBinaryString(file);
            filename = file.name;
            read.onload = function (event) {
                var res = event.currentTarget.result;
                var fileType = getFileType(filename).toString();
                switch (fileType) {
                    case '.xls':
                        x = XLS;
                        break;
                    case '.xlsx':
                        x = XLSX;
                        break;
                    default:
                        x = null;
                }
                if (x) {
                    wb = x.read(res, { type: 'binary' });
                    excel = getEXCEL(wb);
                    output = excel;
                } else {
                    var info = { error: 'can not resolve file end with :' + fileType };
                    output = getOutput(info, 'obj');
                }
                console.log(output);
                out_json = getOutput(output, 'json');
                $('#debug').val(out_json);

            };
        };

        function getEXCEL(wb) {
            var Sheetnames = wb.SheetNames;
            var Sheets = new Object();
            var Sheet = new Object();

            Sheets = wb.Sheets;
            excel['sheetSize'] = Sheetnames.length.toString();
            excel['excelName'] = filename;
            excel["sheets"] = [];


            for (var i = 0; i < Sheetnames.length; i++) {
                Sheet = Sheets[Sheetnames[i]];
                range = getRanger(Sheet['!ref']);
                rows_a = range.row;
                cols_a = range.col;
                var m = 0;
                sheet = new Object();
                sheet['rowSize'] = rows_a.toString();
                sheet['colSize'] = cols_a.toString();
                sheet['sheetName'] = Sheetnames[i];
                sheet["rows"] = [];
                sheet["title"] = new Object();
                for (var j = 0; j < rows_a; j++) {
                    row = new Object();
                    row['cells'] = [];
                    for (var m = 0; m < cols_a; m++) {
                        cell = new Object();
                        row['cells'].push(cell);
                    }
                    sheet["rows"].push(row);
                }
                for (k in Sheet) {
                    if (isCell(k)) {
                        range = getRanger(k);
                        rows_b = range.row.toString();
                        cols_b = range.col.toString();
                        row = sheet["rows"][rows_b - 1];
                        row.val = null;
                        row.val = 'true';
                        cell = row['cells'][cols_b - 1];
                        cell.value = null;
                        debugger;
                        cell.value = Sheet[k].w; //---这个Sheet对象,有几个属性,应该是有 s t w 分别对应 
                    }
                }
                for (t in sheet["rows"]) {
                    var tit = sheet["rows"][t];
                    if (tit.val) {
                        sheet["title"] = sheet["rows"][t];
                        break;
                    }
                }
                excel["sheets"].push(sheet);
            }
            return excel;
        }

        function isCell(str) {
            var reg = /^[a-zA-Z]+[0-9]+$/;
            return reg.test(str);
        }

        function getRowNumber(key) {
            var reg = /[0-9]+$/;
            var rownumber = reg.exec(key);
            return rownumber;
        }

        function getColLetter(key) {
            var reg = /^[a-zA-Z]+/;
            var colletter = reg.exec(key);
            return colletter;
        }

        function getColNumber(key) {
            var reg = /[a-zA-Z]+/;
            var colletter = reg.exec(key);
            return colletter;
        }

        function getOutput(obj, type) {
            switch (type) {
                case 'json':
                    return JSON.stringify(obj);
                    break;
                case 'obj':
                    return obj;
                    break;
                default:
                    return JSON.stringify(obj);
            }
        }

        function getRanger(ref) {
            if (ref == undefined) {
                range.row = "0";
                range.col = "0";
            } else {
                var reg = /:[a-zA-Z]+[0-9]+/;
                var reg2 = /^[a-zA-Z]+[0-9]+$/;
                var pos = reg.exec(ref);
                if (pos == undefined) {
                    pos = reg2.exec(ref);
                }
                var r = getRowNumber(pos).toString();
                var codes = [];
                var n;
                var letter = getColNumber(pos).toString();
                letter = letter.toLocaleLowerCase();
                n = 0;
                for (i in letter) {
                    var code = letter[i].charCodeAt() - 96;
                    codes[i] = code;
                    n++;
                }
                n--;
                var mathstr = 0;
                for (j in codes) {
                    mathstr = 'Math.pow(26,' + n + ')*' + codes[j] + '+' + mathstr;
                    n--;
                }
                var c = eval(mathstr).toString();
                range.row = r;
                range.col = c;
            }
            return range;
        }

        function getFileType(filepath) {
            var reg = /.[a-zA-Z0-9]+$/;
            var filePostfix = reg.exec(filepath);
            return filePostfix;
        }
    </script>

</body>
</html>

  

posted @ 2022-02-04 10:19  苦行者的刀  阅读(91)  评论(0编辑  收藏  举报