Recently, I had to implement a custom button for exporting data shown in a React JS app grid in Microsoft Excel format. The data is received from the NetSuite side using an API call and it is in ‘array of objects’ format. There are multiple libraries that help to create Excel files. But, I used the ExcelJS library which was suitable for my project.
The following is the button action code I used for the button:
async function exportAsExcel(data: Data[], title: string) {
const excelArray = [];
let filteredColumnWidth: number[];
if (data.length > 0) {
const filteredColumns = Object.keys(data[0]).filter(key => (key.toLowerCase() !== ‘id’ && key.toLowerCase() !== ‘recordtype’)); //Remove unnecessary values
filteredColumnWidth = new Array(filteredColumns.length).fill(10);
const valueArr = data.map((el) => {
const testArr = [];
for (let i = 0; i < filteredColumns.length; i++){
testArr.push( el[filteredColumns[i]] )
if (filteredColumnWidth[i] < el[filteredColumns[i]].toString().length) {
filteredColumnWidth[i] = el[filteredColumns[i]].toString().length;
}
}
return testArr;
});
excelArray.push(filteredColumns);
valueArr.map((el) => { excelArray.push(el); })
}
const wb = new ExcelJS.Workbook();
const ws = wb.addWorksheet(title, { properties: { defaultColWidth: 10 } }); // Default column width is set to 10
// Add rows to the worksheet
ws.addRows(excelArray);
// Apply bold style to header
ws.getRow(1).font = { bold: true };
let columnIndex = 0;
ws.columns.forEach(column => {
column.width = filteredColumnWidth[columnIndex] + 5;
columnIndex += 1;
});
const buffer = await wb.xlsx.writeBuffer();
const blob = new Blob([buffer], {type: ‘application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’});
const link = document.createElement(‘a’);
link.href = window.URL.createObjectURL(blob);
link.download = fileNameCreate(title) + ‘.xlsx’; // link.download = ‘Production-Dashboard-‘+ new Date().getTime() + ‘.xlsx’;
link.click();
}
N.B: Make sure to install the ExcelJS library in your React JS project(Enter command: npm install exceljs