Blog

Integrating Microsoft Office Functionality into Your SharePoint Apps

Have you ever come across this snippet of JavaScript before?

try {
   xhr = new XMLHttpRequest();
} catch(e) {
   xhr = new ActiveXObject(“Microsoft.XMLHTTP”);
}

If you haven’t then not to worry, it’s not really the point of this post anyway – but for the uninitiated this is a snippet of JavaScript that prepares an object that will be used to make an ajax request. Ajax is the mechanism by which a webpage can load more data from the server even after the page itself has finished loading, and you find it all over the place.

The reason I bring it up is because of the try… catch construct involved. Essentially it tries to execute the code between the first set of curly braces, and if that fails then it executes the code between the second set instead. In the huge majority of cases the first line of code executes successfully, so why is the second line necessary?

Microsoft. And more specifically, Internet Explorer 6.

For anybody who makes things that live on the web, supporting Internet Explorer 6 is an absolute chore. It does things differently to other (read “standards complaint”) browsers, and you end up having to create everything twice and put a bunch of hacks in place to make your site work with it. The block of code above is a prime example: every other browser has the XMLHttpRequest object built into their JavaScript implementation, but for Microsoft you have to use an ActiveX object instead.

More recent versions of Internet Explorer are much better in this regard, and these days frameworks like jQuery take care of any little annoyances like this that remain so it’s not as big a deal as it was a decade ago, but that’s not the point of this post either.

The point is that ActiveX remains a part of Internet Explorer to this day, and despite the fact that many web programmers know it primarily as a result of the XMLHttp annoyance described above, it does have its uses. So let’s exploit it.

The Downside

If you’ve ever created web content then you’ll know the importance of standards compliance. Writing compliant markup and code helps to ensure that your site works in whatever browser software your end users happen to be using. That’s a good thing.

ActiveX exists only within Internet Explorer. If visitors to your SharePoint webapp are using another browser such as Chrome or Firefox then none of the following example code is going to work for them. The best we’ll be able to do is detect that it hasn’t worked and have our app react in an appropriate way, maybe with a warning message or something similar.

If we were creating something publicly accessible for consumption by a wide variety of internet users then this would be a deal breaker, but in the context of an app built atop SharePoint where all the users are within the four (physical or otherwise) walls of your organization? It might be fine. If your organization is like the one I work for then everybody is using Internet Explorer anyway, because that’s the browser installed on your computer when IT deliver it, and getting them to install an alternative is like pulling teeth.

OK, So What is This ActiveX Thing?

Wikipedia sums it up pretty nicely, including condensing my previous three paragraphs down into a single sentence.

Many Microsoft Windows applications — including many of those from Microsoft itself, such as Internet Explorer, Microsoft Office, Microsoft Visual Studio, and Windows Media Player — use ActiveX controls to build their feature-set and also encapsulate their own functionality as ActiveX controls which can then be embedded into other applications. Internet Explorer also allows the embedding of ActiveX controls in web pages.

However, ActiveX will not work on all platforms, so using ActiveX controls to implement essential functionality of a web page restricts its usefulness.

In other words, one of the things we can use ActiveX for is to take the functionality of a Microsoft application and embed it into a web page. I’m going to put together a fairly simple example, and I’m going to use Excel. Let’s dive in!

The HTML

I’m going to build a simple table in HTML. The data in the table could come from anywhere, such as a subset of some SharePoint-based dataset or other pulled together using some of the techniques we’ve looked at previously, or a script executed server-side if you have the ability to create such a thing. For the sake of simplicity though, I’m just going to define in a static manner in my markup – and I’m not going to worry about making it look good.

<table>
   <thead>
      <tr><th>Fruit</th><th>Qty</th></tr>
   </thead>
   <tbody>
      <tr><td>Bananas</td><td>5</td></tr>
      <tr><td>Apples</td><td>7</td></tr>
      <tr><td>Oranges</td><td>2</td></tr>
      <tr><td>Pears</td><td>3</td></tr>
   </tbody>
</table>

image

The Template

Next, I’m going to define an excel template that we’ll use to place our data into. This could be as detailed or as simple as necessary, so for the purposes of example I’ve gone with simple again. All I’ve done in mine is put headings at the top of column A and B that match the headings in our HTML.

image

The JavaScript

OK, here’s where the clever bit starts. The first thing we’re going to do is create an ActiveX object pointing to excel, and assign it to a variable so we can reference it again further along in the code.

var exApp = new ActiveXObject('Excel.Application');

Next we’re going to open a new document in excel, based upon our template. Doing this also returns an object that we’ll need again, so we’re going to assign this one to a variable too.

