JavaScript based Excel Office Add-in in Visual Studio

    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 Table 

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

    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));
        }
        });
    }
  

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

    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"]
  ]);
  

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

  npm run start

  npm start run:web

JavaScript based Excel Office Add-in in Visual Studio 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: Create Table 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. See here 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.  

Kapil Panchal

A passionate Technical writer and an SEO freak working as a Technical Content Manager at iFour Technolab, USA. With extensive experience in IT, Services, and Product sectors, I relish writing about technology and love sharing exceptional insights on various platforms. I believe in constant learning and am passionate about being better every day.