Jasur Kurbanov

Posted on Feb 22, 2021 • Edited on Feb 17, 2023

How To Export Data To Excel From Api Using React

#react #javascript #excel #api

UPDATED version of this tutorial available here

CREATE AND INSTALL

Create new react project



npx create-react-app react-data-to-excel

Now start project



npm start

Project successfully created.
Lets dive into installing neccassry libraries for our project. Install these three libraries.



npm install xlsx file-saver axios --save-dev

👉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

UI AND LOGIC

Create a component namely 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>
  );
};

Sample data we will get from https://jsonplaceholder.typicode.com/posts


 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(r => setData(r.data) )
    }
    fetchData()
  }, [])

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

export default App;

Start project



npm start

Once it successfully started. You can see one button. By pressing this button you download excel file with data filled from https://jsonplaceholder.typicode.com/posts

Output

Excel File

Github repo for this project link

Top comments (17)

Muzakir Shah • Dec 27 '22

Thanks. Amazing explaination. For those looking for multiple sheet writing, here is the code snippet.

const exportToCSV = () => {
const ws = XLSX.utils.json_to_sheet(sheet_data1);
var ws2 = XLSX.utils.json_to_sheet(sheet_data2);
const wb = XLSX.utils.book_new();
const sheet1 = XLSX.utils.book_append_sheet(wb, ws, "Name of Sheet1");
const shee2 = XLSX.utils.book_append_sheet(wb, ws2, "Name of Sheet2");
XLSX.writeFile(wb, "Output_filename.xlsx");
};

Timur Catakli • Sep 10 '21

Thanks for the article but I don't think you need 👉file-saver - library

XLSX.writeFile(wb, 'sheetjs.xlsx') does the job...

duysau • Jul 6 '21

Hi, I'm very intersting with your solution but how can I export columns that I want to export them. Example: I have id, displayID, name, age which of them are columns and I just want to export data from name, age.
Thanks for your supporting.

Jasur Kurbanov • Jul 10 '21 • Edited

You need to change place where I am retrieving data from server. Add this code and analyze what I did. I put comments.

 React.useEffect(() => {
    const fetchData = () => {
      axios
        .get("https://jsonplaceholder.typicode.com/posts")
        .then((response) => {
          let newArray = [];

          response.data.map((item) => {
            // here i am  extracting only userId and title
            let obj = { userId: item.userId, title: item.title };
            // after extracting what I need, I am adding it to newArray
            newArray.push(obj);
            // now  I am adding newArray to localstate in order to passing it via props for exporting
            setData(newArray);
          });
        });
    };
    fetchData();
  }, []);

Screenshot after exporing data. Checkout
dev-to-uploads.s3.amazonaws.com/up...

P.s
Regarding code style, I did simple solution in order to give you idea. Obviously, you can do code more beautiful and shorter.

ayokunle ogunfidodo • Sep 20 '22

Hi, if to say data in json being exported is a boolean from, i tried writing a function to convert it from true/false to "yes" /"no". this is the result "TypeError: js.forEach is not a function" can you help explain why and what do you think i can do.

Jasur Kurbanov • Sep 26 '22 • Edited

1) Download this repo github.com/jasurkurbanov/react-api...

2) Inside App.js replace useEffect to the code below

React.useEffect(() => {
    const fetchData = () => {
      axios.get('https://www.arbeitnow.com/api/job-board-api').then(postData => {

        // reshaping the array
        const customHeadings = postData.data.data.map(item => ({
          "Company Name": item.company_name,
          "Location": item.location,
          "isRemote": item.remote ? 'Yes' : 'No'
        }))

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

3) Result

Hope it helped to you)

Daniel Adrian Garay Garcia • Jun 21 '21

How can I put custom headers?

Jasur Kurbanov • Jun 21 '21

Can you show where you want to add headers ?

Daniel Adrian Garay Garcia • Jun 21 '21

in the first row a title and in the second row are the column headings

Jasur Kurbanov • Jul 10 '21

Sorry I did not get, what you mean. If it possible please provide some screenshots , in order to figure out.

Andrey Li • Jan 5 '22 • Edited

I wonder if it's possible to send styled excel? I mean at least to adjust columns width to fit data. Also it would be nice to make table borders like this: