Export Excel Functionality in React JS
Introduction:
In React Js, Data can be exported to excel sheet for our reference. There are many libraries to perform this function. Here exporting data through exceljs library is explained.
Prerequisite:
- Install exceljs library.
- Install file-saver.
Implementation:
Step 1:
Install the libraries.
| npm install exceljs |
| npm install file-saver |
Step 2:
Import them in the required files.
import ExcelJS from ‘exceljs’;
import { saveAs } from ‘file-saver’;
Step 3:
We have to create an excel workbook in the required name and add the excel sheet to it.
const workbook = new ExcelJS.Workbook();
const worksheet = workbook.addWorksheet(‘Booking Report’);
To apply special styling to the header row:
const headerRow = worksheet.addRow(Object.keys(dataSource[0]));
//dataSource has the data that needs to be exported
headerRow.height = 50; // Set the desired row height for the header row
headerRow.eachCell((cell) => {
cell.fill = {
type: ‘pattern’,
pattern: ‘solid’,
fgColor: { argb: ‘C6E2FF’ }
};
cell.font = {
bold: true
};
cell.alignment = {
vertical: ‘middle’, // Align text vertically to the middle
horizontal: ‘center’ // Align text horizontally to the center
};
});
To apply multi line formatting:
dataSource.forEach((row) => {
const newRow = worksheet.addRow(Object.values(row));
// Loop through cells in the new row and apply multi line formatting
newRow.eachCell((cell) => {
cell.alignment = {
wrapText: true,
vertical: ‘top’,
horizontal: ‘left’
};
});
});
To apply column width separately:
// Set First column widths
worksheet.getColumn(1).width = 10;
// Set width of other columns to 30
for (let i = 2; i <= headerRow.cellCount; i++) {
worksheet.getColumn(i).width = 30;
}
Final Step to save the workbook:
const blobPromise = workbook.xlsx.writeBuffer();
blobPromise.then((buffer) => {
const blob = new Blob([buffer], { type: ‘application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’ });
saveAs(blob, ‘Booking-Report.xlsx’);
Ex:
const exceldata = reportdetails.map((obj,index) => {
let travelInfo = “”;
if (obj.pickup_type === “1”) {
travelInfo = `Pickup Airport Name: ${obj.airport_name}\nDrop Location: ${obj.destination}\nFlight Number: ${obj.flight_number}`;
} else if (obj.pickup_type === “2”) {
travelInfo = `Pickup Location: ${obj.destination}\nDrop Airport Name: ${obj.airport_name}\nFlight Number: ${obj.flight_number}`;
} else if (obj.pickup_type === “3” || obj.pickup_type === “4” || obj.pickup_type === “5”) {
travelInfo = `Pickup Location: ${obj.pickup_location}\nDrop Location: ${obj.dropoff_location}`;
}
return {
“Sr. No”: index+1,
“Booking ID”: obj.booking_id,
“Booking Date & Time”: obj.booking_date,
“Booking Info”:`Passenger Seat: ${obj.number_of_passengers.adult}\nBaby Seat: ${obj.number_of_passengers.child}\nLuggage: ${obj.number_of_luggage}`,
“Travel Info”:travelInfo,
“Assign Driver”: `${obj.confirmation_status === 2 ? obj.driver_name : ”}\n${obj.confirmation_status === 2 ? “+65″+getDriverDetails(obj.driver_id) : ”}${obj.confirmation_status === 2 ?”\nCar Plate No:”+getDriverDetails1(obj.driver_id) : ”}`,
});
Download function will be:
<button type=”button” onClick={() => handleDownloadExcel(exceldata)}>
Export Excel </button>
const handleDownloadExcel = (dataSource) => {
try {
const workbook = new ExcelJS.Workbook();
const worksheet = workbook.addWorksheet(‘Booking Report’);
const headerRow = worksheet.addRow(Object.keys(dataSource[0]));
headerRow.height = 50;
headerRow.eachCell((cell) => {
cell.fill = {
type: ‘pattern’,
pattern: ‘solid’,
fgColor: { argb: ‘C6E2FF’ }
};
cell.font = {
bold: true
};
cell.alignment = {
vertical: ‘middle’, // Align text vertically to the middle
horizontal: ‘center’ // Align text horizontally to the center
};
});
dataSource.forEach((row) => {
const newRow = worksheet.addRow(Object.values(row));
newRow.eachCell((cell) => {
cell.alignment = {
wrapText: true,
vertical: ‘top’,
horizontal: ‘left’
};
});
});
worksheet.getColumn(1).width = 10;
for (let i = 2; i <= headerRow.cellCount; i++) {
worksheet.getColumn(i).width = 30;
}
const blobPromise = workbook.xlsx.writeBuffer();
blobPromise.then((buffer) => {
const blob = new Blob([buffer], { type: ‘application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’ });
saveAs(blob, ‘Booking-Report.xlsx’);
});
} catch (error) {
console.error(‘Error generating Excel:’, error);
}
};
