Export Excel(XLSX) file in react with images and custom styles in ReactJs

#react #excel #images #styles

If you have ever worked with react and NodeJs. Then you have surely added the export excel file in any of your projects. Exporting an excel file might be easy but sometimes we have to export a more custom excel file with some custom styles as marking cells with different colors and sometimes we also need to add the images in the same file.

For this, you have different options to export the file whether in NodeJs or ReactJs. But, today we will discuss a way to export a feature-rich excel file in ReactJs. We are exporting it in ReactJs. So, it would be useful for both React and full-stack developers.

For this, we are going to use a third-party package - ExecelJs
Why ExcelJs- because it offers a lot of features and works with bulk data. I have tested it with a spreadsheet file with over 50000 records.
This package is used to read, manipulate and write spreadsheet data and styles to XLSX and JSON

We will understand the process by creating a react app by create-react-app.
We will add a free API to get data - https://dummyjson.com/products

In the app, we will first load the data from the API. We will get 30 product data with details of id, title, price, photo, etc. Then, we will create a table with product data id, title, price, thumbnail, etc.

Our primary focus is to export the same table's data in a spreadsheet(xlsx) file. The spreadsheet's data will look something like this

We have also added a conditional styling for the price if the price is between 50 and 1000 then the cell will be red colour.

Implementation

First, we will install the ExcelJs package in our project.

Then, we will call the API to get the data and set it in the state on the first-page load.

const [data, setData] = useState([]);
  useEffect(() => {
    fetch("https://dummyjson.com/products")
      .then((res) => res.json())
      .then(async (data) => {
        setData(data);
      })
      .then((json) => console.log(json));
  }, []);

Now, we will add a button as "Export" on top of the table, when someone clicks on this button it will create a spreadsheet and download it in the front end side.

Now we will create a new workbook with Exceljs.

const workbook = new ExcelJS.Workbook();

Then, we will add a sheet to this workbook

const sheet = workbook.addWorksheet("My Sheet");

Now, we have to add an image to every row in the spreadsheet. So, we will add some default height to every row in the sheet.

sheet.properties.defaultRowHeight = 80;

We have the first row of the sheet as the header of the sheet with different column titles. So we will add some styles to this as well ie. some borders, fonts, fill, etc.

sheet.getRow(1).border = {
      top: { style: "thick", color: { argb: "FFFF0000" } },
      left: { style: "thick", color: { argb: "000000FF" } },
      bottom: { style: "thick", color: { argb: "F08080" } },
      right: { style: "thick", color: { argb: "FF00FF00" } },
    };

    sheet.getRow(1).fill = {
      type: "pattern",
      pattern: "darkVertical",
      fgColor: { argb: "FFFF00" },
    };

    sheet.getRow(1).font = {
      name: "Comic Sans MS",
      family: 4,
      size: 16,
      bold: true,
    };

Now, we will define the columns, this will be an array of objects. And each object has three keys header, key, and width. The "header" is the title of the column, the "key" must be the same as the JSON key of data to map data in the column, and the "width" is the width of the column.

sheet.columns = [
      {
        header: "Id",
        key: "id",
        width: 10,
      },
      { header: "Title", key: "title", width: 32 },
      {
        header: "Brand",
        key: "brand",
        width: 20,
      },
      {
        header: "Category",
        key: "category",
        width: 20,
      },
      {
        header: "Price",
        key: "price",
        width: 15,
      },
      {
        header: "Rating",
        key: "rating",
        width: 10,
      },
      {
        header: "Photo",
        key: "thumbnail",
        width: 30,
      },
    ];

Now, it's time to add the data in each column with the same key name we have in column objects.

data?.products?.map((product) => {
        sheet.addRow({
          id: product?.id,
          title: product?.title,
          brand: product?.brand,
          category: product?.category,
          price: product?.price,
          rating: product?.rating,
        });
      })

The above code is to add simple data to the sheet. But for the images, we need to have a different approach.

