Search

Adding Excel Import and Export to a React App

Spreadsheets in React Apps

You are a software engineer working for a small software vendor. One day, your company receives an email from a big client requesting a proposal for a new web application. It requires the application to be written in ReactJS and includes some screen mockups featuring spreadsheets with Excel import/export capabilities. The app needs to mimic the behavior of Excel spreadsheets.

Let’s look at the client’s motivations for the request. React is currently the most popular front-end library/framework. Its rising adoption in the last few years is carried out by everyone from IT students to big business companies. React is increasingly being used in corporations that invest heavily in technology, productivity suites, and tailor-made line-of-business apps.

Corporate users work not only with specialized business applications, but also with general-purpose tools like spreadsheets, charts, and reporting apps. While adopting ReactJS, companies often look for third-party vendors to provide productivity tools with features that are familiar to their employees or clients. However, these users often end up having to work with apps that don’t deliver the user experience expected for their daily needs.

Spreadsheet Components for React

SpreadJS provides React developers with spreadsheet functionality that would be difficult to find anywhere else. There are so many line-of-business apps that would benefit from having an interactive spreadsheet embedded instead of just using a boring, static table — yet those boring tables are what business apps end up with because developers don’t realize something better is possible.

In this walkthrough, we assume that you already have basic knowledge on ReactJS, and we’re going to evolve the static table approach by showing how to take an existing React app and add a spreadsheet. Then, we’re going to take advantage of the built-in Excel import/export functionality of SpreadJS to make the app even better.

Before we dive into coding, let’s take a look at some of the SpreadJS highlights:

Powerful calculation engine with 450+ functions

This high-speed, optimized engine adds computing power to even the most complex formulas without dragging down your apps, includes more functions than any other spreadsheet component, and supports custom functions.

Import/export Excel files in JavaScript spreadsheet

No JavaScript spreadsheet is complete without full Excel import/export support! Import your Excel spreadsheets and lose little functionality—and then export your SpreadJS spreadsheet to XLSX.

Visualize data with charts and sparklines

Offer insight into your data with interactive charts and sparklines. With most Excel charts, interactions, and animations, your dashboards and analytical spreadsheets will come to life.

Built-in globalization

Select from available cultures, or create your own custom culture and adjust date masks, languages, number separators, currencies, and more.

Data presentation controls

Take advantage of tables, filtering, conditional formatting, and shapes to offer an expanded Excel-like experience.

Adding SpreadJS to Your React App

You can see the static table app running live on StackBlitz and the demo source can be found here.

Alternatively, you can download or clone a copy of the code from the GitHub repository GitHub repository .

Once you’ve done that, open a terminal, navigate to the directory where you cloned the repository and run:

				
					> npm install
				
			

Note that we’ve switched to developing locally with an app based on a Create React App template because the trial version of SpreadJS won’t work on StackBlitz – it must be served from localhost.

Now you’ll see the updated app in action.

Step 1: Static Code

Adding Excel Import and Export to a React App

The front end of this application was built on ReactJS and structured in components created with JSX syntax, a combination of JavaScript and HTML code. The application was created using the syntax of functional components. This approach allows us to avoid writing classes, which could make components more complex and difficult to read.

The Dashboard stands on top of the JSX component hierarchy. It renders HTML content and maintains the application state, which originates from a file with dummy JSON sales data.

Each of the child components is responsible for rendering its contents. Since only the Dashboard holds the app state, it passes the data down to each child component through props.

				
					import React, { useState } from 'react';
import { NavBar } from './NavBar'
import { TotalSales } from './TotalSales'
import { SalesByCountry } from './SalesByCountry'
import { SalesByPerson } from './SalesByPerson'
import { SalesTable } from './SalesTable'
import { groupBySum } from "../util/util";
import { recentSales } from "../data/data";
export const Dashboard = () => {
    const sales = recentSales;
    function totalSales() {
      const items = sales;
      const total = items.reduce(
        (acc, sale) => (acc += sale.value),
        0
      );
      return parseInt(total);
    };
    function chartData() {
      const items = sales;
      const groups = groupBySum(items, "country", "value");
      return groups;
    };
    function personSales() {
      const items = sales;
      const groups = groupBySum(items, "soldBy", "value");
      return groups;
    };
    function salesTableData() {
      return sales;
    };
    return (
        <div style={{ backgroundColor: '#ddd' }}>
            <NavBar title="Awesome Dashboard" />
            <div className="container">
                <div className="row">
                    <TotalSales total={totalSales()}/>
                    <SalesByCountry salesData={chartData()}/>
                    <SalesByPerson salesData={personSales()}/>
                    <SalesTable tableData={salesTableData()}/>
                </div>
            </div>
        </div>
    );
}
				
			

