Open XML SDK and Excel Pivot Tables - Pulling Data

As you may already know, the new Office document formats are pretty much "ZIP" files with XML files inside. These XML documents are formatted according to the Open XML spec, which Microsoft has released version 2.0 of their SDK for interacting with using strongly typed classes in .net.

If you are interested in downloading the SDK it is here:

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=5124

The documentation for the SDK is here (thought it is rather sparse):

http://msdn.microsoft.com/en-us/library/bb448854.aspx

Now, if all you want to do is read data out of particular cells, the best article I have found thus far explaining this is here:

http://blogs.msdn.com/b/brian_jones/archive/2008/11/10/reading-data-from-spreadsheetml.aspx

There is also a useful MSDN article that has a method XLGetCellValue() that you can copy-and-paste into a class:

http://msdn.microsoft.com/en-us/library/ff921204.aspx

However, if you are working with pivot tables, this is probably not the case since you want the data and the presentation of the data in the cells may change if the user, say, reorders the spreadsheet.

First you need to get the PivotCacheDefinition from the WorkbookPart. A sample implementation of this might be...

The PivotCacheDefinition has an enumeration of CacheField's, which define each field in the dataset and also contain the values of a particular field in any given row. Since you will need to reference these often, I found it helpful to put them into a dictionary so that I could reference them by name and have the index handy.

I created a class to store the CacheElement and the index...

...and populated the a dictionary from the PivotCacheDefinition:

To get at the rows themselves, you enumerate the PivotTableCacheDefinitionPart.PivotCacheRecords property. Be careful not to confuse this with the "PivotCacheDefinition" we used earlier. Here is some sample code that would use the dictionary above to pull the address value for each row:

This can all be greatly simplified by putting a method that accepts a CacheElement type and a PivotCacheRecord and extracts the value for us. This will always be a type string (since this is XML) which we will have to cast into whatever type of value that we want.

Not all values are stored in the CacheField elements since some of them are strings that are stored in the row/field itself. For these you can use something like...

PivotCacheRecord.ElementAt(x).GetAttributes().First(a => a.LocalName == "v").Value;

I found it very helpful to look inside the XLSX file itself and dig around to make sure I was pulling the correct data and learn where everything is stored. Just use your favorite ZIP extractor and unzip the XLSX. Here is what I found:

(root)\xl\sharedStrings.xml – An index of strings
(root)\xl\worksheets\sheet1.xml – Sheet 1’s cells, which may reference strings in sharedStrings.xml

(root)\xl\pivotCache\pivotCacheRecords1.xml – Each record which indexes values in pivotCacheDefinition1.xml, unless the particular field is of special String type (then it has the value itself)

(root)\xl\pivotCache\pivotCacheDefinition1.xml – The columns in the pivot table data (not what is shown in the Excel file) and the actual values that are indexed

Forcing Line-Breaks in a Cross-Browser Manner

This works in IE versions 6-9 as well as Firefox and Chrome:

word-wrap:break-word;

word-break:break-all;



This will force linebreaks in strings of really-really-really-really long text so that they do not overflow their container (ie: a DIV).

AJAX CDNs

Both Google and Microsoft have AJAX/JavaScript/jQuery CDNs.

Google works directly with the key stake holders for each library effort and accepts the latest stable versions as they are released. Once we host a release of a given library, we are committed to hosting that release indefinitely.
The Libraries API takes the pain out of developing mashups in JavaScript while using a collection of libraries. We make it easy for you to host the libraries, correctly set cache headers, and stay current with the most recent bug fixes.

By taking advantage of the Microsoft Ajax CDN, you can significantly improve the performance of your Ajax applications. The contents of the Microsoft Ajax CDN are cached on servers located around the world. In addition, the Microsoft Ajax CDN enables browsers to reuse cached JavaScript files for Web sites that are located in different domains.

Both Microsoft and Google’s CDNs support HTTPS as well as HTTP, so they can be included in both. They also include both minified and full versions of the jQuery scripts.