var exDoc = exApp.Workbooks.Add('http://192.168.1.102/web/template.xltx');

It’s important to note here that we have to pass in an absolute reference to the template file – a relative reference is not sufficient because excel has no concept of the location of our webpage. In my test environment the SharePoint server is at 192.168.1.102, but this will undoubtedly be different for you.

At this point, excel is open and has our template loaded, so the next thing to do is iterate over the table in our HTML and plug the data into excel. In general, this is done with the following line of code:

exDoc.ActiveSheet.Cells(1, 1).Value = 'This is row 1, column 1!';

More specifically what we’re going to do is use our old friend jQuery to iterate over the table cells in our HTML page and put them into the right place in excel with the help of a couple of simple counter variables: one for the row we’re targeting, and one for the column. Don’t forget to include a reference to the jQuery library in the document <head> section.

r = 2;
$('table tbody tr').each(function() {
   c = 1;
   $(this).children().each(function() {
      exDoc.ActiveSheet.Cells(r, c).Value = $(this).html();
      c++;
   });
   r++;
});

While we’ve been preparing all this, the Excel window has been invisible to the user. The final step is to make it and its newly imported data appear.

exApp.Visible = true;

Done!

Putting it All Together

The full code of our HTML page is as follows:

<!DOCTYPE html>
<html>
<head>
   <title>Export to Excel Example</title>
   /web/js/jquery-1.11.0.min.js
   
      $(document).ready(function() {
         $('input#export').click(function() {
            var exApp = new ActiveXObject('Excel.Application');
            var exDoc = exApp.Workbooks.Add('http://192.168.1.102/web/template.xltx');

            r = 2;
            $('table tbody tr').each(function() {
               c = 1;
               $(this).children().each(function() {
                  exDoc.ActiveSheet.Cells(r, c).Value = $(this).html();
                  c++;
               });
               r++;
            });

            exApp.Visible = true;
         });
      });
   
</head>
<body>
   <table>
      <thead>
         <tr><th>Fruit</th><th>Qty</th></tr>
      </thead>
      <tbody>
         <tr><td>Bananas</td><td>5</td></tr>
         <tr><td>Apples</td><td>7</td></tr>
         <tr><td>Oranges</td><td>2</td></tr>
         <tr><td>Pears</td><td>3</td></tr>
      </tbody>
   </table>

   <input type="button" id="export" value="Export to Excel">
</body>
</html>

Taking it Further

What I’ve put together here is a pretty simple example, but hopefully you can see the value in some of the possibilities this opens up. Getting complex data from a webapp into Excel is actually fairly straightforward.

With a more detailed template to export data into you could prepare webapp data for analysis in excel, create charts, etc, etc.

Enjoy!

Shrapnel

Late Night Links – Sunday July 27th, 2014

The weekend may be over, but don’t worry! That just means it’s late night links time again. Let’s get on with it then, shall we?

And that’s it! Next week is a holiday weekend, so I may or may not remember to post some links on time. Here’s hoping!

Blog

Home Server Refresh

I get paid every other Thursday, or 26 times a year. That mostly means I get paid twice a month, but twice a year there’s a month where I get paid three times, and this is one of those months.

Since a big chunk of our expenses are monthly, getting paid three times in a month means I have some extra cash left over to play with, and this month I’m going to use it to replace our home server.

Above is our existing home server. It’s a pogoplug device that I’ve hacked to run debian linux. It’s primary function is as network attached storage for all the other devices in the house, and the box to its right is a 2tb USB hard-drive. It also runs a LAMP stack for development, and some torrent software so it can handle downloading duties without any of the other computers in the house needing be kept on.

It only has 256mb of RAM though, and just occasionally if it’s under heavy load things fall down. The torrent daemon is usually the first victim: sometimes I go to check on the status of a download only to find that the downloading process has run out of resources and shut itself down.

My requirements for a replacement are that it handle all the tasks the existing server does – without the problems caused by the limited memory, uses electricity sparingly, and also gives me room to grow and try new things that I haven’t even thought of yet.

My plan is to replace it with a machine I build based on an Intel NUC.

This thing is a barebones machine (it doesn’t include any RAM or storage in the basic package), but it could be useful for many scenarios (I think it would make a great HTPC, for example) including mine.

I’m going to max it out with 8gb of RAM, and this 32-fold increase over what I have now should allow for a whole host of new possibilities.

I’m going to take advantage of the extra resources by attempting to install vSphere Hypervisor on it and split it into a number of virtual servers. One will be linux-based to replicate the functionality of the existing server, one Windows-based VM, and maybe two extra VMs (one linux, one Windows) so I can separate my playing around from mission-critical server duties.

I’ll be posting more as I work my way through the process.