前言
在工作中,我遇到了利用SheetJS库来导出xlsx文件的挑战,以下分享我的解决过程。我非常欢迎各位在评论区指正可能存在的疏漏,或是提出您认为更加高效的方法。让我们借此机会互相学习,共同成长。目标如下图:
导出的xlsx
注意
请确保本机有Node.js及配套的npm(Node Package Manager)。如未安装,请访问Node.js官网(https://nodejs.org/)。
建议阅读sheetjs官网,之后再继续。
1. 新建目录并初始化
mkdir demo
cd demo
npm init -y
2. 安装xlsx
npm install xlsx
3. 在根目录下新建index.js文件
当前目录结构
4. 解决流程
本次使用主要方法:
interface TreeNode {
id: string;
name: string;
children:TreeNode[]
}
4.1 定义class TreeNode
- 先安装uuid以便于生成id,命令如下:
npm install uuid
- 根目录下创建TreeNode.js文件
/*
* 文件名: TreeNode.js
* 作者: 破浪前行·吴
* 创建日期: 2024/5/10
* 描述: 树形节点类
*/
const { v4 } = require("uuid");
class TreeNode {
#id; // 标识
#name; // 名称
#children; // 子节点
#parent; // 父节点
constructor() {
this.#id = v4();
this.#name = null;
this.#children = [];
this.#parent = null;
}
get id() {
return this.#id;
}
/**
* @param {string} value
*/
set id(value) {
this.#id = value;
}
get name() {
return this.#name;
}
/**
* @param {string} value
*/
set name(value) {
this.#name = value;
}
get children() {
return this.#children;
}
get parent() {
return this.#parent;
}
/**
* @param {TreeNode} value
*/
set parent(value) {
this.#parent = value;
}
// 添加子节点
/**
* @param {TreeNode[]} node
*/
addChildNode(...node) {
node.forEach((item) => {
item.parent = this;
});
this.#children.push(...node);
}
// 序列化
serialize() {
const loop = (list) => {
return list.map((item) => {
const newVal = {
id: item.id,
name: item.name,
children: [],
};
if (item.children && item.children.length) {
newVal.children = loop(item.children);
}
return newVal;
});
};
const serializeList = loop(this.children);
return JSON.stringify(serializeList);
}
}
module.exports = TreeNode;
- 在index.js中引入TreeNode.js并生成一个根节点数据
const TreeNode = require("./TreeNode");
// 创建根节点
const ORGANIZATION = "organization";
const originTreeNode = new TreeNode();
originTreeNode.id = ORGANIZATION;
const a = new TreeNode();
a.name = "1";
const aa = new TreeNode();
aa.name = "11";
a.addChildNode(aa);
const b = new TreeNode();
b.name = "2";
originTreeNode.addChildNode(a, b);
console.log(originTreeNode.serialize());
在根目录下使用命令
node ./index.js
运行结果:
至此数据已经准备好了。
4.2 aoa_to_sheet方法aoa输入的数据结构(数组的数组输入)
如下图:参考官网处理 JSON 和 JS 数据
4.2.1 打平数据源 和 找出数据源最大深度(得到有多少列)
以行列来预设输入的aoa以便后续使用。
- 打平数据源(得到有多少行)
在index.js中添加方法如下
/*
* 文件名: index.js
* 作者: 破浪前行·吴
* 创建日期: 2024/5/10
* 描述: 使用sheetjs导出xlsx
*/
//...
/**
* @description 打平数据源
* @param {TreeNode} treeNode 节点
* @param {boolean} isSelf 是否包含自己
* @returns {TreeNode[]}
*/
const flatTreeNodeMethod = (treeNode, isSelf = false) => {
const searchResult = [];
const loop = (list) => {
for (let i = 0; i < list.length; i++) {
const item = list[i];
searchResult.push(item);
if (item.children && item.children.length) {
loop(item.children);
}
}
};
loop(isSelf ? [treeNode] : treeNode.children);
return searchResult;
};
const flatTreeNode = flatTreeNodeMethod(originTreeNode);
- 找出数据源最大深度(得到有多少列)
在TreeNode.js文件中class TreeNode 添加方法如下:
/*
* 文件名: TreeNode.js
* 作者: 破浪前行·吴
* 创建日期: 2024/5/10
* 描述: 树形节点类
*/
// ...
class TreeNode {
//...
// 获取父,祖父...
getChainParent() {
const list = [];
const loop = (node) => {
const parent = node.parent;
if (parent !== null) {
list.push(parent);
loop(parent);
}
};
const parent = this.parent;
parent !== null && loop(this);
return list;
}
//...
}
根据打平的数据源(flatTreeNode)找出最大深度,在index.js添加如下:
// index.js
// ...
let maxDepth = 1;
let hasChildrenNumber = 0;
for (let index = 0; index < flatTreeNode.length; index++) {
const element = flatTreeNode[index];
const depth = element.getChainParent().length; // 当前节点深度
if (depth > maxDepth) {
maxDepth = depth;
}
if (element.children && element.children.length) {
hasChildrenNumber++;
}
}
console.log(maxDepth, "maxDepth");
可以执行看看,深度就是有多少列。
4.2.2 预设aoa
根据flatTreeNode和maxDepth预设aoa,在index.js添加如下:
// index.js
// ...
const aoa = [];
for (let row = 0; row < flatTreeNode.length- hasChildrenNumber; row++) {
aoa[row] = [];
for (let col = 0; col < maxDepth; col++) {
aoa[row][col] = null;
}
}
console.log(aoa);
可以执行命令看看。
4.3 向aoa插值
计算出具体行列插值。
4.3.1 使用Map对象保存键值对,收集每一列的数据
修改4.2.1部分相关代码,如下:
//index.js
// ...
// 每一列的数据
const columnListMap = new Map();
let maxDepth = 1;
let hasChildrenNumber = 0;
for (let index = 0; index < flatTreeNode.length; index++) {
const element = flatTreeNode[index];
const depth = element.getChainParent().length; // 当前节点深度
const values = columnListMap.get(depth) || [];
values.push(element);
columnListMap.set(depth, values);
if (depth > maxDepth) {
maxDepth = depth;
}
if (element.children && element.children.length) {
hasChildrenNumber++;
}
}
console.log(columnListMap);
//...
执行命令:
node ./index.js
结果如下:
4.3.2 插值
向aoa中插值。
4.3.2.1 将模拟数据增加一些,如下:
// index.js
// 创建根节点
// 创建根节点
const ORGANIZATION = "organization";
const originTreeNode = new TreeNode();
originTreeNode.id = ORGANIZATION;
const a = new TreeNode();
a.name = "1";
const aa = new TreeNode();
aa.name = "11";
const aaa = new TreeNode();
aaa.name = "111";
const aab = new TreeNode();
aab.name = "112";
aa.addChildNode(aaa, aab);
const ab = new TreeNode();
ab.name = "12";
const ac = new TreeNode();
ac.name = "13";
a.addChildNode(aa, ab, ac);
const b = new TreeNode();
b.name = "2";
const ba = new TreeNode();
ba.name = "21";
const bb = new TreeNode();
bb.name = "22";
b.addChildNode(ba, bb);
originTreeNode.addChildNode(a, b);
// ...
4.3.2.2 进行插值
在index.js添加如下:
// index.js
//...
/**
* 获取当前节点所有没有子级的子节点数量
* @param {TreeNode} element
* @returns {number}
*/
const getCurrentNodeNoHasChildrenNumber = (element) => {
return (
flatTreeNodeMethod(element).filter(
(node) => !node.children || node.children.length === 0
).length || 1
);
};
/**
* 获取当前下标之前节点的总行数
* @param {TreeNode[]} list
* @param {number} currentIndex
* @returns {number}
*/
const getRowNumber = (list, currentIndex) => {
let total = 0;
for (let index = 0; index < currentIndex; index++) {
const element = list[index];
total += getCurrentNodeNoHasChildrenNumber(element);
}
return total;
};
for (let depth = 1; depth <= maxDepth; depth++) {
const values = columnListMap.get(depth);
if (values) {
const col = depth - 1;
for (let index = 0; index < values.length; index++) {
const element = values[index];
const row = getRowNumber(values, index);
aoa[row][col] = element.name;
}
}
}
console.log(aoa);
执行结果如下:
此时aoa数据已经准备好,还剩合并单元格没有设置,不过现在可以先导出看看效果。
5. 导出xlsx
在index.js中添加如下:
// index.js
//...
const XLSX = require("xlsx");
//...
const workSheet = XLSX.utils.aoa_to_sheet(aoa);
const workBook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workBook, workSheet, "Tree");
XLSX.writeFile("tree.xlsx", workBook);
执行之后会在根目录下生成tree.xlsx文件,打开xlsx如下:
6.合并单元格
在index.js修改插值流程(4.3.2.2)和导出(5.),如下:
// 合并范围
const ranges = [];
for (let depth = 1; depth <= maxDepth; depth++) {
const values = columnListMap.get(depth);
if (values) {
const col = depth - 1;
// 当前列偏移量(占位符)
let offsetRow = 0;
for (let index = 0; index < values.length; index++) {
const element = values[index];
let row = getRowNumber(values, index) + offsetRow;
// 检测当前是否有占位符或值
const loop = () => {
if (aoa[row][col] === "" || aoa[row][col]) {
row += 1;
offsetRow += 1;
loop();
}
};
loop();
// 没有子节点 当前行当前列后填入占位符 最后一列不执行
if (
(!element.children || element.children.length === 0) &&
!(col === maxDepth - 1)
) {
for (let ccindex = col + 1; ccindex < maxDepth; ccindex++) {
aoa[row][ccindex] = "";
}
} else if (element.children && element.children.length > 1) {
// 子节点大于1 计算合并范围
const range = {
s: {
r: row,
c: col,
},
e: {
r: row + getCurrentNodeNoHasChildrenNumber(element) - 1,
c: col,
},
};
ranges.push(range);
}
aoa[row][col] = element.name;
}
}
}
const workSheet = XLSX.utils.aoa_to_sheet(aoa);
workSheet["!merges"] = ranges;
const workBook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workBook, workSheet, "Tree");
XLSX.writeFile(workBook, "tree.xlsx");
执行命令结果,如下:
7. 完整代码
- index.js
/*
* 文件名: index.js
* 作者: 破浪前行·吴
* 创建日期: 2024/5/10
* 描述: 使用sheetjs导出xlsx
*/
const TreeNode = require("./TreeNode");
const XLSX = require("xlsx");
// 创建根节点
const ORGANIZATION = "organization";
const originTreeNode = new TreeNode();
originTreeNode.id = ORGANIZATION;
const a = new TreeNode();
a.name = "1";
const aa = new TreeNode();
aa.name = "11";
const aaa = new TreeNode();
aaa.name = "111";
const aab = new TreeNode();
aab.name = "112";
aa.addChildNode(aaa, aab);
const ab = new TreeNode();
ab.name = "12";
const ac = new TreeNode();
ac.name = "13";
a.addChildNode(aa, ab, ac);
const b = new TreeNode();
b.name = "2";
const ba = new TreeNode();
ba.name = "21";
const bb = new TreeNode();
bb.name = "22";
b.addChildNode(ba, bb);
originTreeNode.addChildNode(a, b);
/**
* @description 打平数据源
* @param {TreeNode} treeNode 节点
* @param {boolean} isSelf 是否包含自己
* @returns {TreeNode[]}
*/
const flatTreeNodeMethod = (treeNode, isSelf = false) => {
const searchResult = [];
const loop = (list) => {
for (let i = 0; i < list.length; i++) {
const item = list[i];
searchResult.push(item);
if (item.children && item.children.length) {
loop(item.children);
}
}
};
loop(isSelf ? [treeNode] : treeNode.children);
return searchResult;
};
const flatTreeNode = flatTreeNodeMethod(originTreeNode);
// 每一列的数据
const columnListMap = new Map();
let maxDepth = 1;
let hasChildrenNumber = 0;
for (let index = 0; index < flatTreeNode.length; index++) {
const element = flatTreeNode[index];
const depth = element.getChainParent().length; // 当前节点深度
const values = columnListMap.get(depth) || [];
values.push(element);
columnListMap.set(depth, values);
if (depth > maxDepth) {
maxDepth = depth;
}
if (element.children && element.children.length) {
hasChildrenNumber++;
}
}
const aoa = [];
for (let row = 0; row < flatTreeNode.length - hasChildrenNumber; row++) {
aoa[row] = [];
for (let col = 0; col < maxDepth; col++) {
aoa[row][col] = null;
}
}
/**
* 获取当前节点所有没有子级的子节点数量
* @param {TreeNode} element
* @returns {number}
*/
const getCurrentNodeNoHasChildrenNumber = (element) => {
return (
flatTreeNodeMethod(element).filter(
(node) => !node.children || node.children.length === 0
).length || 1
);
};
/**
* 获取当前下标之前节点的总行数
* @param {TreeNode[]} list
* @param {number} currentIndex
* @returns {number}
*/
const getRowNumber = (list, currentIndex) => {
let total = 0;
for (let index = 0; index < currentIndex; index++) {
const element = list[index];
total += getCurrentNodeNoHasChildrenNumber(element);
}
return total;
};
// 合并范围
const ranges = [];
for (let depth = 1; depth <= maxDepth; depth++) {
const values = columnListMap.get(depth);
if (values) {
const col = depth - 1;
// 当前列偏移量(占位符)
let offsetRow = 0;
for (let index = 0; index < values.length; index++) {
const element = values[index];
let row = getRowNumber(values, index) + offsetRow;
// 检测当前是否有占位符或值
const loop = () => {
if (aoa[row][col] === "" || aoa[row][col]) {
row += 1;
offsetRow += 1;
loop();
}
};
loop();
// 没有子节点 当前行当前列后填入占位符 最后一列不执行
if (
(!element.children || element.children.length === 0) &&
!(col === maxDepth - 1)
) {
for (let ccindex = col + 1; ccindex < maxDepth; ccindex++) {
aoa[row][ccindex] = "";
}
} else if (element.children && element.children.length > 1) {
// 子节点大于1 计算合并范围
const range = {
s: {
r: row,
c: col,
},
e: {
r: row + getCurrentNodeNoHasChildrenNumber(element) - 1,
c: col,
},
};
ranges.push(range);
}
aoa[row][col] = element.name;
}
}
}
const workSheet = XLSX.utils.aoa_to_sheet(aoa);
workSheet["!merges"] = ranges;
const workBook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workBook, workSheet, "Tree");
XLSX.writeFile(workBook, "tree.xlsx");
- TreeNode.js
/*
* 文件名: TreeNode.js
* 作者: 破浪前行·吴
* 创建日期: 2024/5/10
* 描述: 树形节点类
*/
const { v4 } = require("uuid");
class TreeNode {
#id; // 标识
#name; // 名称
#children; // 子节点
#parent; // 父节点
constructor() {
this.#id = v4();
this.#name = null;
this.#children = [];
this.#parent = null;
}
get id() {
return this.#id;
}
/**
* @param {string} value
*/
set id(value) {
this.#id = value;
}
get name() {
return this.#name;
}
/**
* @param {string} value
*/
set name(value) {
this.#name = value;
}
get children() {
return this.#children;
}
get parent() {
return this.#parent;
}
/**
* @param {TreeNode} value
*/
set parent(value) {
this.#parent = value;
}
/**
* @description 添加子节点
* @param {TreeNode[]} node
*/
addChildNode(...node) {
node.forEach((item) => {
item.parent = this;
});
this.#children.push(...node);
}
// 获取父,祖父...
getChainParent() {
const list = [];
const loop = (node) => {
const parent = node.parent;
if (parent !== null) {
list.push(parent);
loop(parent);
}
};
const parent = this.parent;
parent !== null && loop(this);
return list;
}
// 序列化
serialize() {
const loop = (list) => {
return list.map((item) => {
const newVal = {
id: item.id,
name: item.name,
children: [],
};
if (item.children && item.children.length) {
newVal.children = loop(item.#children);
}
return newVal;
});
};
const serializeList = loop(this.children || []);
return JSON.stringify(serializeList);
}
}
module.exports = TreeNode;
8. 主要文献
9. 总结
以上是本人的处理步骤,恳请各位在评论区不吝赐教,指出疏漏之处。同时,若您有更佳的方法或技巧,欢迎在评论区留言交流,让我们携手共进,共同成长。