I am trying to add formation on column heading(background and text color) in excel sheet when click the Export to Excel button. It is not working in exported excel file. The table made in HTML table tags. Can we apply the format on excel sheet. If it is possible, please guide. It's example Please check.
function exportToExcel() {
const table = document.getElementById('rep_groupingTable_data');
const wb = XLSX.utils.book_new();
const ws = XLSX.utils.table_to_sheet(table, { raw: true });
// Get header cells
const headerCells = table.querySelectorAll('th');
// Set blue background color for header cells
headerCells.forEach((cell, index) => {
const cellRef = XLSX.utils.encode_cell({ c: index, r: 0 });
ws[cellRef].s = { fill: { bgColor: { rgb: 'FF0072C6' } }, font: { color: { rgb: 'FFFFFFFF' } } };
});
XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');
// Generate Excel file
const wbout = XLSX.write(wb, { bookType: 'xlsx', type: 'binary' });
saveAs(new Blob([s2ab(wbout)], { type: 'application/octet-stream' }), 'Report.xlsx');
}
function s2ab(s) {
const buf = new ArrayBuffer(s.length);
const view = new Uint8Array(buf);
for (let i = 0; i < s.length; i++) view[i] = s.charCodeAt(i) & 0xff;
return buf;
}
// Helper function to simulate the saveAs function for exporting files
function saveAs(blob, filename) {
const link = document.createElement('a');
link.href = URL.createObjectURL(blob);
link.download = filename;
link.click();
} #report_expor_table_data_se {
margin-top:20px;
}<!doctype html>
<html>
<head>
<title>Our Funky HTML Page</title>
<meta name="description" content="Our first page">
<meta name="keywords" content="html tutorial template">
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.7.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.0/xlsx.full.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/2.0.5/FileSaver.min.js"></script>
</head>
<body>
<h4>
Export HTML Table Into ExcelSheet
</h4>
<!-- Add an HTML button to trigger the export -->
<button onclick="exportToExcel()">Export to Excel</button>
<!-- Your HTML table -->
<div id="report_expor_table_data_se" >
<table style="border-bottom: 1px solid #dddee2" id="rep_groupingTable_data" width="100%" cellpadding="0" cellspacing="0" class="report_grouping_table">
<thead>
<tr>
<th style="text-align: left;background: #36a7c4;border-bottom: 1px solid #dddee2;">Status</th>
<th style="text-align: left;background: #36a7c4;border-bottom: 1px solid #dddee2;">Code</th>
<th style="text-align: left;background: #36a7c4;border-bottom: 1px solid #dddee2;">Name</th>
</tr>
</thead>
<tbody>
<!-- Your table content -->
<tr>
<td style="text-align: left; padding: 10px 10px;font-size: 14px;color: #4f5764;line-height: 1.5em;border-top: 1px solid #dddee2;border-left: 1px solid #dddee2;">active</td>
<td style="text-align: left; padding: 10px 10px;font-size: 14px;color: #4f5764;line-height: 1.5em;border-top: 1px solid #dddee2;border-left: 1px solid #dddee2;">CUS001</td>
<td style="text-align: left; padding: 10px 10px;font-size: 14px;color: #4f5764;line-height: 1.5em;border-top: 1px solid #dddee2;border-left: 1px solid #dddee2;">Demo Customer</td>
</tr>
<tr>
<td style="text-align: left; padding: 10px 10px;font-size: 14px;color: #4f5764;line-height: 1.5em;border-top: 1px solid #dddee2;border-left: 1px solid #dddee2;">inactive</td>
<td style="text-align: left; padding: 10px 10px;font-size: 14px;color: #4f5764;line-height: 1.5em;border-top: 1px solid #dddee2;border-left: 1px solid #dddee2;">00009</td>
<td style="text-align: left; padding: 10px 10px;font-size: 14px;color: #4f5764;line-height: 1.5em;border-top: 1px solid #dddee2;border-left: 1px solid #dddee2;">Jean</td>
</tr>
</tbody>
</table>
</div>
</body>
</html>