Add color to rows in Excel export in Angular

Question

This is the block of code where I have tried to export the HTML table data to an Excel sheet with the colors present in the table using angular, but it is not working. where I need to make corrections?

exportExcel() {
    const combinedTable = this.combineTables(this.projects, this.projectDetails);                       //combining two tabledata to combinedTable
    const worksheet = xlsx.utils.json_to_sheet(combinedTable);                                          
    const workbook = { Sheets: { data: worksheet }, SheetNames: ['data'] };

    for (let columnIndex = 0; columnIndex < 24; columnIndex++) {
        for (let rowNumber = 1; rowNumber <= combinedTable.length + 1; rowNumber++) {
            const cellAddress = xlsx.utils.encode_cell({ r: rowNumber, c: columnIndex });
            const cellValue = worksheet[cellAddress]?.v;

            if (cellValue === 'budgetStatus') {
                const rowValue = worksheet[xlsx.utils.encode_cell({ r: rowNumber, c: columnIndex })]?.v;

                // Clear existing cell value
                worksheet[cellAddress].v = '';

                // Set the new row value
                const newRowAddress = xlsx.utils.encode_row(rowNumber);
                worksheet[newRowAddress] = { t: 'n', v: rowValue };

                // Apply styles
                worksheet[newRowAddress].s = {
                    fill: {
                        fgColor: { rgb: 'FF000000' },
                        bgColor: { rgb: 'FFFFFFFF' },
                    },
                    font: {
                        color: { rgb: '#FF000000' },
                    },
                };
            }
        }
    }

    const excelBuffer = xlsx.write(workbook, { bookType: 'xlsx', type: 'array' });
    this.saveAsExcelFile(excelBuffer, 'combined_products');
}

combineTables(table1: any[], table2: any[]): any[] {
    return table1.map((item1, index) => {
        const item2 = table2[index] || {};
        return { ...item1, ...item2, OveralProjectStatus: this.overalProjectStatusValue, ManagerProjectStatus: this.managerStatusValue };
    });
}

saveAsExcelFile(buffer: any, fileName: string): void {
    const EXCEL_TYPE = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8';
    const EXCEL_EXTENSION = '.xlsx';
    const data: Blob = new Blob([buffer], { type: EXCEL_TYPE });
    FileSaver.saveAs(data, `${fileName}_export_${new Date().getTime()}${EXCEL_EXTENSION}`);
}

I expect that the Excel sheet's rows will have colors corresponding to the data present in the HTML page table after being exported.

Answer 1

The color code should be 6 characters long. Currently there are 8 characters in your code ex:FF000000, this is wrong. Please use like below,

 workbook.Sheets["Summary"][cellRef].s = {
                            font: { bold: true, color: { rgb: "FFFFFF" } },
                            fill: { fgColor: { rgb: "7f7f7f" } },
                            border: {
                                top: { style: "thin", color: { rgb: "000000" } }, bottom: {
                                    style: "thin", color: { rgb: "000000" }
                                }, right: {
                                    style: "thin", color: { rgb: "000000" }
                                }, left: { style: "thin", color: { rgb: "000000" } }
                            },
                        };