How To Export Data To Excel From Api Using ReactJS (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

Run project locally

npm start

Let's dive into next step.

Install libraries

For this project we need to install following libraries:

npm install xlsx file-saver axios

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>
  );
};

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;

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

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

Result

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" });

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);

  };

Result

Source code links

1st way

2nd way

Comments

[Andrew Baisden]

Cool guide really easy to follow thanks for sharing.

[Jasur Kurbanov]

You're welcome!

[Andrew Baisden]

Sure no problem hope to see more of this content.

[Isaac Hatilima]

Great tutorial, I have one question. When I change SheetNames: ["data"] to SheetNames: ["Sheet 1"] or anything the data does not export, can I not change the sheet name?

[Jasur Kurbanov]

what do you want to achieve bro ?

[Isaac Hatilima]

I want the sheet/page in the export to have a custom name and not data.

[Jasur Kurbanov]

do you need assistance? or you found how to do it ?

[Isaac Hatilima]

I managed to make it work. I needed to add link.fillStyle = 'white'; in the click event function.

[jochercoles]

Hi Isaac, I have the same problem, can you explain with more detail how to you done to change the sheet name from "data" to anything?
Where you inserted linkfillStyle = 'white';
Thank you very much.
Javier.

[Isidro Calderon A.]

thanks for sharing

[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.

[Christian Engel]

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?

[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.

[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.

[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.

[[e-mail]]

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

[Jasur Kurbanov]

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

[[e-mail]]

Sure,Thank you

[Mayur]

@jasurkurbanov
I need a help and its urgent.
I need to set width property for columns that get displayed on excel sheet and also add some colors .
How do i do that ?????

[Jasur Kurbanov]

@kadam you can do it with xlsx pro version sheetjs.com/pro. But there are other libraries which can help you npmjs.com/package/xlsx-color