const promise = Promise.all(
      data?.products?.map(async (product, index) => {
        const rowNumber = index + 1;
        sheet.addRow({
          id: product?.id,
          title: product?.title,
          brand: product?.brand,
          category: product?.category,
          price: product?.price,
          rating: product?.rating,
        });

        const result = await toDataURL(product?.thumbnail);
        const splitted = product?.thumbnail.split(".");
        const extName = splitted[splitted.length - 1];

        const imageId2 = workbook.addImage({
          base64: result.base64Url,
          extension: extName,
        });

        sheet.addImage(imageId2, {
          tl: { col: 6, row: rowNumber },
          ext: { width: 100, height: 100 },
        });
      })
    );

Here, we are calling an async method "toDataURL", in which we are passing the image URL and getting the base64 encoded image.

Then, we are getting the image extension in the next step ie. png, jpeg, etc.

Then, we will create the imageId2 object, by base64 image string and the image extension. And, in the next step, we pass it to the sheet.addImage() method with the "tl" key(column and row) and "ext" key(to define the height and width).

const toDataURL = (url) => {
  const promise = new Promise((resolve, reject) => {
    var xhr = new XMLHttpRequest();
    xhr.onload = function () {
      var reader = new FileReader();
      reader.readAsDataURL(xhr.response);
      reader.onloadend = function () {
        resolve({ base64Url: reader.result });
      };
    };
    xhr.open("GET", url);
    xhr.responseType = "blob";
    xhr.send();
  });

  return promise;
};

The above method is to convert an image from URL to base64 encoded.

In this method, we will call the URL and get the image data, and convert it to a base64 encoded image.

After this step, we will have a promise. Now, in the "then" block of the promise we will add the price column styles to fill the cell red if the price is between 50 and 1000.

promise.then(() => {
      const priceCol = sheet.getColumn(5);

      // iterate over all current cells in this column
      priceCol.eachCell((cell) => {
        const cellValue = sheet.getCell(cell?.address).value;
        // add a condition to set styling
        if (cellValue > 50 && cellValue < 1000) {
          sheet.getCell(cell?.address).fill = {
            type: "pattern",
            pattern: "solid",
            fgColor: { argb: "FF0000" },
          };
        }
      });
    });

Now, in the last step, we use the workbook writeBuffer method to create the xlsx file and download it as below.

workbook.xlsx.writeBuffer().then(function (data) {
        const blob = new Blob([data], {
          type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        });
        const url = window.URL.createObjectURL(blob);
        const anchor = document.createElement("a");
        anchor.href = url;
        anchor.download = "download.xlsx";
        anchor.click();
        window.URL.revokeObjectURL(url);
      });

In the above code snippet we are getting data after the promise is resolved, then we will create it to buffer and define the type of the file ie. spreadsheet.

Then we are creating a temporary anchor to download the file by giving the file name "download.xlsx". And we will click this anchor programmatically and download the file and revoke the anchor in the next step as well.

Now finally the download function and price styling step will look something like this

promise.then(() => {
      const priceCol = sheet.getColumn(5);

      // iterate over all current cells in this column
      priceCol.eachCell((cell) => {
        const cellValue = sheet.getCell(cell?.address).value;
        // add a condition to set styling
        if (cellValue > 50 && cellValue < 1000) {
          sheet.getCell(cell?.address).fill = {
            type: "pattern",
            pattern: "solid",
            fgColor: { argb: "FF0000" },
          };
        }
      });

      workbook.xlsx.writeBuffer().then(function (data) {
        const blob = new Blob([data], {
          type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        });
        const url = window.URL.createObjectURL(blob);
        const anchor = document.createElement("a");
        anchor.href = url;
        anchor.download = "download.xlsx";
        anchor.click();
        window.URL.revokeObjectURL(url);
      });
    });

GitHub Link- https://github.com/sumankalia/export-xlsx-react

Live demo- https://63da86a84679e413b00fd738--dreamy-pithivier-43351e.netlify.app/

Youtube video link in Hindi -