文章 / 公众号历史文章
【开发文档分享】有AI编程加持，是时候重视WPS的JSA宏的价值了
上一篇推文中，分享了WPS多维表格的二次开发的接口文档，反响不错，在AI编程能力越发强大的今天，普通Excel/WPS中高级用户群体，真的挺有必要再进一步，将通过氛围编程（vibe coding）的方式，驾驭一下AI直接写代码。 【开发文档分享】论二次开发能力，WPS多维表格遥遥领先 当然上一篇谈及
上一篇推文中，分享了WPS多维表格的二次开发的接口文档，反响不错，在AI编程能力越发强大的今天，普通Excel/WPS中高级用户群体，真的挺有必要再进一步，将通过氛围编程（vibe coding）的方式，驾驭一下AI直接写代码。
当然上一篇谈及的是WPS多维表格，很多人遇见了一款工具，就把它当前瑞士军刀般来使用，什么需求，什么场景都用一个工具完成，其实是非常不合适的，例如在多维表格群体里，笔者能够感受到的氛围是大家都很排斥着用传统表格了，觉得多维表格，数据结构化程度高了，还可以直接有仪表盘出报告，没有传统表格什么事了，这也只能说有点天真。
传统表格的生态，仍然是非常旺盛的，因其面向的是单元格级别的颗粒度，可以无限自由地构建数据处理和报表。在新的OFFICE365新函数、动态数组公式效果的加持下，可以做出非常强大的效果。
还有，它是本地客户端的工具，可以和本地资源做连接的，这比起线上表格，有了太广阔的扩展空间了，无论是批量性读取其他系统导出的表格、文本等文件，还有对网页非结构化数据的抓取能力，都是在线表格类产品无法相比的。
当然最关键的，它还有VBA/JSA对象模型的生态，可以将绝大多数的界面操作转换为自动化后台代码完成。
有点扯远了，本篇分享的是WPS的JSA开发文档，老规矩，有兴趣的可加笔者微信 190262897 获取。
下面一些小插曲，有兴趣的可以往下看看，笔者亲自实践过，借助开发文档，交由AI来写代码，也是很可行的，当然，前提最好自己懂VBA对象模型、懂点JS语言语法。
本篇其实是一个小财务需求，需要将财务系统的导出xlsx文件，进行再加工，在Excel/WPS环境下方便地查看报告。当然要完全自动化，就需要用代码去驱动，综合评估了使用RPA软件，Excel/WPS的VBA代码和WPS的JSA代码来完成，最终选择了WPS的JSA方式实现。
简单说明下理由：
1、交付方便性：RPA软件，需要安装一个软件，增加交付成本。
2、本篇需求，完全是只以Excel/WPS作交互，RPA软件也没有太大的额外优势需要选择它。
3、交付的效果，只需要点击一个按钮，运行代码后即可，没有界面交互要求，RPA软件作界面（笔者还挺喜欢用RPA软件做简单的交互界面，比起VBA/JSA的古老窗体界面更舒服快捷）的优势也不明显。
4、VBA和JSA的选择上，选择了后者，是为了在数据处理过程中，可以有更现代的数据结构来处理，写出的代码也更加优雅，VBA来写就比较憋屈。
5、交付对象使用的是WPS，交付成VBA也憋屈，默认不自带VBA模块，虽然很容易通过安装实现，但想挑战下极端环境，没有VBA模块的交付场景。
AI编程里，相比JSA、VBA编程，应该更为成熟，毕竟语料更丰富，AI写出来的代码应该更稳，这个结论，估计今天仍然成立。
在人工写代码中，JSA的宏编缉器环境，实在很简陋，很难写代码，极少的智能提示，所以本篇需求，笔者是努力让AI全程来写代码，人只负责复制粘贴，AI写JS的前端项目代码很擅长，但写JSA代码，就非常容易有幻觉，乱写一通，所以，给它一份markdown格式的文档，就显得尤为重要。
当然笔者在今年年初已经爬取了WPS的JSA/WPSJS加载项官方文档，整理成pdf了，这一次重新拿来整理成markdown格式给AI参考，最终AI写出来的代码，正确率也还不错的。
总共整理了4个文件夹
为了尽可能验证可行性，本次的项目，使用的是TRAE CN版本，大家都能轻松获取到的，全免费使用的。用的模型以Kimi-K2-0905为主，
分享部分对话过程，写出来的代码，还真不错，个别有点小错误，多轮对话修正下，可以接受，关键是全程不用在WPS的JSA宏编程器上写代码，就算在TRAE CN上小改一下，也有AI智能提示，也是很流畅。
国产模型，多轮对话过程中，建议反复提交文档给它参考，不知道它哪一轮对话又放飞自我，用幻觉来写出各种不存在的接口方法。而为了多次一口次引用多个文档，最后使用了之前开发的ClipboardTool工具，识别出这个文档的剪切板数据类型，将它固化后多次使用，当然最后在Ditto上，也测试成功，可以添加这种自定义剪切板类型来固化它的内容，供下次粘贴使用。
最后，附上一个项目结果的效果，从财务系统导出的的文件，读到数据源中，通过函数的方式，将数据引用到报表层来使用，用Filter、TextBefore、LET等现代函数来制作，实现简单，又语义性极强，真的太舒服了，特别是WPS个人版全部函数同步到位后，函数能轻松解决的，还懒得写代码实现了。
让笔者有兴趣使用JSA来实现，很大原因是，类似以下这样的科目表数据处理，财务的科目是父子结构的，需要处理成这种多级分类，并且有些级别是长短不一的，例如有些到2级就没有了，有些是有3级分类的，还有有些三级分类下只有一个成员，不需要小计统计的等等，笔者看到这些需求，用VBA来写有些头皮发麻，就想着AI驱动下用JS语言来处理应该更流畅。
最后，附上本篇的JSA代码，大部分是AI写出来的，而且AI写的也很规范，叫它重构，多些注释和日志输出等，也很听话，写出来的代码可读性很不错。每个子方法上还有定义，在TRAE上跳转阅读和悬停也很流畅。
/** * WPS JSA 财务利润报表生成宏 - 重构版 * * 功能说明： * 1. 从"会计科目_导入"文件中读取会计科目数据 * 2. 处理成本数据并写入成本表 * 3. 处理损益类数据并写入成本表 * 4. 合并相同内容的单元格 * 5. 处理辅助余额表数据 * * 代码结构： * - 主函数：main() - 程序入口，协调各模块 * - 文件操作模块：文件查找、工作簿处理 * - 数据处理模块：成本数据处理、损益类数据处理 * - 辅助余额表模块：辅助余额表数据处理 * - 工具函数模块：单元格合并、边框设置等 */
// 全局常量定义const START_ROW = 17; // 数据起始行号（从17行开始填充数据）
/** * 主函数 - 程序入口 * 协调各个功能模块，处理主要业务流程 */function main() { try { console.log("=== 财务利润报表生成程序开始 ===");
// 步骤1：初始化环境 let context = initializeEnvironment();
// 步骤2：查找并打开会计科目源文件 let sourceFile = findTargetFile(context.currentPath, "会计科目_导入", ".xlsx"); if (!sourceFile) { alert("未找到以'会计科目_导入'开头的xlsx文件！"); return; }
let sourceWkb = openOrGetWorkbook(sourceFile);
// 步骤3：读取会计科目数据 let accountingData = readAccountingData(sourceWkb); if (!accountingData) { sourceWkb.Close(false); return; }
// 步骤4：处理成本数据 let costResult = processCostData(accountingData); console.log("成本数据处理完成，共" + costResult.length + "行");
// 步骤5：处理损益类数据 let profitLossResult = processProfitLossData(accountingData); console.log("损益类数据处理完成，共" + profitLossResult.length + "行");
// 步骤6：写入结果数据 writeResultsToWorkbook(context.actWkb, costResult, profitLossResult, accountingData);
// 步骤7：处理辅助余额表 processAuxiliaryBalanceSheet(context.actWkb, context.currentPath);
// 步骤7：处理辅助余额表 processAuxiliaryBalanceSheet(context.actWkb, context.currentPath); //对 汇总表 的表头进行数字文本化处理，单元格内容是数字的，将它设置成文本 let summarySht = context.actWkb.Worksheets("汇总表"); let headerRange = summarySht.Range("名称_表头区域"); headerRange.NumberFormat = "@"; // 新增：对headerRange第三行内容作判断，如果是数字则前面加'强制转文本 let thirdRow = headerRange.Rows(3); for (let c = 1; c <= thirdRow.Columns.Count; c++) { let cell = thirdRow.Cells(1, c); let val = cell.Value2; // 更准确的数字判断 if (val !== null && val !== undefined && val !== "" && !isNaN(val) && !isNaN(parseFloat(val))) { cell.Value2 = "'" + val; } }
// 步骤8：清理资源 cleanupResources(sourceWkb);
console.log("=== 财务利润报表生成程序完成 ==="); } catch (error) { console.log("发生错误: " + error.message); alert("处理过程中发生错误: " + error.message); }}
/** * 初始化环境 * 获取当前工作簿和路径信息 * @returns {Object} 包含actWkb和currentPath的对象 */function initializeEnvironment() { let actWkb = Application.ActiveWorkbook; let currentPath = actWkb.Path;
console.log("当前工作簿: " + actWkb.Name); console.log("当前路径: " + currentPath);
return { actWkb: actWkb, currentPath: currentPath };}
/** * 查找目标文件 * 在指定路径下查找符合前缀和后缀条件的文件 * @param {string} searchPath - 搜索路径 * @param {string} filePrefix - 文件前缀 * @param {string} fileExtension - 文件扩展名 * @returns {string|null} 找到的文件完整路径，未找到返回null */function findTargetFile(searchPath, filePrefix, fileExtension) { console.log(`查找文件：路径=${searchPath}, 前缀=${filePrefix}, 扩展名=${fileExtension}`);
let files = FileSystem.readdirSync(searchPath);
for (let i = 0; i < files.length; i++) { let fileName = files[i]; if (fileName.startsWith(filePrefix) && fileName.endsWith(fileExtension)) { let fullPath = searchPath + "\\" + fileName; console.log("找到目标文件: " + fullPath); return fullPath; } }
console.log("未找到符合条件的文件"); return null;}
/** * 打开或获取已打开的工作簿 * 如果工作簿已打开则直接返回，否则打开新工作簿 * @param {string} filePath - 工作簿文件路径 * @returns {Object} 工作簿对象 */function openOrGetWorkbook(filePath) { // 检查是否已打开 let allWorkbooks = Application.Workbooks; for (let i = 1; i <= allWorkbooks.Count; i++) { let wkb = allWorkbooks.Item(i); if (wkb.FullName === filePath) { console.log("工作簿已打开，直接使用: " + wkb.Name); return wkb; } }
// 未打开则新建 let newWkb = Application.Workbooks.Open(filePath); console.log("已打开工作簿: " + newWkb.Name); return newWkb;}
/** * 读取会计科目数据 * 从源工作簿的"会计科目"工作表中读取数据 * @param {Object} sourceWkb - 源工作簿对象 * @returns {Array|null} 会计科目数据数组，失败返回null */function readAccountingData(sourceWkb) { try { let sourceSheet = sourceWkb.Sheets.Item("会计科目"); if (!sourceSheet) { alert("未找到'会计科目'工作表！"); return null; }
let lastRow = sourceSheet.UsedRange.Rows.Count; let dataRange = sourceSheet.Range("F10:H" + lastRow); let dataArray = dataRange.Value2;
console.log("读取数据范围: F10:H" + lastRow); console.log("数据行数: " + dataArray.length);
return dataArray; } catch (error) { console.log("读取会计科目数据失败: " + error.message); alert("读取会计科目数据失败: " + error.message); return null; }}
/** * 将处理结果写入工作簿 * 将成本数据和损益类数据分别写入指定位置 * @param {Object} targetWkb - 目标工作簿 * @param {Array} costResult - 成本处理结果 * @param {Array} profitLossResult - 损益类处理结果 * @param {Array} subjectData - 原始会计科目数据 */function writeResultsToWorkbook(targetWkb, costResult, profitLossResult, subjectData) { let costSheet = targetWkb.Sheets.Item("成本表"); // 写入成本数据 let costRows = costResult.length; let costCols = costResult[0].length; let costRange = costSheet.Range("A" + START_ROW).Resize(costRows, costCols);
let clearCostRange = Application.Intersect(costRange.CurrentRegion, costRange.CurrentRegion.Offset(1, 0)); clearCostRange.Clear(); //科目编码处理成文本 costSheet.Columns("D:D").NumberFormat = "@"; costRange.Value2 = costResult; console.log("成本数据已写入【成本表】A" + START_ROW + "开始的" + costRows + "行" + costCols + "列区域");
// 写入损益类数据 let profitLossRows = profitLossResult.length; let profitLossCols = profitLossResult[0].length; let profitLossRange = costSheet.Range("H" + START_ROW).Resize(profitLossRows, profitLossCols); let clearProfitLossRange = Application.Intersect(profitLossRange.CurrentRegion, profitLossRange.CurrentRegion.Offset(1, 0)); clearProfitLossRange.Clear(); //科目编码处理成文本 costSheet.Columns("J:J").NumberFormat = "@"; profitLossRange.Value2 = profitLossResult; console.log("损益类数据已写入【成本表】H" + START_ROW + "开始的" + profitLossRows + "行" + profitLossCols + "列区域");
// 激活目标工作簿 targetWkb.Activate();
// 合并单元格和设置边框 mergeIdenticalCells(costSheet, costResult, "A" + (START_ROW - 1), 0); mergeIdenticalCells(costSheet, costResult, "B" + (START_ROW - 1), 1); setRangeBorder(costSheet.Range("A" + (START_ROW - 1)).CurrentRegion);
mergeIdenticalCells(costSheet, profitLossResult, "H" + (START_ROW - 1), 0); setRangeBorder(costSheet.Range("H" + (START_ROW - 1)).CurrentRegion); // 添加E13:F13和K13:L13的公式并填充到CurrentRegion的最后一行 addFormulasToCostSheet(costSheet);
//对C列和H列出现小计字样的进行加粗显示 formatSubtotalCells(costSheet);}
/** * 对C列和H列中内容为"小计："的单元格进行格式化 * 包括扩展为一行三列并加粗显示 * @param {Object} sheet - 工作表对象 */function formatSubtotalCells(sheet) { console.log("开始格式化C列和H列中的小计单元格...");
// 获取工作表的最后一行 let lastRow = sheet.UsedRange.Rows.Count; console.log("工作表最后行号: " + lastRow);
// 处理C列中的小计单元格（从起始行到最后一行） formatSubtotalInRange(sheet, "C", START_ROW, lastRow);
// 处理H列中的小计单元格（从起始行到最后一行） formatSubtotalInRange(sheet, "I", START_ROW, lastRow);
console.log("小计单元格格式化完成");}
/** * 在指定列范围内查找并格式化小计单元格 * @param {Object} sheet - 工作表对象 * @param {string} columnLetter - 列字母（如"C"或"H"） * @param {number} startRow - 开始行号 * @param {number} endRow - 结束行号 */function formatSubtotalInRange(sheet, columnLetter, startRow, endRow) { console.log("开始处理" + columnLetter + "列，范围：" + columnLetter + startRow + ":" + columnLetter + endRow);
// 遍历指定范围内的每一行 for (let row = startRow; row <= endRow; row++) { let cell = sheet.Range(columnLetter + row); let cellValue = cell.Value2; // 使用Text属性获取显示文本
// 检查单元格内容是否为"小计：" if (cellValue === "小计：") { console.log("找到小计单元格: " + columnLetter + row + "，值: " + cellValue);
// 扩展单元格为一行三列 let expandRange = sheet.Range(columnLetter + row).Resize(1, 4); // 设置加粗格式 expandRange.Font.Bold = true;
console.log("已格式化单元格: " + columnLetter + row + "，扩展为一行三列并加粗"); } }}
/** * 处理辅助余额表 * 查找辅助余额表文件，处理其中的数据并写入当前工作簿 * @param {Object} actWkb - 当前活动工作簿 * @param {string} currentPath - 当前工作路径 */function processAuxiliaryBalanceSheet(actWkb, currentPath) { console.log("开始处理辅助余额表...");
// 查找辅助余额表文件 let balanceFile = findTargetFile(currentPath, "科目辅助全景查询", ".xlsx"); if (!balanceFile) { console.log("未找到以'科目辅助全景查询'开头的xlsx文件，跳过辅助余额表处理"); return; }
// 打开或获取辅助余额表工作簿 let balanceWkb = openOrGetWorkbook(balanceFile);
try { // 获取"辅助余额表"工作表（假设一定存在） let balanceSht = actWkb.Sheets.Item("科目辅助全景查询"); console.log("找到'科目辅助全景查询'工作表");
// 查找名为"表_科目辅助全景查询"的ListObject let balanceTable = null; try { balanceTable = balanceSht.ListObjects.Item("表_科目辅助全景查询"); console.log("找到'表_科目辅助全景查询'ListObject");
// 清空ListObject的数据内容（保留表头） if (balanceTable.DataBodyRange) { balanceTable.DataBodyRange.Clear(); console.log("已清空'表_科目辅助全景查询'的数据内容"); }
// 将ListObject大小调整为2行（含标题行） let currentColumns = balanceTable.Range.Columns.Count; let newRange = balanceSht.Range(balanceTable.Range.Cells(1, 1), balanceTable.Range.Cells(2, currentColumns)); balanceTable.Resize(newRange);
balanceSht.Columns.Item("B:B").NumberFormat = "@"; balanceSht.Columns.Item("D:D").NumberFormat = "@"; balanceSht.Columns.Item("F:F").NumberFormat = "@"; balanceSht.Columns.Item("H:H").NumberFormat = "@"; balanceSht.Columns.Item("J:J").NumberFormat = "@"; balanceSht.Columns.Item("L:L").NumberFormat = "@"; balanceSht.Columns.Item("N:N").NumberFormat = "@"; console.log("已将'表_科目辅助全景查询'调整为2行大小（含标题行）"); } catch (tableError) { console.log("未找到'表_科目辅助全景查询'ListObject: " + tableError.message); throw tableError; }
// 读取源数据 let balanceSourceSheet = balanceWkb.Sheets.Item(1); let balanceLastRow = balanceSourceSheet.UsedRange.Rows.Count - 1; // 有合计行，减少一行 let balanceLastCol = balanceSourceSheet.UsedRange.Columns.Count; let lastColLetter = getColumnLetter(balanceLastCol); console.log("科目辅助全景查询源数据最后行号: " + balanceLastRow); console.log("科目辅助全景查询源数据最后列号: " + balanceLastCol + " (" + lastColLetter + ")");
let balanceDataRange = balanceSourceSheet.Range("A15:" + lastColLetter + balanceLastRow); let arrValue = balanceDataRange.Value2;
console.log("读取科目辅助全景查询数据范围: A15:" + lastColLetter + balanceLastRow); console.log("数据数组维度: " + arrValue.length + "行 x " + (arrValue[0] ? arrValue[0].length : 0) + "列");
// 写入数据到A2单元格开始的位置 let targetRange = balanceTable.Range.Offset(1, 0).Resize(arrValue.length, arrValue[0].length); targetRange.Value2 = arrValue; balanceTable.Resize(Application.Union(balanceTable.Range, targetRange.Resize(targetRange.Rows.Count, balanceTable.Range.Columns.Count)));
console.log("已将科目辅助全景查询数据写入当前工作簿的'科目辅助全景查询'工作表，从A2开始"); console.log("写入数据范围: A2:" + lastColLetter + (arrValue.length + 1));
let formulaProjectId = "=LEFT([@项目编码],5)"; let mainProjectIdColumn = balanceTable.ListColumns("主项目编码").DataBodyRange; mainProjectIdColumn.Formula = formulaProjectId;
let formulaCustomerCheck = '=IF(LEFT([@客户编码],1)="1",TRUE,FALSE)'; let customerCheckColumn = balanceTable.ListColumns("客户判断").DataBodyRange; customerCheckColumn.Formula = formulaCustomerCheck;
let formulaTax = "=IFERROR(VLOOKUP([@项目编码],表_子项目信息,4,0),0)"; let taxColumn = balanceTable.ListColumns("项目税率").DataBodyRange; taxColumn.Formula = formulaTax;
let formulaNonTaxIncome = "=[@本期发生-借方本币]/(1+[@项目税率])"; let nonTaxIncomeColumn = balanceTable.ListColumns("不含税收入金额").DataBodyRange; nonTaxIncomeColumn.Formula = formulaNonTaxIncome;
// 处理配置表（包含项目表、公司表和子项目信息表） processSettingTable(actWkb, arrValue); } catch (error) { console.log("处理科目辅助全景查询时发生错误: " + error.message); throw error; } finally { // 确保关闭科目辅助全景查询工作簿 if (balanceWkb) { try { balanceWkb.Close(false); console.log("已关闭科目辅助全景查询源工作簿"); } catch (closeError) { console.log("关闭工作簿时发生错误: " + closeError.message); } } }}
/** * 添加E13和J13的公式并填充到CurrentRegion的最后一行 * @param {Object} costSheet - 成本表工作表对象 */function addFormulasToCostSheet(costSheet) { console.log("开始添加E" + START_ROW + ":F" + START_ROW + "和K" + START_ROW + ":L" + START_ROW + "的公式...");
try { // 获取E列起始行的CurrentRegion范围 let eStartCurrentRegion = costSheet.Range("E" + START_ROW).CurrentRegion; let e13LastRow = eStartCurrentRegion.Rows.Count + eStartCurrentRegion.Row - 1; console.log("E" + START_ROW + "的CurrentRegion范围最后一行: " + e13LastRow);
// 设置E列起始行的公式 let e13Formula = "=IFS(LEN(D" + START_ROW + ")=6,TRUE,AND(LEN(D" + START_ROW + ")=8,COUNTIF($D$" + START_ROW + ":$D$" + e13LastRow + ",LEFT(D" + START_ROW + ',6)&"*")=1),TRUE,TRUE,FALSE)'; let f13Formula = '=IF(AND(名称_公司="",LEFT(D' + START_ROW + ',4)="4406"),0,SUM(FILTER(表_科目辅助全景查询[本期发生-借方本币],((表_科目辅助全景查询[主项目编码]=名称_项目编号)+(名称_项目编号=""))*((表_科目辅助全景查询[核算账簿]=成本表!名称_公司)+(成本表!名称_公司=""))*(表_科目辅助全景查询[科目编码]=D' + START_ROW + "),0)))";
costSheet.Range("E" + START_ROW).Formula = e13Formula; costSheet.Range("F" + START_ROW).Formula = f13Formula; console.log("已设置E" + START_ROW + "、F" + START_ROW + "单元格公式");
// 对E列起始行到F{lastRow}范围进行FillDown填充 let efFillRange = costSheet.Range("E" + START_ROW + ":F" + e13LastRow); efFillRange.FillDown(); console.log("已完成E" + START_ROW + ":F" + e13LastRow + "范围的FillDown填充"); efFillRange.Columns.Item(2).NumberFormat = '_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * "-"??_ ;_ @_ '; // 获取K列起始行的CurrentRegion范围 let kStartCurrentRegion = costSheet.Range("K" + START_ROW).CurrentRegion; let k13LastRow = kStartCurrentRegion.Rows.Count + kStartCurrentRegion.Row - 1; console.log("K" + START_ROW + "的CurrentRegion范围最后一行: " + k13LastRow);
// 设置K列起始行的公式 let k13Formula = "=IFS(LEN(J" + START_ROW + ")=4,TRUE,AND(LEN(J" + START_ROW + ")=6,COUNTIF($J$" + START_ROW + ":$J$" + k13LastRow + ",LEFT(J" + START_ROW + ',4)&"*")=1),TRUE,TRUE,FALSE)'; let l13Formula = '=SUM(FILTER(表_科目辅助全景查询[本期发生-借方本币],((表_科目辅助全景查询[主项目编码]=名称_项目编号)+(名称_项目编号=""))*((表_科目辅助全景查询[核算账簿]=成本表!名称_公司)+(成本表!名称_公司=""))*(表_科目辅助全景查询[科目编码]=J' + START_ROW + "),0))"; costSheet.Range("K" + START_ROW).Formula = k13Formula; costSheet.Range("L" + START_ROW).Formula = l13Formula; console.log("已设置K" + START_ROW + "、L" + START_ROW + "单元格公式");
// 对K列起始行到L{lastRow}范围进行FillDown填充 let klFillRange = costSheet.Range("K" + START_ROW + ":L" + k13LastRow); klFillRange.FillDown(); console.log("已完成K" + START_ROW + ":L" + k13LastRow + "范围的FillDown填充"); klFillRange.Columns.Item(2).NumberFormat = '_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * "-"??_ ;_ @_ '; // 设置L5单元格的公式，使用动态的e13LastRow和k13LastRow let l5Formula = "=SUMIF(E" + START_ROW + ":E" + e13LastRow + ",TRUE,F" + START_ROW + ":F" + e13LastRow + ")+SUMIF(K" + START_ROW + ":K" + k13LastRow + ",TRUE,L" + START_ROW + ":L" + k13LastRow + ")"; costSheet.Range("L5").Formula = l5Formula; console.log("已设置L5单元格公式: " + l5Formula);
console.log("E" + START_ROW + "、J" + START_ROW + "、K" + START_ROW + "和L5的公式添加完成"); } catch (error) { console.log("添加公式时发生错误: " + error.message); throw error; }}
/** * 处理配置表（包含项目表、公司表和子项目信息表） * 1. 从辅助余额表数据中提取第3、4列数据，去重后写入配置表的"表_项目"ListObject * 2. 处理公司表：从辅助余额表数据中提取第5列数据，去重后写入配置表的"表_公司"ListObject * 3. 处理子项目信息表：使用项目表处理后的数据 * @param {Object} actWkb - 当前活动工作簿 * @param {Array} arrValue - 辅助余额表数据数组 */function processSettingTable(actWkb, arrValue) { console.log("开始处理配置表中的项目表...");
// 定义uniqueData变量在外层作用域，供后续processSubprojectInfoTable函数使用 let uniqueData = [];
try { // 获取配置表工作表 let configSheet = actWkb.Sheets.Item("配置表"); console.log("找到'配置表'工作表");
// 从arrValue中提取第3、4列数据（JavaScript数组是0-based索引） let projectData = []; for (let i = 0; i < arrValue.length; i++) { if (arrValue[i][3] && arrValue[i][4]) { // 第3列和第4列都有值时，添加到projectData数组 projectData.push([arrValue[i][3], arrValue[i][4]]); } }
console.log("提取项目数据，共" + projectData.length + "行");
// 去重处理 let seen = new Set();
for (let i = 0; i < projectData.length; i++) { let key = projectData[i][0] + "|" + projectData[i][1]; if (!seen.has(key)) { seen.add(key); uniqueData.push(projectData[i]); } }
console.log("去重后项目数据，共" + uniqueData.length + "行");
// 查找名为"表_项目"的ListObject let projectTable = null; try { projectTable = configSheet.ListObjects.Item("表_项目"); console.log("找到'表_项目'ListObject");
// 清空整个数据区域 if (projectTable.DataBodyRange) { projectTable.DataBodyRange.Clear(); console.log("已清空'表_项目'ListObject的所有数据内容"); }
// 将ListObject调整为2行大小（保留标题行和1行数据行） let currentColumns = projectTable.Range.Columns.Count; let newRange = configSheet.Range(projectTable.Range.Cells(1, 1), projectTable.Range.Cells(2, currentColumns)); projectTable.Resize(newRange); console.log("已将'表_项目'ListObject调整为2行大小"); newRange.Columns.Item(1).EntireColumn.NumberFormat = "@"; // 将去重后的数组写入ListObject.DataRange.Cells(1)开始的位置 if (uniqueData.length > 0) { // 筛选第1列成员长度小于7的成员 let filteredUniqueData = []; for (let i = 0; i < uniqueData.length; i++) { if (uniqueData[i][0] && String(uniqueData[i][0]).length < 7) { filteredUniqueData.push(uniqueData[i]); } } console.log("筛选后项目数据（第1列长度<7），共" + filteredUniqueData.length + "行");
let targetCell = projectTable.Range.Offset(1, 0).Cells(1); // 第1个数据单元格 let targetRange = targetCell.Resize(filteredUniqueData.length, filteredUniqueData[0].length); targetRange.Value2 = filteredUniqueData; projectTable.Resize(Application.Union(projectTable.Range, targetRange.Resize(targetRange.Rows.Count, projectTable.Range.Columns.Count)));
console.log("已将去重后的项目数据写入'表_项目'ListObject，从DataRange.Cells(1)开始"); console.log("写入数据范围: " + targetRange.Address());
// 重新设置第3列的公式 // 使用ListColumn.DataBodyRange.Formula设置整列公式（FormulaArray是只读的） let savedFormula = '=[@项目编码]&"-"&[@项目名称]'; let column3Range = projectTable.ListColumns(3).DataBodyRange; if (column3Range) { column3Range.Formula = savedFormula; console.log("已重新设置第3列的公式: " + savedFormula); }
// 更新定义名称"名称_项目引用"的引用区域为projectTable第3列的数据体区域 try { let projectNameRef = actWkb.Names.Item("名称_项目引用"); projectNameRef.RefersTo = projectTable.ListColumns(3).DataBodyRange; console.log("已更新定义名称'名称_项目引用'，引用区域为: " + projectTable.ListColumns(3).DataBodyRange.Address()); } catch (nameError) { console.log("更新定义名称'名称_项目引用'时出错: " + nameError.message); } } } catch (tableError) { console.log("未找到'表_项目'ListObject: " + tableError.message); throw tableError; } } catch (error) { console.log("处理配置表项目表时发生错误: " + error.message); throw error; }
// 继续处理公司表和子项目信息表 processCompanyTable(actWkb, arrValue);
// 处理子项目信息表（使用项目表处理后的uniqueData） processSubprojectInfoTable(actWkb, uniqueData);
console.log("配置表处理完成（包含项目表、公司表和子项目信息表）");}
/** * 处理配置表中的公司表 * 从辅助余额表数据中提取第5列数据，去重后写入配置表的"表_公司"ListObject * @param {Object} actWkb - 当前活动工作簿 * @param {Array} arrValue - 辅助余额表数据数组 */function processCompanyTable(actWkb, arrValue) { console.log("开始处理配置表中的公司表...");
try { // 获取配置表工作表 let configSheet = actWkb.Sheets.Item("配置表"); console.log("找到'配置表'工作表");
// 从arrValue中提取第1列数据（JavaScript数组是0-based索引，所以是第0列） let companyData = []; for (let i = 0; i < arrValue.length; i++) { if (arrValue[i][0]) { // 第1列有值时，添加到companyData数组 companyData.push([arrValue[i][0]]); } }
console.log("提取公司数据，共" + companyData.length + "行");
// 去重处理 let uniqueData = []; let seen = new Set();
for (let i = 0; i < companyData.length; i++) { let key = companyData[i][0]; if (!seen.has(key)) { seen.add(key); uniqueData.push(companyData[i]); } }
console.log("去重后公司数据，共" + uniqueData.length + "行");
// 查找名为"表_公司"的ListObject let companyTable = null; try { companyTable = configSheet.ListObjects.Item("表_公司"); console.log("找到'表_公司'ListObject");
// 清空ListObject的数据内容 if (companyTable.DataBodyRange) { companyTable.DataBodyRange.Clear(); console.log("已清空'表_公司'ListObject的数据内容"); }
// 将ListObject调整为2行大小（保留标题行和1行数据行） let currentColumns = companyTable.Range.Columns.Count; let newRange = configSheet.Range(companyTable.Range.Cells(1, 1), companyTable.Range.Cells(2, currentColumns)); companyTable.Resize(newRange); console.log("已将'表_公司'ListObject调整为2行大小");
// 将去重后的数组写入ListObject.DataRange.Cells(1)开始的位置 if (uniqueData.length > 0) { let targetCell = companyTable.Range.Offset(1, 0).Cells(1); // 第1个数据单元格 let targetRange = targetCell.Resize(uniqueData.length, uniqueData[0].length); targetRange.Value2 = uniqueData; companyTable.Resize(Application.Union(companyTable.Range, targetRange.Resize(targetRange.Rows.Count, companyTable.Range.Columns.Count)));
console.log("已将去重后的公司数据写入'表_公司'ListObject，从DataRange.Cells(1)开始"); console.log("写入数据范围: " + targetRange.Address());
// 更新定义名称"名称_公司引用"的引用区域为companyTable第1列的数据体区域 try { let companyNameRef = actWkb.Names.Item("名称_公司引用"); companyNameRef.RefersTo = companyTable.ListColumns(1).DataBodyRange; console.log("已更新定义名称'名称_公司引用'，引用区域为: " + companyTable.ListColumns(1).DataBodyRange.Address()); } catch (nameError) { console.log("更新定义名称'名称_公司引用'时出错: " + nameError.message); } } } catch (tableError) { console.log("未找到'表_公司'ListObject: " + tableError.message);
throw tableError; } } catch (error) { console.log("处理配置表公司表时发生错误: " + error.message);
throw error; }}
/** * 处理配置表中的子项目信息表 * 从uniqueData数组中过滤出第1列长度为7位的成员，写入配置表的"表_子项目信息"ListObject * @param {Object} actWkb - 当前活动工作簿 * @param {Array} uniqueData - 已经去重的项目数据（来自processProjectTable） */function processSubprojectInfoTable(actWkb, uniqueData) { console.log("开始处理配置表中的子项目信息表...");
try { // 获取配置表工作表 let configSheet = actWkb.Sheets.Item("配置表"); console.log("找到'配置表'工作表");
// 过滤uniqueData，找出第1列以1、2或3开头的成员 let filteredData = []; const validPrefixes = ["1", "2", "3"]; // 定义有效前缀
for (let i = 0; i < uniqueData.length; i++) { if (uniqueData[i][0]) { const firstChar = String(uniqueData[i][0]).charAt(0); if (validPrefixes.includes(firstChar)) { filteredData.push(uniqueData[i]); } } }
console.log("过滤后子项目信息数据（找出第1列以1、2或3开头的成员），共" + filteredData.length + "行");
// 查找名为"表_子项目信息"的ListObject let subprojectTable = null; try { subprojectTable = configSheet.ListObjects.Item("表_子项目信息"); console.log("找到'表_子项目信息'ListObject");
// 在ListObject的Range.Offset(Range.Rows.Count, 0).Cells(1)位置赋值 let currentRows = subprojectTable.Range.Rows.Count; let targetCell = subprojectTable.Range.Offset(currentRows, 0).Cells(1);
// 将过滤后的数组写入目标位置，先resize为数组的行、列大小 if (filteredData.length > 0) { let targetRange = targetCell.Resize(filteredData.length, filteredData[0].length); targetRange.Value2 = filteredData; subprojectTable.Resize(Application.Union(subprojectTable.Range, targetRange.Resize(targetRange.Rows.Count, subprojectTable.Range.Columns.Count))); console.log("已将过滤后的子项目信息数据写入'表_子项目信息'ListObject"); console.log("写入起始位置: " + targetCell.Address()); console.log("写入数据范围: " + targetRange.Address());
// 使用WPSJS API的RemoveDuplicates方法按第1列去重 try { let beforeCount = targetRange.Rows.Count; targetRange.RemoveDuplicates([1]); // 按第1列去重 let afterCount = targetRange.Rows.Count; let removedCount = beforeCount - afterCount;
console.log("子项目信息表去重完成:"); console.log("- 去重前记录数: " + beforeCount); console.log("- 去重后记录数: " + afterCount); console.log("- 已删除重复记录: " + removedCount);
if (removedCount > 0) { console.log("子项目信息表按第1列成功去除 " + removedCount + " 条重复记录"); } else { console.log("子项目信息表未发现重复记录"); } } catch (dedupError) { console.log("使用RemoveDuplicates去重时出错: " + dedupError.message); console.log("已跳过去重步骤，保留原始数据"); } } } catch (tableError) { console.log("未找到'表_子项目信息'ListObject: " + tableError.message);
throw tableError; } } catch (error) { console.log("处理配置表子项目信息表时发生错误: " + error.message);
throw error; }}
/** * 将列号转换为列字母（如1->A，26->Z，27->AA） * @param {number} columnNumber - 列号（从1开始） * @returns {string} 列字母 */function getColumnLetter(columnNumber) { let letter = ""; while (columnNumber > 0) { columnNumber--; letter = String.fromCharCode(65 + (columnNumber % 26)) + letter; columnNumber = Math.floor(columnNumber / 26); } return letter;}
/** * 清理资源 * 关闭不再需要的工作簿等资源 * @param {Object} workbook - 需要关闭的工作簿 */function cleanupResources(workbook) { if (workbook) { try { workbook.Close(false); console.log("已关闭工作簿: " + workbook.Name); } catch (error) { console.log("关闭工作簿失败: " + error.message); } }}
// ==================== 数据处理函数 ====================
/** * 处理成本数据（基于转换脚本.js逻辑） * 过滤4401或4403开头的成本科目，按层级结构组织数据 * @param {Array} subjectData - 会计科目原始数据 * @returns {Array} 处理后的成本数据结构 */function processCostData(subjectData) { // 过滤4401或4403开头的成本科目（新格式：第1列是科目编码，第3列是会计要素名称） let costSubjects = []; for (let i = 0; i < subjectData.length; i++) { let item = subjectData[i]; let code = String(item[0]); // 第1列是科目编码 if ((code.startsWith("4401") || code.startsWith("4403") || code.startsWith("4406")) && item[2] === "成本") { // 第3列是会计要素名称 costSubjects.push(item); } }
// 按层级结构组织数据，同时记录科目编码 let structure = {}; let codeToNameMap = {}; // 用于存储科目编码到名称的映射
for (let i = 0; i < costSubjects.length; i++) { let item = costSubjects[i]; let code = String(item[0]); // 第1列是科目编码 let name = item[1]; // 第2列是科目名称
// 记录编码到名称的映射 codeToNameMap[code] = name;
// 根据编码长度判断层级 if (code.length === 4) { // 一级科目 if (!structure[code]) { structure[code] = { name: name, code: code, children: {} }; } } else if (code.length === 6) { // 二级科目 let parentCode = code.substring(0, 4); if (structure[parentCode]) { if (!structure[parentCode].children[code]) { structure[parentCode].children[code] = { name: name, code: code, children: {} }; } } } else if (code.length === 8) { // 三级科目 let parentCode = code.substring(0, 4); let subParentCode = code.substring(0, 6); if (structure[parentCode] && structure[parentCode].children[subParentCode]) { structure[parentCode].children[subParentCode].children[code] = { name: name, code: code }; } } }
// 转换为整理结构格式（包含科目编号） let result = [];
let firstLevelCodes = Object.keys(structure); for (let i = 0; i < firstLevelCodes.length; i++) { let firstLevelCode = firstLevelCodes[i]; let firstLevel = structure[firstLevelCode];
let secondLevelCodes = Object.keys(firstLevel.children); for (let j = 0; j < secondLevelCodes.length; j++) { let secondLevelCode = secondLevelCodes[j]; let secondLevel = firstLevel.children[secondLevelCode]; let thirdLevelCount = Object.keys(secondLevel.children).length;
if (thirdLevelCount === 0) { // 没有三级分类的二级分类，二级和三级都叫相同的名字 result.push([ firstLevel.name, secondLevel.name, secondLevel.name, secondLevel.code // 添加二级科目编号 ]); } else if (thirdLevelCount === 1) { // 只有一个三级分类时，不添加小计 let thirdLevelCodes = Object.keys(secondLevel.children); for (let k = 0; k < thirdLevelCodes.length; k++) { let thirdLevelCode = thirdLevelCodes[k]; let thirdLevel = secondLevel.children[thirdLevelCode]; result.push([ firstLevel.name, secondLevel.name, thirdLevel.name, thirdLevel.code // 添加三级科目编号 ]); } } else { // 有多个三级分类的，正常添加三级科目 let thirdLevelCodes = Object.keys(secondLevel.children); for (let k = 0; k < thirdLevelCodes.length; k++) { let thirdLevelCode = thirdLevelCodes[k]; let thirdLevel = secondLevel.children[thirdLevelCode]; result.push([ firstLevel.name, secondLevel.name, thirdLevel.name, thirdLevel.code // 添加三级科目编号 ]); }
// 小计时返回当前二级分类的科目编号 result.push([ firstLevel.name, secondLevel.name, "小计：", secondLevel.code // 小计时使用二级科目编号 ]); } } }
return result;}
/** * 处理损益类数据（基于转换脚本_损益类.js逻辑） * 过滤损益类科目，应用排除规则，按层级结构组织数据 * @param {Array} subjectData - 会计科目原始数据 * @returns {Array} 处理后的损益类数据结构 */function processProfitLossData(subjectData) { // 过滤损益类科目，应用排除规则 let profitLossSubjects = []; for (let i = 0; i < subjectData.length; i++) { let item = subjectData[i]; let code = String(item[0]); // 第1列是科目编码 let category = item[2]; // 第3列是会计要素名称
// 必须是损益类 if (category !== "损益") { continue; }
// 排除5001、5301开头的 if (code.startsWith("5001") || code.startsWith("5301")) { continue; }
// 5401开头的处理：包含一级科目5401，二级科目只取540102，不取540101 if (code.startsWith("5401")) { // 一级科目5401要包含 if (code === "5401") { profitLossSubjects.push(item); continue; } // 二级科目只取540102 if (code === "540102") { profitLossSubjects.push(item); } continue; }
profitLossSubjects.push(item); }
// 按层级结构组织数据（损益类只有一级和二级） let structure = {};
for (let i = 0; i < profitLossSubjects.length; i++) { let item = profitLossSubjects[i]; let code = String(item[0]); // 第1列是科目编码 let name = item[1]; // 第2列是科目名称
// 根据编码长度判断层级（损益类只有4位和6位编码） if (code.length === 4) { // 一级科目 if (!structure[code]) { structure[code] = { name: name, code: code, children: {} }; } } else if (code.length === 6) { // 二级科目 let parentCode = code.substring(0, 4); if (structure[parentCode]) { structure[parentCode].children[code] = { name: name, code: code }; } } }
// 转换为整理结构格式 let result = [];
let firstLevelCodes = Object.keys(structure); for (let i = 0; i < firstLevelCodes.length; i++) { let firstLevelCode = firstLevelCodes[i]; let firstLevel = structure[firstLevelCode]; let secondLevelCount = Object.keys(firstLevel.children).length;
if (secondLevelCount === 0) { // 没有二级科目的一级科目，直接添加本身，不添加小计 result.push([ firstLevel.name, firstLevel.name, firstLevel.code // 一级科目编码 ]); } else if (secondLevelCount === 1) { // 只有一个二级科目时，添加二级科目本身，不添加小计 let secondLevelCodes = Object.keys(firstLevel.children); for (let j = 0; j < secondLevelCodes.length; j++) { let secondLevelCode = secondLevelCodes[j]; let secondLevel = firstLevel.children[secondLevelCode]; result.push([ firstLevel.name, secondLevel.name, secondLevel.code // 二级科目编码 ]); } } else { // 有多个二级科目的，先添加所有二级科目 let secondLevelCodes = Object.keys(firstLevel.children); for (let j = 0; j < secondLevelCodes.length; j++) { let secondLevelCode = secondLevelCodes[j]; let secondLevel = firstLevel.children[secondLevelCode]; result.push([ firstLevel.name, secondLevel.name, secondLevel.code // 二级科目编码 ]); }
// 添加小计行，使用一级科目编码 result.push([ firstLevel.name, "小计：", firstLevel.code // 一级科目编码 ]); } }
return result;}
// ==================== 工具函数 ====================
/** * 合并表头单元格（第一行相同内容的单元格） * 基于转置后的数组第一行数据，找出相同内容的列并合并 * @param {Object} sheet - 工作表对象 * @param {Array} transposedArray - 转置后的数组（第一行包含要合并的表头数据） * @param {string} startCell - 起始单元格（如"E1"） */function mergeHeaderCells(sheet, transposedArray, startCell) { if (!transposedArray || transposedArray.length === 0 || !transposedArray[0]) { console.log("转置数组为空或无效，跳过表头合并"); return; }
let firstRow = transposedArray[0]; // 第一行是表头数据 let startCol = sheet.Range(startCell).Column; let currentValue = null; let mergeStartCol = 0; let mergeCount = 0;
console.log("开始合并表头单元格，从" + startCell + "开始，共" + firstRow.length + "列");
for (let i = 0; i < firstRow.length; i++) { let value = firstRow[i];
if (i === 0) { currentValue = value; mergeCount++; } else if (value !== currentValue) { // 处理前一个合并组 if (mergeCount > 1) { let mergeRange = sheet.Range(sheet.Cells(1, startCol + mergeStartCol), sheet.Cells(1, startCol + mergeStartCol + mergeCount - 1)); console.log("合并表头单元格地址：" + mergeRange.Address() + "，值：" + currentValue); mergeRange.Merge(false); }
// 开始新的合并组 currentValue = value; mergeStartCol = i; mergeCount = 1; } else { // 相同值，增加合并计数 mergeCount++; } }
// 处理最后一组合并 if (mergeCount > 1) { let mergeRange = sheet.Range(sheet.Cells(1, startCol + mergeStartCol), sheet.Cells(1, startCol + mergeStartCol + mergeCount - 1)); console.log("合并最后一组表头单元格地址：" + mergeRange.Address() + "，值：" + currentValue); mergeRange.Merge(false); }
console.log("表头单元格合并完成");}
/** * 合并相同内容的单元格 * 对指定列中连续相同内容的单元格进行合并 * @param {Object} sheet - 工作表对象 * @param {Array} dataArray - 数据数组 * @param {string} startCell - 起始单元格（如"A12"） * @param {number} columnIndex - 要合并的列索引 */function mergeIdenticalCells(sheet, dataArray, startCell, columnIndex) { let startRow = sheet.Range(startCell).Row; // 从第12行开始,不从第13行，会出现第1个合并单元格后，算offset不准 let currentValue = null; let mergeStartRow = 1; //这里从1开始，将12移到13位置 let mergeCount = 0;
for (let i = 0; i < dataArray.length; i++) { let value = dataArray[i][columnIndex];
if (i == 0) { currentValue = value; mergeCount++; } else if (value !== currentValue) { // 处理前一个合并组 if (mergeCount > 1) { let mergeRange = sheet.Range(startCell).Offset(mergeStartRow, 0).Resize(mergeCount, 1); console.log("合并单元格地址：" + mergeRange.Address()); mergeRange.Merge(false); console.log("合并单元格: " + startCell + " 行" + (startRow + mergeStartRow) + "-" + (startRow + mergeStartRow + mergeCount - 1) + " 列" + columnIndex + " 值: " + currentValue); }
// 开始新的合并组 currentValue = value; mergeStartRow = i + 1; mergeCount = 1; } else { // 相同值，增加合并计数 mergeCount++; } }
// 处理最后一组合并 if (mergeCount > 1) { let mergeRange = sheet.Range(startCell).Offset(mergeStartRow, 0).Resize(mergeCount, 1); console.log("合并单元格地址：" + mergeRange.Address()); mergeRange.Merge(false); console.log("合并最后一组单元格: " + startCell + " 行" + (startRow + mergeStartRow) + "-" + (startRow + mergeStartRow + mergeCount - 1) + " 列" + columnIndex + " 值: " + currentValue); }}
/** * 设置区域边框 * 为指定区域设置统一的边框样式 * @param {Object} rng - 要设置边框的区域对象 */function setRangeBorder(rng) { // 定义所有边框类型 const borderTypes = [xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlInsideVertical, xlInsideHorizontal];
// 设置边框样式和粗细 borderTypes.forEach((borderType) => { let border = rng.Borders.Item(borderType); border.Weight = xlThin; border.LineStyle = xlContinuous; border.ColorIndex = xlColorIndexAutomatic; border.TintAndShade = 0; });}