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

 

Content source: https://www.ifourtechnolab.com/blog/a-step-by-step-guide-on-excel-add-in-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.

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

 

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

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

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

salaryTable.columns.getItemAt(3).getRange().numberFormat = [['##0.00']];

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

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 Home from "./Home";
   
   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 (
           <>
           
       );
     }
   }
   
   

6. Create one event handler function for the button which will contain the logic for creating a new table.
7. Excel.js business logic will be added to the handleCreateTable function that is passed to Excel.run method.
8. In Excel.run method, first we have to get the current worksheet, and to do so, use the following method.
9. Now, add a header row using the code shown below.
10. We can change the format of salary to decimal. For that, we have to pass the column zero-based index to the getItemAt() method.
11. 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.
12. 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.
13. 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.

const currentWorksheet = context.workbook.worksheets.getActiveWorksheet();
const salaryTable = currentWorksheet.tables.getItem('salaryTable');

const occupationFilter = salaryTable.columns.getItem('Occupation').filter;

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

occupationFilter.applyValuesFilter(['Software Developer', 'Teacher']);

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

1. Open the project in Visual Studio code
2. Open the file which is located in src\taskpane\components\app.tsx
3. Add a new button for filter data below Generate Table button.
4. Create one event handler function for the button that will contain the filter data logic.
5. filterData function:

   filterData = async () => {
       await Excel.run(async (context) => {
         await context.sync();
       }).catch((err) => {
         console.log("Error: " + err);
       });
     }
   
   
   

6. Then we will get the current worksheet and table.
7. To begin filtering data, we must first access the column from which we will be filtering data.
8. Next, pass the values as a filter query.
9. Meanwhile, take a look at how the whole function looks like.
10. 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.
11. 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,

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

SEE HERE

 

button onclick="{this.sortData}">Sort Data

const currentWorksheet = context.workbook.worksheets.getActiveWorksheet();
const salaryTable = currentWorksheet.tables.getItem('salaryTable');

salaryTable.sort.apply(sortFields);

Read More: Comparative Analysis of Blazor, Angular, React, Vue and Node for Web development

 

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

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.

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

6. Let's start by getting the current worksheet and table.
7. 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).
8. 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,
           }
         ];
   
   

9. Subsequently, we use the sort and apply method on the table and pass the sortFields object.
10. Here is what the whole function might look like.
11. Run the code using the npm start command
12. 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.