Geekly Articles each Day
📜 ⬆️ ⬇️

Office Add-Ins for Excel - new features for VBA and VSTO developers

Prehistory


It all started about four years ago. While working on another business process automation project for a large Russian retail chain, I became interested in developing add-ons for office applications, in particular, for Excel. It took me a few days to observe how the employees of the customer company spend a lot of time on routine repetitive operations, how I got a lot of ideas about how I could simplify their lives.

At that time, we, as developers, had two ways to “extend” Excel for atypical tasks:



I think all Excel add-in developers are well aware of the advantages and disadvantages of both approaches. The big advantage of both is a very rich API, which allows you to automate almost any task. The disadvantages are the difficulty in installing such extensions. This is especially true for VSTO-based add-ons, where, often, installation requires administrative rights, the acquisition of which may be problematic for end users.

For several reasons, the discussion of which is beyond the scope of this article, I chose for myself the option with VSTO. So our first Microsoft Excel add-in was born - XLTools . The first version of the product includes tools that allow:
')

Office Store Appearance


Literally a year after the release of the first version of the XLTools add-on, we learned that Microsoft is launching a new platform to promote extensions under the Office - Office Store. My first thought is - can we publish our new add-on XLTools there? Maybe unfortunately, maybe fortunately, but the answer to this question is NO. Neither VBA nor VSTO add-ons can be published to the Office Store. But is it worth getting upset? Fortunately, the answer is NO, not worth it. Next, I will explain why.

New concept Add-Ins for Office


What is the Office Store and why do we need it? In short, this is a platform that helps users and developers to search, download, sell and buy add-ons that extend the standard functionality of Office programs, be it Excel , Word , Outlook , OneNote or PowerPoint . If earlier, end users had to search for add-ons they need in search engines, now for this a single place has been created - the Office Store, which can be accessed directly from the office software interface. The menu item "Insert" -> "My Add-ons":



As we have already found out, publishing add-ins developed using VBA or VSTO will not work in the Office Store. With the release of Office 365 and Office Store , Microsoft has offered us a new way of developing add-ons using the JavaScript API for Office, which involves developing applications using web technologies such as HTML5, CSS, JavaScript and Web Services .

The new approach has both advantages and disadvantages. The advantages include:


Of the shortcomings of the new approach, I can single out only one, though, so far, quite significant:

Development of add-ins for Excel "by new rules"



So, where to start, if we want to keep up with the times and do not miss the new wave of applications for Office?

There are two options. Currently, we can develop applications based on the JavaScript API in:


In this article we will look at the development using Visual Studio, because I myself use it. If you are interested in trying Napa, you can familiarize yourself with this tool and start working with it here .

Before starting the development, you should also pay attention to a couple of significant differences between VBA / VSTO add-ons and add-ins for the Office Store:

Development of add-ins for Excel using Visual Studio and JavaScript API


By default, Visual Studio has predefined project templates for developing add-ons for the Office Store, so creating a new project takes literally seconds.



The project itself consists of a manifest file and a website. The manifest file looks like this:
<?xml version="1.0" encoding="UTF-8"?> <OfficeApp xmlns="http://schemas.microsoft.com/office/appforoffice/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="TaskPaneApp"> <Id>11111111-1111-1111-1111-111111111111</Id> <Version>1.0.1</Version> <ProviderName>WavePoint Co. Ltd.</ProviderName> <DefaultLocale>en-US</DefaultLocale> <DisplayName DefaultValue="XLTools.net Data Cleaning for Excel" /> <Description DefaultValue=" Clean bulk data fast: remove extra spaces, change text case, convert text format to numbers, etc."/> <IconUrl DefaultValue="~remoteAppUrl/App/DataCleansing/Images/Logo32.png"></IconUrl> <SupportUrl DefaultValue="http://xltools.net/excel-apps/data-cleaning-app/"></SupportUrl> <Capabilities> <Capability Name="Workbook" /> </Capabilities> <DefaultSettings> <SourceLocation DefaultValue="~remoteAppUrl/App/DataCleansing/Home.html" /> </DefaultSettings> <Permissions>ReadWriteDocument</Permissions> </OfficeApp>


The main thing to note in this file is:


The website consists of the minimum set of HTML, JavaScript and CSS files necessary for the application to work, and by default provides a basic UI, on the basis of which we can build a UI for a new solution. It is worth noting that one of the requirements for the site is to work on HTTPS . This means that if you publish the site on your own servers or on your own domain, you will need an SSL certificate. In case you plan to use, for example, the Azure Website, this problem can be avoided, because All sites deployed in the azurewebsites.net subdomain are available by default using both HTTP and HTTPS protocols.

A standard set of methods is provided for interacting with Excel data in the JavaScript API. I will give examples of using only a few, from among those that we used when developing the XLTools.net Data Cleansing add- in :


 Office.context.document.bindings.addFromPromptAsync(Office.BindingType.Matrix, { id: "RangeToClean" }, function (asyncResult) { if (asyncResult.status == "failed") { // Some specific code here } else { // Some specific code here } }); 


 Office.select("bindings#RangeToClean", onBindingNotFound).getDataAsync( {}, doDataCleaning ); 


 Office.select("bindings#RangeToClean").setDataAsync(range,function (asyncResult) { if (asyncResult.status == "failed") { // Some specific code here } else { app.showNotification(UIText.ExecutedSuccessfully, '', 'success'); } }).

All JavaScript API methods are well documented, their detailed description can be found on the MSDN website .

Depending on the scenario, data processing can occur both directly on the client, i.e. in the javascript code and on the server. To process data on the server, you can add the necessary services directly to the site, for example, using the Web API. Client communication (add-ons) with web services is the same as we used to do on any other site - with the help of AJAX requests. The only thing to consider is that if you plan to use third-party services located on foreign domains, you will certainly encounter the same-origin policy problem.

Add-on Publish to Office Store


To publish an add-in in the Office Store, you need to register on the Microsoft Seller Dashboard site. After registration, you will get access to your personal account, where you can download the manifest of your application and fill in all the necessary information about it. Based on personal experience, I can say that checking an application after sending for approval usually takes from one to three business days. After checking the application by Microsoft employees and approving it, it becomes available for download to millions of users worldwide via the Office Store:



findings


In conclusion, XLTools add-ons are an excellent example of how to transform existing solutions based on VBA / VSTO technologies into cross-platform solutions for Office 365. In our case, we were able to transfer a good half of the functions from the Desktop version of XLTools, by implementing six separate applications.

All of them are currently available for download through the Office Store:


I would also like to note that in addition to the usual scenarios, with the advent of the Office Store and Office 365, we, as developers, have new opportunities to develop extensions using the Office 365 API , which allows access to data from services such as Mails , Calendars , SharePoint Online , OneDrive for Business , etc. Who knows that we can build tomorrow using these features. Time will tell!



about the author


Peter Lyapin - Technical Director of Wave Point Ltd.

More than 10 years of experience in implementing automation projects
business processes. Worked with many Russian and
foreign companies. Founder of the XLTools.net project.

Source: https://habr.com/ru/post/270763/

More articles:


All Articles