目录
5.1. JS宏获取所有子文件夹中的文件名称，并生成超链接，你还有更简洁的代码吗？
5.2. wps js宏获取工作簿、工作表、单元格的值、链接转图片、单元格区域数据转二维数组、保存工作簿的常用Excel操作
前言
某一天领导需要获取近年来所有车组的数据，它们分布在不同的文件不同的列中，而且因历史问题，可能某些列不存在，某些列后面才加上去的，需要将它们都汇总到一个表格当中。
这个时候有没有什么办法，在不添加其它依赖的情况下，在内网环境任意一台电脑都能执行收集给定目录下所有相关文件的信息（当然表格中表头格式是一样的，但标题不一定一样）。
一、目录结构及数据收集需求
参考图1~6。如图1所示，现在需要将 故障 目录下的142个表格文件共计40000+条数据汇总到图2所示的表头格式下。各表数据结构各不相同，大致可以按照年份区分为如图3~6所示的结构，可能存在部分特殊的，默认所有数据的工作簿里只有一个工作表Sheet1（可能有其他人改了）。
现在需要就图2表头中非红色字样的列，将各文件中符合的数据逐行传输过来，因数据过多和其它因素，需要使用WPS-JS宏（VBA宏需要VIP），统计所有数据。本文将按照下面的流程进行：
- 弹窗选择目录
- 读取目录下所有文件的绝对路径
- 移除不需要的文件或筛选目标文件
- 逐个目标文件数据读取并写入到汇总表
- 输出各个数据的记录情况
- 返回合计统计量
二、JS宏流程
首先新建汇总表格，将工作表名称改为汇总表，然后打开JS宏（工具 > 开发工具 > WPS宏编辑器），将默认的模块名称Module改为Main，并添加以下代码，接下来每一个三级标题（比如2.1）都将在Main函数里逐个修改。
function Main() {
// 存放设置信息
// ...
// 程序开始
// 2.1. 弹窗选择目录
// 读取目录下所有文件的绝对路径
// 2.2. 移除不需要的文件或筛选目标文件
// 2.3. 逐个目标文件数据读取并写入到汇总表
// 输出各个数据的记录情况
// 2.4. 返回合计统计量
}2.1. 弹窗选择目录
参考<文章5.1>的【JSA代码】，修改为需要的内容，根据用户选择文件夹返回所有文件的绝对路径。插入模块获取目录下所有文件绝对路径，其代码如下
function 获取目录下所有文件绝对路径() {
const ph = Application.FileDialog(msoFileDialogFolderPicker);
Application.ScreenUpdating = false;
ph.Show();
const PH = [...ph.SelectedItems];
if(0 === PH.length) return ;
const FileFullPaths = []; // 这里存放目录下所有文件的绝对路径
const getAllFileName = (path) => {
if(0 === path.length) return ;
const subFileFullPaths = [];
path.forEach(it => {
let f = Dir(`${it}\\`, 16);
while(f) {
if("." !== f && ".." !== f) {
const attr = `${it}\\${f}`;
if(32 !== GetAttr(attr) && 1056 !== GetAttr(attr)) {
subFileFullPaths.push(`${attr}\\`);
}
else {
FileFullPaths.push(attr.replace("\\\\", "\\"));
}
}
f = Dir();
}
});
return getAllFileName(subFileFullPaths);
};
getAllFileName(PH);
return FileFullPaths;
}修改Main模块的内容
// 2.1. 弹窗选择目录
// 读取目录下所有文件的绝对路径
const 目录下所有文件绝对路径 = 获取目录下所有文件绝对路径();2.2. 移除不需要的文件或筛选目标文件
插入模块过滤文件类型，其代码如下，主要判断方法为目标绝对路径是否以某字串结尾，使用String.prototype.endsWith
function 过滤文件类型(pathArray, extensionIncludesArray) {
if(!(pathArray instanceof Array)) {
alert(`参数<目录下所有文件绝对路径>异常`);
return [];
}
if(extensionIncludesArray instanceof String) {
extensionIncludesArray = [extensionIncludesArray];
} else if(!(extensionIncludesArray instanceof Array)) {
alert(`参数<需要的文件类型>异常`);
return [];
}
if(0 === pathArray.length) {
alert(`目录下没有文件`);
return [];
}
const _res = [];
function _retPath(item) {
let canIUse = false;
extensionIncludesArray.forEach(it => {
if(item.endsWith(it)) canIUse = true;
});
return canIUse;
}
pathArray.forEach(it => {
if(_retPath(it)) _res.push(it);
});
return _res;
}修改Main模块的内容
// 存放设置信息
const 需要的文件类型 = [".xls", ".xlsx"];
// 2.2. 移除不需要的文件或筛选目标文件
const 所有目标文件绝对路径 = 过滤文件类型(目录下所有文件绝对路径, 需要的文件类型);2.3. 逐个目标文件数据读取并写入到汇总表
插入模块获取列标签，其代码如下，用于将列号转为表格的列标签（26=>Z, 54=>BB）
function 获取列标签(第几列) {
if(676 < 第几列) {
alert('暂不支持超过676列的数据');
throw new Error(`获取了第${第几列}列的数据，超过 676 的限制`);
}
if(1 > 第几列) return "";
if(1 === 第几列) return "A";
let temp = 第几列;
let result = '';
let base = 26;
while (temp > 0) {
let remainder = (temp-1) % base;
result = String.fromCharCode(65 + remainder) + result; // A 的 ASCII 码是 65
temp = temp === base ? 0 : Math.floor(temp / base);
}
return result;
}插入模块数据保存，其代码如下，用于将读取到的 data 数据写入到汇总表中，其中 Value2 参考了<文章5.2>的读写单元格
// data 是拿到的数据，是个二维数组，data[i] 是行数组， data[i][j]是第(j+1)列的数据
// keys_o 是Main模块下的 “保存设置” 对象
// startLine 是数据 data 在汇总表写入的 开始那一行
function 数据保存(data, keys_o, startLine) {
const cols = Object.values(keys_o);
for(let row = startLine; row - startLine < data.length ; row++) {
for(const index in cols) {
const col = cols[index];
const key = `${获取列标签(col)}${row}`;
const value = data[row-startLine][index];
Range(key).Value2 = value;
}
}
}插入模块设置表格数据，其代码如下，用于读取路径的数据，以保存设置的结构保存到汇总表中，其中 title 数据获取参考了<文章5.3>的第二个代码块
// key_col 读取数据时，判断该行是否结束的指定列，如果该行的 key_col 列为空，判断文件数据读取完毕
function 设置表格数据(文件路径, 工作表默认名称, 开始行, save_o, key_col, isDebug) {
const wb = Workbooks.Open(文件路径); // 工作簿
const sheet = wb.Sheets(工作表默认名称); // 工作表
const destRow = 2; // 起始行 / 标题行
const cells = sheet.UsedRange.Cells;
// 获取数据列 和 对应的下标
const keys = Object.keys(save_o);
const keyDataIndex = Array(keys.length).fill(undefined);
for(let i = 1; ; i++) {
const title = cells.Item(2, i).Value2;
if(!title) break;
const col = keys.indexOf(title) + 1;
if(0 === col) continue;
// console.log(`${title} -> ${i}`);
keyDataIndex[col-1] = i;
}
let inc = 0;
const rowDatas = [];
for(let row = 3; cells.Item(row, key_col).Value2; row++) {
const rowdata = 获得行数据(row);
rowDatas.push(rowdata);
inc++;
}
// 关闭
wb.Close();
function 获得行数据(row) {
const rowData = [];
keys.forEach((key, index) => {
const curRow = 开始行 + inc;
const curCol = save_o[key];
const col = keyDataIndex[index];
const value = col ? cells.Item(row, col).Value2 : '';
rowData.push(value ? value : '');
});
return rowData;
}
if(isDebug) {
console.log(`对应的文件：${文件路径}![${工作表默认名称}]`);
console.log(`数据行：(${开始行}, ${开始行 + inc - 1})`);
console.log(`保存列 => [获取的列]: 对应列名`);
keys.forEach((it, i) => {
if(!keyDataIndex[i]) {
console.log(`${save_o[it]} => 无: ${it}`);
}
else {
console.log(`${save_o[it]} => [${获取列标签(keyDataIndex[i])}]: ${it}`);
}
});
console.log(`\n\n`);
}
数据保存(rowDatas, save_o, 开始行);
return {
currentRow: 开始行 + inc,
dataCount: rowDatas.length
};
}修改Main模块的内容
// 存放设置信息
const 关键列 = 2; // 注意：请设置某一列为关键列，用于检查数据是否结束（该列有数据为空则该表结束）
const 工作表默认名称 = "Sheet1"; // 数据表需要是这个名称，否则程序会报错并终止运行
const 保存设置 = { // 这个是汇总表的标题名称和标题列号，从1开始，程序会检索该标题名称并拿到数据
// 标题名称: 列号
"车组号": 2,
"车号": 3,
"功能分类（一级系统）": 5,
"功能分类（子系统）": 6,
"结构分类": 7,
"故障描述": 8,
"发现时间": 9,
"处理结果": 10,
"处理方法": 11,
"详细方法": 12
};
const 起始行 = 2; // 汇总表开始写记录的行 以及 被汇总的表的标题行
let 调试模式 = true; // 如果拿到的数据有异常，可以将这个设置为 true，逐个文件查看数据设置方式
// 2.3. 逐个目标文件数据读取并写入到汇总表
// 输出各个数据的记录情况
let 开始行 = 起始行;
let 数据量 = 0;
所有目标文件绝对路径.forEach(path => {
try {
const data = 设置表格数据(path, 工作表默认名称, 开始行, 保存设置, 关键列, 调试模式);
开始行 = data.currentRow;
数据量 += data.dataCount;
} catch (e) {
console.log(`错误：${e.toString()}\npath=${path}`);
}
});2.4. 返回合计统计量
完成上面的设置后，修改Main模块的内容
// 2.4. 返回合计统计量
alert(`成功！\n共统计了${数据量}条记录，${数据量 * Object.keys(保存设置).length}个值`);三、实现效果
如图7所示，在汇总表设置参数后运行Main模块，得到该结果。过程中如果读取表格出错，运行后错误表格会在标签栏，修改所有错误后重新执行，得到图8所示的结果，筛选了前后各两个运行结果信息。
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
