关于js-xlsx的简单使用
来源：互联网 发布：网络数字高清摄像头 编辑：程序博客网 时间：2024/06/05 20:05
关于js-xlsx的简单使用
最近要做一个东西用到对excel的操作，就是在前端将excel加载进来，操作后再生成excel，在网上找了很多种办法，能够实现的demo是下面这个：
纯前端利用 js-xlsx 实现 Excel 文件导入导出功能示例，
官方的文档请看这：js-xlsx，
以下是我自己实现的功能，前端框架使用的是bootstrap；
使用时上传excel文件，进行自己的操作，然后点击下载，具体的细节还要根据自己的需求来改，这里只是一个例子
我的excel格式：
demo：
<!DOCTYPE html><html lang="zh-CN"> <head> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1"> <!-- 上述3个meta标签*必须*放在最前面，任何其他内容都*必须*跟随其后！ --> <meta name="description" content=""> <meta name="author" content=""> <link rel="icon" href="favicon.ico"> <title>demo</title> <!-- Bootstrap core CSS --> <link href="https://cdn.bootcss.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet"> <script src="https://cdn.bootcss.com/jquery/1.12.4/jquery.min.js"></script> <script src="http://oss.sheetjs.com/js-xlsx/xlsx.full.min.js"></script> <style type="text/css"> /* * Base structure */ /* Move down content because we have a fixed navbar that is 50px tall */ body { padding-top: 50px; } /* * Global add-ons */ .sub-header { padding-bottom: 10px; border-bottom: 1px solid #eee; } /* * Top navigation * Hide default border to remove 1px line. */ .navbar-fixed-top { border: 0; } /* * Sidebar */ /* Hide for mobile, show later */ .sidebar { display: none; } @media (min-width: 768px) { .sidebar { position: fixed; top: 51px; bottom: 0; left: 0; z-index: 1000; display: block; padding: 20px; overflow-x: hidden; overflow-y: auto; /* Scrollable contents if viewport is shorter than content. */ background-color: #f5f5f5; border-right: 1px solid #eee; } } /* Sidebar navigation */ .nav-sidebar { margin-right: -21px; /* 20px padding + 1px border */ margin-bottom: 20px; margin-left: -20px; } .nav-sidebar > li > a { padding-right: 20px; padding-left: 20px; } .nav-sidebar > .active > a, .nav-sidebar > .active > a:hover, .nav-sidebar > .active > a:focus { color: #fff; background-color: #428bca; } /* * Main content */ .main { padding: 20px; } @media (min-width: 768px) { .main { padding-right: 40px; padding-left: 40px; } } .main .page-header { margin-top: 0; } /* * Placeholder dashboard ideas */ .placeholders { margin-bottom: 30px; text-align: center; } .placeholders h4 { margin-bottom: 0; } .placeholder { margin-bottom: 20px; } .placeholder img { display: inline-block; border-radius: 50%; } </style> <script type="text/javascript"> // 为所有checkbox添加点击事件，不用就去掉 function iCheck(id) { var table = document.getElementById("myTable"); var row = table.getElementsByTagName("tr"); var col = row[0].getElementsByTagName("th"); var idEle = document.getElementById(id); var idTds = idEle.getElementsByTagName("td"); var idInput = idTds[idTds.length - 1].getElementsByTagName("input"); // 往上查找同组的 for (var i = id; i > 0; --i) { var ele = document.getElementById(i); var tds = ele.getElementsByTagName("td"); var input = tds[tds.length - 1].getElementsByTagName("input"); if(idInput[0].checked == true) input[0].checked = true; else input[0].checked = false; if(tds[1].innerHTML != "") break; } // 往下查找同组的 for (var i = id + 1; i < row.length; ++i) { var ele = document.getElementById(i); var tds = ele.getElementsByTagName("td"); if(tds[1].innerHTML != "") break; var input = tds[tds.length - 1].getElementsByTagName("input"); if(idInput[0].checked == true) input[0].checked = true; else input[0].checked = false; } } /* table -> json -> excel */ $(function() { $("#mainButton").click(function() { var json = Table2Json("myTable"); function Table2Json(tableid) { var txt = "["; var table = document.getElementById(tableid); var row = table.getElementsByTagName("tr"); var col = row[0].getElementsByTagName("th"); for (var j = 1; j < row.length; j++) { var tds = row[j].getElementsByTagName("td"); var input = tds[tds.length - 1].getElementsByTagName("input"); var r = "{"; for (var i = 1; i < col.length - 1; i++) { r += "\"" + col[i].innerHTML + "\"\:\"" + tds[i].innerHTML + "\","; } if(input[0].checked == true) { r += "\"" + col[col.length - 1].innerHTML + "\"\:\"" + 1 + "\","; } else { r += "\"" + col[col.length - 1].innerHTML + "\"\:\"" + 0 + "\","; } r = r.substring(0, r.length - 1) r += "},"; txt += r; } txt = txt.substring(0, txt.length - 1); txt += "]"; return txt; } downloadExl(JSON.parse(json)); }); }); /* excel -> json -> table FileReader共有4种读取方法： 1.readAsArrayBuffer(file)：将文件读取为ArrayBuffer。 2.readAsBinaryString(file)：将文件读取为二进制字符串 3.readAsDataURL(file)：将文件读取为Data URL 4.readAsText(file, [encoding])：将文件读取为文本，encoding缺省值为'UTF-8' */ var wb;//读取完成的数据 var rABS = false; //是否将文件读取为二进制字符串 function importf(obj) {//导入 if(!obj.files) { return; } var f = obj.files[0]; var reader = new FileReader(); reader.onload = function(e) { var data = e.target.result; if(rABS) { wb = XLSX.read(btoa(fixdata(data)), {//手动转化 type: 'base64' }); } else { wb = XLSX.read(data, { type: 'binary' }); } //wb.SheetNames[0]是获取Sheets中第一个Sheet的名字 //wb.Sheets[Sheet名]获取第一个Sheet的数据 //document.getElementById("demo").innerHTML= JSON.stringify( XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]) ); var jsonArray = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]); document.getElementById("myTableBody").innerHTML = ""; for(var i = 0; i < jsonArray.length; ++i) { var temp = jsonArray[i]; if(temp.StepName == null) temp.StepName = ""; if(i == 1 || i == 2) document.getElementById("myTableBody").innerHTML += '<tr id="' + i + '"><td>'+ (i + 1) + '</td><td>' + temp.StepName + '</td><td>' + temp.NodeName + '</td><td><input type="checkbox" checked="true" onclick="iCheck(' + i + ')" disabled="disabled" /></td></tr>'; else document.getElementById("myTableBody").innerHTML += '<tr id="' + i + '"><td>'+ (i + 1) + '</td><td>' + temp.StepName + '</td><td>' + temp.NodeName + '</td><td><input type="checkbox" onclick="iCheck(' + i + ')" /></td></tr>'; } }; 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; } var tmpDown; //导出的二进制对象 function downloadExl(json, type) { var tmpdata = json[0]; json.unshift({}); var keyMap = []; //获取keys for (var k in tmpdata) { keyMap.push(k); json[0][k] = k; } var tmpdata = [];//用来保存转换好的json json.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: ['updateProcess'], //保存的表标题 Sheets: { 'updateProcess': 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); //创建对象超链接 var a = document.getElementById("hf"); var dateObj = new Date(); var year = dateObj.getFullYear(); var month = dateObj.getMonth()+1;//月 (注意：月份+1) var date = dateObj.getDate();//日 var day = dateObj.getDay(); var hours = dateObj.getHours();//小时 var minutes = dateObj.getMinutes();//分钟 var seconds = dateObj.getSeconds();//秒 a.href = href; //绑定a标签 a.download = "updateStep" + "-" + year + "-" + month + "-" + date + "-" + hours + "-" + minutes + "-" + seconds + ".xlsx"; a.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> <!-- 导航条 --> <nav class="navbar navbar-inverse navbar-fixed-top"> <div class="container-fluid"> <div class="navbar-header"> <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#navbar" aria-expanded="false" aria-controls="navbar"> <span class="sr-only">Toggle navigation</span> <span class="icon-bar"></span> <span class="icon-bar"></span> <span class="icon-bar"></span> </button> <a class="navbar-brand" href="#">demo</a> </div> <div id="navbar" class="navbar-collapse collapse"> <ul class="nav navbar-nav navbar-right"> <li class="active"><a href="#">Home Page<span class="sr-only">(current)</span></a></li> <li><a href="">demo</a></li> <li><a href="">demo</a></li> <li class="dropdown"> <a href="#" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-haspopup="true" aria-expanded="false">demo<span class="caret"></span></a> <ul class="dropdown-menu"> </ul> </li> </ul> <form class="navbar-form navbar-right"> <input type="text" class="form-control" placeholder="Search..."> </form> </div> </div> </nav> <!-- 内容 --> <div class="container-fluid"> <div class="row"> <!-- 侧边栏 --> <div class="col-sm-3 col-md-2 sidebar"> <ul class="nav nav-sidebar"> <li class="active"><a href="#"><span class="badge">#</span> demo<span class="sr-only">(current)</span></a></li> <li><a href="#"><span class="badge">1</span> demo</a></li> <li><a href="#"><span class="badge">2</span> demo</a></li> <li><a href="#"><span class="badge">3</span> demo</a></li> </ul> <ul class="nav nav-sidebar"> <li class="active"><a href="#"><span class="badge">#</span> have a try<span class="sr-only">(current)</span></a></li> <li><object type="application/x-shockwave-flash" style="outline:none;" data="http://cdn.abowman.com/widgets/ballclock/ballclock.swf?" width="340" height="220"><param name="movie" value="http://cdn.abowman.com/widgets/ballclock/ballclock.swf?"></param><param name="AllowScriptAccess" value="always"></param><param name="wmode" value="opaque"></param></object></li> </ul> </div> <!-- 主体 --> <div class="col-sm-9 col-sm-offset-3 col-md-10 col-md-offset-2 main"> <div class="page-header"> <h2>1.上传excel文件（<a href="#">没有文件点击这里下载，请忽略</a>）</h2> </div> <div class="form-group"> <input type="file" id="inputFile" onchange="importf(this)"> <p class="help-block">注意格式：excel内容表头前2列请使用StepName，NodeName</p> </div> <br/> <div class="page-header"> <h2>2.demo</h2> </div> <div class="table-responsive"> <table class="table table-striped table-bordered" id="myTable"> <thead> <tr> <th>demo1</th> <th>demo2</th> <th>demo3</th> <th>demo4</th> </tr> </thead> <tbody id="myTableBody"> <tr> <td>1</td> <td>demo</td> <td>demo</td> <td><input type="checkbox" /></td> </tr> <tr> <td>2</td> <td>demo</td> <td>demo</td> <td><input type="checkbox" /></td> </tr> <tr> <td>3</td> <td>demo</td> <td>demo</td> <td><input type="checkbox" /></td> </tr> <tr> <td>4</td> <td>demo</td> <td>demo</td> <td><input type="checkbox" /></td> </tr> <tr> <td>3</td> <td>demo</td> <td>demo</td> <td><input type="checkbox" /></td> </tr> <tr> <td>4</td> <td>demo</td> <td>demo</td> <td><input type="checkbox" /></td> </tr> </tbody> </table> </div> <br/> <div class="page-header"> <h2>3.点击生成excel文件</h2> </div> <button type="button" class="btn btn-primary btn-lg btn-block" id="mainButton">生成Excel</button> <!-- <a class="btn btn-warning btn-lg btn-block" href="/cgi-bin/test.py" role="button">Python生成Excel</a> --> <a href="" download="updateStep.xlsx" id="hf"> </div> </div> </div> <!-- Bootstrap core JavaScript ================================================== --> <!-- Placed at the end of the document so the pages load faster --> <script>window.jQuery || document.write('<script src="../../assets/js/vendor/jquery.min.js"><\/script>')</script> <script src="https://cdn.bootcss.com/bootstrap/3.3.7/js/bootstrap.min.js"></script> <!-- Just to make our placeholder images work. Don't actually copy the next line! --> </body></html>题外话，大神js小游戏：http://abowman.com/
阅读全文
0 0
- 关于js-xlsx的简单使用
- 关于xlsx转成json的工具（记录后续使用）
- 使用XLSX.js处理excle表
- SheetJS/js-xlsx的小demo
- 在浏览器和node中使用 GitHub的js-xlsx库进行Excel文件处理(一)
- JS的简单使用
- JS实现下载xlsx格式文件--js-xlsx
- 关于JS简单的介绍
- xlsx简单分析
- javascript--利用js-xlsx实现Excel表格的导入导出
- 关于用POI处理xlsx文件的一些心得
- 关于导入.xlsx和.xls文件的一点心得
- js ajax的简单使用
- NW.js的简单使用
- js定时器的简单使用
- js 正则的简单使用
- 关于ajaxfileupload.js的使用
- node-xlsx使用教程
- [PAT]1006. Sign In and Sign Out (25)@Java
- C# 调用C++ DLL (一)
- 框架
- JavaScript简介
- 页面滑动，防止a链接误触
- 关于js-xlsx的简单使用
- PHP面向对象
- 原码、反码、补码的转换
- Java NIO系列教程（十一） Pipe
- javase集合
- 一图读懂JVM架构解析
- caffe框架下目标检测——faster-rcnn实战篇问题集锦
- Android开发笔记: Socket通信--【带例子】
- BAT级别和待遇
