DEV Community

Cover image for How To Export Data To Excel From Api Using React
Jasur Kurbanov
Jasur Kurbanov

Posted on • Updated on

How To Export Data To Excel From Api Using React

CREATE AND INSTALL

Create new react project

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

Now start project

npm start
Enter fullscreen mode Exit fullscreen mode

image

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

npm install xlsx file-saver axios --save-dev
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

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>
  );
};
Enter fullscreen mode Exit fullscreen mode

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;

Enter fullscreen mode Exit fullscreen mode

Start project

npm start
Enter fullscreen mode Exit fullscreen mode

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

image

Excel File

image

Github repo for this project link

Discussion (14)

Collapse
timurcatakli profile image
Timur Catakli

Thanks for the article but I don't think you need πŸ‘‰file-saver - library

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

Collapse
duysau profile image
duysau

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.

Collapse
jasurkurbanovinit profile image
Jasur Kurbanov Author • Edited on

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();
  }, []);
Enter fullscreen mode Exit fullscreen mode

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.

Collapse
hanuz06 profile image
Andrey Li • Edited on

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:

Collapse
jasurkurbanovinit profile image
Jasur Kurbanov Author

Nice idea, thank you. I need to do research about your idea

Collapse
hanuz06 profile image
Andrey Li

Thank you buddy

Collapse
seema_semwal_d6c746dce089 profile image
Seema Semwal

Any update?

Thread Thread
jasurkurbanovinit profile image
Jasur Kurbanov Author

Checkout my new post here

Collapse
danieladrian_garaygarci profile image
Daniel Adrian Garay Garcia

How can I put custom headers?

Collapse
jasurkurbanovinit profile image
Jasur Kurbanov Author

Can you show where you want to add headers ?

Collapse
danieladrian_garaygarci profile image
Daniel Adrian Garay Garcia

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

Thread Thread
jasurkurbanovinit profile image
Jasur Kurbanov Author

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

Collapse
seema_semwal_d6c746dce089 profile image
Seema Semwal

Are there any updates on this? How can we add headers and style to the sheet?

Collapse
jasurkurbanovinit profile image
Jasur Kurbanov Author

Checkout my new post here