I to needed a way to copy html table information and paste into excel. I used a combination of techniques. First I added this code so I could automatically copy data into the paste buffer of the clients pc.
How do I copy to the clipboard in JavaScript?
// Copies a string to the clipboard. Must be called from within an
// event handler such as click. May return false if it failed, but
// this is not always possible. Browser support for Chrome 43+,
// Firefox 42+, Safari 10+, Edge and IE 10+.
// IE: The clipboard feature may be disabled by an administrator. By
// default a prompt is shown the first time the clipboard is
// used (per session).
function copyToClipboard(text) {
if (window.clipboardData && window.clipboardData.setData) {
// IE specific code path to prevent textarea being shown while dialog is visible.
return clipboardData.setData("Text", text);
} else if (document.queryCommandSupported && document.queryCommandSupported("copy")) {
var textarea = document.createElement("textarea");
textarea.textContent = text;
textarea.style.position = "fixed"; // Prevent scrolling to bottom of page in MS Edge.
document.body.appendChild(textarea);
textarea.select();
try {
return document.execCommand("copy"); // Security exception may be thrown by some browsers.
} catch (ex) {
console.warn("Copy to clipboard failed.", ex);
return false;
} finally {
document.body.removeChild(textarea);
}
}
}
I then created a copy string. Separate the cell values by a tab \t and separate the rows by a new line \n. see the example below.
I create the copy data string using PHP.
//-- Create data for a single table row
//-- We needed to escape single quotes for HTML and javascript
$copy_data = str_replace("'","\'",$col1."\t".$col2."\t".$col3);
//-- Create data for a multi rows
//-- Separate multiple rows by \n but needs to be escaped so use \\n
$copy_data = str_replace("'","\'",$col1."\t".$col2."\t".$col3."\\n".$col1."\t".$col2."\t".$col3."\\n".$col1."\t".$col2."\t".$col3);
Then I place it in a copy button on the web page.
<a href="#" onclick="copyToClipboard('<?=$copy_data?>')">Copy</a>
Then you click on the "Copy" link to get the data into your copy buffer. Next go to Excel and click in the cell where you want to paste the data, then do a paste.
