Javascript Copy to Clipboard for Excel

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 this was not a normal situation.  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 compromise 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 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.

WHAT’S NEW IN OFFICE 365 (SEPTEMBER 2018)

Time flies when you’re having fun and that means Ignite will be here before we know it.  As expected Microsoft has already started to lay the ground work for some of the talking points at Ignite with their feature and enhancement announcements in August and will likely continue that trend through September.

Office 365 Roadmap Transitioning to Microsoft 365 Roadmap

When Microsoft first rolled out the Microsoft 365 package (Office 365, Windows 10, and Enterprise Mobility & Security) there was a lot of confusion around what the differences were between Microsoft 365 and Office 365.  In fact, we still get questions about this regularly and with this latest change that trend is likely to tick upward.  In mid-September 2018 Microsoft will be moving the Office 365 Roadmap to a new location on the web and adding Microsoft 365 features.  Continue reading “WHAT’S NEW IN OFFICE 365 (SEPTEMBER 2018)”

MS Teams Bug: Comments in Documents and Modified Information

On Friday a co-worker indicated that they had added some comments to a Word document I created in one of our MS Teams.  The following Tuesday morning, using the MS Teams Desktop Application, I went to the team to review the comments but noticed something a bit weird, the modified information hadn’t changed.  It was still showing as last modified by me over a week ago.  I’m typically a good “techie” and keep my stuff up to date, shut down my machine every night and I’m regularly in the MS Teams application and never noticed this.  A bit perplexed, I opened the document in Word and sure enough the comments my co-worker added were there, but she hadn’t changed anything else, she just added comments. Continue reading “MS Teams Bug: Comments in Documents and Modified Information”

WHAT’S NEW IN OFFICE 365 (August 2018)

With a little over 6 weeks to go until Ignite and the Business Application Summit last month we are sure to be getting some new features and enhancements galore over the next couple months.  As an FYI: Microsoft has made most of, if not all the Business Applications Summit sessions available on demand. If you’re thinking about how your organization can take advantage of the Microsoft Power Platform (Power BI, PowerApps, Flow and Dynamics 365) it’s worth looking through the sessions.

Continue reading “WHAT’S NEW IN OFFICE 365 (August 2018)”

MS Flow and Azure: Creating Parent and Sub sites

The Assignment

Recently we were asked by a client to develop a MS Flow that creates a sub site when users enter a new item into a SharePoint list. What we found was there’s no simple, out of the box way to have the new sub site inherit the top navigation of the parent site. As a result, we needed to create an Azure Function to preform this function.  Continue reading “MS Flow and Azure: Creating Parent and Sub sites”

MS Teams and Skype for Business transition is coming: Are you ready?

Administrators of Office 365 be warned…the Teams/Skype for Business migrations are going to be coming and you need to start being proactive sooner rather than later.  Most of us have already got the “Teams & Skype” admin center but we can still utilize the Services & add-ins section to manage tenant wide settings we don’t have access to in the new admin center. As you may have already guessed that’s going to be changing, quickly.  Continue reading “MS Teams and Skype for Business transition is coming: Are you ready?”

Postman – Using Environments and Tokens

Rick Herrmann

Postman is an incredibly  useful tool for testing API’s.  I’ve been using it for a few years now and have found some features that make it a breeze to work with when it comes to secured API’s. It’s good for handling different environments as well.

Login and Tokens

A common pattern we use with our API’s is to use Javascript Web Tokens(JWT’s) for authentication. To work with this in Postman, I typically have a /login post call which returns a token. Then for any other calls to that API, I manually copy the returned token, and set the Authorization header to “Bearer <token>”. Although this works, it’s a pain when the login token expires and I have to copy a new token to the Authorization header for every saved API call.  For an API where you have a lot of endpoints saved, updating all the Authorization headers can become tedious. Continue reading “Postman – Using Environments and Tokens”