Import Excel data to your Nextjs application

Here, we will look into the process of importing Excel data into your 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 - a spreadsheet data parser and writer. 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.