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
Top comments (20)
Cool guide really easy to follow thanks for sharing.
You're welcome!
Sure no problem hope to see more of this content.
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?
what do you want to achieve bro ?
I want the sheet/page in the export to have a custom name and not data.
do you need assistance? or you found how to do it ?
I managed to make it work. I needed to add link.fillStyle = 'white'; in the click event function.
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.
thanks for sharing
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,
xlsxwill 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.
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?
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
xlsxfeature, so either examples work.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.
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.
How can I show values into rows rather than showing it in column
Nice question, I will research on this than answer to you
Sure,Thank you
@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 ?????
@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