Create your first Microsoft Office add-in with the Excel JavaScript APIs
Objective
Scenario
Virtual Machines
- QSC 1511 Master
Exercise 1 : Create a basic add-in
In this exercise, you will build an Expense Report with the new Excel JavaScript APIs in Office 2016.
- Launch Visual Studio 2015 as administratorShift Right click Visual Studio 2015 on the task bar and select Run as Administrator.
- Create a new projectFrom the File menu select the New Project command. When the New Project dialog appears, select the Excel Add-in project template from the Office/SharePoint template folder, in Templates > Visual C# > Office/SharePoint > Web Add-ins
Name the new project ExpenseReport and click OK to create the new project. - Complete the creation with defaultsWhen you create a new Office Add-ins project, Visual Studio prompts you with the Choose the add-in type page of the Create Office Add-in dialog. This is the point where you select the type of add-in you want to create. Leave the default setting with the radio button titled Add new functionalities to Excel and select Finish to continue.
- Open the new project xmlOpen ExpenseReport.xml.
Examine the contents of the file
Now, find the XML block that looks like this. Take a minute and read through it as it describes how add-ins can integrate with the Office UI.
The example below demonstrates how an add-in can add a button to the Excel ribbon's Home tab.
Modify the button
Let's modify the button to say "Expense Report" instead of "Show Taskpane". Find the following element in the file.
</div>
<div class="detailsBox taskIdea" style="padding: 12px 12px 12px 44px; background-color: rgb(238, 237, 237); margin: 5px 0px; background-size: 32px; background-position: 8px 5px; background-repeat: no-repeat; background-image: url("/images/Knowledge.png");">
This indicates that the label of the title is stored in a string resource named Contoso.TaskpaneButton.Label.</div>
</li>
<li style="padding: 5px;"><div>
<strong>Find the ShortString resource</strong></div>
<div class="taskText" style="margin: 7px 0px;">
Scroll down until you find the ShortString string resource with the label - Contoso.TaskpaneButton.Label.<br></div>
</li>
<li style="padding: 5px;"><div>
<strong>Set the DefaultValue</strong></div>
<div class="taskText" style="margin: 7px 0px;">
Now, set the DefaultValue attribute to Expense Report. Your XML should look like this: <br><bt:String id="Contoso.TaskpaneButton.Label" DefaultValue="Expense Report" /><br></div>
</li>
<li style="padding: 5px;"><div>
<strong>Test the changes</strong></div>
<div class="taskText" style="margin: 7px 0px;">
Press F5 to try your changes. You should see you add-in deploy in Excel and a button appear on the Home Tab. <br></div>
<div class="detailsBox taskIdea" style="padding: 12px 12px 12px 44px; background-color: rgb(238, 237, 237); margin: 5px 0px; background-size: 32px; background-position: 8px 5px; background-repeat: no-repeat; background-image: url("/images/Knowledge.png");">
Don't worry if you don't see the label updated, this is a known issue with this build.<br></div>
</li>
</ol>
<p style="color: rgb(44, 44, 48); font-family: "Segoe UI", Tahoma, Verdana, Arial, sans-serif; font-size: 12.8px;">
<i></i></p>
<h2 style="font-size: 15px; color: rgb(27, 47, 92); font-family: "Segoe UI", Tahoma, Verdana, Arial, sans-serif;">
Exercise 2 : Add Excel JavaScript API code</h2>
<p style="color: rgb(44, 44, 48); font-family: "Segoe UI", Tahoma, Verdana, Arial, sans-serif; font-size: 12.8px;">
<i>In this exercise, you will Add Excel JavaScript API code.</i></p>
<ol class="taskList" style="margin-top: 5px; margin-bottom: 5px; color: rgb(44, 44, 48); font-family: "Segoe UI", Tahoma, Verdana, Arial, sans-serif; font-size: 12.8px;">
<li style="padding: 5px;"><div>
<strong>Open the add-in homepage</strong></div>
<div class="taskText" style="margin: 7px 0px;">
Locate and open the homepage for the add-in: Home.html.<br></div>
</li>
<li style="padding: 5px;"><div>
<strong>Add a script reference</strong></div>
<div class="taskText" style="margin: 7px 0px;">
Immediately after the reference to Office.js in the <head> portion of the page, add the following script reference to the Excel JavaScript API beta CDN:<br><script src="https://appsforoffice.microsoft.com/lib/beta/hosted/office.js"></script><br></div>
<div class="detailsBox taskIdea" style="padding: 12px 12px 12px 44px; background-color: rgb(238, 237, 237); margin: 5px 0px; background-size: 32px; background-position: 8px 5px; background-repeat: no-repeat; background-image: url("/images/Knowledge.png");">
Note: Eventually the all Excel JavaScript APIs will be merged into the single Office.js file so this step will not be necessary, but in the 1.2 API preview timeframe it is required as an extra step.<br></div>
</li>
<li style="padding: 5px;"><div>
<strong>Update the UI for the add-in</strong></div>
<div class="taskText" style="margin: 7px 0px;">
Locate the <body> section of the page within the home.html file.<br>Replace the contents of the <body> with the following markup (copy from below and paste):<br><div id="content-main">
<br><div class="padding">
<br><br /><br>vi. <br><button class="ms-Button ms-Button--primary" id="insertData"><br><span class="ms-Button-icon"><i class="ms-Icon ms-Icon--plus"></i></span><br><span class="ms-Button-label" id="button-text">Insert Data</span><br><span class="ms-Button-description" id="button-desc">Inseart Data, add a table and adjust layout</span><br></button><br><br /><br><br /><br><button class="ms-Button ms-Button--primary" id="sort"><br><span class="ms-Button-icon"><i class="ms-Icon ms-Icon--plus"></i></span><br><span class="ms-Button-label" id="button-text">Sort</span><br><span class="ms-Button-description" id="button-desc">Sort my data based on transaction date</span><br></button><br><br /><br><br /><br><button class="ms-Button ms-Button--primary" id="filter"><br><span class="ms-Button-icon"><i class="ms-Icon ms-Icon--plus"></i></span><br><span class="ms-Button-label" id="button-text">Filter</span><br><span class="ms-Button-description" id="button-desc">Only show my transtions in fuel and education</span><br></button><br><br /><br><br /><br><button class="ms-Button ms-Button--primary" id="report"><br><span class="ms-Button-icon"><i class="ms-Icon ms-Icon--plus"></i></span><br><span class="ms-Button-label" id="button-text">Report</span><br><span class="ms-Button-description" id="button-desc">Create a report on my spending and Protect the report </span><br></button><br></div>
<br></div>
<br></div>
</li>
<li style="padding: 5px;"><div>
<strong>Add the Business Logic</strong></div>
<div class="taskText" style="margin: 7px 0px;">
The next step is to code the business logic for the add-in.<br>Locate the Home.js file.<br>Remove all the sample code except the add-in initialization code so all that is left is the following:<br>(function () {<br>"use strict";<br>// The initialize function must be run each time a new page is loaded.<br>Office.initialize = function (reason) {<br>$(document).ready(function () {<br>// Initialize the FabricUI notification mechanism and hide it<br>var element = document.querySelector('.ms-MessageBanner');<br>messageBanner = new fabric.MessageBanner(element);<br>messageBanner.hideBanner();<br><br>// attach click handlers to the workbook<br>// TODO-1<br><br>// TODO-2<br><br>// TODO-3<br><br>// TODO-4<br>return;<br><br>});<br>}<br>// Helper function for treating errors<br>function errorHandler(error) {<br>// Always be sure to catch any accumulated errors that bubble up from the Excel.run execution<br>showNotification("Error", error);<br>console.log("Error: " + error);<br>if (error instanceof OfficeExtension.Error) {<br>console.log("Debug info: " + JSON.stringify(error.debugInfo));<br>}<br>}<br><br>// Helper function for displaying notifications<br><br>function showNotification(header, content) {<br>$("#notificationHeader").text(header);<br>$("#notificationBody").text(content);<br>messageBanner.showBanner();<br>messageBanner.toggleExpansion();<br>}<br>})();<br></div>
</li>
<li style="padding: 5px;"><div>
<strong>Add a function that will add data</strong></div>
<div class="taskText" style="margin: 7px 0px;">
Replace the comment // TODO-1 with the following jQuery code that creates a click event handler on one of the buttons in the home.html page you added previously:<br>$('#insertData').click(insertData);<br></div>
</li>
<li style="padding: 5px;"><div>
<strong>Add a function</strong></div>
<div class="taskText" style="margin: 7px 0px;">
Add the following function (copy from below and paste).<br>Notice how the code in this function is very different from the code in the previous exercises. The Excel JavaScript API uses a context (Excel.run()) to allow you to batch multiple operations (such as context.workbook.worksheets.add()) that will be sent to the hosting Excel client application for processing at one time using the context.sync() method:<br>function insertData() {<br>Excel.run(function (ctx) {<br><br>// Get the current worksheet<br>var sheet = ctx.workbook.worksheets.getActiveWorksheet();<br><br>//Rename the current Worsheet to Data<br>sheet.name = "Data";<br><br>//Insert Data<br>var range = sheet.getRange("A1:E11");<br>range.values = [[<br>"Date",<br>"Merchant",<br>"Category",<br>"Sub-Category",<br>"Amount"],<br>[<br>"01/12/2014",<br>"WHOLE FOODS MARKET",<br>"Merchandise & Supplies",<br>"Groceries",<br>"84.99"<br>],<br>[<br>"01/13/2014",<br>"COSTCO GAS",<br>"Transportation",<br>"Fuel",<br>"52.20"<br>],<br>[<br>"01/13/2014",<br>"COSTCO WHOLESALE",<br>"Merchandise & Supplies",<br>"Wholesale Stores",<br>"163.67"<br>],<br>[<br>"01/13/2014",<br>"ITUNES",<br>"Merchandise & Supplies",<br>"Internet Purchase",<br>"9.83"<br>],<br>[<br>"01/13/2014",<br>"SMITH BROTHERS FARMS INC",<br>"Merchandise & Supplies",<br>"Groceries",<br>"21.45"<br>],<br>[<br>"01/14/2014",<br>"SHELL",<br>"Transportation",<br>"Fuel",<br>"44.00"<br>],<br>[<br>"01/14/2014",<br>"WHOLE FOODS MARKET",<br>"Merchandise & Supplies",<br>"Groceries",<br>"17.98"<br>],<br>[<br>"01/15/2014",<br>"BRIGHT EDUCATION SERVICES",<br>"Other",<br>"Education",<br>"59.92"<br>],<br>[<br>"01/15/2014",<br>"BRIGHT EDUCATION SERVICES",<br>"Other",<br>"Education",<br>"59.92"<br>],<br>[<br>"01/17/2014",<br>"SMITH BROTHERS FARMS INC-HQ",<br>"Merchandise & Supplies",<br>"Groceries",<br>"21.45"<br>]];<br><br>//Autofit row height and column width<br>range.getEntireColumn().format.autofitColumns();<br>range.getEntireRow().format.autofitRows();<br><br>// Add a table<br>var table = ctx.workbook.tables.add("Data!A1:E11", true);<br>return ctx.sync().then(function () {<br>});<br>}).catch(function (error) {<br>console.log("Error: " + error);<br>if (error instanceof OfficeExtension.Error) {<br>console.log("Debug info: " + JSON.stringify(error.debugInfo));<br>}<br>});<br>}<br></div>
</li>
<li style="padding: 5px;"><div>
<strong>Add a sort function</strong></div>
<div class="taskText" style="margin: 7px 0px;">
Now add functionality to sort data based on transaction date:<br>Go back to the Office.initialize statement and replace the comment // TODO-2 with the following jQuery code that creates a click handler for the button that will add a range of unformatted data to the current worksheet:<br>$('#sort').click(sort);<br></div>
</li>
<li style="padding: 5px;"><div>
<strong>Add another function.</strong></div>
<div class="taskText" style="margin: 7px 0px;">
Copy and paste the following into the statement<br>function sort() {<br>Excel.run(function (ctx) {<br>var sheet = ctx.workbook.worksheets.getActiveWorksheet();<br><br>// Only Sort the range that has data<br>var sortRange = sheet.getRange("A1:E1").getEntireColumn().getUsedRange();<br>// Apply sorting on the first column and in descending order<br>sortRange.sort.apply([<br>{<br>key: 0,<br>ascending: false,<br>},<br>]);<br>return ctx.sync().then(function () {<br>})<br>}).catch(function (error) {<br>console.log("Error: " + error);<br>if (error instanceof OfficeExtension.Error) {<br>console.log("Debug info: " + JSON.stringify(error.debugInfo));<br>}<br>});<br>}<br></div>
</li>
<li style="padding: 5px;"><div>
<strong>Add a filter function</strong></div>
<div class="taskText" style="margin: 7px 0px;">
Then we add functionality to filter data. We only want to focus on the transaction in the sub-category of Fuel and Education.<br>Go back to the Office.initialize statement and replace the comment // TODO-3 with the following jQuery code that creates a click handler for the button that will add a range of unformatted data to the current worksheet: (copy and paste the filter into place)<br>$('#filter').click(filter);<br></div>
</li>
<li style="padding: 5px;"><div>
<strong>Add another function</strong></div>
<div class="taskText" style="margin: 7px 0px;">
Next, add the following function.<br>Notice how the code works with ranges in a similar way to the last function, but this one assigns some formats to the range's numberFormats property (copy and paste from below):<br>function filter() {<br>Excel.run(function (ctx) {<br>var sheet = ctx.workbook.worksheets.getActiveWorksheet();<br>var table = sheet.tables.getItemAt(0);<br><br>//Apply a value filter on the 4th column, which is sub-category. We want to focus on transactions in the category of Fuel and Education<br>var filter = table.columns.getItemAt(3).filter;<br>filter.applyValuesFilter(["Fuel","Education"]);<br>return ctx.sync().then(function () {<br>})<br>}).catch(function (error) {<br>console.log("Error: " + error);<br>if (error instanceof OfficeExtension.Error) {<br>console.log("Debug info: " + JSON.stringify(error.debugInfo));<br>}<br>});<br>}<br></div>
</li>
<li style="padding: 5px;"><div>
<strong>Add the ability to use Excel formulas and charts</strong></div>
<div class="taskText" style="margin: 7px 0px;">
Now, we add functionality to use Excel formulas and charting to generate a report and then protect the report from editing. Here we first create a new sheet named "Summary", then create a summary table with total spendings based on Category. We then add a chart to visualize the data. Finally we protect the sheet from further changes.<br>Go back to the Office.initialize statement and replace the comment // TODO-4 with the following jQuery code that creates a click handler for the button that will add a range of unformatted data to the current worksheet (copy and paste from below):<br>$('#report').click(report);<br></div>
</li>
<li style="padding: 5px;"><div>
<strong>Add a report function</strong></div>
<div class="taskText" style="margin: 7px 0px;">
Notice how the code works with ranges in a similar way to the last function, but this one assigns some formats to the range's numberFormats property (copy and paste from below):<br>function report() {<br>Excel.run(function (ctx) {<br>//Add a new worksheet<br>var sheet = ctx.workbook.worksheets.add("Summary");<br>//Activate the worksheet<br>sheet.activate();<br><br>// Use Excel formulas to calculate the total spending based on categories<br>var sumRange = sheet.getRange("A1:B6");<br>sumRange.values = [['Category', 'Total'],<br>['Groceries', '=SUMIF( Data!D2:D100, "Groceries", Data!E2:E100 )'],<br>['Fuel', '=SUMIF( Data!D2:D100, "Fuel", Data!E2:E100 )'],<br>['Wholesale Store', '=SUMIF( Data!D2:D100, "Wholesale Stores", Data!E2:E100 )'],<br>['Internet Purchase', '=SUMIF( Data!D2:D100, "Internet Purchase", Data!E2:E100 )'],<br>['Education', '=SUMIF( Data!D2:D100, "Education", Data!E2:E100 )']];<br><br>//Add a Table<br>ctx.workbook.tables.add("Summary!A1:B6", true);<br><br>// Add a pie chart<br>var chartRange = sheet.getRange("A1:B6");<br>var chart = ctx.workbook.worksheets.getItem("Summary").charts.add("Pie", chartRange);<br><br>//Update the chart title<br>chart.title.text = "Spending based on catagory";<br><br>// Protect the report from editing<br>sheet.protection.protect();<br><br>return ctx.sync().then(function () {<br><br>})<br>.then(ctx.sync);<br>}).catch(function (error) {<br>console.log("Error: " + error);<br>if (error instanceof OfficeExtension.Error) {<br>console.log("Debug info: " + JSON.stringify(error.debugInfo));<br>}<br>});<br>}<br></div>
</li>
</ol>
<p style="color: rgb(44, 44, 48); font-family: "Segoe UI", Tahoma, Verdana, Arial, sans-serif; font-size: 12.8px;">
<i></i></p>
<h2 style="font-size: 15px; color: rgb(27, 47, 92); font-family: "Segoe UI", Tahoma, Verdana, Arial, sans-serif;">
Exercise 3 : Test the Add-in end to end</h2>
<p style="color: rgb(44, 44, 48); font-family: "Segoe UI", Tahoma, Verdana, Arial, sans-serif; font-size: 12.8px;">
<i>In this exercise, you will test the Add-in end to end.</i></p>
<ol class="taskList" style="margin-top: 5px; margin-bottom: 5px; color: rgb(44, 44, 48); font-family: "Segoe UI", Tahoma, Verdana, Arial, sans-serif; font-size: 12.8px;">
<li style="padding: 5px;"><div>
<strong>Deploy the Excel add-in to the local Excel client</strong></div>
<div class="taskText" style="margin: 7px 0px;">
Select the ExpenseReport project within the Solution Explorer tool window.<br>Within the Properties window, set the Start Action selector to Office Desktop Client and press F5 to start the project.<br></div>
<div class="detailsBox taskIdea" style="padding: 12px 12px 12px 44px; background-color: rgb(238, 237, 237); margin: 5px 0px; background-size: 32px; background-position: 8px 5px; background-repeat: no-repeat; background-image: url("/images/Knowledge.png");">
Visual Studio will launch the Excel desktop client and create a new Excel workbook. <br></div>
</li>
<li style="padding: 5px;"><div>
<strong>Launch the add-in</strong></div>
<div class="taskText" style="margin: 7px 0px;">
Launch your add-in by clicking the Show Taskpane button on the Ribbon. This button is defined in your add-in manifest. You can change the label if you want by edditing the manifest.xml file in your project. <br></div>
</li>
<li style="padding: 5px;"><div>
<strong>Click the Insert Data button</strong></div>
<div class="taskText" style="margin: 7px 0px;">
On the taskpane, click the button Insert Data. <br></div>
<div class="detailsBox taskIdea" style="padding: 12px 12px 12px 44px; background-color: rgb(238, 237, 237); margin: 5px 0px; background-size: 32px; background-position: 8px 5px; background-repeat: no-repeat; background-image: url("/images/Knowledge.png");">
Notice how Excel update the worksheet name to "Data", inserted my transaction data and added a table on top of my data.<br></div>
</li>
<li style="padding: 5px;"><div>
<strong>Click the Sort button</strong></div>
<div class="taskText" style="margin: 7px 0px;">
Click the button Sort.<br></div>
<div class="detailsBox taskIdea" style="padding: 12px 12px 12px 44px; background-color: rgb(238, 237, 237); margin: 5px 0px; background-size: 32px; background-position: 8px 5px; background-repeat: no-repeat; background-image: url("/images/Knowledge.png");">
Notice how Excel sorted my transactions based on date, the latest transaction is on top now.<br></div>
</li>
<li style="padding: 5px;"><div>
<strong>Click the Filter button</strong></div>
<div class="taskText" style="margin: 7px 0px;">
Click the button Filter. <br></div>
<div class="detailsBox taskIdea" style="padding: 12px 12px 12px 44px; background-color: rgb(238, 237, 237); margin: 5px 0px; background-size: 32px; background-position: 8px 5px; background-repeat: no-repeat; background-image: url("/images/Knowledge.png");">
Notice how Excel filtered my transactions based on sub-category, now only transactions under Education and Fuel are shown.<br></div>
</li>
<li style="padding: 5px;"><div>
<strong>Click the Report button</strong></div>
<div class="taskText" style="margin: 7px 0px;">
Click the button Report.<br>Congratulations! You've now written an Excel add-in that uses the new Excel JavaScript API.<br></div>
<div class="detailsBox taskIdea" style="padding: 12px 12px 12px 44px; background-color: rgb(238, 237, 237); margin: 5px 0px; background-size: 32px; background-position: 8px 5px; background-repeat: no-repeat; background-image: url("/images/Knowledge.png");">
Notice how Excel summarized my transactions with formulas and created a pie chart to visualize my spending.</div>
</li>
</ol>
-->
No comments:
Post a Comment