Export HTML Tables to Excel with Colours

Export HTML tables to Excel is not a big deal with today’s libraries. The XLSX library is the most used library to export Excels and xlsx-js-style library is used to export the Excel with styles. Basically, we can generate a worksheet from data as ‘Array of Arrays’ or ‘Array of Objects.

If we have already have a HTML table with the data, this table can be directly exported to an Excel using XLSX library and xlsx-js-style library is used to add basic styling options.

To export the table with colors using the xlsx-js-style library, you need to define the styles (like background colors) explicitly when creating the worksheet, as XLSX.utils.table_to_sheet does not automatically pick up styles from HTML tables.

Here’s the way to include cell styles.

Here’s a step-by-step guide to export HTML tables to Excel file while applying custom styles using the xlsx-js-style library.

exportExcel() {
    let book_name = 'Example Export';
    const workbook = XLSX.utils.book_new();
    let table = document.getElementById('html_table_id');

    // Convert the table to a worksheet
    let worksheet = XLSX.utils.table_to_sheet(table, {});

    // Example of adding background colors
    for (let cell in worksheet) {
        if (cell[0] === '!') continue;  // Skip metadata cells

        // Set custom background color (for example: yellow)
        worksheet[cell].s = {
            fill: {
                fgColor: { rgb: "FFFF00" } // Yellow background color
            },
            font: {
                bold: true, // Optional: make text bold
            },
        };
    }

    XLSX.utils.book_append_sheet(workbook, worksheet, 'Results');
    XLSX.writeFile(workbook, book_name + ".xlsx");
}

The fill property defines the cell’s background color.
fgColor takes the RGB value for the color you want to set.
You can also customise font and other properties using the fontproperty.

When the table is generated dynamically and cell colours are not static…

In such cases, you will need to retrieve the styles from the HTML table and apply them to the corresponding cells in the Excel file. Here’s how you can modify your code to capture the styles from the HTML table and apply them when exporting:

Iterate through the HTML table cells to extract the background color.
Apply the extracted color to the corresponding Excel cell using the xlsx-js-style library.

exportExcel() {
    console.log(id, tablecode);
    let book_name = 'CRT - ' + tablecode;
    const workbook = XLSX.utils.book_new();
    let table = document.getElementById('html_table_id');

    // Convert the table to a worksheet
    let worksheet = XLSX.utils.table_to_sheet(table, {});

    if (table) {

      // Get rows and cells from the HTML table
      let rows = table.getElementsByTagName('tr');

      // Loop through each row and cell
      for (let r = 0; r < rows.length; r++) {
        for (let c = 0; c < rows[r].cells.length; c++) {
          let cell = rows[r].cells[c];
          let bgColor = this.getComputedBackgroundColor(cell);
          let hexColor = this.rgbToHex(bgColor);

          if (hexColor) {
            let address = XLSX.utils.encode_cell({ r, c });
            if (!worksheet[address]) {
              worksheet[address] = { v: "", t: 's' }; // Set empty value
            }
            if (worksheet[address]) {
              worksheet[address].s = {
                fill: {
                  patternType: "solid",
                  fgColor: { rgb: hexColor },
                  bgColor: { indexed: 64 }
                }
              };
              if (!worksheet[address].v) {
                worksheet[address].v = "";
              }
              if (worksheet[address].t === 'z') {
                worksheet[address].t = 's';
              }
            }
          }
        }
      }

      XLSX.utils.book_append_sheet(workbook, worksheet, 'Results');
      XLSX.writeFile(workbook, book_name + ".xlsx");
    }
  }

  getComputedBackgroundColor(cell: HTMLElement): string {
    let computedStyle = window.getComputedStyle(cell, null);
    let backgroundColor = computedStyle.getPropertyValue("background-color");
    return backgroundColor;
  }


  // Utility function to convert any color to hex
  rgbToHex(color: string): string | null {
    // Handle hex colors directly
    if (color.startsWith('#')) {
      return color.slice(1).toUpperCase();
    }

    // Handle rgb/rgba colors
    const rgba = color.match(/\d+/g);
    if (!rgba || rgba.length < 3) return null; // Invalid color

    const r = parseInt(rgba[0]);
    const g = parseInt(rgba[1]);
    const b = parseInt(rgba[2]);

    // Handle transparency (rgba) - treat fully transparent as no color
    if (rgba.length === 4 && parseFloat(rgba[3]) === 0) {
      return null;
    }

    // Convert RGB to hex
    return (
      ((1 << 24) + (r << 16) + (g << 8) + b)
        .toString(16)
        .slice(1)
        .toUpperCase());

  }

The window.getComputedStyle(cell) is used to get the styles of each table cell.
The rgbToHex function converts the rgb() or rgba() format to a hex format, which is required by the xlsx-js-stylelibrary.
After extracting the colours, the fill property of the worksheet cell is set with the corresponding hex colour.
To ensure the styles are applied even to cells without values, it will be needed to handle cells regardless of their content. To acheive this ensure that every cell is assigned a default value. This prevents Excel from skipping styling for cells with no values.

This should allow you to export the dynamically generated table with the same colours as displayed in the HTML.

Testing

Create a Test Table: Include both populated and empty cells in your table to ensure all cells are styled as expected.
Inspect the Result: Open the generated Excel file to verify that all cells, including those without values, display the correct colors.

Happy Coding…

Share the article with your friends