As you can see, data from the SalesTable component displays the individual sales, obtained directly from the data in the JSON file. First, the totalSaleschartData and personSales functions take in raw sales data. Then they provide the calculations for the TotalSalesSalesByCountry and SalesByPerson panel results, respectively.

The JSX syntax allows you to return HTML content directly from code without having to concatenate strings. We can see how the props are inserted into the HTML segments inside curly braces and passed to each component as static data. The SalesTable panel you see at the bottom of the page is responsible for displaying sales details statically.

In the next step, you will need to replace the SalesTable static grid with an interactive worksheet.

Step 2: From Static to SpreadSheet

Before writing any line of code, we must first install GrapeCity’s Spread.Sheets Wrapper Components for React. Just stop the app, and run these two commands:

				
					> npm install @grapecity/spread-sheets-react
> npm start
				
			

Before using SpreadJS, you must modify the SalesTable.js file to declare the imports of the GrapeCity components. These Imports will enable access to the SpreadSheets, Worksheet and Column objects of the SpreadJS library.

				
					import React from 'react';
import { TablePanel } from "./TablePanel";
// SpreadJS imports
import '@grapecity/spread-sheets-react';
/* eslint-disable */
import "@grapecity/spread-sheets/styles/gc.spread.sheets.excel2016colorful.css";
import { SpreadSheets, Worksheet, Column } from '@grapecity/spread-sheets-react';
				
			

