Last active
June 6, 2022 00:48
-
-
Save jkpieterse/81989bd686618606c7432ca2a870ec5e to your computer and use it in GitHub Desktop.
Finds all cells on a worksheet
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| name: FindItAll (5) | |
| description: Finds all cells on a worksheet | |
| host: EXCEL | |
| api_set: {} | |
| script: | |
| content: > | |
| document.addEventListener("keydown", function(event) { | |
| if (event.altKey && event.code === "KeyT") { | |
| $("#searchText").focus(); | |
| event.preventDefault(); | |
| } | |
| if (event.altKey && event.code === "KeyF") { | |
| $("#findText").click(); | |
| event.preventDefault(); | |
| } | |
| if (event.altKey && event.code === "KeyA") { | |
| $("#allSheets").focus(); | |
| event.preventDefault(); | |
| } | |
| if (event.altKey && event.code === "KeyO") { | |
| $("#matchComplete").focus(); | |
| event.preventDefault(); | |
| } | |
| if (event.altKey && event.code === "KeyC") { | |
| $("#matchCase").focus(); | |
| event.preventDefault(); | |
| } | |
| if (event.altKey && event.code === "KeyI") { | |
| $("#foundItems").focus(); | |
| $("#foundItems").val(0); | |
| event.preventDefault(); | |
| } | |
| if (event.altKey && event.code === "KeyD") { | |
| $("#foundItemsDetail").focus(); | |
| event.preventDefault(); | |
| } | |
| }); | |
| //hide the foundItemsDetail div when app loads | |
| window.onload = (event) => { | |
| $("#foundItemsDetailDiv").hide(); | |
| }; | |
| $("#findText").click(() => tryCatch(findText)); | |
| $("#searchText").on("change paste", function() { | |
| clearSelect("foundItems"); | |
| clearSelect("foundItemsDetail"); | |
| $("#foundItemsDetailDiv").hide(); | |
| }); | |
| $("#foundItems").on("keyup mouseup", function() { | |
| let index = $("#foundItems option:selected").index(); | |
| if (index !== -1) { | |
| let txt = $("#foundItems option:selected") | |
| .val() | |
| .toString(); | |
| clickItem(index, txt); | |
| } | |
| }); | |
| $("#foundItemsDetail").on("keyup mouseup", function() { | |
| let index = $("#foundItemsDetail option:selected").index(); | |
| if (index !== -1) { | |
| let txt = $("#foundItemsDetail option:selected") | |
| .val() | |
| .toString(); | |
| clickItemdetail(index, txt); | |
| } | |
| }); | |
| let foundSet; | |
| //Question: I decided to store the context variable of 'findText' here so I | |
| can use it in the two clickItem functions below, do I need to? | |
| // Jakob: Nope. You don't need the "old" context. You can and should use the | |
| new one you get by wrapping your code in Excel.run. I recommend not storing | |
| the context object. | |
| //Responds to click in found items listbox | |
| //Question: is context passed to this routine a different one than the one | |
| we got in function 'findText' below? | |
| // Jakob: Yes it is a new context object. And that is just fine since you | |
| are making a new set of requests that are independant of the previous ones. | |
| async function clickItem(index, txt: string) { | |
| await Excel.run(async (context) => { | |
| // JKP3: this fails | |
| // let sht = foundSet.getItems[index].sheet; | |
| // JKP3: This works: | |
| let sht = foundSet.getItems[index].location; | |
| // Jakob: Why not 'sheet' instead of 'sht'.. :) Feels more clear to me what the variable contains when it is called 'sheet' | |
| clearSelect("foundItemsDetail"); | |
| sht.activate(); | |
| await sht.context.sync(); | |
| $("#foundItemsDetailInfo").hide(); | |
| $("#foundItemsDetailDiv").hide(); | |
| if (foundSet.getItems[index].objOnSheet instanceof Excel.Chart) { | |
| let cht = foundSet.getItems[index].objOnSheet; | |
| cht.activate(); | |
| $("#foundItemsDetailInfo").show(); | |
| $("#foundItemsDetailInfo").html("Found in text: <p>" + foundSet.getItems[index].foundText + "</p>"); | |
| await sht.context.sync(); | |
| } else if (Instr(foundSet.getItems[index].objType, "formatCondition", true, false)) { | |
| let txt = foundSet.getItems[index].foundText; | |
| if (txt.substr(0, 1) == "=") { | |
| txt = txt.substr(1, txt.length - 1); | |
| } | |
| addItemToList(txt, "foundItemsDetail"); | |
| $("#foundItemsDetailInfo").show(); | |
| $("#foundItemsDetailInfo").html("Format Condition formula: <p>" + foundSet.getItems[index].foundText + "</p>"); | |
| let oneRng = sht.getRange(foundSet.getItems[index].objOnSheet.address); | |
| oneRng.select(); | |
| await oneRng.context.sync(); | |
| } else { | |
| $("#foundItemsDetailDiv").show(); | |
| let oneRng = foundSet.getItems[index].objOnSheet.areas.items[0]; | |
| sht.getRange(oneRng.address).select(); | |
| await oneRng.context.sync(); | |
| let items = txt.split(","); | |
| for (let i = 0; i < items.length; i++) { | |
| addItemToList(items[i], "foundItemsDetail"); | |
| } | |
| } | |
| }); | |
| } | |
| //Responds to click in detailed found items listbox | |
| async function clickItemdetail(index, txt: string) { | |
| await Excel.run(async (context) => { | |
| //JKP2: This routine no longer works, worksheet nor range gets selected | |
| let shtName = ""; | |
| if (Instr(txt, "!", false, false)) { | |
| shtName = txt.split("!")[0]; | |
| } | |
| let sht; | |
| if (shtName.length > 0) { | |
| sht = context.workbook.worksheets.getItem(shtName); | |
| } else { | |
| sht = context.workbook.worksheets.getActiveWorksheet(); | |
| } | |
| let rng = sht.getRange(txt); | |
| sht.activate(); | |
| // Jakob: Why not just write 'range'... :) | |
| // JKP: habit I suppose, I don't like variable names that resemble object or method/property names. | |
| rng.select(); | |
| await context.sync(); | |
| }); | |
| } | |
| async function findText() { | |
| await Excel.run(async (context) => { | |
| let matchComplete = $("#matchComplete").prop("checked"); | |
| let matchCase = $("#matchCase").prop("checked"); | |
| let searchAllSheets = $("#allSheets").prop("checked"); | |
| clearSelect("foundItems"); | |
| const searchString = $("#searchText") | |
| .val() | |
| .toString(); | |
| let wb = context.workbook; | |
| const selectedSheet = wb.worksheets.getActiveWorksheet(); | |
| selectedSheet.load("name"); | |
| let sheetsToSearch = wb.worksheets; | |
| sheetsToSearch.load("items,name,count"); | |
| await context.sync(); | |
| foundSet = await SearchInSheets( | |
| context, | |
| wb, | |
| searchString, | |
| selectedSheet, | |
| sheetsToSearch, | |
| matchComplete, | |
| matchCase, | |
| searchAllSheets | |
| ); | |
| if (foundSet.getLength > 0) { | |
| for (let i: number = 0; i < foundSet.getLength; i++) { | |
| let foundItem = foundSet.getItems[i]; | |
| if (foundItem) { | |
| addItemToList(foundItem.textToShow, "foundItems"); | |
| } | |
| } | |
| } | |
| }); | |
| } | |
| async function SearchInSheets( | |
| context, | |
| wb, | |
| searchString, | |
| selectedSheet, | |
| sheetsToSearch, | |
| matchComplete, | |
| matchCase, | |
| searchAllSheets | |
| ) { | |
| let foundSet = new foundCollection(); | |
| for (let i = 0; i < sheetsToSearch.items.length; i++) { | |
| let thisSheet = sheetsToSearch.items[i]; | |
| let searchThis: boolean; | |
| if (searchAllSheets) { | |
| searchThis = true; | |
| } else { | |
| searchThis = thisSheet.name == selectedSheet.name; | |
| } | |
| if (searchThis) { | |
| let foundRange = thisSheet.findAllOrNullObject(searchString, { | |
| completeMatch: matchComplete, | |
| matchCase: matchCase | |
| }); | |
| await context.sync(); | |
| if (foundRange.isNullObject) { | |
| //Do nothing | |
| } else { | |
| //Question: do I need to load 'areas' to be able to access them? This whole load thing and whether or not it is needed for object members is a bit of a mystery still. | |
| // Jakob: I don't think you need to load collections like Areas since you always need to call a method on the collection to get something out of it. | |
| foundRange.load("areaCount,address,formulasLocal,values,areas"); | |
| await context.sync(); | |
| foundSet.newFoundItem("range", "formula", foundRange.address, thisSheet, foundRange, undefined); | |
| } | |
| } | |
| let foundChartItems = await SearchInCharts(context, thisSheet, searchString, matchComplete, matchComplete); | |
| if (foundChartItems.getLength > 0) { | |
| for (let j: number = 0; j < foundChartItems.getLength; j++) { | |
| let temp = foundChartItems.getItems[j]; | |
| // Jakob: You are not using temp for anything... | |
| foundSet.newFoundItemObj(temp); | |
| } | |
| } | |
| let foundCFRuleItems = await SearchInFormatConditions( | |
| context, | |
| wb, | |
| thisSheet, | |
| searchString, | |
| matchComplete, | |
| matchComplete | |
| ); | |
| if (foundCFRuleItems.getLength > 0) { | |
| for (let j: number = 0; j < foundCFRuleItems.getLength; j++) { | |
| foundSet.newFoundItemObj(foundCFRuleItems.getItems[j]); | |
| } | |
| } | |
| let foundValidationRuleItems = await SearchInValidation( | |
| context, | |
| wb, | |
| thisSheet, | |
| searchString, | |
| matchComplete, | |
| matchComplete | |
| ); | |
| if (foundValidationRuleItems.getLength > 0) { | |
| for (let j: number = 0; j < foundValidationRuleItems.getLength; j++) { | |
| foundSet.newFoundItemObj(foundValidationRuleItems.getItems[j]); | |
| } | |
| } | |
| } | |
| return foundSet; | |
| } | |
| async function SearchInValidation( | |
| context, | |
| wb: Excel.Workbook, | |
| sht: Excel.Worksheet, | |
| searchString: string, | |
| matchComplete: boolean, | |
| matchCase: boolean | |
| ) { | |
| let valsFound = new foundCollection(); | |
| let rng = sht.getUsedRange().getSpecialCellsOrNullObject(Excel.SpecialCellType.dataValidations); | |
| rng.load("items,areaCount,address"); | |
| await context.sync(); | |
| if (!rng.isNullObject) { | |
| for (let i: number = 0; i < rng.areaCount; i++) { | |
| let thisArea = rng.areas.getItemAt(i).load("rowCount,columnCount"); | |
| await context.sync(); | |
| for (let rw: number = 0; rw < thisArea.rowCount; rw++) { | |
| for (let col: number = 0; col < thisArea.columnCount; col++) { | |
| let dVRng = thisArea.getCell(rw, col).load("address"); | |
| let dvVal = dVRng.dataValidation.load(); | |
| await context.sync(); | |
| let dvRule = dvVal.rule; | |
| // Message, title, ... | |
| let dvPromptMsg = dvVal.prompt.message; | |
| let dvPromptTitle = dvVal.prompt.title; | |
| let dvAlertMsg = dvVal.errorAlert.message; | |
| let dvAlertTitle = dvVal.errorAlert.title; | |
| if(Instr(dvPromptMsg,searchString,matchCase,matchComplete)){ | |
| valsFound.newFoundItem( | |
| "Validation prompt", | |
| dvPromptMsg, | |
| "Data Validation prompt in " + dVRng.address, | |
| sht, | |
| dVRng, | |
| dvPromptMsg | |
| ) | |
| } | |
| if (Instr(dvPromptTitle, searchString, matchCase, matchComplete)) { | |
| valsFound.newFoundItem( | |
| "Validation prompt title", | |
| dvPromptTitle, | |
| "Data Validation prompt title in " + dVRng.address, | |
| sht, | |
| dVRng, | |
| dvPromptTitle | |
| ) | |
| } | |
| if (Instr(dvAlertMsg, searchString, matchCase, matchComplete)) { | |
| valsFound.newFoundItem( | |
| "Validation alert", | |
| dvAlertMsg, | |
| "Data Validation alert in " + dVRng.address, | |
| sht, | |
| dVRng, | |
| dvAlertMsg | |
| ) | |
| } | |
| if (Instr(dvAlertTitle, searchString, matchCase, matchComplete)) { | |
| valsFound.newFoundItem( | |
| "Validation alert title", | |
| dvAlertTitle, | |
| "Data Validation alert title in " + dVRng.address, | |
| sht, | |
| dVRng, | |
| dvAlertTitle | |
| ) | |
| } | |
| // | |
| // date | |
| try { | |
| let vals = checkValidations(sht, dVRng, dvRule.date, "date", searchString, matchCase, matchComplete); | |
| if (vals.getLength > 0) { | |
| for (let j: number = 0; j < vals.getLength; j++) { | |
| valsFound.newFoundItemObj(vals.getItems[j]); | |
| } | |
| } | |
| } catch { | |
| //do nothing here | |
| } | |
| // decimal | |
| try { | |
| let vals = checkValidations(sht, dVRng, dvRule.decimal, "decimal", searchString, matchCase, matchComplete); | |
| if (vals.getLength > 0) { | |
| for (let j: number = 0; j < vals.getLength; j++) { | |
| valsFound.newFoundItemObj(vals.getItems[j]); | |
| } | |
| } | |
| } catch { | |
| //do nothing here | |
| } | |
| // textLength | |
| try { | |
| let vals = checkValidations( | |
| sht, | |
| dVRng, | |
| dvRule.textLength, | |
| "textLength", | |
| searchString, | |
| matchCase, | |
| matchComplete | |
| ); | |
| if (vals.getLength > 0) { | |
| for (let j: number = 0; j < vals.getLength; j++) { | |
| valsFound.newFoundItemObj(vals.getItems[j]); | |
| } | |
| } | |
| } catch { | |
| //do nothing here | |
| } | |
| // time | |
| try { | |
| let vals = checkValidations(sht, dVRng, dvRule.time, "time", searchString, matchCase, matchComplete); | |
| if (vals.getLength > 0) { | |
| for (let j: number = 0; j < vals.getLength; j++) { | |
| valsFound.newFoundItemObj(vals.getItems[j]); | |
| } | |
| } | |
| } catch { | |
| //do nothing here | |
| } | |
| // wholeNumber | |
| try { | |
| let vals = checkValidations( | |
| sht, | |
| dVRng, | |
| dvRule.wholeNumber, | |
| "wholeNumber", | |
| searchString, | |
| matchCase, | |
| matchComplete | |
| ); | |
| if (vals.getLength > 0) { | |
| for (let j: number = 0; j < vals.getLength; j++) { | |
| valsFound.newFoundItemObj(vals.getItems[j]); | |
| } | |
| } | |
| } catch { | |
| //do nothing here | |
| } | |
| //custom | |
| let dvFormula; | |
| try { | |
| dvFormula = dvRule.custom.formula; | |
| } catch { | |
| dvFormula = ""; | |
| } | |
| if (dvFormula.length > 0) { | |
| if (Instr(dvFormula, searchString, matchCase, matchComplete)) { | |
| valsFound.newFoundItem( | |
| "Validation formula", | |
| dvFormula, | |
| "Data Validation custom formula in " + dVRng.address, | |
| sht, | |
| dVRng, | |
| dvFormula | |
| ); | |
| } | |
| } | |
| // | |
| try { | |
| dvFormula = dvRule.list.source; | |
| } catch { | |
| dvFormula = ""; | |
| } | |
| if (dvFormula.length > 0) { | |
| if (Instr(dvFormula, searchString, matchCase, matchComplete)) { | |
| valsFound.newFoundItem( | |
| "Validation list source", | |
| dvFormula, | |
| "Data Validation list source in " + dVRng.address, | |
| sht, | |
| dVRng, | |
| dvFormula | |
| ); | |
| } | |
| } | |
| // | |
| } | |
| } | |
| } | |
| } | |
| return valsFound; | |
| } | |
| function checkValidations( | |
| sht: Excel.Worksheet, | |
| dVRng: Excel.Range, | |
| dvRuleType, | |
| ruleName: string, | |
| searchString: string, | |
| matchComplete: boolean, | |
| matchCase: boolean | |
| ) { | |
| // | |
| let dvFormula; | |
| let valsFound = new foundCollection(); | |
| try { | |
| dvFormula = dvRuleType.formula1; | |
| } catch { | |
| dvFormula = ""; | |
| } | |
| if (dvFormula.length > 0) { | |
| if (Instr(dvFormula, searchString, matchCase, matchComplete)) { | |
| valsFound.newFoundItem( | |
| "Validation formula " + ruleName + " formula 1", | |
| dvFormula, | |
| "Data Validation " + ruleName + " formula 1 in " + dVRng.address, | |
| sht, | |
| dVRng, | |
| dvFormula | |
| ); | |
| } | |
| } | |
| // | |
| try { | |
| dvFormula = dvRuleType.formula2; | |
| } catch { | |
| dvFormula = ""; | |
| } | |
| if (dvFormula.length > 0) { | |
| if (Instr(dvFormula, searchString, matchCase, matchComplete)) { | |
| valsFound.newFoundItem( | |
| "Validation formula " + ruleName + " formula 2", | |
| dvFormula, | |
| "Data Validation " + ruleName + " formula 2 in " + dVRng.address, | |
| sht, | |
| dVRng, | |
| dvFormula | |
| ); | |
| } | |
| } | |
| return valsFound; | |
| } | |
| async function SearchInFormatConditions( | |
| context, | |
| wb: Excel.Workbook, | |
| sht: Excel.Worksheet, | |
| searchString: string, | |
| matchComplete: boolean, | |
| matchCase: boolean | |
| ) { | |
| let foundRules = new foundCollection(); | |
| let rng = sht.getUsedRange(); | |
| let cfRules = rng.conditionalFormats; | |
| let cfCt = cfRules.getCount(); | |
| await context.sync(); | |
| for (let i = 0; i < cfCt.value; i++) { | |
| let cf = cfRules.getItemAt(i); | |
| let cfRange = cf.getRange().load("address"); | |
| let customcfRule = cf.customOrNullObject; | |
| let cellValueRule = cf.cellValueOrNullObject; | |
| await context.sync(); | |
| if (cellValueRule) { | |
| cellValueRule.load(); | |
| await context.sync(); | |
| let ruleFormula1; | |
| let ruleFormula2; | |
| try { | |
| ruleFormula1 = cellValueRule.rule.formula1; | |
| } catch { | |
| ruleFormula1 = ""; | |
| } | |
| try { | |
| ruleFormula2 = cellValueRule.rule.formula2; | |
| } catch { | |
| ruleFormula2 = ""; | |
| } | |
| if (ruleFormula1 + "|" + ruleFormula2 !== "|") { | |
| if (Instr(ruleFormula1, searchString, matchCase, matchComplete)) { | |
| foundRules.newFoundItem( | |
| "formatCondition formula 1", | |
| ruleFormula1, | |
| "formatCondition formula 1 in " + cfRange.address, | |
| sht, | |
| cfRange, | |
| ruleFormula1 | |
| ); | |
| } | |
| if (ruleFormula2.length > 0) { | |
| if (Instr(ruleFormula2, searchString, matchCase, matchComplete)) { | |
| foundRules.newFoundItem( | |
| "formatCondition formula 2", | |
| ruleFormula2, | |
| "formatCondition formula 2 in " + cfRange.address, | |
| sht, | |
| cfRange, | |
| ruleFormula2 | |
| ); | |
| } | |
| } | |
| //console.log("One or two formulas: " + ruleFormula1 + "|" + ruleFormula2); | |
| } | |
| if (customcfRule && ruleFormula1 + "|" + ruleFormula2 == "|") { | |
| let cfRule = customcfRule.rule; | |
| cfRule.load(); | |
| await context.sync(); | |
| if (cfRule.formula) { | |
| if (Instr(cfRule.formula, searchString, matchCase, matchComplete)) { | |
| foundRules.newFoundItem( | |
| "formatCondition formula", | |
| cfRule.formula, | |
| "formatCondition formula in " + cfRange.address, | |
| sht, | |
| cfRange, | |
| cfRule.formula | |
| ); | |
| } | |
| //console.log("custom formula: " + cfRule.formula); | |
| } else { | |
| let colorScaleCF = cf.colorScaleOrNullObject; | |
| if (colorScaleCF) { | |
| colorScaleCF.load(); | |
| await context.sync(); | |
| let max = colorScaleCF.criteria.maximum; | |
| let min = colorScaleCF.criteria.minimum; | |
| if (min.formula.length > 0) { | |
| if (Instr(min.formula, searchString, matchCase, matchComplete)) { | |
| foundRules.newFoundItem( | |
| "formatCondition min formula", | |
| min.formula, | |
| "formatCondition min formula in " + cfRange.address, | |
| sht, | |
| cfRange, | |
| min.formula | |
| ); | |
| } | |
| } | |
| if (max.formula.length > 0) { | |
| if (Instr(max.formula, searchString, matchCase, matchComplete)) { | |
| foundRules.newFoundItem( | |
| "formatCondition max formula", | |
| max.formula, | |
| "formatCondition max formula in " + cfRange.address, | |
| sht, | |
| cfRange, | |
| max.formula | |
| ); | |
| } | |
| } | |
| // console.log(min.formula + "|" + max.formula); | |
| } | |
| } | |
| } | |
| } | |
| } | |
| return foundRules; | |
| } | |
| async function SearchInCharts( | |
| context, | |
| selectedSheet: Excel.Worksheet, | |
| searchString: string, | |
| matchComplete: boolean, | |
| matchCase: boolean | |
| ) { | |
| let foundCharts = new foundCollection(); | |
| let charts = selectedSheet.charts; | |
| charts.load("count"); | |
| await context.sync(); | |
| let chtCt = charts.count; | |
| for (let i = 0; i < chtCt; i++) { | |
| let chartOnSht: Excel.Chart = selectedSheet.charts.getItemAt(i); | |
| chartOnSht.load("name,title"); | |
| chartOnSht.axes.categoryAxis.load("title"); | |
| await context.sync(); | |
| let valueAxisPri; | |
| let valueAxisSec; | |
| try { | |
| valueAxisPri = chartOnSht.axes.getItem("Value", "Primary"); | |
| valueAxisPri.title.load(); | |
| await context.sync(); | |
| } catch { | |
| valueAxisPri = null; | |
| } | |
| try { | |
| valueAxisSec = chartOnSht.axes.getItem("Value", "Secondary"); | |
| valueAxisSec.title.load(); | |
| await context.sync(); | |
| } catch { | |
| valueAxisSec = null; | |
| } | |
| if (Instr(chartOnSht.title.text, searchString, matchCase, matchComplete)) { | |
| foundCharts.newFoundItem( | |
| "chart,title", | |
| chartOnSht.title.text, | |
| selectedSheet.name + ", " + chartOnSht.name + " Title", | |
| selectedSheet, | |
| chartOnSht, | |
| chartOnSht.title | |
| ); | |
| } | |
| if (Instr(chartOnSht.axes.categoryAxis.title.text, searchString, matchCase, matchComplete)) { | |
| foundCharts.newFoundItem( | |
| "chart,categoryAxis,title", | |
| chartOnSht.axes.categoryAxis.title.text, | |
| selectedSheet.name + ", " + chartOnSht.name + ", Category Axis Title", | |
| selectedSheet, | |
| chartOnSht, | |
| chartOnSht.axes.categoryAxis.title | |
| ); | |
| } | |
| if (valueAxisPri) { | |
| if (Instr(valueAxisPri.title.text, searchString, matchCase, matchComplete)) { | |
| foundCharts.newFoundItem( | |
| "chart,valueAxisPri,title", | |
| valueAxisPri.title.text, | |
| selectedSheet.name + ", " + chartOnSht.name + ", Primary Value Axis Title", | |
| selectedSheet, | |
| chartOnSht, | |
| valueAxisPri.title | |
| ); | |
| } | |
| } | |
| if (valueAxisSec) { | |
| if (Instr(valueAxisSec.title.text, searchString, matchCase, matchComplete)) { | |
| foundCharts.newFoundItem( | |
| "chart,valueAxisSec,title", | |
| valueAxisSec.title.text, | |
| selectedSheet.name + ", " + chartOnSht.name + ", Secondary Value Axis Title", | |
| selectedSheet, | |
| chartOnSht, | |
| valueAxisSec.title | |
| ); | |
| } | |
| } | |
| } | |
| return foundCharts; | |
| } | |
| // Jakob: Instr -> inString | |
| function Instr(searchIn: string, searchFor: string, matchCase: boolean, | |
| matchComplete: boolean) { | |
| if (searchIn) { | |
| if (matchComplete) { | |
| if (matchCase) { | |
| return searchIn == searchFor; | |
| } else { | |
| return searchIn.toLocaleLowerCase() == searchFor.toLocaleLowerCase(); | |
| } | |
| } else { | |
| if (matchCase) { | |
| return searchIn.indexOf(searchFor) >= 0; | |
| } else { | |
| return searchIn.toLocaleLowerCase().indexOf(searchFor.toLocaleLowerCase()) >= 0; | |
| } | |
| } | |
| } | |
| } | |
| function addItemToList(text2add: string, listName: string) { | |
| let shortText: string; | |
| if (text2add.length > 50) { | |
| shortText = text2add.substr(0, 45) + " ..."; | |
| } else { | |
| shortText = text2add; | |
| } | |
| $("#" + listName).append( | |
| $("<option>") | |
| .val(text2add) | |
| .text(shortText) | |
| ); | |
| } | |
| function clearSelect(listName: string) { | |
| $("#" + listName).empty(); | |
| } | |
| /** Default helper for invoking an action and handling errors. */ | |
| async function tryCatch(callback) { | |
| try { | |
| await callback(); | |
| } catch (error) { | |
| // Note: In a production add-in, you'd want to notify the user through your add-in's UI. | |
| console.error(error); | |
| } | |
| } | |
| //https://www.digitalocean.com/community/tutorials/how-to-use-classes-in-typescript | |
| class foundThing { | |
| constructor( | |
| public index: number, | |
| public objType: string, | |
| public foundText: string, | |
| public textToShow: string, | |
| public location: Excel.Workbook | Excel.Worksheet, | |
| public objOnSheet: any, | |
| public subObjOfObj: any | |
| ) { | |
| //This runs every time a new instance is created | |
| } | |
| } | |
| class foundCollection { | |
| collection; | |
| index: number; | |
| constructor() { | |
| this.collection = []; | |
| this.index = 0; | |
| } | |
| newFoundItem( | |
| objType: string, | |
| foundText: string, | |
| textToShow: string, | |
| location: Excel.Workbook | Excel.Worksheet, | |
| objOnSheet: any, | |
| subObjOfObj: any | |
| ) { | |
| let p = new foundThing(this.index, objType, foundText, textToShow, location, objOnSheet, subObjOfObj); | |
| this.collection.push(p); | |
| this.index++; | |
| return p; | |
| } | |
| newFoundItemObj(obj: foundThing) { | |
| //these properties already set: textToShow, sheet, objOnSheet, subObjOfObj | |
| //Only needs an index: | |
| obj.index = this.index; | |
| this.collection.push(obj); | |
| this.index++; | |
| } | |
| get getLength() { | |
| return this.index; | |
| } | |
| get getItems() { | |
| return this.collection; | |
| } | |
| } | |
| language: typescript | |
| template: | |
| content: "<section class=\"ms-font-m\">\n\t<p>Find a matching string within a range or in objects.</p>\n</section>\n<section class=\"setup ms-font-m\">\n\t<h3>Find text</h3>\n\t<p>Enter text to search for in the box below and press <b>Find text</b> to\n\t\tdisplay where the text was found.</p>\n\t<label for=\"searchText\"><u>T</u>ext to find</label><br>\n\t<textarea id=\"searchText\">title</textarea>\n\t<p />\n\t<button id=\"findText\" class=\"ms-Button\">\n <span class=\"ms-Button-label\"><u>F</u>ind text</span>\n </button>\n\t<p />\n\t<p>Toggle the following search options to test different search behaviors.</p>\n\n\t<input type=\"checkbox\" id=\"allSheets\" name=\"allSheets\" checked=\"true\" value=\"true\">\n\t<label for=\"allSheets\"> Search <u>A</u>ll Worksheets</label><br>\n\t<input type=\"checkbox\" id=\"matchCase\" name=\"matchCase\" value=\"true\">\n\t<label for=\"matchCase\"> Match <u>C</u>ase</label><br>\n\t<input type=\"checkbox\" id=\"matchComplete\" name=\"matchComplete\" value=\"true\">\n\t<label for=\"matchComplete\"> Match C<u>o</u>mplete</label><br>\n\t<p />\n\t<div id=\"foundItemsDiv\">\n\t\t<label for=\"foundItems\">Found <u>i</u>tems</label><br>\n\t\t<select id=\"foundItems\" size=\"20\">\n\t </select></div>\n\t</div>\n\t<div id=\"foundItemsDetailDiv\"> <label for=\"foundItemsDetail\">Items <u>d</u>etail</label><br>\n\t\t<div id=\"foundItemsDetailSelect\"><select id=\"foundItemsDetail\" size=\"20\">\n\t\t\t\t </select>\n\t\t</div>\n\t</div>\n\t<div id=\"foundItemsDetailInfo\">\n\t</div>\n</section>" | |
| language: html | |
| style: | |
| content: | | |
| section.samples { | |
| margin-top: 20px; | |
| } | |
| section.samples .ms-Button, section.setup .ms-Button { | |
| display: block; | |
| margin-bottom: 5px; | |
| margin-left: 20px; | |
| min-width: 80px; | |
| } | |
| #foundItemsDiv { | |
| /*float: left; | |
| position: relative; | |
| max-width: 45%; | |
| margin-right: 10px; | |
| text-overflow: ellipsis;*/ | |
| display: inline-block; | |
| } | |
| #foundItemsDetailDiv { | |
| /*max-width: 45%;*/ | |
| display: inline-block; | |
| } | |
| #foundItemsDetailInfo { | |
| display: inline-block; | |
| position: absolute; | |
| } | |
| language: css | |
| libraries: |- | |
| https://appsforoffice.microsoft.com/lib/1/hosted/office.js | |
| @types/office-js | |
| office-ui-fabric-js@1.4.0/dist/css/fabric.min.css | |
| office-ui-fabric-js@1.4.0/dist/css/fabric.components.min.css | |
| core-js@2.4.1/client/core.min.js | |
| @types/core-js | |
| jquery@3.1.1 | |
| @types/jquery@3.3.1 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment