×

iFour Logo

A step-by-step guide on Excel Add-in development using React.js

Kapil Panchal - October 04, 2022

Listening is fun too.

Straighten your back and cherish with coffee - PLAY !

  • play
  • pause
  • pause
 A step-by-step guide on Excel Add-ins development using React.js

What is an Excel Add-in?


MS Excel Add-in is a kind of program or a utility that lets you perform fundamental processes more quickly. It does this by integrating new features into the excel application that boosts its basic capabilities on various platforms like Windows, Mac & Web.

The Excel Add-in, as part of the Office platform, allows you to modify and speed up your business processes. Office Add-ins are well-known for their centralized deployment, cross-platform compatibility, and AppSource distribution. It enables developers to leverage web technologies including HTML, CSS, and JavaScript.

More importantly, it provides the framework and the JavaScript library Office.js for constructing Excel Add-ins. In this tutorial, we will walk through the basic yet effective process of creating the Excel Addin using ReactJS.

Prerequisites for setting up your development environment


Before you start creating Excel Add-ins, make sure you have these prerequisites installed on your PC.

  • NPM
  • Node.js
  • Visual Studio
  • A Microsoft 365 account with a subscription

Looking for the best Excel Add-in development company ? Connect us now.

How to build Excel Add-in using React


To begin, configure and install the Yeoman and Yeoman generator for Office 365 Add-in development.

    
    npm install -g yo generator-office
    
    

Now run the following yo command to create an Add-in

    
    yo office
    
    

After running the above command, select the Project type as a React framework. Take a look at the reference image below.

Choose a project type - Reactjs

Figure 1 Choose a Project Type

After selecting the project, choose TypeScript as your script type.

Choose a script type - Reactjs

Figure 2 Choose a Script Type

Now, name your Excel Add-in project as shown below. You can give whatever name you like but giving a project-relevant name would be an ideal move.

Give name to addin - Reactjs

Figure 3 Give Name to Add-in

Because it is critical to provide support for the office application, choose Excel as the Office client.

Choose an office client - Reactjs

Figure 4 Choose an Office Client

Congratulations!! Your first Excel Add-in is created successfully.

How to run Excel Add-in?


Add-ins are not instantly accessible in Excel by default. We must activate them before we may use them. Let's have a look at how to use a command prompt to execute Add-ins in MS Excel.

Use the following command and open the project folder on the command prompt.


    cd Excel_Tutorial
    
    

Now start the dev-server as shown below.


    npm run dev-server
    
    

To test Add-in in your Excel, run the following command in the project’s root directory.


    npm start
    
    

When you complete running this command, you should see a task pane added to Excel that operates like an Excel Add in.

Excel Addin taskpane - Reactjs

Figure 5 Excel Addin Taskpane

How to create a Table using ReactJS?


Businesses commonly use tables to present their business data whether it be price, comparison, financial comparison, etc. React.js makes it simple and quick for organizations to manage large amounts of data. Let’s understand the process of creating a table using React.js.

To begin,

Planning to hire dedicated ReactJS developers ? Contact us now.

  1. Open the project in VS code
  2. Open the file which is located in src\taskpane\components\app.tsx
  3. Remove the componentDidMount() method and click() method from app.tsx
  4. Remove all tags which are inside the return method and add one button inside the return method to generate a table
  5. App.tsx
    
        import * as React from "react";
        import Progress from "./Progress";
        
        export interface AppProps {
          title: string;
          isOfficeInitialized: boolean;
        }
        
        export default class App extends React.Component {
          constructor(props, context) {
            super(props, context);
            this.state = {
              listItems: [],
            };
          }
        
          render() {
            const { title, isOfficeInitialized } = this.props;
        
            if (!isOfficeInitialized) {
              return (
      
     );
            }
        
            return (
     
                 
                
            );
          }
        }
        
        
    • Create one event handler function for the button which will contain the logic for creating a new table.
      
     
    • Excel.js business logic will be added to the handleCreateTable function that is passed to Excel.run method.
    ```

    This logic will not execute immediately, Instead, it will be added to the queue of pending commands.

    The context.sync method sends all pending commands which are in queue to Excel for execution.

    The Excel.run method is followed by the catch block.

        handleCreateTable = async () => {
            await Excel.run(async (context) => {
        
              // logic for create table
        
              await context.sync();
            }).catch((err) => {
                console.log("Error: " + err);
              });
          }
     
    • In Excel.run method, first we have to get the current worksheet, and to do so, use the following method.
        const currentWorksheet = context.workbook.worksheets.getActiveWorksheet();
    

    Once we get the worksheet, we’ll create a table. Use the following method to create a table.

    
        
        const salaryTable = currentWorksheet.tables.add("A1:D1", true);
        

    The table is generated by using the add() function on the table collection of the current worksheet. The method accepts the first parameter as a range of the top row of the table.

    We can also give a name to our table as shown below.

     salaryTable.name = "SalaryTable";   
    
    • Now, add a header row using the code shown below.
    
    
         salaryTable.getHeaderRowRange().values = 
        [["Name", "Occupation", "Age","Salary"]];
        
        

    The table's rows are then inserted using the add() function of the table's row collection. We may add several rows in a single request by sending an array of cell values within the parent array.

    
    
    
    
         salaryTable.rows.add(null /*add at the end*/, [
          ["Poojan", "Software Developer","39", "50,000"],
                ["Meera", "Fashion Designer","23", "30,000"],
                ["Smit", "Teacher", "25","35,000"],
                ["Kashyap", "Scientist", "29","70,000"],
                ["Neha", "Teacher","34", "15,000"],
                ["Jay", "DevOps Developer","31", "65,000"]
              ]);
        
        
        
    • We can change the format of salary to decimal. For that, we have to pass the column zero-based index to the getItemAt() method.
        salaryTable.columns.getItemAt(3).getRange().numberFormat = [['##0.00']]; 
    
    • When we use the table to represent business data, it is important to ensure content is displayed clearly. With the fine use of the autofitColumns() and autofitRows() methods, we can perfectly fit the content into cells.
        salaryTable.getRange().format.autofitColumns();
        salaryTable.getRange().format.autofitRows();
     

    Let’s take a look at how the entire function appears to be.

        handleCreateTable = async () => {
            await Excel.run(async (context) => {
        
              const currentWorksheet=context.workbook.worksheets.getActiveWorksheet();
              const salaryTable = currentWorksheet.tables.add("A1:D1", true );
              salaryTable.name = "SalaryTable";
        
              salaryTable.getHeaderRowRange().values =
                [["Name", "Occupation", "Age", "Salary"]];
        
              salaryTable.rows.add(null /*add at the end*/, [
                ["Poojan", "Software Developer", "39", "50,000"],
                ["Meera", "Fashion Designer", "23", "30,000"],
                ["Smit", "Teacher", "25", "35,000"],
                ["Kashyap", "Scientist", "29", "70,000"],
                ["Neha", "Teacher", "34", "15,000"],
                ["Jay", "DevOps Developer", "31", "65,000"]
              ]);
        
              salaryTable.columns.getItemAt(3).getRange().numberFormat = [['##0.00']];
              salaryTable.getRange().format.autofitColumns();
              salaryTable.getRange().format.autofitRows();
        
              await context.sync();
        
            }).catch((err) => {
              console.log("Error: " + err);
            });
          }
     
    • Now, use the npm start command to run the code. That's all there is to it; now, when the user hits the generate table button, he'll see the following result.
    • Output: Generate a new table - Reactjs

      Figure 6 Generate a new table

How to Filter data in a table?


Filtering data is critical because organizations utilize it to exclude undesired results for analysis. Let's see how data in a table may be filtered for better analysis.


    
    
    

    
    
    
  1. Open the file which is located in src\taskpane\components\app.tsx
  2. Add a new button for filter data below Generate Table button.
  3. Create one event handler function for the button that will contain the filter data logic.
  4. filterData function:
        filterData = async () => {
            await Excel.run(async (context) => {
              await context.sync();
            }).catch((err) => {
              console.log("Error: " + err);
            });
          }
    
    • Then we will get the current worksheet and table.
        const currentWorksheet = context.workbook.worksheets.getActiveWorksheet();
        const salaryTable = currentWorksheet.tables.getItem('salaryTable');
    
    • To begin filtering data, we must first access the column from which we will be filtering data.
        const occupationFilter = salaryTable.columns.getItem('Occupation').filter;
        

    Here, Occupation is the column name on which we want to apply the filter.

    • Next, pass the values as a filter query.
        occupationFilter.applyValuesFilter(['Software Developer', 'Teacher']);
        
    • Meanwhile, take a look at how the whole function looks like.
        filterData = async () => {
            await Excel.run(async (context) => {
        
              const currentWorksheet=context.workbook.worksheets.getActiveWorksheet();
              const salaryTable = currentWorksheet.tables.getItem('salaryTable');
              const occupationFilter=salaryTable.columns.getItem('Occupation').filter;
              occupationFilter.applyValuesFilter(['Software Developer', 'Teacher']);
        
              await context.sync();
            }).catch((err) => {
              console.log("Error: " + err);
            });
          }
        
    • Finally, run the code using the npm start command. Now when the user clicks on the filter data button, he’ll see the following result.
    • Output: Filter data - Reactjs

      Figure 7 Filter data

How to sort data in the table?


Data sorting is also important since it helps to obtain well-organized data in a sequential manner. Let’s understand in simple ways, how data can be sorted in a table.

To start with,


    
    

Searching for the best Microsoft 365 development solutions ? Your search ends here.

 

  1. Open the project in VS code
  2. Open the file from the path: src\taskpane\components\app.tsx
  3. Add a new button for sorting data below the filter data button.
  4. Create one event handler function for the button which will contain the logic for sorting the data.
  5. sortData function:
    
    
    
    
        sortData=async()=>{
           await Excel.run(async (context) => {
            await context.sync();
            }).catch((err) => {
              console.log("Error: " + err);
            });
          }   
        
        
    • Let's start by getting the current worksheet and table.
    
    
    
    
        const currentWorksheet = context.workbook.worksheets.getActiveWorksheet();
        const salaryTable = currentWorksheet.tables.getItem('salaryTable');
        
        
        
    • In the function, we will build a sort field object and supply two parameters to it: the key and the type of sorting (ascending or descending).
    • Note:

      The key property is the zero-based index of the column, and it is used for sorting. All the rows of data are sorted according to key.

          const sortFields = [
                  {
                    key: 3,
                    ascending: false,
                  }
                ];
          
      • Subsequently, we use the sort and apply method on the table and pass the sortFields object.
          salaryTable.sort.apply(sortFields);
          
      • Here is what the whole function might look like.
              sortData = async () => {
                  await Excel.run(async (context) => {
              
                    const currentWorksheet=context.workbook.worksheets.getActiveWorksheet();
                    const salaryTable = currentWorksheet.tables.getItem('salaryTable');
              
                    const sortFields = [
                      {
                        key: 3,
                        ascending: false,
                      }
                    ];
              
                    salaryTable.sort.apply(sortFields);
              
                    await context.sync();
                  }).catch((err) => {
                    console.log("Error: " + err);
                  });
                }        
          
      • Run the code using the npm start command

      Finally, run the code with the npm start command. The user will see the following result every time he clicks on the sort data button.

      output Sort data - Reactjs

      Figure 8 Sort Data

Conclusion

Office Add-ins benefit businesses with faster operations and processes. In Office Add-ins, you can use familiar technologies like HTML, CSS & JavaScript to create Outlook, Excel, Word, and PowerPoint Add-ins. In this blog, we learned how to create an Excel Addin with React library from scratch and how to create tables, filter & sort data in Excel using Excel Add-in.

A step-by-step guide on Excel Add-in development using React.js Table of Content 1.What is an Excel Add-in? 2.Prerequisites for setting up your development environment 3.How to build Excel Add-in using React 4.How to run Excel Add-in? 5.How to create a Table using ReactJS? 6.How to Filter data in a table? 7.How to sort data in the table? 8.Conclusion What is an Excel Add-in? MS Excel Add-in is a kind of program or a utility that lets you perform fundamental processes more quickly. It does this by integrating new features into the excel application that boosts its basic capabilities on various platforms like Windows, Mac & Web. The Excel Add-in, as part of the Office platform, allows you to modify and speed up your business processes. Office Add-ins are well-known for their centralized deployment, cross-platform compatibility, and AppSource distribution. It enables developers to leverage web technologies including HTML, CSS, and JavaScript. More importantly, it provides the framework and the JavaScript library Office.js for constructing Excel Add-ins. In this tutorial, we will walk through the basic yet effective process of creating the Excel Addin using ReactJS. Prerequisites for setting up your development environment Before you start creating Excel Add-ins, make sure you have these prerequisites installed on your PC. NPM Node.js Visual Studio A Microsoft 365 account with a subscription Looking for the best Excel Add-in development company ? Connect us now. See here How to build Excel Add-in using React To begin, configure and install the Yeoman and Yeoman generator for Office 365 Add-in development. npm install -g yo generator-office Now run the following yo command to create an Add-in yo office After running the above command, select the Project type as a React framework. Take a look at the reference image below. Figure 1 Choose a Project Type After selecting the project, choose TypeScript as your script type. Figure 2 Choose a Script Type Now, name your Excel Add-in project as shown below. You can give whatever name you like but giving a project-relevant name would be an ideal move. Figure 3 Give Name to Add-in Read More: React Element vs Component: A deep dive into differences Because it is critical to provide support for the office application, choose Excel as the Office client. Figure 4 Choose an Office Client Congratulations!! Your first Excel Add-in is created successfully. How to run Excel Add-in? Add-ins are not instantly accessible in Excel by default. We must activate them before we may use them. Let's have a look at how to use a command prompt to execute Add-ins in MS Excel. Use the following command and open the project folder on the command prompt. cd Excel_Tutorial Now start the dev-server as shown below. npm run dev-server To test Add-in in your Excel, run the following command in the project’s root directory. npm start When you complete running this command, you should see a task pane added to Excel that operates like an Excel Add in. Figure 5 Excel Addin Taskpane How to create a Table using ReactJS? Businesses commonly use tables to present their business data whether it be price, comparison, financial comparison, etc. React.js makes it simple and quick for organizations to manage large amounts of data. Let’s understand the process of creating a table using React.js. To begin, Planning to hire dedicated ReactJS developers ? Contact us now. See here Open the project in VS code Open the file which is located in src\taskpane\components\app.tsx Remove the componentDidMount() method and click() method from app.tsx Remove all tags which are inside the return method and add one button inside the return method to generate a table App.tsx import * as React from "react"; import Progress from "./Progress"; export interface AppProps {   title: string;   isOfficeInitialized: boolean; } export default class App extends React.Component {   constructor(props, context) {     super(props, context);     this.state = {       listItems: [],     };   }   render() {     const { title, isOfficeInitialized } = this.props;     if (!isOfficeInitialized) {       return ( );     }     return (          Generate Table             );   } } Create one event handler function for the button which will contain the logic for creating a new table. Generate Table Excel.js business logic will be added to the handleCreateTable function that is passed to Excel.run method. ``` This logic will not execute immediately, Instead, it will be added to the queue of pending commands. The context.sync method sends all pending commands which are in queue to Excel for execution. The Excel.run method is followed by the catch block. handleCreateTable = async () => { await Excel.run(async (context) => { // logic for create table await context.sync(); }).catch((err) => { console.log("Error: " + err); }); } In Excel.run method, first we have to get the current worksheet, and to do so, use the following method. const currentWorksheet = context.workbook.worksheets.getActiveWorksheet(); Once we get the worksheet, we’ll create a table. Use the following method to create a table. const salaryTable = currentWorksheet.tables.add("A1:D1", true); The table is generated by using the add() function on the table collection of the current worksheet. The method accepts the first parameter as a range of the top row of the table. We can also give a name to our table as shown below.  salaryTable.name = "SalaryTable"; Now, add a header row using the code shown below.  salaryTable.getHeaderRowRange().values =  [["Name", "Occupation", "Age","Salary"]]; The table's rows are then inserted using the add() function of the table's row collection. We may add several rows in a single request by sending an array of cell values within the parent array.  salaryTable.rows.add(null /*add at the end*/, [ ["Poojan", "Software Developer","39", "50,000"],         ["Meera", "Fashion Designer","23", "30,000"],         ["Smit", "Teacher", "25","35,000"],         ["Kashyap", "Scientist", "29","70,000"],         ["Neha", "Teacher","34", "15,000"],         ["Jay", "DevOps Developer","31", "65,000"]       ]); We can change the format of salary to decimal. For that, we have to pass the column zero-based index to the getItemAt() method. salaryTable.columns.getItemAt(3).getRange().numberFormat = [['##0.00']]; When we use the table to represent business data, it is important to ensure content is displayed clearly. With the fine use of the autofitColumns() and autofitRows() methods, we can perfectly fit the content into cells. salaryTable.getRange().format.autofitColumns(); salaryTable.getRange().format.autofitRows(); Read More: Flutter vs. React Native: Choose the Best for your App in 2022 Let’s take a look at how the entire function appears to be. handleCreateTable = async () => {     await Excel.run(async (context) => {       const currentWorksheet=context.workbook.worksheets.getActiveWorksheet();       const salaryTable = currentWorksheet.tables.add("A1:D1", true );       salaryTable.name = "SalaryTable";       salaryTable.getHeaderRowRange().values =         [["Name", "Occupation", "Age", "Salary"]];       salaryTable.rows.add(null /*add at the end*/, [         ["Poojan", "Software Developer", "39", "50,000"],         ["Meera", "Fashion Designer", "23", "30,000"],         ["Smit", "Teacher", "25", "35,000"],         ["Kashyap", "Scientist", "29", "70,000"],         ["Neha", "Teacher", "34", "15,000"],         ["Jay", "DevOps Developer", "31", "65,000"]       ]);       salaryTable.columns.getItemAt(3).getRange().numberFormat = [['##0.00']];       salaryTable.getRange().format.autofitColumns();       salaryTable.getRange().format.autofitRows();       await context.sync();     }).catch((err) => {       console.log("Error: " + err);     });   } Now, use the npm start command to run the code. That's all there is to it; now, when the user hits the generate table button, he'll see the following result. Output: Figure 6 Generate a new table How to Filter data in a table? Filtering data is critical because organizations utilize it to exclude undesired results for analysis. Let's see how data in a table may be filtered for better analysis. Filter Data Filter Data Open the file which is located in src\taskpane\components\app.tsx Add a new button for filter data below Generate Table button. Create one event handler function for the button that will contain the filter data logic. filterData function: filterData = async () => {     await Excel.run(async (context) => {       await context.sync();     }).catch((err) => {       console.log("Error: " + err);     });   } Then we will get the current worksheet and table. const currentWorksheet = context.workbook.worksheets.getActiveWorksheet(); const salaryTable = currentWorksheet.tables.getItem('salaryTable'); To begin filtering data, we must first access the column from which we will be filtering data. const occupationFilter = salaryTable.columns.getItem('Occupation').filter; Here, Occupation is the column name on which we want to apply the filter. Next, pass the values as a filter query. occupationFilter.applyValuesFilter(['Software Developer', 'Teacher']); Meanwhile, take a look at how the whole function looks like. filterData = async () => {     await Excel.run(async (context) => {       const currentWorksheet=context.workbook.worksheets.getActiveWorksheet();       const salaryTable = currentWorksheet.tables.getItem('salaryTable');       const occupationFilter=salaryTable.columns.getItem('Occupation').filter;       occupationFilter.applyValuesFilter(['Software Developer', 'Teacher']);       await context.sync();     }).catch((err) => {       console.log("Error: " + err);     });   } Finally, run the code using the npm start command. Now when the user clicks on the filter data button, he’ll see the following result. Output: Figure 7 Filter data How to sort data in the table? Data sorting is also important since it helps to obtain well-organized data in a sequential manner. Let’s understand in simple ways, how data can be sorted in a table. To start with, Sort Data Searching for the best Microsoft 365 development solutions ? Your search ends here. See here   Sort Data --> Open the project in VS code Open the file from the path: src\taskpane\components\app.tsx Add a new button for sorting data below the filter data button. Create one event handler function for the button which will contain the logic for sorting the data. sortData function: sortData=async()=>{ await Excel.run(async (context) => {   await context.sync();     }).catch((err) => {       console.log("Error: " + err);     });   }    Let's start by getting the current worksheet and table. const currentWorksheet = context.workbook.worksheets.getActiveWorksheet(); const salaryTable = currentWorksheet.tables.getItem('salaryTable'); In the function, we will build a sort field object and supply two parameters to it: the key and the type of sorting (ascending or descending). Note: The key property is the zero-based index of the column, and it is used for sorting. All the rows of data are sorted according to key. const sortFields = [         {           key: 3,           ascending: false,         }       ]; Subsequently, we use the sort and apply method on the table and pass the sortFields object. salaryTable.sort.apply(sortFields); Read More: Comparative Analysis of Blazor, Angular, React, Vue and Node for Web development Here is what the whole function might look like. sortData = async () => { await Excel.run(async (context) => { const currentWorksheet=context.workbook.worksheets.getActiveWorksheet(); const salaryTable = currentWorksheet.tables.getItem('salaryTable'); const sortFields = [ { key: 3, ascending: false, } ]; salaryTable.sort.apply(sortFields); await context.sync(); }).catch((err) => { console.log("Error: " + err); }); } Run the code using the npm start command Finally, run the code with the npm start command. The user will see the following result every time he clicks on the sort data button. output Figure 8 Sort Data Conclusion Office Add-ins benefit businesses with faster operations and processes. In Office Add-ins, you can use familiar technologies like HTML, CSS & JavaScript to create Outlook, Excel, Word, and PowerPoint Add-ins. In this blog, we learned how to create an Excel Addin with React library from scratch and how to create tables, filter & sort data in Excel using Excel Add-in.

Build Your Agile Team

Enter your e-mail address Please enter valid e-mail

Categories

Ensure your sustainable growth with our team

Talk to our experts
Sustainable
Sustainable
 

Blog Our insights

Azure DevOps Pipeline Deployment for Competitive Business: The Winning Formula
Azure DevOps Pipeline Deployment for Competitive Business: The Winning Formula

We always hear about how important it is to be competitive and stand out in the market. But as an entrepreneur, how would you truly set your business apart? Is there any way to do...

React 18 Vs React 19: Key Differences To Know For 2024
React 18 Vs React 19: Key Differences To Know For 2024

Ever wondered how a simple technology can spark a revolution in the IT business? Just look at React.js - a leading Front-end JS library released in 2013, has made it possible. Praised for its seamless features, React.js has altered the way of bespoke app development with its latest versions released periodically. React.js is known for building interactive user interfaces and has been evolving rapidly to meet the demands of modern web development. Thus, businesses lean to hire dedicated React.js developers for their projects. React.js 19 is the latest version released and people are loving its amazing features impelling them for its adoption.

React 19 For Business: Latest Features and Updates
React 19 For Business: Latest Features and Updates

When I first started exploring React.js for my company’s needs, I felt a bit lost. It was like trying to solve a big puzzle without all the pieces! But as I kept learning and trying them practically, I discovered some really cool features that blew my mind making everything seamlessly easier.