Besides, a SpreadJS worksheet doesn’t work properly without a few basic settings, so let’s create a config object to hold the worksheet parameters.

				
					export const SalesTable = ({ tableData } ) => {
    const config = {
        sheetName: 'Sales Data',
        hostClass: ' spreadsheet',
        autoGenerateColumns: false,
        width: 200,
        visible: true,
        resizable: true,
        priceFormatter: '$ #.00',
        chartKey: 1
    }
				
			

First, we’ll have to eliminate the JSX code that renders the static panel inside the SalesTable component:

				
					return (
    <TablePanel title="Recent Sales">
        <table className="table">
            <thead>
            <tr>
                <th>Client</th>
                <th>Description</th>
                <th>Value</th>
                <th>Quantity</th>
            </tr>
            </thead>
            <tbody>
            {tableData.map((sale) =>
            (<tr key={sale.id}>
                <td>{sale.client}</td>
                <td>{sale.description}</td>
                <td>${sale.value}</td>
                <td>{sale.itemCount}</td>
            </tr>))}
            </tbody>
        </table>
    </TablePanel>
);
				
			

By eliminating this code block, we end up with the TablePanel alone, which is just a generic UI wrapper that we are using in each component.

				
					return (
    <TablePanel title="Recent Sales">
    </TablePanel>
);
				
			

At this point, we can now insert the SpreadJS SpreadSheets component inside the TablePanel. Note that a SpreadSheets component may contain one or more Worksheets, just like an Excel workbook may contain one or more worksheets.

				
					return (
    <TablePanel key={config.chartKey} title="Recent Sales">
        <SpreadSheets hostClass={config.hostClass}>
            <Worksheet name={config.sheetName} dataSource={tableData} autoGenerateColumns={config.autoGenerateColumns}>
                <Column width={50} dataField='id' headerText="ID"></Column>
                <Column width={200} dataField='client' headerText="Client"></Column>
                <Column width={320} dataField='description' headerText="Description"></Column>
                <Column width={100} dataField='value' headerText="Value" formatter={config.priceFormatter} resizable="resizable"></Column>
                <Column width={100} dataField='itemCount' headerText="Quantity"></Column>
                <Column width={100} dataField='soldBy' headerText="Sold By"></Column>
                <Column width={100} dataField='country' headerText="Country"></Column>                   
            </Worksheet>
        </SpreadSheets>
    </TablePanel>
);
				
			

As a finishing touch, we’ll add these lines below to the App.css file, to fix the Spreadsheets dimensions so that this component takes up the full width of the bottom panel and the appropriate height for the Sales Dashboard page.

				
					/*SpreadJS Spreadsheet Styling*/ 
.container.spreadsheet {
    width: 100% !important;
    height: 400px !important;
    border: 1px solid lightgray !important;
    padding-right: 0;
    padding-left: 0;
}
				
			

And… voilà! This gives us the stunning spreadsheet below:

Adding Excel Import and Export to a React App

Note how the SpreadJS worksheet gives us the same look and feel as would an Excel spreadsheet.

Within the worksheet, we can see the Column components, which define the characteristics of each column, such as width, binding field and header text. We’ve also added currency formatting to the sales value column.

Like the old static table, the new SpreadJS Spreadsheets component receives data from the props passed down from the Dashboard. As you can see, the spreadsheet allows you to change values directly, just as you would in an Excel spreadsheet. However, as you would expect from a React application, these changes are not reflected automatically in the other components.

Why is that so?

After receiving the data from the Dashboard, a SpreadJS worksheet begins working with a copy and not with the sales data declared in the Dashboard component. Any data modification should be handled by events and functions to update the app’s state accordingly.

For the next task, you’ll have to make the application reflect the changes made to the SpreadJS worksheet on all Dashboard components.

Step 3: Spreadsheet with Reactive Binding

Currently, the sales constant declared in the Dashboard is responsible for maintaining the state of the application.

				
					const sales = recentSales;
				
			

As we can see, this construction implies static data, preventing the dynamic updates that we wish to implement. So, we’ll replace that line of code with an assignment called hook. In React, hooks have a simplified syntax that provides the declaration of both the state value and the handler function at the same time.

				
					const[sales, setSales] = new useState(recentSales);
				
			

The code line above shows the JavaScript array destructuring syntax. The useState function is used to declare the sales constant, which holds the state data, and setSales, which references the function that changes the sales array. All in just one line.

However, this useState function does not exist in our application yet. We need to import it from react package at the beginning of the component file:

				
					import React, { useState } from 'react';
				
			

Now, we are ready to update the state of the sales array when necessary.

We want to propagate changes made to the worksheet over the rest of the Dashboard. So, we have to subscribe to an event to detect the changes made to the Worksheet component cells and implement the corresponding event handling.

Let’s call this event handler handleValueChanged.

				
					<SpreadSheets hostClass={config.hostClass} valueChanged={handleValueChanged}>
				
			

 

We still need to implement a function with the same name. Inside it, we get the worksheet’s data source array that was changed and pass this array to a function named valueChangeCallback.

				
					function handleValueChanged(e, obj) {
    valueChangedCallback(obj.sheet.getDataSource());
}
handleValueChanged.bind(this);
				
			

The valueChangedCallback function is then passed down from the Dashboard to the SalesTable component:

Now, you have to pass this callback function as a parameter for the SalesTable component:

				
					export const SalesTable = ({ tableData, valueChangedCallback } ) => {
				
			

Any changes to a cell in the worksheet trigger the callback function, which in turn executes the handleValueChanged function in the Dashboard component. The handleValueChanged function below must be created in the Dashboard component. It calls the setSales() function, which updates the state of the component. As a result, the change propagates over the other components of the application.

				
					    function handleValueChanged(tableData) {
        setSales(tableData.slice(0));
    }
				
			

Step 4: Adding Excel Import/Export Features

So far, we’ve seen how to replace the static sales table with the SpreadJS spreadsheet. We also learned how to propagate the data update over application components through React‘s hooks and callbacks. And we managed to provide these features with very little code. Your application already looks awesome, and you feel confident it will impress your future client. But before that, let’s put the icing on the cake.

You already know your corporate users often work with Excel in their day-to-day lives. The same users will start using your brand new app made on top of React and SpreadJS. But at some point, they will miss the integration between Excel and your awesome dashboard.

The application would be much more powerful if you only could export spreadsheet data to Excel, and import data from Excel into SpreadJS. How can you implement these features?

Let’s stop the app once again and install GrapeCity’s Spread.Sheets Client-Side Excel IO package, along with the file saver package:

				
					> npm install @grapecity/spread-excelio
> npm install file-saver
> npm start
				
			

To export data from our application to an Excel file (.xlsx extension), we have to modify the SalesTable component, declaring the import for the Excel.IO and the file-saver components.

				
					import Excel from "@grapecity/spread-excelio";
import { saveAs } from 'file-saver';
				
			

Next, we’ll change the JSX code of SalesTable.js file to add a button to export the SpreadJS worksheet data to a local file. Clicking that button will trigger an event handler called exportSheet.

				
					    .
    .
    .
    {/* EXPORT TO EXCEL */}
    <div className="dashboardRow">
        <button className="btn btn-primary dashboardButton" 
          onClick={exportSheet}>Export to Excel</button>
    </div>
</TablePanel>
				
			

The exportSheet function, in turn, will save the data from the worksheet to a file named SalesData.xslx. This function starts by serializing data from the Spread object into JSON format and then converting it into an Excel format through an Excel.IO object.

				
					function exportSheet() {
    const spread = _spread;
    const fileName = "SalesData.xlsx";
    const sheet = spread.getSheet(0);
    const excelIO = new Excel.IO();
    const json = JSON.stringify(spread.toJSON({ 
        includeBindingSource: true,
        columnHeadersAsFrozenRows: true,
    }));
    excelIO.save(json, (blob) => {
        saveAs(blob, fileName);
    }, function (e) {  
        alert(e);  
    });     
}
				
			

But wait. Note how the above function requires a spread object, which must be the same instance of the SpreadJS worksheet we are using in the SalesTable component. Once the SpreadSheet object is defined, the getSheet(0) call in the listing above retrieves the first worksheet in the spreadsheets array:

				
					const sheet = spread.getSheet(0);
				
			

But how can we obtain the instance of SpreadSheet programmatically?

Once the SpreadSheet object is initialized, the SpreadJS library triggers an event named workbookInitialized. We must handle it and store the instance as a state of the SalesTable component. Let’s first declare a state const for the spreadsheet instance with the useState hook:

				
					const [_spread, setSpread] = useState({});
				
			

But, once again, the useState function doesn’t come from nowhere. We need to import it into the React declaration at the beginning of the SalesTable component file:

				
					import React, { useState } from 'react';
				
			

Now we can declare a function to handle the workbookInit event…

				
					function workbookInit(spread) {
    setSpread(spread)
}
				
			

… and then bind the workbookInit event to the function we just created:

				
					<SpreadSheets hostClass={config.hostClass} workbookInitialized={workbookInit} valueChanged={handleValueChanged}>
				
			

Now, the Export To Excel button will look like this:

Adding Excel Import and Export to a React App

Now let’s demonstrate how to implement Excel data import. This process is the inverse of export, so let’s start with the XLSX file.

The access point for this functionality is another button, which we need to add to the end of the SalesStable component’s JSX code. Note that here we use a different button type: an input element of the “file” type, which produces a button that selects a file. When the file is selected, the onChange event fires the fileChange event handler:

				
					<div className="dashboardRow">
    {/* EXPORT TO EXCEL */}
    <button className="btn btn-primary dashboardButton" 
      onClick={exportSheet}>Export to Excel</button>
    {/* IMPORT FROM EXCEL */}
    <div>
        <b>Import Excel File:</b>
        <div>
            <input type="file" className="fileSelect" 
              onChange={(e) => fileChange(e)} />
        </div>
    </div>
</div>
				
			

The fileChange function, in turn, will use the Excel.IO object to import the file into the sheet object. At the end of the function, a fileImportedCallback event is triggered to bring the data to the Dashboard component:

				
					function fileChange(e) {
    if (_spread) {
        const fileDom = e.target || e.srcElement;
        const excelIO = new Excel.IO();
        const spread = _spread;
        const deserializationOptions = {
            frozenRowsAsColumnHeaders: true
        };
        excelIO.open(fileDom.files[0], (data) => {
            const newSalesData = extractSheetData(data);
            fileImportedCallback(newSalesData);
        });
    }
}
				
			

But this callback needs to be declared as a parameter of the SalesTable component:

				
					export const SalesTable = ({ tableData, valueChangedCallback, 
    fileImportedCallback } ) => {
				
			

Also, we must provide the SalesTable component with the extractSheetData function by importing it from the util.js file:

				
					import { extractSheetData } from "../util/util.js";
				
			

On the Dashboard component, we need to implement the event handler for the saved file. The only thing this function does is update the state of the Dashboard with the data coming from the SpreadJS worksheet.

				
					function handleFileImported(newSales) {
    setSales(newSales.slice(0));
}

<SalesTable tableData={salesTableData()} 
    valueChangedCallback={handleValueChanged}
    fileImportedCallback={handleFileImported}/>
				
			

Adding Excel Import and Export to a React App

In just a few simple steps, we’ve turned our boring app with static data into a reactive app centered around a spreadsheet with Excel import and export functionality. In the end, you review your client’s request and verify that your app satisfies all the requirements!

We could expand on these ideas and explore other interesting features for our application. For example, we can automatically and silently save our worksheet data, thus keeping a change log and rollback mistakes made to the table when needed. Also, you could use SpreadJS events to synchronize the tabular data with a remote database. Or you could implement a Save button to replicate table data to an external system through web service methods.

In another article series, we demonstrate how to import and export Excel Spreadsheets in your WinForms app using C#, import/export Excel spreadsheets using JavaScript and how to import and export Excel spreadsheets in Angular.

If you have any questions or comments, be sure to leave them below.

Happy Coding!

This article first appeared on GrapeCity’s Blog.

If you’re interested in developing expert technical content that performs, let’s have a conversation today.

 

Facebook
Twitter
LinkedIn
Reddit
Email

POST INFORMATION

If you work in a tech space and aren’t sure if we cover you, hit the button below to get in touch with us. Tell us a little about your content goals or your project, and we’ll reach back within 2 business days. 

Share via
Copy link
Powered by Social Snap