关于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- 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- 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/