Last active
May 7, 2018 07:44
-
-
Save jipyua/d4393b0d95eacacccbb234ab844fc2d9 to your computer and use it in GitHub Desktop.
Shared with 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: Add & hide worksheet | |
description: '' | |
author: jipyua | |
host: EXCEL | |
api_set: {} | |
script: | |
content: | | |
$("#add-worksheet").click(addWorksheet); | |
async function addWorksheet() { | |
try { | |
await Excel.run(async (context) => { | |
const sheets = context.workbook.worksheets; | |
const sheet = sheets.add(); | |
sheet.load("name, position"); | |
await context.sync(); | |
sheet.visibility = Excel.SheetVisibility.hidden; | |
await context.sync(); | |
console.log(`Added worksheet named "${sheet.name}" in position ${sheet.position}`) | |
}); | |
} | |
catch (error) { | |
OfficeHelpers.UI.notify(error); | |
OfficeHelpers.Utilities.log(error); | |
} | |
} | |
async function deleteWorksheet() { | |
try { | |
await Excel.run(async (context) => { | |
const sheets = context.workbook.worksheets; | |
sheets.load("items/name"); | |
await context.sync(); | |
if (sheets.items.length > 1) { | |
const lastSheet = sheets.items[sheets.items.length - 1]; | |
console.log(`Deleting worksheet named "${lastSheet.name}"`); | |
lastSheet.delete(); | |
await context.sync(); | |
} else { | |
console.log("Unable to delete the last worksheet in the workbook"); | |
} | |
}); | |
} | |
catch (error) { | |
OfficeHelpers.UI.notify(error); | |
OfficeHelpers.Utilities.log(error); | |
} | |
} | |
async function renameWorksheet() { | |
try { | |
await Excel.run(async (context) => { | |
const currentSheet = context.workbook.worksheets.getActiveWorksheet(); | |
currentSheet.name = await uniqueWorksheetName(context); | |
await context.sync(); | |
}); | |
} | |
catch (error) { | |
OfficeHelpers.UI.notify(error); | |
OfficeHelpers.Utilities.log(error); | |
} | |
} | |
async function uniqueWorksheetName(context: Excel.RequestContext) { | |
let number = 1; | |
let name: string; | |
while (true) { | |
name = `Renamed${number}`; | |
try { | |
const sheet = context.workbook.worksheets.getItem(name); | |
await context.sync(); | |
++number; | |
} | |
catch (e) { | |
break; | |
} | |
} | |
return name; | |
} | |
async function moveWorksheet() { | |
try { | |
await Excel.run(async (context) => { | |
const sheets = context.workbook.worksheets; | |
sheets.load("items"); | |
await context.sync(); | |
const lastSheet = sheets.items[sheets.items.length - 1]; | |
lastSheet.position = 0; | |
await context.sync(); | |
}); | |
} | |
catch (error) { | |
OfficeHelpers.UI.notify(error); | |
OfficeHelpers.Utilities.log(error); | |
} | |
} | |
language: typescript | |
template: | |
content: | | |
<section class="ms-font-m"> | |
<p>This sample shows how to add, delete, rename and change the position of a worksheet using the Excel API.</p> | |
</section> | |
<section class="samples ms-font-m"> | |
<h3>Try it out</h3> | |
<button id="add-worksheet" class="ms-Button"> | |
<span class="ms-Button-label">Add & Hide worksheet</span> | |
</button> | |
</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: | | |
# Office.js | |
https://appsforoffice.microsoft.com/lib/1/hosted/office.js | |
# CSS Libraries | |
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 | |
# NPM libraries | |
core-js@2.4.1/client/core.min.js | |
@microsoft/office-js-helpers@0.7.4/dist/office.helpers.min.js | |
jquery@3.1.1 | |
# IntelliSense: @types/library or node_modules paths or URL to d.ts files | |
@types/office-js | |
@types/core-js | |
@microsoft/office-js-helpers@0.7.4/dist/office.helpers.d.ts | |
@types/jquery |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment