Created
November 20, 2023 09:37
-
-
Save MiaofeiWang/661fde09edc17921717911a5a6ab8824 to your computer and use it in GitHub Desktop.
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: BindingDataLabelsWithFormula_20231120 | |
description: '' | |
host: EXCEL | |
api_set: {} | |
script: | |
content: > | |
$("#setup").click(() => tryCatch(setup)); | |
$("#import-news").click(() => tryCatch(importNews)); | |
$("#show-data-callout").click(() => tryCatch(addAnnotationToDataPoints)); | |
$("#style-the-annotation").click(() => | |
tryCatch(changeTheStyleOfSubstrings)); | |
$("#change-annotation-to-roundRect").click(() => | |
tryCatch(changeAnnotationShapesToRoundRect)); | |
$("#change-annotation-to-any").click(() => | |
tryCatch(changeAnnotationShapesToAny)); | |
const sheetName = "Data Label APIs Demo"; | |
const annotations = [ | |
{ | |
index: 7, | |
date: "7/9/2023", | |
news: "The market was in a neutral mood on the potential growth." | |
}, | |
{ | |
index: 20, | |
date: "7/21/2023", | |
news: "A new product 'Creator' was released by the company." | |
}, | |
{ | |
index: 39, | |
date: "8/24/2023", | |
news: "'Creator' was reported to have severe security issue." | |
}, | |
{ | |
index: 89, | |
date: "9/23/2023", | |
news: "'Creator 2.0' was released with huge upgrade on performance." | |
} | |
]; | |
let NewsData = [ | |
["Date", "News"], | |
["7/9/2023\r", "The market was in a neutral mood on the potential growth."], | |
["7/21/2023\r", "A new product 'Creator' was released by the company."], | |
["8/24/2023\r", '"Creator" was reported to have severe security issue.'], | |
["9/23/2023\r", '"Creator 2.0" was released with huge upgrade on performance.'] | |
]; | |
const NewsDataRange = "H3:I7"; // TODO: Insert to the selection pos | |
async function changeAnnotationShapesToAny() { | |
await Excel.run(async (context) => { | |
let sheet = context.workbook.worksheets.getItem(sheetName); | |
let chart = sheet.charts.getItemAt(0); | |
await context.sync(); | |
let series = chart.series.getItemAt(0); | |
let label = series.points.getItemAt(annotations[0].index).dataLabel; | |
label.geometricShapeType = Excel.GeometricShapeType.snip1Rectangle; | |
label = series.points.getItemAt(annotations[1].index).dataLabel; | |
label.geometricShapeType = Excel.GeometricShapeType.snip2DiagonalRectangle; | |
label.format.fill.setSolidColor("90EE90"); | |
label = series.points.getItemAt(annotations[2].index).dataLabel; | |
label.geometricShapeType = Excel.GeometricShapeType.snip1Rectangle; | |
label.format.fill.setSolidColor("FFFFED"); | |
label = series.points.getItemAt(annotations[3].index).dataLabel; | |
label.geometricShapeType = Excel.GeometricShapeType.snip2DiagonalRectangle; | |
label.format.fill.setSolidColor("90EE90"); | |
await context.sync(); | |
}); | |
} | |
async function changeAnnotationShapesToRoundRect() { | |
await Excel.run(async (context) => { | |
let sheet = context.workbook.worksheets.getItem(sheetName); | |
let chart = sheet.charts.getItemAt(0); | |
await context.sync(); | |
let series = chart.series.getItemAt(0); | |
series.load("*"); | |
await context.sync(); | |
series.points.load("*"); | |
await context.sync(); | |
let label = series.points.getItemAt(annotations[0].index).dataLabel; | |
label.geometricShapeType = Excel.GeometricShapeType.roundRectangle; | |
label = series.points.getItemAt(annotations[1].index).dataLabel; | |
label.geometricShapeType = Excel.GeometricShapeType.roundRectangle; | |
label = series.points.getItemAt(annotations[2].index).dataLabel; | |
label.geometricShapeType = Excel.GeometricShapeType.roundRectangle; | |
label = series.points.getItemAt(annotations[3].index).dataLabel; | |
label.geometricShapeType = Excel.GeometricShapeType.roundRectangle; | |
await context.sync(); | |
}); | |
} | |
async function addAnnotationToDataPoints() { | |
await Excel.run(async (context) => { | |
let sheet = context.workbook.worksheets.getItem(sheetName); | |
let chart = sheet.charts.getItemAt(0); | |
await context.sync(); | |
let series = chart.series.getItemAt(0); | |
series.points.load("*"); | |
await context.sync(); | |
let sharedPropSet: Excel.Interfaces.ChartDataLabelUpdateData = { | |
format: { font: { size: 11 } }, | |
geometricShapeType: Excel.GeometricShapeType.rectangle, | |
horizontalAlignment: Excel.HorizontalAlignment.left | |
}; | |
let propSet: Excel.Interfaces.ChartDataLabelUpdateData[] = [ | |
{ | |
left: 50, | |
top: 70 | |
}, | |
{ | |
top: 200 | |
}, | |
{ | |
top: 50 | |
}, | |
{ | |
left: 600, | |
top: 190 | |
} | |
]; | |
let news = sheet.getRange(NewsDataRange); | |
news.load("text"); | |
await context.sync(); | |
for (let i = 0; i < annotations.length; i++) { | |
series.points.getItemAt(annotations[i].index).hasDataLabel = true; | |
await context.sync(); | |
let label = series.points.getItemAt(annotations[i].index).dataLabel; | |
label.formula = `='${sheetName}'!\$H\$${i + 4}:\$I\$${i + 4}`; | |
label.set(sharedPropSet); | |
await context.sync(); | |
label.set(propSet[i]); | |
await context.sync(); | |
} | |
await context.sync(); | |
}); | |
} | |
async function changeTheStyleOfSubstrings() { | |
await Excel.run(async (context) => { | |
let sheet = context.workbook.worksheets.getItem(sheetName); | |
let chart = sheet.charts.getItemAt(0); | |
await context.sync(); | |
let series = chart.series.getItemAt(0); | |
series.load("points"); | |
await context.sync(); | |
series.points.load("items"); | |
await context.sync(); | |
// Annotation 1 | |
let searchStr = "neutral mood"; | |
let label = series.points.getItemAt(annotations[0].index).dataLabel.load("text"); | |
await context.sync(); | |
let substrStart = label.text.indexOf(searchStr); | |
let subLabel = label.getSubstring(substrStart, searchStr.length); | |
subLabel.font.size = 13; | |
subLabel.font.bold = true; | |
// Annotation 2 | |
searchStr = "'Creator'"; | |
label = series.points.getItemAt(annotations[1].index).dataLabel.load("text"); | |
await context.sync(); | |
substrStart = label.text.indexOf(searchStr); | |
subLabel = label.getSubstring(substrStart, searchStr.length); | |
subLabel.font.name = "Calibri"; | |
subLabel.font.size = 13; | |
subLabel.font.bold = true; | |
subLabel.font.color = "blue"; | |
// Annotation 3 | |
searchStr = "severe security issue"; | |
label = series.points.getItemAt(annotations[2].index).dataLabel.load("text"); | |
await context.sync(); | |
substrStart = label.text.indexOf(searchStr); | |
subLabel = label.getSubstring(substrStart, searchStr.length); | |
subLabel.font.name = "Calibri"; | |
subLabel.font.size = 14; | |
subLabel.font.bold = true; | |
subLabel.font.color = "red"; | |
// Annotation 4 | |
searchStr = `'Creator 2.0'`; | |
label = series.points.getItemAt(annotations[3].index).dataLabel.load("text"); | |
await context.sync(); | |
substrStart = label.text.indexOf(searchStr); | |
subLabel = label.getSubstring(substrStart, searchStr.length); | |
subLabel.font.bold = true; | |
subLabel.font.size = 13; | |
searchStr = "performance"; | |
substrStart = label.text.indexOf(searchStr); | |
subLabel = label.getSubstring(substrStart, searchStr.length); | |
subLabel.font.color = "green"; | |
subLabel.font.italic = true; | |
subLabel.font.size = 13; | |
await context.sync(); | |
}); | |
} | |
async function setup() { | |
await Excel.run(async (context) => { | |
context.workbook.worksheets.getItemOrNullObject(sheetName).delete(); | |
const sheet = context.workbook.worksheets.add(sheetName); | |
let dataRange = sheet.getRange("A1:E100"); | |
dataRange.values = SampleData; | |
sheet.activate(); | |
await context.sync(); | |
let chart = sheet.charts.add(Excel.ChartType.stockOHLC, dataRange); | |
chart.setPosition("C9", "M30"); | |
chart.legend.visible = false; | |
chart.title.text = "Contoso Stock Price"; | |
chart.title.format.font.size = 20; | |
chart.axes.valueAxis.minimum = 20; | |
await context.sync(); | |
}); | |
} | |
async function importNews() { | |
await Excel.run(async (context) => { | |
const sheet = context.workbook.worksheets.getItem(sheetName); | |
let dataRange = sheet.getRange(NewsDataRange); | |
dataRange.values = NewsData; | |
dataRange.format.autofitColumns(); | |
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); | |
} | |
} | |
let SampleData = [ | |
["Contoso", "Open", "High", "Low", "Closing"], | |
["7/1/2023", 100, 112.22, 87.67, 96.71], | |
["7/2/2023", 96.71, 107.93, 92.75, 103.24], | |
["7/3/2023", 103.24, 117.82, 101.15, 109.09], | |
["7/4/2023", 109.09, 118.09, 107.22, 113.68], | |
["7/5/2023", 113.68, 120.26, 112.07, 118.68], | |
["7/6/2023", 118.68, 131.36, 110.65, 123.2], | |
["7/7/2023", 123.2, 140.54, 114.04, 135.05], | |
["7/8/2023", 135.05, 148.28, 119.38, 138.68], | |
["7/9/2023", 138.68, 140.39, 123.58, 129.63], | |
["7/10/2023", 129.63, 135.9, 125.77, 130.85], | |
["7/11/2023", 130.85, 138.1, 120.16, 135.71], | |
["7/12/2023", 135.71, 152.26, 124.35, 124.83], | |
["7/13/2023", 124.83, 131.87, 111.88, 118.94], | |
["7/14/2023", 118.94, 123.5, 112.4, 119.63], | |
["7/15/2023", 119.63, 127.79, 113.1, 127.2], | |
["7/16/2023", 127.2, 127.35, 113.37, 113.98], | |
["7/17/2023", 113.98, 124.74, 110.21, 110.32], | |
["7/18/2023", 110.32, 122.95, 109.71, 119.3], | |
["7/19/2023", 119.3, 121.09, 103.2, 120.36], | |
["7/20/2023", 120.36, 135.41, 104.44, 111.88], | |
["7/21/2023", 111.88, 121.22, 102.09, 118.88], | |
["7/22/2023", 118.88, 124.79, 109.57, 124.37], | |
["7/23/2023", 124.37, 143.02, 111.4, 119.53], | |
["7/24/2023", 119.53, 135.02, 116.04, 133.42], | |
["7/25/2023", 133.42, 141.91, 125.63, 125.67], | |
["7/26/2023", 125.67, 136.5, 107.22, 135.82], | |
["7/27/2023", 135.82, 144.97, 124.57, 137.87], | |
["7/28/2023", 137.87, 145.24, 130.18, 138.9], | |
["7/29/2023", 138.9, 146.75, 128.77, 139.36], | |
["7/30/2023", 139.36, 139.67, 125.51, 138.75], | |
["7/31/2023", 138.75, 151.17, 125.11, 145.53], | |
["8/1/2023", 145.53, 153.38, 132.82, 135.75], | |
["8/2/2023", 135.75, 144.25, 122.89, 130.4], | |
["8/3/2023", 130.4, 147.42, 116.76, 144.54], | |
["8/4/2023", 144.54, 151.53, 128.93, 133.08], | |
["8/5/2023", 133.08, 143.01, 118.07, 138.45], | |
["8/6/2023", 138.45, 157.98, 130.58, 149.12], | |
["8/7/2023", 149.12, 149.82, 145.98, 146.16], | |
["8/8/2023", 146.16, 163.54, 140.25, 150.98], | |
["8/9/2023", 150.98, 166.45, 129.61, 162.15], | |
["8/10/2023", 162.15, 181.66, 153.74, 157.47], | |
["8/11/2023", 157.47, 164.12, 134.44, 147.61], | |
["8/12/2023", 147.61, 162.32, 139.42, 149.62], | |
["8/13/2023", 149.62, 150.18, 145.75, 147.81], | |
["8/14/2023", 147.81, 151.8, 139.52, 149.31], | |
["8/15/2023", 149.31, 164.21, 141.6, 143.21], | |
["8/16/2023", 143.21, 145.12, 129.56, 138.66], | |
["8/17/2023", 138.66, 139.9, 135.58, 135.83], | |
["8/18/2023", 135.83, 137.19, 120.68, 127.99], | |
["8/19/2023", 127.99, 139.26, 118.72, 123.37], | |
["8/20/2023", 123.37, 125.14, 119.55, 120.71], | |
["8/21/2023", 120.71, 125.54, 108.7, 114.69], | |
["8/22/2023", 114.69, 128.99, 112.08, 113.82], | |
["8/23/2023", 113.82, 117.63, 110.16, 111.82], | |
["8/24/2023", 111.82, 115.83, 105.42, 110.03], | |
["8/25/2023", 110.03, 120.95, 98.98, 116.9], | |
["8/26/2023", 116.9, 131.95, 103.23, 119.56], | |
["8/27/2023", 119.56, 129.25, 113.14, 121.66], | |
["8/28/2023", 121.66, 131.51, 107.14, 127.37], | |
["8/29/2023", 127.37, 128.14, 123.21, 124.07], | |
["8/30/2023", 124.07, 135.25, 110.09, 126.09], | |
["8/31/2023", 126.09, 144.45, 116.73, 124.26], | |
["9/1/2023", 124.26, 132.16, 119.47, 122.46], | |
["9/2/2023", 122.46, 125.92, 117.83, 121.51], | |
["9/3/2023", 121.51, 127.05, 108.46, 111.92], | |
["9/4/2023", 111.92, 121.91, 104.63, 118.03], | |
["9/5/2023", 118.03, 128.29, 115.83, 119.96], | |
["9/6/2023", 119.96, 131.61, 106.96, 109.76], | |
["9/7/2023", 109.76, 115.1, 101.73, 112.86], | |
["9/8/2023", 112.86, 129.32, 102.44, 124.27], | |
["9/9/2023", 124.27, 130.47, 112.47, 119.26], | |
["9/10/2023", 119.26, 126.3, 114.16, 123.27], | |
["9/11/2023", 123.27, 139.37, 112.41, 124.8], | |
["9/12/2023", 124.8, 128.09, 107.67, 123.02], | |
["9/13/2023", 123.02, 129.24, 117, 125.32], | |
["9/14/2023", 125.32, 131.52, 114.85, 127.58], | |
["9/15/2023", 127.58, 128.35, 115.58, 120.9], | |
["9/16/2023", 120.9, 124.65, 114.84, 121.62], | |
["9/17/2023", 121.62, 135.96, 108.44, 125.65], | |
["9/18/2023", 125.65, 143.93, 116.95, 125.08], | |
["9/19/2023", 125.08, 136.62, 112.55, 122.14], | |
["9/20/2023", 122.14, 137.38, 118.23, 126.65], | |
["9/21/2023", 126.65, 139.92, 115.86, 125.71], | |
["9/22/2023", 125.71, 129.03, 123.4, 127.92], | |
["9/23/2023", 127.92, 139.17, 112.08, 115.51], | |
["9/24/2023", 115.51, 120.47, 105.8, 115.69], | |
["9/25/2023", 115.69, 120.07, 103.3, 112.48], | |
["9/26/2023", 112.48, 122.99, 98.81, 113.33], | |
["9/27/2023", 113.33, 121.37, 101.56, 114.35], | |
["9/28/2023", 114.35, 117.66, 104.92, 109.06], | |
["9/29/2023", 109.06, 123.13, 99.91, 115.2], | |
["9/30/2023", 115.2, 131.39, 110.24, 113.8], | |
["10/1/2023", 113.8, 124.68, 100.19, 120.36], | |
["10/2/2023", 120.36, 138.26, 116.56, 121.89], | |
["10/3/2023", 121.89, 136.97, 120.92, 131.86], | |
["10/4/2023", 131.86, 147.7, 122, 146.75], | |
["10/5/2023", 146.75, 162.65, 142.85, 153.55], | |
["10/6/2023", 153.55, 153.62, 140.01, 148.29], | |
["10/7/2023", 148.29, 158.98, 136.51, 144.47] | |
]; | |
language: typescript | |
template: | |
content: "<section class=\"ms-font-m\">\n\t<p>This sample shows how to insert annotations and bind the data.</p>\n</section>\n\n<section class=\"setup ms-font-m\">\n\t<h3>Set up</h3>\n\t<button id=\"setup\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Fetch data and visualize into Chart</span>\n </button>\n\t<p />\n\t<h3>Import News to Worksheet</h3>\n\t<button id=\"import-news\" class=\"ms-Button\">\n\t\t\t\t\t<span class=\"ms-Button-label\">Import news to worksheet</span>\n\t\t\t</button>\n\t<p />\n\t<h3>Add Annotations</h3>\n\t<button id=\"show-data-callout\" class=\"ms-Button\">\n\t\t\t\t<span class=\"ms-Button-label\">Add annotations</span>\n\t\t</button>\n\t<p />\n\t<!--\n\t<h3>Styling</h3>\n\t<button id=\"style-the-annotation\" class=\"ms-Button\">\n\t\t\t\t\t<span class=\"ms-Button-label\">Style annotation</span>\n\t\t\t</button>\n\t<p />\n\t<button id=\"change-annotation-to-roundRect\" class=\"ms-Button\">\n\t\t\t\t\t\t<span class=\"ms-Button-label\">change annotation to RoundRect</span>\n\t\t\t\t</button>\n\t<p />\n\n\t<button id=\"change-annotation-to-any\" class=\"ms-Button\">\n\t\t\t\t\t\t\t\t\t<span class=\"ms-Button-label\">change annotation to any</span>\n\t\t\t\t\t\t\t</button>\n\t<p />\n\t-->\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://unpkg.com/@microsoft/office-js@1.1.88-custom.18/dist/office.debug.js | |
https://unpkg.com/@microsoft/office-js@1.1.88-custom.18/dist/office.d.ts | |
//@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