Import Excel/datasheet data to your Nextjs application

Here, we will look into the process of importing Excel data into your nextjs application and show it in the way you want. I'll keep it crisp and to the point. So let's dive into the process-

1. Create the Nextjs project

To create a Nextjs project use the command -
npx create-next-app excel-next

And to run the application
npm run dev

2. Node module for Excel import

The node module we will use to import Excel data is xlsx. To import the module run command -

npm install xlsx

3. Create a file to import and show Excel data

To get the data we will make an Axios fetch call to the sheet url-

const options = { url, responseType: "arraybuffer", };
let axiosResponse = await axios(options);

4. Read Workbook

To get the data in readable form we will use xlsx. read attempts to parse data and return a workbook object.

const workbook = XLSX.read(axiosResponse.data)

5. Simplify and show data

We are getting all the sheets from our excel in this workbook data. So let's sort out sheet and data in simplified object.

 let worksheets = workbook.SheetNames.map((sheetName) => {
       return {
         sheetName,
         data: XLSX.utils.sheet_to_json(workbook.Sheets[sheetName]),
       };
     });

 console.log("json:\n", JSON.stringify(worksheets), "\n\n");

Output:

Here you can notice the sheet name and the corresponding data. Also, the first object of the data is the title of the rows. So, while showing the data we will slice the array.

 {sheetData &&
    sheetData.map((sheet) => (
      <>
       <p>{sheet?.sheetName}</p>
       <Table dataSource={sheet?.data?.slice(1)} columns={columns} />
      </>
 ))}

For convenience adding the combined code.

import { Col, Row, Table } from "antd";
import { useEffect, useState } from "react";

export default function MyNextJsExcelSheet() {
  const axios = require("axios");
  const XLSX = require("xlsx");
  const [sheetData, setSheetData] = useState<any>([]);

  const testAxiosXlsx = async (url) => {
    const options = {
      url,
      responseType: "arraybuffer",
    };
    let axiosResponse = await axios(options);
    const workbook = XLSX.read(axiosResponse.data);

    let worksheets = workbook.SheetNames.map((sheetName) => {
      return {
        sheetName,
        data: XLSX.utils.sheet_to_json(workbook.Sheets[sheetName]),
      };
    });
    setSheetData(worksheets);
    console.log("json:\n", JSON.stringify(worksheets), "\n\n");
  };
  const validate = () => {
    testAxiosXlsx(
      "https://docs.google.com/spreadsheets/d/1arazUp1Aq9WeNMYDAK8d4_kz8YpwcHv1UdxMJKFOUIk/edit?usp=sharing"
    );
  };

  const columns = [
    {
      title: "Segment",
      dataIndex: "A",
      key: "Segment",
    },
    {
      title: "Country",
      dataIndex: "B",
      key: "Country",
    },
    {
      title: "Product",
      dataIndex: "C",
      key: "Product",
    },
    {
      title: "Units Sold",
      dataIndex: "D",
      key: "Units Sold",
    },
    {
      title: "Manufacturing Price",
      dataIndex: "E",
      key: "Manufacturing Price",
    },
    {
      title: "Sale Price",
      dataIndex: "F",
      key: "Sale Price",
    },
  ];

  useEffect(() => validate(), []);
  return (
    <div>
        <Col lg={12}>
          <h3>The Data of The Uploaded Excel Sheet</h3>
        </Col>
        <Col lg={24}>
          {sheetData &&
            sheetData.map((sheet) => (
              <>
                <p>{sheet?.sheetName}</p>
                <Table dataSource={sheet?.data?.slice(1)} columns={columns} />
              </>
            ))}
        </Col>
    </div>
  );
}

With these steps, you're now equipped to seamlessly integrate Excel data into your Next.js application, opening up a world of possibilities for dynamic and data-driven web experiences.

Blog

Import Excel/datasheet data to your Nextjs application

Shagun Bidawatka

1. Create the Nextjs project

2. Node module for Excel import

3. Create a file to import and show Excel data

4. Read Workbook

5. Simplify and show data

Join Our Newsletter. No Spam, Only the good stuff.

Related