Love working in Excel, digging in large datasets or planning budgeting strategies, or creating reports, Excel is a powerful program to help in all business processes. But to do so we need data to be populated in Excel.

Let’s explore how we can integrate Microsoft Office 365 Excel, bring data from data sources by implementing Office Add-ins using Office JavaScript APIs and consuming third-party APIs straight from the add-ins.

Office Add-ins are websites embedded in Office applications. The platform allows building solutions that can interact with the contents of the Office document as well as external data.

Microsoft Excel Task Pane Add-In that works on Web, Windows & Mac

Excel Add-ins for Office 365 and Office Desktop allows working with database and cloud data in Microsoft Excel as with usual Excel spreadsheets.

Excel Add-ins can get exactly the data needed with visual Query Builder or SQL and refresh the queried external data in a workbook any time with a single click. External data can be edited in Excel, and then saved back to the data source.

Connect Microsoft Excel to various data sources
Work with live data directly in Excel
Modify external data easily
Save modified data back to the data source

Excel add-ins overview

An Excel add-in allows extending Excel application functionality across multiple platforms including Windows, Mac, iPad, and in a browser. Add-ins can be used within a workbook to:

Interact with Excel objects, and read and write Excel data.
Extend functionality using a web-based task pane or content pane
Add custom functions
Provide richer interaction using the dialog window

Using the Office Add-ins platform to create Excel add-ins has the following benefits.

Cross-platform support: Excel add-ins run in Office on the web, Windows, Mac, and iPad.
Centralized deployment: Admins can quickly and easily deploy Excel add-ins to users throughout an organization.
Use of standard web technology: Create an Excel add-in using familiar web technologies such as HTML, CSS, and JavaScript.
Distribution via AppSource: Share Excel add-in with a broad audience by publishing it to AppSource.

Task panes

Task panes are interface surfaces, that typically appear on the right side of the window within Excel. It gives users access to interface controls that run code to modify the Excel document or display data from a data source.

The following are two main components of Office Add-ins:

Manifest file – an XML file that defines the setting and capability of the add-in. It contains metadata to describe the add-in, specifies the target Office client and permission allowed, and most importantly dictates how the add-in will extend and interact with the Office client, e.g., custom ribbon buttons, task panes, etc.
Web application – standard web technologies like HTML, CSS, and JavaScript can be utilized to provide UI and functionality for the add-in components. In this context, Office JavaScript APIs can be used to interact with the Office client and the content of the Office document.

With the web application, one can develop and display a login page, to access third party back-end data in a secure way as shown in below screenshot –

 

By using the call to web API, after authentication, relevant information will get displayed inside the side pane.

In the screenshot below, a list of reports is being displayed, whose data can be displayed in excel worksheet.

 

With a single click intended report is generated in excel.

 

 

The web application can be an MVC application that can be used to interact with the data source and have a user interactive interface just inside the Task Pane.

 Develop Excel Web Add-ins using Visual Studio

Steps –

Open Visual Studio and create a New Project.
Search “Excel Web Add-in” (with C#) and press Next.

 

 

Enter Project Name for example – “ExcelWebAddinDemo”
Change the Location by clicking on the Browse button
Select the “Place solution and project in the same directory” checkbox.
Create Office Add-in wizard will appear.
Select “Add new functionalities to Excel” and press Finish.

 

 

This creates a solution that contains two projects:
ExcelWebAddinDemo – Contains the XML manifest file (ExcelWebAddInDemoManifest).
ExcelWebAddinDemoWeb – Contains the corresponding Web application.

 

 

ExcelWebAddinDemo.xml –

 

In the manifest file, update the URL with the web project URL and run the web project from the visual studio.
Login to Office 365 and open a blank excel workbook.
From the Insert tab, click the Office Add-ins button to upload a newly developed add-in

 

 

Click on Upload My Add-in –

 

 

 

 

Upload the manifest file, here it is ExcelWebAddinDemo.xml

Once the manifest file is uploaded, the add-in will get added to excel. A button will get added to the Home tab

Press Got It button to dismiss the popup message.
Press the “Show Task pane” button to display the task pane.
This displays the demo add-in. Try to run the simple functionality for testing.
Select the sample data, cell range “B3:D5” and press the “Highlight” button.
The largest number in the current selection will be shaded orange.

All done, successfully created an Excel task pane add-in!

 

Reference – https://docs.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-overview