将xlsx解析的excel表头json数据转成树结构
项目需求需要将用户上传的excel解析成前端可以使用的树数据并且绘制表格到页面上，这里我将用户上传的excel使用xlsx解析后，拿到json数据对他进行解析生成树，上传的excel为：
，解析方法如下：
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
</head>
<body>
</body>
</html>
<script>
let ref = "A1:L3"//xlsx解析后获取到的单元格所占行列数
let marge = [
{
"s": { "c": 6, "r": 0 },
"e": { "c": 11, "r": 0 }
},
{
"s": { "c": 9, "r": 1 },
"e": { "c": 11, "r": 1 }
},
{
"s": { "c": 7, "r": 1 },
"e": { "c": 8, "r": 1 }
},
{
"s": { "c": 6, "r": 1 },
"e": { "c": 6, "r": 2 }
},
{
"s": { "c": 0, "r": 0 },
"e": { "c": 0, "r": 2 }
},
{
"s": { "c": 1, "r": 0 },
"e": { "c": 1, "r": 2 }
},
{
"s": { "c": 2, "r": 0 },
"e": { "c": 2, "r": 2 }
},
{
"s": { "c": 3, "r": 0 },
"e": { "c": 4, "r": 1 }
},
{
"s": { "c": 5, "r": 0 },
"e": { "c": 5, "r": 1 }
}
];//xlsx解析后获取到的单元格合并数据
let arr = [
{ "key_": "A1", "data": "点点滴滴" },
{ "key_": "B1", "data": "数量" },
{ "key_": "C1", "data": "双数" },
{ "key_": "D1", "data": "单数" },
{ "key_": "F1", "data": "单双数" },
{ "key_": "G1", "data": "2019年" },
{ "key_": "G2", "data": "计算2018" },
{ "key_": "H2", "data": "中央大的" },
{ "key_": "J2", "data": "阿达" },
{ "key_": "D3", "data": "法法" },
{ "key_": "E3", "data": "大师傅" },
{ "key_": "F3", "data": "阿发" },
{ "key_": "H3", "data": "阿法狗" },
{ "key_": "I3", "data": "算法22" },
{ "key_": "J3", "data": "阿嘎" },
{ "key_": "K3", "data": "阿法阿发" },
{ "key_": "L3", "data": "谁改的根" }
];//xlsx解析后获取到的单元格数据，我将它转化为了数组格式
let tree = [];//存储目标树形结构
let margezh = [];//存储合并单元格数据
marge.forEach(item => {
let t = Number(item.s.r) + 1;
margezh.push({
"s": item.s,
"e": item.e,
"s_": numToString(item.s.c) + "" + t
})
})
console.log(margezh, "margezh");
function numToString(number) {//解析单元格合并
let char = "";
let array = [];
let numToStringAction = function (nnum) {
let num = nnum;
let a = parseInt(num / 26);
let b = num % 26;
array.push(b);
if (a > 0) {
numToStringAction(a);
}
}
numToStringAction(number);
array = array.reverse();
for (let i = 0; i < array.length; i++) {
char += String.fromCharCode(64 + parseInt(array[i] + 1));
}
return char;
}
arr.forEach((item, idx) => {//存储字母数字
let separat = separation(item.key_);
item.letter = separat.letter[0];
item.digital = Number(separat.digital[0]);
let a = stringTonum(separat.letter[0]);
item.letter_digital = a;
})
function separation(num) {//拆分字母数字
let letter = num.match(/^[a-z|A-Z]+/gi);
let digital = num.match(/\d+$/gi);
console.log(letter + "," + digital);
return { "letter": letter, "digital": digital }
}
for (let j = 0; j < arr.length; j++) {
if (arr[j].digital == 1) {
let t = matchMerger(arr[j].key_);
if (t && t.e) {
tree.push(
{
name: arr[j].data,
startrow: t.s.r,
startcol: t.s.c,
endrow: t.e.r,
endcol: t.e.c,
children: [],
flagkey: arr[j].key_,
flag_r: t.e.r+1, //自身所在行
flag_c: arr[j].letter_digital//自身所在列
}
)
} else {
tree.push(
{
name: arr[j].data,
children: [],
flagkey: arr[j].key_,
flag_r: arr[j].digital, //自身所在行
flag_c: arr[j].letter_digital//自身所在列
}
)
}
} else {
recursive(tree, arr[j].letter_digital, arr[j].digital, arr[j].key_, arr[j].data);
}
}
console.log(tree,"tree");
function stringTonum(a) {//excel字母转数字
var str = a.toLowerCase().split("");
var num = 0;
var al = str.length;
var getCharNumber = function (charx) {
return charx.charCodeAt() - 96;
};
var numout = 0;
var charnum = 0;
for (var i = 0; i < al; i++) {
charnum = getCharNumber(str[i]);
numout += charnum * Math.pow(26, al - i - 1);
};
return numout;
}
function matchMerger(data) {//获取所合并行数据
for (let i = 0; i < margezh.length; i++) {
if (margezh[i].s_ == data) {
return margezh[i];
}
}
}
function recursive(data, xcol, xrow, key, name) {//递归树
let flag = 0;
for (let i = 0; i < data.length; i++) {
if (data[i].children) {
if (data[i].flag_c <= xcol && data[i].flag_r == xrow - 1&&data[i].endcol+1 >= xcol ) {
if (flag == 0) {
let t = matchMerger(key);
if (t && t.s) {
data[i].children.push({
name: name,
startrow: t.s.r,
startcol: t.s.c,
endrow: t.e.r,
endcol: t.e.c,
children: [],
flagkey: key,
flag_r: t.e.r+1, //自身所在行
flag_c: xcol//自身所在列
})
} else {
data[i].children.push({
name: name,
children: [],
flagkey: key,
flag_r: xrow, //自身所在行
flag_c: xcol//自身所在列
})
}
}
falag = 1;
} else {
recursive(data[i].children, xcol, xrow, key, name);
}
} else {
}
}
}
console.log(tree)//打印最后生成树
</script>
最后打印的结果为
其中有的参数或许你们不需要，可以在生成树后递归去掉，别在代码里去掉，这会出现错误。
下面贴出xlsx解析excel需要取得数据
这些就是具体核心方法，如果不知道如何解析excel，请参考前面发的文章，若有问题请留言，大家一起学习，谢谢。
