Created
June 17, 2019 18:11
-
-
Save jakobpn/02716fdc16b34e331669c96589a6089b to your computer and use it in GitHub Desktop.
Add hyperlinks to a column in a table.
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: Hyperlinks in a table | |
description: Add hyperlinks to a column in a table. | |
host: EXCEL | |
api_set: {} | |
script: | |
content: | | |
$("#setup").click(() => tryCatch(setup)); | |
$("#add-hyperlinks").click(() => tryCatch(addHyperlinks)); | |
async function addHyperlinks() { | |
await Excel.run(async (context) => { | |
const sheet = context.workbook.worksheets.getItem("Sample"); | |
const table = sheet.tables.getItem("Expenses"); | |
const merchants = table.getDataBodyRange().getColumn(1).load('values'); | |
const links = table.getDataBodyRange().getColumn(2).load('values'); | |
await context.sync(); | |
for (var row = 0; row < merchants.values.length; row++) { | |
merchants.getCell(row, 0).hyperlink = { | |
textToDisplay: merchants.values[row][0], | |
address: links.values[row][0] | |
}; | |
} | |
await context.sync(); | |
}); | |
} | |
async function setup() { | |
await Excel.run(async (context) => { | |
await OfficeHelpers.ExcelUtilities.forceCreateSheet(context.workbook, "Sample"); | |
const sheet = context.workbook.worksheets.getItem("Sample"); | |
const expensesTable = sheet.tables.add("A1:E1", true /*hasHeaders*/); | |
expensesTable.name = "Expenses"; | |
expensesTable.getHeaderRowRange().values = [["Date", "Merchant", "Link", "Category", "Amount"]]; | |
expensesTable.rows.add(null /*add at the end*/, [ | |
["1/1/2017", "The Phone Company", "http://www.thephonecompany.com", "Communications", "$120"], | |
["1/2/2017", "Northwind Electric Cars", "http://www.northwindelectriccars.com", "Transportation", "$142"], | |
["1/5/2017", "Best For You Organics Company", "http://bestorganics.com", "Groceries", "$27"], | |
["1/10/2017", "Coho Vineyard", "http://www.cohovineyard.com", "Restaurant", "$33"], | |
["1/11/2017", "Bellows College", "http://bellowscollege.edu", "Education", "$350"], | |
["1/15/2017", "Trey Research", "http://treyresearch.com", "Other", "$135"], | |
["1/15/2017", "Best For You Organics Company", "http://bestorganics.com", "Groceries", "$97"] | |
]); | |
if (Office.context.requirements.isSetSupported("ExcelApi", 1.2)) { | |
sheet.getUsedRange().format.autofitColumns(); | |
sheet.getUsedRange().format.autofitRows(); | |
} | |
sheet.activate(); | |
await context.sync(); | |
}); | |
} | |
/** 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); | |
} | |
} | |
language: typescript | |
template: | |
content: "<section class=\"ms-font-m\">\n\t<p>This sample shows how to dd hyperlinks to a column in a table.</p>\n</section>\n\n<section class=\"samples ms-font-m\">\n\t<h3>Setup</h3>\n\t<button id=\"setup\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Create table</span>\n </button>\n</section>\n\n<section class=\"samples ms-font-m\">\n\t<h3>Try it out</h3>\n\t<button id=\"add-hyperlinks\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Add hyperlinks</span>\n </button>\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; | |
} | |
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 | |
@microsoft/office-js-helpers@0.7.4/dist/office.helpers.min.js | |
@microsoft/office-js-helpers@0.7.4/dist/office.helpers.d.ts | |
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