dev-resources.site

for different kinds of informations.

Import Excel/datasheet data to your Nextjs application

Published at

7/17/2024

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.

import Article's

30 articles in total

Bootcamping 02: Named exports and default exports - does it really matter?