I recently had a requirement to “export” data shown in a table on a web page to an Excel file.  In a normal situation I’d do something server-side to generate an Excel file with the necessary data, but unfortunately this situation was not a normal. The project I was working on did not have a server-side component – it was completely client-side code – so I couldn’t just create an Excel file.  The solution I came up with was to provide a way for the users to copy the required data to their clipboard and they could then paste it into an Excel document.

The issue was now 2-fold:

1. Access the clipboard and copy arbitrary data to it
2. Make sure the data is formatted for Excel

After some trial and error, I discovered that if I manually copy a table from a web page, that table will – more or less – be pasted intact into Excel.  Knowing that I could do this manually, I needed a way to “highlight” and copy the table automatically for the user.

I accomplished this by generating a textarea in the page, grabbing the html for my table, setting the value of the textarea to that html snippet, and selecting then executing a copy command on the textarea.  The code looks like this:

$("#textarea-holder").css("position", "absolute").css("top", "-1000px").css("left", "-1000px");
var el = document.createElement("textarea");
el.value = $(coreInputListingClone)[0].outerHTML;
el.setAttribute("readonly", "");
$("#textarea-holder").append(el);
el.select();
document.execCommand("copy");
<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>$(el).remove();

You’ll notice that I set the textarea to render way outside the bounds of the web page so that the user won’t see what’s going on.  As a side note, I originally tried just hiding the textarea (via display: none) but it turns out the copy command won’t copy stuff that is hidden.

So now that I could get some arbitrary text copied to the user’s clipboard, the next challenge was making it look good for Excel.  I’d already proven that I could take an HTML table and more or less copy the structure of the table to Excel – meaning my Excel copy would have the correct number of columns and rows and each cell would contain the correct data (mostly…).  However what didn’t look good was the more intricate formatting, or any cell that had HTML as the cell content.  In my case I had a column that rendered user comments with line breaks as HTML with break tags for the line breaks, and once that was copied into Excel, the line breaks were lost.  I also had other cells where the content was a link to some related item in the site, or an edit icon, both of which don’t make sense when in Excel.

Overcoming the simple things like formatting or links was easy enough using jQuery.  I made a copy of my table and manipulated each cell that needed to be altered, usually by just grabbing the text of the cell and replacing the entire cell contents with just the text.  What was interesting though was my comments column that had line breaks.  As mentioned above, the web page rendered the line breaks using break tags, which didn’t translate when copied to Excel.  Replacing the break tags with a “\n” or “\n\r” character(s) also didn’t produce the desired effect.

What I ended up doing was basically transforming all of my comment cells into Excel functions so I could append line breaks where necessary using the CHAR() command.  For example, if my comments were:

An Example Comment
On 2 lines

Then my resulting transformed cell in the table copied to the clipboard would look like this:

="An Example Comment" & CHAR(13) & CHAR(10) & "On 2 lines"

Which would produce the correct output when pasted into Excel (Char(13) and CHAR(10) producing line breaks/carriage returns).

Knowing that you can basically produce an Excel function by manipulating the text being copied to the clipboard, I imagine all sorts of interesting things could be accomplished to allow for copying data from a web page into an Excel file.

Looking for a new job? We work with some of the biggest names in tech, and we’re hiring! Check out our open jobs and make your next career move with Planet.