Last active
June 13, 2023 08:19
-
-
Save MiaofeiWang/c4c91401db4f55acc8ed6cf9318def70 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: Set and Save Annotations | |
description: '' | |
host: EXCEL | |
api_set: {} | |
script: | |
content: | | |
$("#prepareChart").click(() => tryCatch(prepareChart)); | |
$("#setAnnotation").click(() => tryCatch(setAnnotation)); | |
$("#getAnnotation").click(() => tryCatch(getAnnotation)); | |
$("#clean").click(() => tryCatch(clean)); | |
const SHEET_NAME = "SampleSheet"; | |
const LINE_CHART_NAME = "SampleLineChart"; | |
const RANGE_DATA = [ | |
["Date", "Market Value"], | |
["2023/06/01", 100], | |
["2023/06/02", 105], | |
["2023/06/03", 113], | |
["2023/06/04", 103], | |
["2023/06/05", 98] | |
]; | |
const NEWS = { | |
"2023/06/01": "Some good news!", | |
"2023/06/04": "Some bad news..." | |
}; | |
async function prepareChart() { | |
await Excel.run(async (context) => { | |
let sampleSheet = context.workbook.worksheets.getItemOrNullObject(SHEET_NAME); | |
await context.sync(); | |
if (sampleSheet.isNullObject) { | |
sampleSheet = context.workbook.worksheets.add(SHEET_NAME); | |
} | |
sampleSheet.activate(); | |
let range = sampleSheet.getRange("A1:B6"); | |
range.values = RANGE_DATA; | |
let chart = sampleSheet.charts.getItemOrNullObject(LINE_CHART_NAME); | |
await context.sync(); | |
if (!chart.isNullObject) { | |
chart.delete(); | |
await context.sync(); | |
} | |
chart = sampleSheet.charts.add(Excel.ChartType.line, range); | |
chart.set({ | |
name: LINE_CHART_NAME, | |
left: 200, | |
top: 150, | |
height: 350, | |
width: 600 | |
}); | |
sampleSheet.charts.onDeleted.add((args) => removeSettings(args)); | |
chart.onActivated.add((args) => listAnnotation(args)); | |
await context.sync(); | |
}); | |
} | |
async function setAnnotation() { | |
await Excel.run(async (context) => { | |
const sheet = context.workbook.worksheets.getItemOrNullObject(SHEET_NAME); | |
let chart = sheet.charts.getItemOrNullObject(LINE_CHART_NAME); | |
let series = chart.series.getItemAt(0); | |
let points = series.points.load("name, datalabel"); | |
let categoryNames = series.getDimensionValues(Excel.ChartSeriesDimension.categories); | |
const settings = context.workbook.settings; | |
await context.sync(); | |
for (let key in NEWS) { | |
for (let i = 0; i < points.items.length; i++) { | |
let pointDate = getJSDateFromExcelDate(parseInt(categoryNames.value[i])).toLocaleDateString(); | |
//console.log(pointDate); | |
if (new Date(key).toLocaleDateString() == pointDate) { | |
let point = points.items[i]; | |
point.hasDataLabel = true; | |
point.dataLabel.text = NEWS[key]; | |
point.dataLabel.format.font.size = 13; | |
point.dataLabel.format.font.color = "red"; | |
point.dataLabel.format.border.lineStyle = Excel.ChartLineStyle.continuous; | |
point.dataLabel.format.fill.setSolidColor("white"); | |
settings.add(getSettingKey(chart.id, pointDate), key); | |
} | |
} | |
} | |
settings.load(); | |
await context.sync(); | |
console.log(settings.toJSON()); | |
}); | |
} | |
async function listAnnotation(args: Excel.ChartActivatedEventArgs) { | |
await Excel.run(async (context) => { | |
const sheet = context.workbook.worksheets.getItemOrNullObject(args.worksheetId); | |
await context.sync(); | |
if (sheet.isNullObject) { | |
console.error("ChartActivatedEventArgs doesn't have valid sheet id"); | |
return; | |
} | |
let chart = sheet.charts.getItemOrNullObject(args.chartId); | |
await context.sync(); | |
if (chart.isNullObject) { | |
console.error(`Didn't find the chart with id: ${args.chartId}`); | |
return; | |
} | |
const settings = context.workbook.settings; | |
let series = chart.series.getItemAt(0); | |
let points = series.points.load("hasDataLabel, datelabel"); | |
let categoryNames = series.getDimensionValues(Excel.ChartSeriesDimension.categories); | |
await context.sync(); | |
let annoCount = 0; | |
for (let i = 0; i < points.items.length; i++) { | |
if (points.items[i].hasDataLabel) { | |
let pointDate = getJSDateFromExcelDate(parseInt(categoryNames.value[i])).toLocaleDateString(); | |
let setting = settings.getItemOrNullObject(getSettingKey(chart.id, pointDate)).load(); | |
await context.sync(); | |
if (!setting.isNullObject) { | |
annoCount += 1; | |
console.log(`News for date ${setting.value}: ${NEWS[setting.value]}.`); | |
} | |
} | |
} | |
if (annoCount == 0) { | |
console.log("Didn't find any annotation in this chart"); | |
} else { | |
console.log(`Found ${annoCount} annotations in this chart.`); | |
} | |
}); | |
} | |
async function getAnnotation() { | |
await Excel.run(async (context) => { | |
const sheet = context.workbook.worksheets.getItemOrNullObject(SHEET_NAME); | |
const settings = context.workbook.settings; | |
let chart = sheet.charts.getItemOrNullObject(LINE_CHART_NAME); | |
let series = chart.series.getItemAt(0); | |
let points = series.points.load("hasDataLabel, datelabel"); | |
let categoryNames = series.getDimensionValues(Excel.ChartSeriesDimension.categories); | |
await context.sync(); | |
for (let i = 0; i < points.items.length; i++) { | |
if (points.items[i].hasDataLabel) { | |
let pointDate = getJSDateFromExcelDate(parseInt(categoryNames.value[i])).toLocaleDateString(); | |
let setting = settings.getItemOrNullObject(getSettingKey(chart.id, pointDate)).load(); | |
await context.sync(); | |
if (!setting.isNullObject) { | |
console.log(setting.key, setting.value); | |
} | |
} | |
} | |
}); | |
} | |
async function removeSettings(args: Excel.ChartDeletedEventArgs) { | |
await Excel.run(async (context) => { | |
let settings = context.workbook.settings.load("items"); | |
await context.sync(); | |
settings.items.forEach((item) => { | |
if (item.key.search(args.chartId) >= 0) { | |
item.delete(); | |
} | |
}); | |
}); | |
} | |
function getJSDateFromExcelDate(serialDate: number) { | |
return new Date(Math.round((serialDate - 25569) * 86400 * 1000)); | |
} | |
function getSettingKey(chartId, date) { | |
return `key_chart${chartId}_date${date}`; | |
} | |
async function clean() { | |
await Excel.run(async (context) => { | |
let sheet = context.workbook.worksheets.getItemOrNullObject(SHEET_NAME); | |
await context.sync(); | |
if (!sheet.isNullObject) { | |
sheet.delete(); | |
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: |- | |
<button id="prepareChart" class="ms-Button"> | |
<span class="ms-Button-label">Initialize data and Chart</span> | |
</button> | |
<br><br> | |
<button id="setAnnotation" class="ms-Button"> | |
<span class="ms-Button-label">Add Annotation</span> | |
</button> | |
<br><br> | |
<button id="getAnnotation" class="ms-Button"> | |
<span class="ms-Button-label">List Annotations</span> | |
</button> | |
<br><br> | |
<button id="clean" class="ms-Button"> | |
<span class="ms-Button-label">Clean sample worksheet</span> | |
</button> | |
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 | |
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