Copy Table to Excel with Table formatting

Copying text from a webpage table using the clipboard is a common implementation. However, copying a table containing said text while keeping formatting intact in Excel isn’t addressed as often.

This blog gives an insight into the process with which a table can be copied to Excel while preserving the table’s format with a few simple steps.

HTML for the table design

Here’s the HTML code for the table that can be designed with normal HTML or CSS design or by using any library material:

<button mat-raised-button (click)="copyTable()">Copy Table</button>

<table mat-table [dataSource]="data" class="mat-elevation-z8">
  <ng-container [matColumnDef]="column" *ngFor="let column of displayedColumns">
    <th mat-header-cell *matHeaderCellDef> {{column}} </th>
    <td mat-cell *matCellDef="let element"> {{element[column]}} </td>
  </ng-container>

  <tr mat-header-row *matHeaderRowDef="columnsToDisplay"></tr>
  <tr mat-row *matRowDef="let row; columns: columnsToDisplay;"></tr>
</table>

Following is the UI for the above table design:

UI is here

Note — The data in this Table data can be static or API based, depending on the type of data you are having.

Let us move on to the next step, which is copying the table.

Function to copy table content

This being the major step, do make sure you thoroughly read through each point.

In this step, we will be creating one table within the function with a table row and header and append them to the table. We will be selecting the table content range and preparing it for copy.

Here are a couple of steps that I followed for creating the tables based on input data:

1. Firstly, Create a table and table body:

var tbl = document.createElement(“table”);
var tblBody = document.createElement(“tbody”);

2. Create a table row, a table header, and another table row with the same table we have containing the same data.

headerow.innerHTML = `<td>${data.header}</td>`;
tblBody.appendChild(headerow);
nodeData.forEach(data => {
var row = document.createElement(“tr”);
row.innerHTML = `<td>${data.value}</td>`;
tblBody.appendChild(row);
});

3. Append the row and headers to the table:

tbl.appendChild(tblBody);
document.body.appendChild(tbl);

4. Copy the inner Text to clipboard

        tbl.appendChild(tblBody);
        document.body.appendChild(tbl);
        // Copy the table element innerText to clipboard
        navigator.clipboard.writeText(tbl.innerText);
        // Hide the table element from DOM after copied
        tbl.style.display = "none";

5. Here is the ‘copyTable’ function code put together:

copyTable() {
        let nodeData = this.data;
        var tbl = document.createElement('table');
        var tblBody = document.createElement('tbody');
        var headerow = document.createElement('tr');
        var header = this.displayedColumns;
        headerow.innerHTML = `<td>${header[0]}</td><td>${header[1]}</td><td>${header[2]}</td><td>${header[3]}</td>`;
        tblBody.appendChild(headerow);
        nodeData.forEach((data) => {
            var row = document.createElement('tr');
            row.innerHTML = `<td>${data.name}</td><td>${data.weight}</td><td>${data.symbol}</td><td>${data.position}</td>`;
            tblBody.appendChild(row);
        });
        tbl.appendChild(tblBody);
        document.body.appendChild(tbl);
        // Copy the table element innerText to clipboard
        navigator.clipboard.writeText(tbl.innerText);
        // Hide the table element from DOM after copied
        tbl.style.display = "none";
    }

6. Complete class logic with the data to diplay in UI

const ELEMENT_DATA: PeriodicElement[] = [
  { position: 1, name: 'Software Engineer', weight: 7, symbol: 'SE' },
  { position: 2, name: 'Integration Specialist ', weight: 4, symbol: 'IE' },
  { position: 3, name: 'Pre-Sales Support  ', weight: 4, symbol: 'SS' },
  { position: 4, name: 'Sales Assistant  ', weight: 5, symbol: 'SA' },
  { position: 5, name: 'Javascript Developer', weight: 7, symbol: 'JD' },
  { position: 6, name: 'Tech Lead', weight: 8, symbol: 'TL' },
  { position: 7, name: 'Accountant', weight: 6, symbol: 'AC' },
  { position: 8, name: 'Chief Executive Officer', weight: 10, symbol: 'CEO' },
];

@Component({
    selector: 'table-dynamic-columns-example',
    styleUrls: ['table-dynamic-columns-example.css'],
    templateUrl: 'table-dynamic-columns-example.html',
})
export class TableDynamicColumnsExample {
    displayedColumns: string[] = ['name', 'weight', 'symbol', 'position'];
    columnsToDisplay: string[] = this.displayedColumns.slice();
    data: PeriodicElement[] = ELEMENT_DATA;

    copyTable() {
        let nodeData = this.data;
        var tbl = document.createElement('table');
        var tblBody = document.createElement('tbody');
        var headerow = document.createElement('tr');
        var header = this.displayedColumns;
        headerow.innerHTML = `<td>${header[0]}</td><td>${header[1]}</td><td>${header[2]}</td><td>${header[3]}</td>`;
        tblBody.appendChild(headerow);
        nodeData.forEach((data) => {
            var row = document.createElement('tr');
            row.innerHTML = `<td>${data.name}</td><td>${data.weight}</td><td>${data.symbol}</td><td>${data.position}</td>`;
            tblBody.appendChild(row);
        });
        tbl.appendChild(tblBody);
        document.body.appendChild(tbl);
        // Copy the table element innerText to clipboard
        navigator.clipboard.writeText(tbl.innerText);
        // Hide the table element from DOM after copied
        tbl.style.display = "none";
    }
}

Here, I am using angular component class, you can use this function logic in anywhere JavaScript, React..

You must be thinking, if we already have a table, why do we need to create it again in code? Copying content from HTML will not show the data in the desired table format if we do not do so.

Copy content to Excel

You can now copy by clicking the “Copy Table” button (on UI) and pasting it in Excel. This will copy the content in the desired table format, as shown below:

We discovered how to generate a table with JavaScript to create the table and copy that table to clipboard by using command.

**Copying the table element innerText to clipboard
navigator.clipboard.writeText(tbl.innerText);

Here we are using navigator.clipboard to copy. Earlier we were using exceCommand(‘copy’) that required to create range and then select range code also, That’s having lots of line of code involved.

**Copying the content can be done using exceCommand(‘copy’)
document.execCommand(‘copy’);

In this tutorial, we can now not only copy the single data but entire table into excel format to clipboard with very less line of code using navigator.clipboard.