Created
February 13, 2023 12:32
-
-
Save skysan87/c320d318112819b58661ec1ffde2e1b5 to your computer and use it in GitHub Desktop.
[Excel][Script Lab] 選択された行の値を列を表示するスニペット
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: show-row | |
description: 選択された行の値を列を表示 | |
host: EXCEL | |
api_set: {} | |
script: | |
content: | | |
$("#run").click(() => tryCatch(run)); | |
const rowName = [ | |
"A", | |
"B", | |
"C", | |
"D", | |
"E", | |
"F", | |
"G", | |
"H", | |
"I", | |
"J", | |
"K", | |
"L", | |
"M", | |
"N", | |
"O", | |
"P", | |
"Q", | |
"R", | |
"S", | |
"T", | |
"U", | |
"V", | |
"W", | |
"X", | |
"Y", | |
"Z" | |
]; | |
async function run() { | |
await Excel.run(async (context) => { | |
// NOTE: 行や列の選択ではnullが返ってくる | |
// let range = context.workbook.getSelectedRange(); | |
let selectedRange = context.workbook.getSelectedRange(); | |
selectedRange.load("address"); | |
await context.sync(); | |
const regexpSize = /![A-Z]*([0-9]+):?/; | |
const match = selectedRange.address.match(regexpSize); | |
let rowIndex = match[1]; | |
if (!rowIndex || rowIndex === "") return; | |
let sheet = context.workbook.worksheets.getActiveWorksheet(); | |
// とりあえずZ列で固定 | |
let range = sheet.getRange(`A${rowIndex}:Z${rowIndex}`); | |
range.load("values"); | |
await context.sync(); | |
const $list = $("#list"); | |
$list.empty(); | |
// NOTE: 日付対応 | |
range.values[0].forEach((value, index) => { | |
$list.append(`<tr><td>${rowName[index]}</td><td>${value}</td></tr>`); | |
}); | |
}); | |
} | |
/** 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: "<button id=\"run\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">表示</span>\n</button>\n<span>任意のセルを選択する(Z列まで対応)</span>\n<div>\n\t<table>\n\t\t<tbody id=\"list\">\n\t\t</tbody>\n\t</table>\n</div>" | |
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; | |
} | |
td { | |
border: 1px solid black; | |
} | |
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