Documentation on Microsoft’s AJAX CDN is here:
http://www.asp.net/ajaxlibrary/cdn.ashx
An example of loading jQuery from Microsoft’s CDN:
http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.5.2.min.js

Documentation on Google’s AJAX CDN is here:
http://code.google.com/apis/libraries/
An example of loading jQuery from Google’s CDN:
http://ajax.googleapis.com/ajax/libs/jquery/1.5.2/jquery.min.js
Google also has a “libraries API” so that JavaScript files can be loaded using code rather than inserting additional script tags:
http://code.google.com/apis/libraries/devguide.html

Android "Hello World": a Tale of Woe

I consider myself a fairly patient man, but if there's one thing on this good green earth that should be stupid simple to do for a development platform is to find the development tools, install them, and be able to run a well documented "Hello World" example. I've done this any number of times in the past with everything from FORTRAN (on punch cards) to Objective-C, and it's generally been a positive experience.

And then there's Android.

I work every day with Visual Studio and the .NET Framework, and I've dabbled with iPhone development, so perhaps I'm spoiled...the stuff "just works" (at least for simple stuff). But my attempts to install the Android tools were an exercise in frustration.

The Android developer site provides lots of great, well organized information...so I knew that I had to install 3 different items to get up and running:

• Java SDK
• Android SDK
• Eclipse IDE

It started to get interesting when I had to figure out exactly which version of each of these I needed (since they all need to play nicely together), and found that the current version of Eclipse (Helios v3.6) is incompatible with the Android SDK, so I needed to install the previous version (Galileo v3.5). No biggie. Install different version of Eclipse.

And I needed to install the ADT Plugin for Eclipse. OK, done.

And I need to configure Eclipse so it knows where the Android SDK lives. Gotcha. (C:\Program Files (x86)\Android\android-sdk-windows was my location, for those of you who may be playing along at home.)

Things got a little more interesting when fired up the Android SDK and AVD Manager to create my first virtual device (i.e. the phone emulator). Ah, but which of the 5 different available versions of Android should I target? The latest and greatest is Gingerbread (v2.3), so I figured "Cool, I'll go with that!" I gave it the very clever device name of "gingerbread", and all seemed right with the world. But when I tried to start my shiny new virtual device, I got an error: "could not find virtual device named 'gingerbread'".

Grrrr.

A little digging, and I found that for some inexplicable reason, the virtual device had been created under a different machine user account, so the AVD Manager couldn't find the files it needed. Well, I can just drag and drop the files to my user directory.

No joy.

Well, I can just change the default location the AVD Manager uses when it creates an AVD. Dig, dig, dig. Ah, I can specify the location of the AVD files...by using a command line utility. Seriously? A command line utility?!? It's 2011...the year AFTER we make contact. WTH?

I find the syntax, and after much frustration (apparently it's both case-sensitive, and the order of the switches must also be important...because the example I copied and pasted from the page didn't work) I get it to work. I also figure that maybe the latest and greatest Android version isn't quite ready for prime time, so I revert back to the previous version (Froyo v2.2). So I now have an AVD (called "froyo") created in the location I wanted using the following:

android create avd –t 4 –n froyo –p c:\users\wloescher\.android\avd\ --force

