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.

After selecting the project, choose TypeScript as your 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.

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.

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.

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<appprops> {
  constructor(props, context) {
    super(props, context);
    this.state = {
      listItems: [],
    };
  }

  render() {
    const { title, isOfficeInitialized } = this.props;

    if (!isOfficeInitialized) {
      return (

      );
    }

    return (
        <>
         <button>Generate Table</button>

    );
  }
}

</appprops> 

Create one event handler function for the button which will contain the logic for creating a new table.

<appprops><button onclick="{this.handleCreateTable}">Generate Table</button>

</appprops>

Excel.js business logic will be added to the handleCreateTable function that is passed to Excel.run method.
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:

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.

<button>Filter Data</button>

<button onclick="{this.filterData}">Filter Data</button>

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:

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,

<button>Sort Data</button>

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

<button onclick="{this.sortData}">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);
    });
  }   

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

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.