CODEWITHSUNDEEP
javascripthtmlexcelclipboard
Mathias Bader
Mathias Bader

Reputation: 3826

How to copy table with link to clipboard to paste it into excel

I create data on a website, which is table like. I want to copy that data to excel. I do that using Tabs to go to the next cell in the row and newlines to go to the next row:

let clipboard = 'My link' + "\t" + secondCell + "\t" + thirdCell + "\n" + 
firstCellSecondRow + "\t" + secondCellSecondRow + "\t" + thirdCellSecondRow;
navigator.clipboard.writeText(clipboard);

Copying that text into an excel table works well:

Data in excel

Now my challenge is, that I want to add a link to text of one cell. My intuitive approach was to add the following text to the clipboard:

let clipboard = '<a href="https://www.my-url.com">My link</a>' + "\t" + secondCell + "\t" + thirdCell + "\n" + 
firstCellSecondRow + "\t" + secondCellSecondRow + "\t" + thirdCellSecondRow;
navigator.clipboard.writeText(clipboard);

But that doesn't work, it will actually display the text with the html in it:

Data in excel

What I want is only the text "My link" and an actual link that is clickable:

Data in excel

Since copying of text with links is possible in excel, I feel that it should somehow work. Can I do that with javascript?

Upvotes: 7

Views: 5273

Answers (2)

Guerric P
Guerric P

Reputation: 31805

Here is how you achieve it in JavaScript:

let html = '<table><tr><td><a href="https://www.my-url.com">My link</a></td><td>secondCell</td></tr><tr><td>firstCellSecondRow</td><td>secondCellSecondRow</td></tr></table>';

const blob = new Blob([html], { type: "text/html" });

navigator.clipboard.write([new ClipboardItem({ [blob.type]: blob })]);

I didn't write it as a snippet in the answer because StackOverflow has security limitations that disallows the clipboard in snippets.

If you try it in your console, this error may occur:

Uncaught (in promise) DOMException: Document is not focused.

Here is a console-testable version of the code, it implies that you click on the page during the 3s timeout:

let html = '<table><tr><td><a href="https://www.my-url.com">My link</a></td><td>secondCell</td></tr><tr><td>firstCellSecondRow</td><td>secondCellSecondRow</td></tr></table>';

const blob = new Blob([html], { type: "text/html" });

setTimeout(() => navigator.clipboard.write([new ClipboardItem({ [blob.type]: blob })]), 3000);

Result in Excel:

enter image description here

Upvotes: 12

Subhajit Maji
Subhajit Maji

Reputation: 72

I found a really simple solution for your problem.

I first stored the linked text in another variable. Like this:

    let mylink = 'My link';

I then added the link to the variable as an attribute.

    mylink.link = 'https://www.my-url.com';

I broke the variable into two parts, replacing the first part with our above variable. Like this:

    let clipboard = mylink + "\t" + secondCell + "\t" + thirdCell + "\n" + 
    firstCellSecondRow + "\t" + secondCellSecondRow + "\t" + thirdCellSecondRow;
    navigator.clipboard.writeText(clipboard);

Upvotes: 0

Related Questions