JavaScript based Excel Office Add-in in Visual Studio

If you build an Office application in Office add-in you can use these technologies HTML, CSS, and JavaScript, and interact with Word, Excel, PowerPoint, OneNote, Project, and Outlook. Your code run in Office across multiple platforms, including Windows, Mac, iPad, and in a browser.

The Office Add-ins can do anything that any webpage can do inside a browser. The Office Add-ins use the below platform in Visual Studio.

Add New Functionality to Office Clients: You can bring external data into Office, automate Office documents, expose third-party functionality in Office clients, and more.

The interactive objects can be embedded in Office documents: In the Office add-in, you can embed maps, charts, and interactive visualizations so the user can add to their own Excel spreadsheets and PowerPoint presentations.

COM and VSTO Add-ins Different to Office-Add-ins?

The COM or VSTO add-ins are earlier Office integration that runs only in Office on windows. The Office Add-ins don’t involve code that runs on the user’s device.

The Office Add-ins provide below advantages over add-ins built using VBA, COM, or VSTO:

• It is cross-platform support. The Office Add-ins run in Office on the web, Windows, Mac, and iPad.
• It is a centralized deployment and distribution. The admins can deploy Office Add-ins in the center across an organization.
• It is easy to access via AppSource. Your solution is available to a broad audience by submitting it to AppSource.
• It is based on standard web technology. You will use any you like to build Office.

Components of an Office Add-in

There are basically two components in Office Add-in

An XML manifest file: The manifest file defines the various setting, including how your add-in integrates with the office clients.

Your own web application: it needs to be hosted on a web server, or web hosting service, such as Microsoft Azure.

Manifest

It is specified settings and capabilities of the add-in, such as:

• In the add-in’s we saw the display name, description ID, version, and default locale.
• You can add-in integrates with Office.
• You can add the permission level and data access requirements for the add-in.

Web App

You can use the static HTML page build for the Office application. Your HTML page doesn’t interact with either the Office document or any other Internet resource. Hence, you can use both client and server-side technologies used in Office applications.

Read More: What Is Xml Manifest In Office Add-in?

Extending and Interacting with Office Clients

There are two ways you can extend and interact with Office client’s applications.

• You can extend functionality with any Office application.
• You can create a new object with Excel or PowerPoint Office application.

Create an Excel Task Pane Add-in

Prerequisites

• Download Node.js with the latest LTS version.
• You can also download the latest version of Yeoman and the Yeoman generator for Office Add-ins, and install these tools globally, and run these commands via the command prompt.

Example:

    npm install -g yo generator-office

Create Your Add-In Project

You can run the following command to create an add-in project using the Yeoman generator.

Example:

    yo office

Figure: These are the following information for the add-in project

Create a Table

You can follow the steps to create a table. You can test your add-in supports for your current version of Excel. You can add a table to the worksheet, occupy the table with data, and format it.

Code the Add-In

Your Excel add-in project open in your Visual Studio Code.

You can open the taskpane.html file in the task pane folder.

In taskpane.html file, you can see main tag element and delete all lines between main tag and closing main tag.

You can add the below line after opening the main tag.

Example:

<button class="ms-button" id="createTable">Create Table</button> 

You can open the taskpane.js file in the task pane folder, the taskpane.js file contains the Office JavaScript API code that facilities interaction between the task pane and the Office client application.

You can remove all references to the run button and the run () method by doing the following.

• Delete the line document.getElementById(“run”).onclick = run;
• Delete the entire run () function.

After you can call Office.onReady method, you see the if (info.host === Office.HostType.Excel )

In the first part, your code determines whether the user’s version of Excel supports a version of Excel.js that includes all the JavaScript APIs will use. You can use the body of the conditional block to hide or disable the UI that would call unsupported APIs. If you enable to the user to still make use of the parts of the add-in that are supported by their version of Excel.

In the second part, this code adds an event handler for the createTable button.

Example:

  if(!Office.context.requirements.isSetSupported('ExcelApi','1.7')){
    console.log("This Javascript API is not available ")
  }
document.getElementById("createTable").onclick= createTable;  

You can add the following function to the end of the file.

• In the createTable function you can pass the Excel.run function pass. This function will not execute immediately.
• Return context.sync() method sends all queued commands to Excel for execution.
• The catch block is followed to Excel.run() function.

One Destination for Excel Add-in Development Solutions? Enquire Today.

Example:

    function createTable() {
      Excel.run(function (context){
        return context.sync();
      })
      .catch(function (error){
        console.log("Error: "+ error);
        if(error instanceof OfficeExtension.Error) {
          console.log("Debug info: "+JSON.stringify(error.debugInfo));
        }
        });
    }

First of all, if you pass four values so you can add the table with column A1 to D1. Then, you define the table name.

Example:

    var currentSheet = context.workbook.worksheets.getActiveWorksheet();
    var employeeTable = currentSheet.tables.add("A1:D1");
employeeTable.name = "EmployeeTable";

You can add the data of employee with getHeaderRowRange() method.

Example

    employeeTable.getHeaderRowRange().values = [["EmployeeID","Name","Designation","DOB"]];
    employeeTable.rows.add([
      ["Emp1","Bhavin","Sales","12/1/1997"],
      ["Emp2","Hardik","Team Leader","10/12/1997"],
      ["Emp3","Gaurav","Project Manger","12/12/1997"],
      ["Emp4","Maulik","Sales","21/1/1997"],
      ["Emp5","Tejas","Project Manager","25/5/1997"],
      ["Emp6","Maulik","Team Leader","19/11/1997"]
  ]);

After that, you can fit the columns and rows with getRange() method.

Example:

    employeeTable.getRange().format.autofitColumns();
    employeeTable.getRange().format.autofitRows();

Test the Add-In

If your project runs in a desktop application so you can pass this command.

  npm run start

If your project run in a web browser so you can pass this command

  npm start run:web

Conclusion

In this blog, we have created a table with an Excel add-in. You can also implement sort table, filter table, and charts, etc. in Excel add-in and can explore more features with JavaScript in Excel add-in.