(And don't get me started about TargetID vs. Version Number...sheesh!)

But of course this AVD doesn't show up in the AVD Manager, so I can't just click a button to fire it up. So by this time I'm calling in some help from my co-worker (who actually owns an Android phone and has gotten all this stuff working just fine on his machine). He comes up with the following command line to actually launch the virtual device:

emulator -data c:\users\wloescher\.android\avd\userdata.img

A minute or two later, and the virtual device is booted up and I have a virtual Android phone screen. Great! Now all I have to do is write a little code, and I'm done...right?

Wrong. There seems to be a disconnect between Eclipse and my AVD. The code seems to run fine (i.e. no errors), but nothing happens. Nothing. Not an electronic sausage. Just this error in the console:

[2011-01-06 10:43:11 - HelloAndroid] ------------------------------
[2011-01-06 10:43:11 - HelloAndroid] Android Launch!
[2011-01-06 10:43:11 - HelloAndroid] adb is running normally.
[2011-01-06 10:43:11 - HelloAndroid] Performing com.example.helloandroid.HelloAndroid activity launch
[2011-01-06 10:43:11 - HelloAndroid] Automatic Target Mode: launching new emulator with compatible AVD 'froyo'
[2011-01-06 10:43:11 - HelloAndroid] Launching a new emulator with Virtual Device 'froyo'
[2011-01-06 10:43:11 - Emulator] emulator: ERROR: unknown virtual device name: 'froyo'
[2011-01-06 10:43:11 - Emulator] emulator: could not find virtual device named 'froyo'

So...what have we learned?

Well, first off I'd like to thank the fine folks at Microsoft and Apple for making development tools that have a low cost of entry. Secondly, I hate the command line. Thirdly, I picked up a few new (to me) command line trick from my (very patient) coworker Michael Sneade. Fourthly, don't get involved in a land war in Asia. (Or was it don't bring a knife to a gunfight...I can never remember. Maybe it's both. Probably both, yeah.)

And fifthly and finally...if you want people to embrace and be excited about your development platform, don't frustrate the hell out of them right out of the gate. Goodbye (at least for now) to the world of Android...let's see how quickly I can get up and running on Windows Phone 7 and XNA Game Studio.

------------------------------------------------------------

UPDATE:
I was finally able to get the "Hello World" sample app to run in the emulator by adding a new Windows user environment variable:
1. Start Menu > Control Panel > System > Advanced System Settings (on the left) > Environment Variables
2. Add a new user variable (at the top):
     Variable name: ANDROID_SDK_HOME
     Variable value: C:\Users\WLoescher

I good friend reminded about Google's App Inventor (still in beta), and I'll do a post about that experience soon...it's very cool!

Extending the javascript Array object

The ubiquitous javascript Array object. We use it every day, but unfortunately it is missing a few useful things like simple get() and contains() methods. But luckily for us, its easy to extend. Here are a couple simple examples of how to extend the Array object with methods we wish it had by default.

Get Method
Create a simple array of name/value pairs.
Get an array member based on its name.

Array.prototype.get = function(key) {
var i = this.length;
while (i--) { if (this[i].split(":")[0]==key){return this[i].split(":")[1];} }
}

function testGetMethod() {
var opts=["1:one","2:two","3:three","4:four","5:five"];
var item = opts.get("3");
alert(item);
}
//output: three


Contains method
Create a simple array, and see if an item exists in the array.

Array.prototype.contains = function(obj) {
var i = this.length;
while (i--) { if (this[i] === obj){return true;} }
return false;
}

function testContainsMethod() {
var opts=[1,2,3,4,5,10];
if(opts.contains(3)){alert('true');}
}
//output: true


Implementation
Just include the "Array.prototype..." methods somewhere in your page, or a global .js file to make them available to all your pages.

Enjoy!

Get a Fully Qualified URL from a Virtual Path

I needed to create a page to handle 404 errors ("Page Not Found"), and automatically redirect them to a related page within the web site. I ran into a little problem when I tried to display the fully qualified URL from a virtual path (all done on the server side in C#). 

With some input from an associate (who suggested I use Url.GetLeftPart), I came up with the following solution:

string fullyQualifiedUrl = GetFullyQualifiedUrl("~/Home");

private string GetFullyQualifiedUrl(string virtualPath)
{
    HttpRequest request = HttpContext.Current.Request;
    string urlLeftPart = request.Url.GetLeftPart(UriPartial.Authority);
    string appPath = request.ApplicationPath;
    string fullyQualifiedUrl = string.Format("{0}{1}{2}", urlLeftPart, appPath, virtualPath).Replace("~/", "");
    return fullyQualifiedUrl;
}

Share & Enjoy!