Skip to main content

GatsbyJS

Gatsby is a framework for creating websites. It uses React components for page templates and GraphQL for loading data.

gatsby-transformer-excel is a transformer that generates GraphQL nodes for each row of each worksheet. The plugin is officially supported by the Gatsby team. The plugin documentation includes examples and more detailed usage instructions.

note

gatsby-transformer-excel is maintained by the Gatsby core team and all bugs should be directed to the main Gatsby project. If it is determined to be a bug in the parsing logic, issues should then be raised with the SheetJS project.

caution

gatsby-transformer-excel uses an older version of the library. It can be overridden through a package.json override in the latest versions of NodeJS:

{
"overrides": {
"xlsx": "https://cdn.sheetjs.com/xlsx-0.19.2/xlsx-0.19.2.tgz"
}
}

GraphQL details

gatsby-transformer-excel generates nodes for each data row of each worksheet. Under the hood, it uses sheet_to_json to generate row objects using the headers in the first row as keys.

pres.xlsx

Assuming the file name is pres.xlsx and the data is stored in "Sheet1", the following nodes will be created:

[
{ Name: "Bill Clinton", Index: 42, type: "PresXlsxSheet1" },
{ Name: "GeorgeW Bush", Index: 43, type: "PresXlsxSheet1" },
{ Name: "Barack Obama", Index: 44, type: "PresXlsxSheet1" },
{ Name: "Donald Trump", Index: 45, type: "PresXlsxSheet1" },
{ Name: "Joseph Biden", Index: 46, type: "PresXlsxSheet1" },
]

The type is a proper casing of the file name concatenated with the sheet name.

The following query pulls the Name and Index fields from each row:

{
allPresXlsxSheet1 { # "all" followed by type
edges {
node { # each line in this block should be a field in the data
Name
Index
}
}
}
}

GatsbyJS Demo

note

This demo was tested on 2022 November 11 against [email protected]. The generated project used [email protected] and [email protected].

Project setup

1) Run npm init gatsby -- -y sheetjs-gatsby to create the template site.

2) Follow the on-screen instructions for starting the local development server:

cd sheetjs-gatsby
npm run develop

Open a web browser to the displayed URL (typically http://localhost:8000/)

3) Edit package.json and add the highlighted lines in the JSON object:

{
"overrides": {
"xlsx": "https://cdn.sheetjs.com/xlsx-0.19.2/xlsx-0.19.2.tgz"
},
"name": "sheetjs-gatsby",
"version": "1.0.0",

4) Install the library and plugins:

npm i --save https://cdn.sheetjs.com/xlsx-0.19.2/xlsx-0.19.2.tgz
npm i --save gatsby-transformer-excel gatsby-source-filesystem

5) Edit gatsby-config.js and add the following lines to the plugins array:

  plugins: [
{
resolve: `gatsby-source-filesystem`,
options: {
name: `data`,
path: `${__dirname}/src/data/`,
},
},
`gatsby-transformer-excel`,
],

Stop and restart the development server process (npm run develop).

6) Make a src/data directory, download https://sheetjs.com/pres.xlsx, and move the downloaded file into the new folder:

mkdir -p src/data
curl -L -o src/data/pres.xlsx https://sheetjs.com/pres.xlsx

GraphiQL test

7) Open the GraphiQL editor at http://localhost:8000/___graphql.

There is an editor in the left pane. Paste the following query into the editor:

{
allPresXlsxSheet1 {
edges {
node {
Name
Index
}
}
}
}

Press the Execute Query button and data should show up in the right pane:

GraphiQL Screenshot

React page

8) Create a new file src/pages/pres.js that uses the query and displays the result:

src/pages/pres.js
import { graphql } from "gatsby"
import * as React from "react"

export const query = graphql`query {
allPresXlsxSheet1 {
edges {
node {
Name
Index
}
}
}
}`;

const PageComponent = ({data}) => {
return ( <pre>{JSON.stringify(data, 2, 2)}</pre> );
};
export default PageComponent;

After saving the file, access http://localhost:8000/pres. The displayed JSON is the data that the component receives:

{
"allPresXlsxSheet1": {
"edges": [
{
"node": {
"Name": "Bill Clinton",
"Index": 42
}
},
// ....

9) Change PageComponent to display a table based on the data:

src/pages/pres.js
import { graphql } from "gatsby"
import * as React from "react"

export const query = graphql`query {
allPresXlsxSheet1 {
edges {
node {
Name
Index
}
}
}
}`;

const PageComponent = ({data}) => {
const rows = data.allPresXlsxSheet1.edges.map(r => r.node);
return ( <table>
<thead><tr><th>Name</th><th>Index</th></tr></thead>
<tbody>{rows.map(row => ( <tr>
<td>{row.Name}</td>
<td>{row.Index}</td>
</tr> ))}</tbody>
</table> );
};

export default PageComponent;

Going back to the browser, http://localhost:8000/pres will show a table:

Data in Table

Live refresh

10) Open the file src/data/pres.xlsx in Excel or LibreOffice or Numbers. Add a new row at the end of the file:

New Row in File

Save the file and notice that the table has refreshed with the new data:

Updated Table

Static site

11) Stop the development server and run npm run build. Once the build is finished, the display will confirm that the /pres route is static:

Pages

┌ src/pages/404.js
│ ├ /404/
│ └ /404.html
├ src/pages/index.js
│ └ /
└ src/pages/pres.js
└ /pres/

╭────────────────────────────────────────────────────────────────╮
│ │
│ (SSG) Generated at build time │
│ D (DSG) Deferred static generation - page generated at runtime │
│ ∞ (SSR) Server-side renders at runtime (uses getServerData) │
│ λ (Function) Gatsby function │
│ │
╰────────────────────────────────────────────────────────────────╯

The built page will be placed in public/pres/index.html. Open the page with a text editor and search for "SheetJS" to verify raw HTML was generated:

<tr><td>SheetJS Dev</td><td>47</td></tr>