DEV Community

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

How To Export Data To Excel From Api Using React

Jasur Kurbanov on February 22, 2021

UPDATED version of this tutorial available here CREATE AND INSTALL Create new react project npx create-react-app react-da...
Collapse
 
mozi47 profile image
Muzakir Shah

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

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
 
jasurkurbanov profile image
Jasur Kurbanov • 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();
  }, []);
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
 
fidodo profile image
ayokunle ogunfidodo

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.

Collapse
 
jasurkurbanov profile image
Jasur Kurbanov • 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()
  }, [])
Enter fullscreen mode Exit fullscreen mode

3) Result
image

Hope it helped to you)

Collapse
 
danieladrian_garaygarci profile image
Daniel Adrian Garay Garcia

How can I put custom headers?

Collapse
 
jasurkurbanov profile image
Jasur Kurbanov

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
 
jasurkurbanov profile image
Jasur Kurbanov

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

Collapse
 
hanuz06 profile image
Andrey Li • 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:

Collapse
 
jasurkurbanov profile image
Jasur Kurbanov

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
 
jasurkurbanov profile image
Jasur Kurbanov

Checkout my new post here

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
 
jasurkurbanov profile image
Jasur Kurbanov

Checkout my new post here