详细说明查看官网文档:
- handsontable 官网:https://handsontable.com/
- handsontable github:https://github.com/handsontable/handsontable
- js-XLSX github:https://github.com/SheetJS/js-xlsx
demo
<!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>Document</title>
<!--引入相关文件-->
<link rel="stylesheet" type="text/css" href="https://cdn.bootcss.com/handsontable/0.32.0/handsontable.full.css" />
<script src="https://cdn.bootcss.com/handsontable/0.32.0/handsontable.full.min.js"></script>
<script src="http://oss.sheetjs.com/js-xlsx/xlsx.full.min.js"></script>
<script>
//----工具类 开始----
var utl = {};
utl.Binary = {
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;
},
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;
}
}
utl.XLSX = {
wb: null,
rABS: false,
import(f, c) {//导入根据文件
this.wb = null;
var reader = new FileReader();
reader.onload = function (e) {
var data = e.target.result;
if (utl.XLSX.rABS) {
utl.XLSX.wb = XLSX.read(btoa(utl.Binary.fixdata(data)), {//手动转化
type: 'base64'
});
} else {
utl.XLSX.wb = XLSX.read(data, {
type: 'binary'
});
}
if (c && typeof (c)) {
c();
}
};
if (utl.XLSX.rABS) {
reader.readAsArrayBuffer(f);
} else {
reader.readAsBinaryString(f);
}
},
onImport(obj, c) {//导入根据 input file标签
if (!obj.files) {
return;
}
this.import(obj.files[0], c);
},
getSheetsByIndex(index = 0) {//获取数据根据Sheets索引
return XLSX.utils.sheet_to_json(this.wb.Sheets[this.wb.SheetNames[index]]);
},
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
},
export(json, title, type) {//导出
var keyMap = [];//获取keys
for (k in json[0]) {
keyMap.push(k);
}
var tmpdata = [];//用来保存转换好的json
json.map((v, i) => keyMap.map((k, j) => Object.assign({}, {
v: v[k],
position: (j > 25 ? utl.XLSX.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 = new Object();
title = title ? title : "mySheet";
tmpWB.SheetNames = [title];
tmpWB.Sheets = {};
tmpWB.Sheets[title] = Object.assign({}, tmpdata, //内容
{
'!ref': outputPos[0] + ':' + outputPos[outputPos.length - 1] //设置填充区域
});
return new Blob([utl.Binary.s2ab(XLSX.write(tmpWB,
{ bookType: (type == undefined ? 'xlsx' : type), bookSST: false, type: 'binary' }//这里的数据是用来定义导出的格式类型
))], { type: "" }); //创建二进制对象写入转换好的字节流
},
onExport(json, title, type) {//直接下载
utl.Download.byObj(this.export(json, title, type), "下载.xlsx");
}
};
utl.Download = {
byURL(url, fileName) {//动态下载
var tmpa = document.createElement("a");
tmpa.download = fileName || "下载";
tmpa.href = url; //绑定a标签
tmpa.click(); //模拟点击实现下载
},
byObj(obj, fileName) {//内存二进制数据下载
this.byURL(URL.createObjectURL(obj), fileName);
setTimeout(function () { //延时释放
URL.revokeObjectURL(obj); //用URL.revokeObjectURL()来释放这个object URL
}, 100);
}
}
utl.Object = {
reverse(obj) {//对象键值倒转
var o = new Object();
for (var k in obj) {
o[obj[k]] = k;
}
return o;
},
deepCopy() {//深拷贝
let temp = obj.constructor === Array ? [] : {}
for (let val in obj) {
temp[val] = typeof obj[val] == 'object' ? deepCopy(obj[val]) : obj[val];
}
return temp;
},
copyJson(o) { return JSON.parse(JSON.stringify(o)); }//拷贝JSON格式
}
//----工具类 结束----
</script>
<style>
* {
margin: 0;
padding: 0;
}
body {
font-family: Helvetica Neue, Helvetica, PingFang SC, Hiragino Sans GB, Microsoft YaHei, SimSun, sans-serif;
overflow: auto;
font-weight: 400;
-webkit-font-smoothing: antialiased;
}
</style>
<script>
//---导入头信息 开始--
const xHred = {
"ID": "id",
"账号": "uname",
"用户名": "rname",
"密码": "pwd",
"性别": "sex",
"出生日期": "birth",
"电话": "mobPhone"
};
const filterVal = {//<=====值转化
"pwd":function(value){
return "假装 被加密了"
},
"sex": function (key) {
key = { "男": 0, "女": 1, "": 0, undefined: 0 }[key];
return key ? key : 0;
}
};
//---导入头信息 结束--
</script>
</head>
<body>
<script>
//------导入导出 -----
function impt(emt) {//<===导入
utl.XLSX.onImport(emt, function () {
var rt = utl.XLSX.getSheetsByIndex();//<===默认获取Sheet1
var tmp = [];
rt.forEach(function (value, index, array) {
var t = new Object();
for (var k in value) {
t[xHred[k]] = value[k];
}
tmp.push(t);
});
hot.loadData(tmp);//<====将读取完成数据显示到handsontable
});
}
function down() {//<===下载导出数据
var d = utl.Object.copyJson(hot.getSourceData());//<=====读取handsontable的数据
d.unshift(utl.Object.reverse(xHred));//<====追加到列头
utl.XLSX.onExport(d);//<====导出
}
function showData() {//<====获取真实数据
var tmp = [];
hot.getSourceData().forEach(function (value, index, array) {
var t = new Object();
for (var k in value) {
t[k] = filterVal[k] ? filterVal[k](value[k]) : value[k];//<====将xlsx数据转为实际需要的值
}
tmp.push(t);
});
/*
*更多规则自己定义吧
*/
alert(JSON.stringify(tmp));
window.open("data:text/html;charset=utf-8,"+JSON.stringify(tmp));
console.log(tmp);
return tmp;
}
//------导入导出 -----
</script>
数据量大于1万条时,文件解析可能需要10秒以上读取,当然这也跟你当前使用电脑性能有关系
<input type="file" onchange="impt(this)" />
<input type="button" value="下载" onclick="down()" />
<input type="button" value="查看结果数据数据" onclick="showData()" />
<div id="hot"></div>
<script>
//----handsontable 初始化 开始------
var hotElement = document.querySelector('#hot');//<====绑定handsontable初始化div
var colHeaders = [];//<===handsontable列头显示值
var cols = [];//<====handsontable列头显示值对应的实际字段与其他配置
for (var key in xHred) {
cols.push({ data: xHred[key] });
colHeaders.push(key);
}
var hotSettings = {//<====handsontable的配置
colHeaders: colHeaders,//当值为true时显示列头，当值为数组时，列头为数组的值
data: [],//数据源
columns: cols,//列具体配置
// width: 906,
autoWrapRow: true,
// height: 641,
// maxRows: 22,
minRows: 1,
// currentRowClassName：当前行样式的名称,
// currentColClassName：当前列样式的名称,
manualColumnResize: true,//当值为true时，允许拖动，当为false时禁止拖动
manualRowResize: true,//当值为true时，允许拖动，当为false时禁止拖动
stretchH: "all", //last:延伸最后一列,all:延伸所有列,none默认不延伸。
manualColumnMove: true,// 当值为true时，列可拖拽移动到指定列
manualRowMove: true,// 当值为true时，行可拖拽至指定行
rowHeaders: true, //当值为true时显示行头，当值为数组时，行头为数组的值
columnSorting: true,//允许排序
sortIndicator: true,
contextMenu: true,//显示右键菜单
autoColumnSize: true //当值为true且列宽未设置时，自适应列大小
};
var hot = new Handsontable(hotElement, hotSettings);
//----handsontable 初始化 完成------
</script>
</body>
</html>
