React Read Excel and Data Visualize

Enes Akkaya — Mon, 18 Mar 2024 21:38:06 +0000

Hello everyone, I will tell you Excel data read and fetched data visualization in this article. We will use xlsx and tremor.so libraries and in this article after, you can create interactive components.

First, I will install the requriment packages and create a sample Excel Data. In addition, we will install React via Vite and write code JSX.

npm create vite@latest

And, starting "npm install" command also, We installed React via Vite!

Okay, let's install the xlsx and tremor.so libraries;

xlsx; You can read Excel and convert to json objects.
tremor; You created objects of json, you can visualize or KPI Metric card via Tremor.

Install xlsx via NPM;
npm install xlsx

Install tremor.so via NPM;
npm install @tremor/react

And "tremor" also has package dependency with tailwindcss we will install tailwindcss.
npm install -D tailwindcss postcss autoprefixer npx tailwindcss init -p

At last, we finished installing the packages and let's start the project.
npm run dev

Before, We will create an xlsx file via Excel. For example, We have a company and company sales data on hand in addition this data want analysis and visualization.

And, We will create KPI metric cards' and inside add month change percentange.
In addition, We will do trend and visualization with company's sales.

I have sample data on Excel;

Now, We can read Excel data and convert json object. First, We can import libraries dependency. In addition We will usecustom hooks.

import { useEffect, useState } from "react"; import * as XLSX from "xlsx";

And, create file for custom hooks, custom hook will run below code.



const useFile = () => {
  // Returned Excel JSON datas keep.
  const [data, setData] = useState([]);

  useEffect(() => {
    // Fetch Excel file.
    fetch("./datas.xlsx")
      // Convert to ArrayBuffer.
      .then((res) => res.arrayBuffer())
      .then((data) => {
        const wb = XLSX.read(data, { type: "buffer" });

        const wsname = wb.SheetNames[0];
        const ws = wb.Sheets[wsname];

        // Convert to JSON.
        const json = XLSX.utils.sheet_to_json(ws);

        setData(json);
      });
  }, []);

  return data;
};

Now let's check the returned JSON data. We have done this and output the result to the console.

When I checked the output, we saw the image we wanted, but we should make some changes and the "tremor" library needs a data format.

Now we will change the object shown this in the picture. We will create a new component.



const useConverted = (value) => {
  const result = {};

  value.forEach(({ Month, Product, Amount, TotalPrice }) => {
    if (!result[Product]) {
      result[Product] = { fyAmount: 0, trend: {} };
    }

    if (!result[Product].trend[Month]) {
      result[Product].trend[Month] = { amount: 0, totalPrice: 0 };
    }

    result[Product].fyAmount += Amount;
    result[Product].trend[Month]["amount"] += Amount;
    result[Product].trend[Month]["totalPrice"] += TotalPrice;
  });

  const final = Object.keys(result).map((key) => {
    return { product: key, ...result[key] };
  });

  return final;
};

We created a new component and gave it component a custom hook. Finally, we completed the code output.

We have completed the sales data. And we begin the data visualization stage.
First, we will choose which line chart component to use. In addition, we will trend analysis, and can line chart.



<section className="p-10 flex justify-center items-center h-screen gap-3">
        {data.map((item) => {
          const trend = Object.keys(item.trend).map((key) => {
            return {
              date: key,
              ...item.trend[key],
            };
          });

          return (
            <Card key={crypto.randomUUID()}>
              <Title>{item.product} Product Sales Trend</Title>
              <LineChart
                className="mt-6"
                data={trend}
                index="date"
                categories={["totalPrice"]}
                colors={["emerald", "gray"]}
                yAxisWidth={40}
                curveType="monotone"
              />
            </Card>
          );
        })}
</section>

Finally, We created chart components at bellow.

Forem: Enes Akkaya

React Read Excel and Data Visualize