项目需求需要将用户上传的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,请参考前面发的文章,若有问题请留言,大家一起学习,谢谢。