DEV Community

Cover image for How To Export Data To Excel From Api Using React (incl. Custom Headers)
Jasur Kurbanov
Jasur Kurbanov

Posted on

How To Export Data To Excel From Api Using React (incl. Custom Headers)

In my previous post, about exporting data to Excel from api using React, there were comments like how to add custom header and style to the sheet!?. So considering these comments, in this post I decided to show solution for first issue which is adding custom headers to your Excel sheet.

In this post two ways of adding custom headers will be shown

First way

Setup

Create a new project

npx create-react-app react-data-to-excel
Enter fullscreen mode Exit fullscreen mode

Run project locally

npm start
Enter fullscreen mode Exit fullscreen mode

How To Export Data To Excel From Api Using React (incl. Custom Headers)

Let's dive into next step.

Install libraries

For this project we need to install following libraries:

npm install xlsx file-saver axios
Enter fullscreen mode Exit fullscreen mode

xlsx - library for parsing and writing various spreadsheet formats
file-saver - library for saving files on the client-side
axios - promise based HTTP client for the browser and node.js. We will use it for fetching data from server

Components

Inside your project create component ExportToExcel.js

import React from 'react'
import * as FileSaver from "file-saver";
import * as XLSX from "xlsx";

export const ExportToExcel = ({ apiData, fileName }) => {
  const fileType =
    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8";
  const fileExtension = ".xlsx";

  const exportToCSV = (apiData, fileName) => {
    const ws = XLSX.utils.json_to_sheet(apiData);
    const wb = { Sheets: { data: ws }, SheetNames: ["data"] };
    const excelBuffer = XLSX.write(wb, { bookType: "xlsx", type: "array" });
    const data = new Blob([excelBuffer], { type: fileType });
    FileSaver.saveAs(data, fileName + fileExtension);
  };

  return (
    <button onClick={(e) => exportToCSV(apiData, fileName)}>Export</button>
  );
};
Enter fullscreen mode Exit fullscreen mode

Update your App.js

import React from 'react'
import axios from 'axios'
import './App.css';

import {ExportToExcel} from './ExportToExcel'

function App() {
  const [data, setData] = React.useState([])
  const fileName = "myfile"; // here enter filename for your excel file

  React.useEffect(() => {
    const fetchData = () =>{
     axios.get('https://jsonplaceholder.typicode.com/posts').then(postData => {

     // reshaping the array
     const customHeadings = postData.data.map(item=>({
       "Article Id": item.id,
       "Article Title": item.title
     }))

      setData(customHeadings) 
     })
    }
    fetchData()
  }, [])

  return (
    <div className="App">
      <ExportToExcel apiData={data} fileName={fileName} />
    </div>
  );
}

export default App;
Enter fullscreen mode Exit fullscreen mode

According to official SheetJS CE docs. By default, json_to_sheet creates a worksheet with a header row.

This way of adding header is reshaping array to object, based on our needs. Since the headers for Excel file come from the object keys we defined.

In our case headers will be "Article Id" and "Article Title".

Run project

npm start
Enter fullscreen mode Exit fullscreen mode

How To Export Data To Excel From Api Using React (incl. Custom Headers)

Once project started successfully, click to button to download Excel file.

How To Export Data To Excel From Api Using React (incl. Custom Headers)

Result

How To Export Data To Excel From Api Using React (incl. Custom Headers)

Second way

No need to reshape array inside App.js. Just need to add this code

 XLSX.utils.sheet_add_aoa(ws, [["Name", "Birthday", "Age", "City"]], { origin: "A1" });
Enter fullscreen mode Exit fullscreen mode

inside your ExportToExcel.js file

  const exportToCSV = (apiData, fileName) => {
    const ws = XLSX.utils.json_to_sheet(apiData);
    /* custom headers */
    XLSX.utils.sheet_add_aoa(ws, [["Name", "Birthday", "Age", "City"]], { origin: "A1" });
    const wb = { Sheets: { data: ws }, SheetNames: ["data"] };
    const excelBuffer = XLSX.write(wb, { bookType: "xlsx", type: "array" });
    const data = new Blob([excelBuffer], { type: fileType });
    FileSaver.saveAs(data, fileName + fileExtension);
  };
Enter fullscreen mode Exit fullscreen mode

Result

How To Export Data To Excel From Api Using React (incl. Custom Headers)

Source code links

1st way

2nd way

Discussion (12)

Collapse
andrewbaisden profile image
Andrew Baisden

Cool guide really easy to follow thanks for sharing.

Collapse
jasurkurbanov profile image
Jasur Kurbanov Author

You're welcome!

Collapse
andrewbaisden profile image
Andrew Baisden

Sure no problem hope to see more of this content.

Collapse
isidro_calderon profile image
Isidro Calderon A.

thanks for sharing

Collapse
paratron profile image
Christian Engel • Edited on

Okay, wait a second - did I get this correctly: I load JSON data from your server and turn that into an excel file INSIDE YOUR BROWSER using massive amounts of client side javascript.

Why?

Instead of requesting JSON from the server, why not requesting the excel file directly from the server (create it serverside)?

That would result in a much smaller client side app and works even in older browsers.

Or did I miss any reason to do that stuff in the browser?

Collapse
hunghvu profile image
Hung Vu

Server-side export is a different use case, and is not always a solution. For example, if you want to create a reusable table component with XLSX exporting feature, then client side is a way to do so. Certainly, information in the table is from an API, and at least I would not want to have my table feature being disabled due to internet connection issue.

The article is an introduction to xlsx feature, so either examples work.

Collapse
paratron profile image
Christian Engel

You should definitely mention just HOW BIG the xlsx module is. Its over 400kb of minified javascript. Thats nothing you should casually load alongside your app just because someone might want to save an excel file.

And if you want to create a React table component, it should not be built with excel export included in the component. That logic definitely belongs someplace else.

There may be a rare case of when there is really no other way than generating the excel file in the browser - but the way your article is written most people get the idea that there is no downside at all to that approach. "Just install those three modules" sounds simple but in the background you are getting truckloads of additional code into your application. Your article seems to be aimed at beginners.

So big red warning: you would normally NOT want to do it this way, but there MAY be reasons to do so.

Thread Thread
hunghvu profile image
Hung Vu

I'm not the author by the way, just a passerby.

I agree it has an impact on performance and should have more consideration in a real product implementation, but again, that was not the point of this article (at least in the way I interpret it).

The table example was simply based on MUI data grid, which was on top of my head at that time.

Collapse
hunghvu profile image
Hung Vu

There is one big catch though, you must define cell format yourself.

Let's say you already had an Excel sheet (with styling), and want to use that sheet to store data. An approach is to open your existing sheet, copy all existing cell information, write API data, then save to a new file (in-place replacement is not available AFAIK). However, xlsx will nullify all existing style of the sheet so you must manually define cell style upon exporting.

Apparently, this feature is only available in the Pro version, so it is a huge blockage IMO.

Collapse
shiv_5 profile image
Shivendra.tiwari4u@gmail.com

How can I show values into rows rather than showing it in column

Collapse
jasurkurbanov profile image
Jasur Kurbanov Author

Nice question, I will research on this than answer to you

Collapse
shiv_5 profile image
Shivendra.tiwari4u@gmail.com

Sure,Thank you