当前位置: 首页 > 工具软件 > SheetJS > 使用案例 >

sheetjs的使用

孟选
2023-12-01

<!--  https://sheetjs.com/     资源下载地址  xlsx.full.min.js  -->

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>sheetjs的使用</title>
    <style type="text/css">
        body
        {
            font-family: Arial;
            font-size: 10pt;
        }
        table
        {
            border: 1px solid #ccc;
            border-collapse: collapse;
        }
        table th
        {
            background-color: #F7F7F7;
            color: #333;
            font-weight: bold;
        }
        table th, table td
        {
            padding: 5px;
            border: 1px solid #ccc;
        }
    </style>
     <script type="text/javascript" src="./xlsx.full.min.js"></script>
    
    <script type="text/javascript">
        function myUpload() {
            //Reference the FileUpload element.
            var fileUpload = document.getElementById("fileUpload");

            //Validate whether File is valid Excel file.
            var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.xls|.xlsx)$/;
            if (regex.test(fileUpload.value.toLowerCase())) {
                if (typeof (FileReader) != "undefined") {
                    var reader = new FileReader();

                    //For Browsers other than IE.
                    if (reader.readAsBinaryString) {
                        reader.onload = function (e) {
                            ProcessExcel(e.target.result);
                        };
                        reader.readAsBinaryString(fileUpload.files[0]);
                    } else {
                        //For IE Browser.
                        reader.onload = function (e) {
                            var data = "";
                            var bytes = new Uint8Array(e.target.result);
                            for (var i = 0; i < bytes.byteLength; i++) {
                                data += String.fromCharCode(bytes[i]);
                            }
                            ProcessExcel(data);
                        };
                        reader.readAsArrayBuffer(fileUpload.files[0]);
                    }
                } else {
                    alert("This browser does not support HTML5.");
                }
            } else {
                alert("Please upload a valid Excel file.");
            }
        };
        function ProcessExcel(data) {
            //Read the Excel File data.
            var workbook = XLSX.read(data, {
                type: 'binary'
            });

            //Fetch the name of First Sheet.
            var firstSheet = workbook.SheetNames[0];

            //Read all rows from First Sheet into an JSON array.
            var excelRows = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[firstSheet]);

            //Create a HTML Table element.
            var table = document.createElement("table");
            table.border = "1";

            //Add the header row.
            var row = table.insertRow(-1);

            //Add the header cells.
            var headerCell = document.createElement("TH");
            headerCell.innerHTML = "Id";
            row.appendChild(headerCell);

            headerCell = document.createElement("TH");
            headerCell.innerHTML = "Name";
            row.appendChild(headerCell);

            headerCell = document.createElement("TH");
            headerCell.innerHTML = "Country";
            row.appendChild(headerCell);
            
             headerCell = document.createElement("TH");
            headerCell.innerHTML = "PROVICE";
            row.appendChild(headerCell);

            //Add the data rows from Excel file.
            for (var i = 0; i < excelRows.length; i++) {
                //Add the data row.
                var row = table.insertRow(-1);

                //Add the data cells.
                var cell = row.insertCell(-1);
                cell.innerHTML = excelRows[i].This;

                cell = row.insertCell(-1);
                cell.innerHTML = excelRows[i].is;

                cell = row.insertCell(-1);
                cell.innerHTML = excelRows[i].a;
                
                cell = row.insertCell(-1);
                cell.innerHTML = excelRows[i].Test;
            }

            var dvExcel = document.getElementById("dvExcel");
            dvExcel.innerHTML = "";
            dvExcel.appendChild(table);
        };
        
        var table = document.getElementById("table");
        /*
         var jsono = [{ //测试数据
            "保质期临期预警(天)": "adventLifecycle",
            "商品标题": "title",
            "建议零售价": "defaultPrice"            
        }];
        */
        var tmpDown; //导出的二进制对象
        function downloadExl(json, type) {
            var aarray=[];
            
            var rowsorg=document.getElementById("dvExcel").childNodes[0].rows;
            
            keyMap=document.getElementById("dvExcel").childNodes[0].rows[0].outerText.split();
            
            for(var z=1;z<rowsorg.length;z++)
            {
             var temp = rowsorg[z].outerText.split();
             var  aObj=new Object();
                aObj.Id=temp[0];
                  aObj.Name=temp[1];    
                aObj.Country=temp[2];    
                aObj.PROVICE=temp[3];
                aarray.push(aObj);                
            }
        
            var tmpdata = aarray[0];
            aarray.unshift({});
            var keyMap = []; //获取keys
            //keyMap =Object.keys(json[0]);
            for (var k in tmpdata) {
                keyMap.push(k);
                aarray[0][k] = k;
            }
            
            
          var tmpdata = [];//用来保存转换好的json
                aarray.map((v, i) => keyMap.map((k, j) => Object.assign({}, {
                    v: v[k],
                    position: (j > 25 ? getCharCol(j) : String.fromCharCode(65 + j)) + (i + 1)
                }))).reduce((prev, next) => prev.concat(next)).forEach((v, i) => tmpdata[v.position] = {
                    v: v.v
                });
                var outputPos = Object.keys(tmpdata); //设置区域,比如表格从A1到D10
                var tmpWB = {
                    SheetNames: ['mySheet'], //保存的表标题
                    Sheets: {
                        'mySheet': Object.assign({},
                            tmpdata, //内容
                            {
                                '!ref': outputPos[0] + ':' + outputPos[outputPos.length - 1] //设置填充区域
                            })
                    }
                };
                tmpDown = new Blob([s2ab(XLSX.write(tmpWB,
                    {bookType: (type == undefined ? 'xlsx':type),bookSST: false, type: 'binary'}//这里的数据是用来定义导出的格式类型
                    ))], {
                    type: ""
                }); //创建二进制对象写入转换好的字节流
            var href = URL.createObjectURL(tmpDown); //创建对象超链接
            document.getElementById("hf").href = href; //绑定a标签
            document.getElementById("hf").click(); //模拟点击实现下载
            setTimeout(function() { //延时释放
                URL.revokeObjectURL(tmpDown); //用URL.revokeObjectURL()来释放这个object URL
            }, 100);
        }

        function 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;
        }
         // 将指定的自然数转换为26进制表示。映射关系:[0-25] -> [A-Z]。
        function getCharCol(n) {
            let temCol = '',
            s = '',
            m = 0
            while (n > 0) {
                m = n % 26 + 1
                s = String.fromCharCode(m + 64) + s
                n = (n - m) / 26
            }
            return s
        }
    </script>
</head>
<body>
    <input type="file" id="fileUpload" />
    <input type="button" id="upload" value="Upload" οnclick="myUpload()" />
    <hr />
    <div id="dvExcel">
    </div>
    
    <button οnclick="downloadExl(jsono)">导出</button>
     
    <a href="" download="这里是下载的文件名.xlsx" id="hf"></a>
        
   
</body>
</html>

 

 类似资料: