115-創建工作表目錄 · WPS Office JavaScript 宏教程（JS宏）

[TOC] > Sat May 01 2021 20:55:51 GMT+0800 (GMT+08:00) 為工作表創建目錄（實質是跳轉到指定工作表的超鏈接），我們用宏的方式批量創建，并利用工作表新增、刪除、重命名事件自動更新目錄。 > 跨工作簿的鏈接無法更新，因為工作表事件僅針對當前工作簿（除非雙向部署）其實吧，目錄就是由超鏈接構成的，當工作表變多的時候定位比較困難，有個目錄就很方便了。這里先提供低級版的（占用單元格） > 可能會出加載項版的，窗體版的因 WPS JSAPI 缺少 UserForm 的說明，還不知道如何進行。 ## 低配版目錄 > 會占用單元格。后續的章節提供“中配版”——窗體目錄。為 WPS 表格工作表插入目錄注意，**目錄所在列不應再填入數據（它們會隨工作表數量變化而變化），有被覆蓋、清除的可能性。** 使用方法： * 復制當前代碼到 WPS JS宏編輯器的模塊內。 * 新建你自己的宏，然后通過 new 使用。 * 要想變成"智能"目錄，請將宏加入到Application、Workbook級別的事件內部。 > "智能"：隨工作簿工作表的增、刪、隱藏（取消隱藏）而更新目錄。 **Kind**: global class * [shSummary](#shSummary) * [new shSummary(anchor, options)](#new_shSummary_new) * [.selectRange](#shSummary+selectRange) * [.addToFirRange](#shSummary+addToFirRange) * [.sHname](#shSummary+sHname) * [.sH](#shSummary+sH) * [.wb](#shSummary+wb) * [._getWbNames()](#shSummary+_getWbNames) * [._getSheetNames(wbName, ignoreHiddenOne)](#shSummary+_getSheetNames) * [._clearRanges()](#shSummary+_clearRanges) * [.thisWorkbookSummary()](#shSummary+thisWorkbookSummary) * [.otherWorkbookSummary()](#shSummary+otherWorkbookSummary) * [.addSummary()](#shSummary+addSummary) <a name="new_shSummary_new"></a> ### new shSummary(anchor, options) | Param | Type | Description | | --- | --- | --- | | anchor | <code>string</code> | 目錄插入點，例如：'Sheet1!A1' | | options | <code>object</code> | | | options.selectRange | <code>string</code> | 跳轉到的具體單元格名，例如：'A3',即跳轉后被選中的單元格，默認每個工作表的 A1 單元格。 | | options.useNameAsLinkText | <code>boolean</code> | 僅使用工作表名稱作為鏈接文本（false則帶`selectRange`的值，true則只顯示表名），其他工作簿的鏈接始終帶有工作簿名（如果`forAllOpenedWorkbook`為 true的話）。 | | options.ignoreHiddenSheets | <code>boolean</code> | 是否忽略隱藏工作表，默認 true：忽略（因為隱藏的工作表是無法顯示的，結果是“死鏈”點擊時會提示引用無效） | | options.forAllOpenedWorkbook | <code>boolean</code> | 是否將所有的打開的工作簿都納入目錄（注意：本工作簿以外的工作簿的工作表事件無法監測，但會在生成更新本工作簿目錄時跑一遍其他工作簿）,默認 false；不加入目錄。 | **Example** ```js function _m_addSummary(){ let summary = new shSummary("Sheet1!A1",{selectRange="A1"}) // 具體配置可以自行查看文檔（都有默認值，可不配置，僅提供目錄插入點即可） summary.addSummary() // 創建目錄 } ``` <a name="shSummary+selectRange"></a> ### shSummary.selectRange 鏈接目標單元格 **Kind**: instance property of [<code>shSummary</code>](#shSummary) <a name="shSummary+addToFirRange"></a> ### shSummary.addToFirRange 插入目錄區域的第一格 **Kind**: instance property of [<code>shSummary</code>](#shSummary) <a name="shSummary+sHname"></a> ### shSummary.sHname 工作表名 **Kind**: instance property of [<code>shSummary</code>](#shSummary) <a name="shSummary+sH"></a> ### shSummary.sH 目錄所在工作表 **Kind**: instance property of [<code>shSummary</code>](#shSummary) <a name="shSummary+wb"></a> ### shSummary.wb 目錄所在工作簿 **Kind**: instance property of [<code>shSummary</code>](#shSummary) <a name="shSummary+_getWbNames"></a> ### shSummary.\_getWbNames() 獲取當前打開的所有工作簿名稱（新建未保存工作簿將忽略） **Kind**: instance method of [<code>shSummary</code>](#shSummary) <a name="shSummary+_getSheetNames"></a> ### shSummary.\_getSheetNames(wbName, ignoreHiddenOne) 獲取某一工作簿所有工作表名稱。 **Kind**: instance method of [<code>shSummary</code>](#shSummary) | Param | Type | Description | | --- | --- | --- | | wbName | <code>string</code> | 工作簿名稱 | | ignoreHiddenOne | <code>boolean</code> | 是否忽略隱藏工作表 | <a name="shSummary+_clearRanges"></a> ### shSummary.\_clearRanges() 清除目錄 **Kind**: instance method of [<code>shSummary</code>](#shSummary) <a name="shSummary+thisWorkbookSummary"></a> ### shSummary.thisWorkbookSummary() 創建本（宏所在的）工作簿的目錄 **Kind**: instance method of [<code>shSummary</code>](#shSummary) <a name="shSummary+otherWorkbookSummary"></a> ### shSummary.otherWorkbookSummary() 將其他工作簿納入目錄 **Kind**: instance method of [<code>shSummary</code>](#shSummary) <a name="shSummary+addSummary"></a> ### shSummary.addSummary() 創建目錄 **Kind**: instance method of [<code>shSummary</code>](#shSummary) ## 代碼 ```js /** * 為 WPS 表格工作表插入目錄 * * 注意，**目錄所在列不應再填入數據（它們會隨工作表數量變化而變化），有被覆蓋、清除的可能性。** * * * 使用方法： * * * 復制當前代碼到 WPS JS宏編輯器的模塊內。 * * 新建你自己的宏，然后通過 new 使用。 * * 要想變成"智能"目錄，請將宏加入到Application、Workbook級別的事件內部。 * * > "智能"：隨工作簿工作表的增、刪、隱藏（取消隱藏）而更新目錄。 * * @example * * function _m_addSummary(){ * let summary = new shSummary("Sheet1!A1",{selectRange="A1"}) // 具體配置可以自行查看文檔（都有默認值，可不配置，僅提供目錄插入點即可） * summary.addSummary() // 創建目錄 * } * * @class */ class shSummary { /** * * @param {string} anchor 目錄插入點，例如：'Sheet1!A1' * @param {object} options * @param {string} options.selectRange 跳轉到的具體單元格名，例如：'A3',即跳轉后被選中的單元格，默認每個工作表的 A1 單元格。 * @param {boolean} options.useNameAsLinkText 僅使用工作表名稱作為鏈接文本（false則帶`selectRange`的值，true則只顯示表名），其他工作簿的鏈接始終帶有工作簿名（如果`forAllOpenedWorkbook`為 true的話）。 * @param {boolean} options.ignoreHiddenSheets 是否忽略隱藏工作表，默認 true：忽略（因為隱藏的工作表是無法顯示的，結果是“死鏈”點擊時會提示引用無效） * @param {boolean} options.forAllOpenedWorkbook 是否將所有的打開的工作簿都納入目錄（注意：本工作簿以外的工作簿的工作表事件無法監測，但會在生成更新本工作簿目錄時跑一遍其他工作簿）,默認 false；不加入目錄。 */ constructor(anchor, options = { selectRange: "A1", useNameAsLinkText: true, ignoreHiddenSheets: true, forAllOpenedWorkbook: false }) { let a, b, c; if (options.useNameAsLinkText == undefined || options.useNameAsLinkText == null) { a = true } else { a = options.useNameAsLinkText } if (options.ignoreHiddenSheets == undefined || options.ignoreHiddenSheets == null) { b = true } else { b = options.ignoreHiddenSheets } if (options.forAllOpenedWorkbook == undefined || options.forAllOpenedWorkbook == null) { c = false } else { c = options.forAllOpenedWorkbook } /** 鏈接目標單元格*/ this.selectRange = options.selectRange || "A1"; // 默認為 A1 /** 插入目錄區域的第一格*/ this.addToFirRange = Range(anchor) /** 工作表名*/ this.sHname = this.addToFirRange.Parent.Name; /** 目錄所在工作表*/ this.sH = Worksheets.Item(this.sHname) this.wbName = this.sH.Parent.Name; /** 目錄所在工作簿*/ this.wb = Workbooks.Item(this.wbName) this.useNameAsLinkText = a; this.ignoreHiddenSheets = b; this.forAllOpenedWorkbook = c; /* this.useNameAsLinkText = options.useNameAsLinkText ?? true; this.ignoreHiddenSheets = options.ignoreHiddenSheets ?? true; this.forAllOpenedWorkbook = options.forAllOpenedWorkbook ?? false; */ } /** 獲取當前打開的所有工作簿名稱（新建未保存工作簿將忽略）*/ _getWbNames() { let n = []; for (let i = 1; i <= Application.Workbooks.Count; i++) { let item = Workbooks.Item(i).Name if (item !== "") { n.push(Workbooks.Item(i).Name) } } return n; } /** * 獲取某一工作簿所有工作表名稱。 * @param {string} wbName 工作簿名稱 * @param {boolean} ignoreHiddenOne 是否忽略隱藏工作表 */ _getSheetNames(wbName, ignoreHiddenOne) { let wb = wbName || this.wbName let curWb = Workbooks.Item(wb) let n = [] for (let i = 1; i <= curWb.Worksheets.Count; i++) { let item = curWb.Worksheets.Item(i) let ishidden = item.Visible let iname = item.Name if (ishidden && !ignoreHiddenOne) { n.push([wbName, iname]) } if (ishidden) { n.push([wbName, iname]) } } return n; } /** * 清除目錄 */ _clearRanges() { let summRang = this.addToFirRange; let nextRisBlank = summRang.Offset(1, 0).Value() == null; if (!nextRisBlank) { Range(summRang, summRang.End(xlDown)).Value2 = undefined; } } /** * 創建本（宏所在的）工作簿的目錄 */ thisWorkbookSummary() { let TR = this.selectRange let SR = this.addToFirRange let igHidden = this.ignoreHiddenSheets let useName = this.useNameAsLinkText this._clearRanges() // 開始寫入 let links = this._getSheetNames(this.wbName, igHidden) if (links.length == 0) { return } for (let i = 0; i < links.length; i++) { let link_text; let item = links[i][1] if (useName) { link_text = item } else { link_text = `${item}!${TR}` } let fo = { link: `${item}!${TR}`, text: link_text } let summitem = SR.Offset(i, 0) let tSh = this.sH tSh.Hyperlinks.Add(summitem, "", fo.link, "", fo.text) } } /** * 將其他工作簿納入目錄 */ otherWorkbookSummary() { if (this.forAllOpenedWorkbook == false) { return } let allWb = this._getWbNames() let fo = allWb.indexOf(this.sHname) allWb.splice(fo, 1)// 剔除當前工作簿 let allWb_sheets = [] for (let i = 1; i <= a.length; i++) { let item = a[i] let item_SheetNames = this._getSheetNames(item, this.ignoreHiddenSheets) allWb_sheets.push(item_SheetNames) } // 查找目錄末尾單元格 let lastRange = this.addToFirRange.End(xlDown) for (let p = 0; p < allWb_sheets.length; p++) { let ptem = allWb_sheets[i] // [wbname,shname] let summaryLi_text if (this.useNameAsLinkText) { summaryLi_text = ptem.join('!') } else { summaryLi_text = `${ptem.join('!')}${this.selectRange}` } let summaryLi = { text: summaryLi_text, link: `[${ptem[0]}]${ptem[1]}!${this.selectRange}` } let nextSummaryAddTo = lastRange.Item(i, 0) let summarySh = this.sH; summarySh.Hyperlinks.Add(nextSummaryAddTo, "", summaryLi.link, "", summaryLi.text) } } /** * 創建目錄 */ addSummary() { // 先添加本工作表 this.thisWorkbookSummary() // 接著補其他工作簿 this.otherWorkbookSummary() } } ```