Skip to main content Skip to footer

How to Import and Export Excel XLSX Using React

Many enterprise React applications require a familiar spreadsheet UI with the additional need to import and export data to and from Excel .xslx files, including pretty complex in-house spreadsheets that the company has been using for years.

React and Spreadsheets

React is currently one of the most popular front-end library/frameworks. It’s rising adoption in the last few years has been carried out by everyone from small start-ups to large Fortune 500 companies. React is increasingly being used in corporations that invest heavily in technology, productivity suites, and tailor-made line-of-business apps.

Corporate developers often turn to commercially available options for these specialized productivity components like spreadsheets and reporting for their ease of use, extensive feature set, commercial support, and general dependability. SpreadJS provides React developers with advanced spreadsheet functionality and UI familiarity they need to create any business application.

Let's Get Started

In this walkthrough, we’re going to evolve the static table approach by showing you how to take an existing React app and convert that table to use a spreadsheet to gain the added benefits that come along with it. Then, we'll take advantage of the built-in Excel import/export functionality of SpreadJS to make the app even better by loading and saving the data to Excel .xlsx files. We do assume that you already have basic knowledge of ReactJS so let's get started!

Be sure to try out all our features in our demos, and download a trial of SpreadJS today!


Adding Excel Import and Export Capabilities to Your React App

  1. Add the SpreadJS Spreadsheet
  2. Using Static Code
  3. Convert from Static Code to Spreadsheet UI
  4. Add Reactive Binding to the Spreadsheet
  5. Adding Excel Export Features to a React Application
  6. Adding Excel Import Features to a React Application
    1. Bonus Tip - How to Import Excel from a URL

Step 1: 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.

If you want the full-fledged app with SpreadJS already added, download this sample.

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

> npm install

Now you’ll see the updated app in action.


Step 2: Static Code

Static Code

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, originating 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 totalSales, chartData, and personSales functions take in raw sales data. Then they provide the calculations for the TotalSales, SalesByCountry, 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 at the bottom of the page is responsible for statically displaying sales details.

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


Step 3: 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, a generic UI wrapper we use 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 Spreadsheet 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:

Spreadsheet

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

Within the Worksheet component, 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, not the sales data declared in the Dashboard component. Events and functions should handle any data modification 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 4: Spreadsheet with Reactive Binding

Currently, the sales constant declared in the Dashboard.js file 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 we wish to implement. So, we'll replace that line of code with an assignment called a hook. In React, hooks have a simplified syntax that provides the declaration of the state value and the handler function simultaneously.

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 in just one line.

However, this useState function does not exist in our application yet. We need to import it from the React package at the beginning of the Dashboard.js 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 in the SalesTable.js file.

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:

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

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 executes the handleValueChangedfunction 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 application's other components.

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

You can try this out by editing some of the values for the sales and seeing the sales numbers at the top of the dashboard change:

Spreadsheet

It looks like Bill made some great sales!


Step 5: Adding Excel Export Features to a React Application

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 on top of React and SpreadJS. But at some point, they will miss the integration between Excel and your excellent 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 { IO } from "@grapecity/spread-excelio";
import { saveAs } from 'file-saver';

Next, we'll change the JSX code of the 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 IO();
    const json = JSON.stringify(spread.toJSON({ 
        includeBindingSource: true,
        columnHeadersAsFrozenRows: true,
    }));
    excelIO.save(json, (blob) => {
        saveAs(blob, fileName);
    }, function (e) {  
        alert(e);  
    });     
}

Note how the above function requires a spread object, which must be the same instance as the SpreadJS worksheet we use 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 the 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({});

We need to import useState function into the React declaration at the beginning of the SalesTable.js 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:

Export to Excel


Step 6: Adding Excel Import Features to a React Application

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

We need to implement the event handler for the saved file on the Dashboard component. 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}/>

Import Excel File

Bonus Tip - How to Import Excel (.xlsx) from a URL: 

Users of SpreadJS can import Excel (.xlsx) files from a URL. To accomplish this, developers can use an API to transfer data between a web browser and a web server, returning the Excel file's data from the specified URL as a blob – the below example uses the Fetch API. Next, using the SpreadJS API, convert the blob to a JSON string and load the spreadsheet instance from that JSON string.

loadFromURL(e){
        let spread = this.spread;
        let excelIo = new IO();

        var url = document.getElementById("importURL").value;
        fetch(url)
          .then((res) => res.blob()) // Returns URL Excel file’s data as a blob
          .then((blob) => {             
            excelIo.open( // The ExcelIO open method converts the blob to a JSON string
              blob,
              (json) => {
                spread.fromJSON(json); // Loads the spreadsheet instance with the JSON string created from the ExcelIO open method
              },
              (message) => {
                console.log(message);
              }
            );
          });
        }


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. Finally, you review your client’s request and verify that your app satisfies all the requirements!

We could expand on these ideas and explore other exciting 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.

Check out some of the other benefits of using the SpreadJS Spreadsheet:

  • Powerful Calculation Engine With 500+ 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.
  • 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, etc.
  • Data Presentation Controls: Take advantage of tables, filtering, conditional formatting, and shapes to offer an expanded Excel-like experience.

Learn more about this JavaScript Spreadsheet Component:

An extensive list of the features of SpreadJS and live demos can be found here: SpreadJS Demo Samples

Be sure to download a trial of SpreadJS today and try it for yourself!

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

Happy Coding!

comments powered by Disqus