Anything related to the .NET framework
11 Jun 2009

Reading and writing to Excel 2007 or Excel 2010 from C# - Part I: Primer

[Note: See the series index for a list of all parts in this series.]

Over the past week I have been learning about the complexity of working with Excel 2007 native file format - XLSX or as it is known correctly, SpreadsheetXL. There is three ways to work with it, firstly build your own parser - just too much work for me or second use OpenXML SDK format which Microsoft provides. The current version, at time of writing that was version 1, of the SDK is not great: there is very little (if any) benefit of using it over the third method. There is a V2 SDK currently in beta which looks brilliant and frankly when released would be the recommend route.

The third way, which is the way I chose is the uses new features introduced in the .NET Framework 3.0.

What is a XLSX file? A XLSX file is actually just a ZIP file which contains a number of XML files in it.


This means all you need to do is open the XLSX file as a ZIP file, get the right XML files (or parts as they are referred to) out of it and parse those.

If you are thinking this is a .NET only solutionthe chart below is from Doug Mahugh which shows a number of ways across a number of technologies/OS’s to do the same thing. This series will focus on the .NET way.


What is nice about using System.IO.Packaging to read the file over the direct ZIP options, is that there are some helper methods to make it easier when working with any of the new formats (docx, xlsx etc...)


08 May 2009

My Presentation @ Dev4Devs

If you are attending Dev4Devs on Saturday (or are here after the event) and you are looking for a copy of the slides and code you can get them below! If are looking for the ADO.NET Data Services cheat sheet I mentioned then you need to go here.


The code here is also different from what I presented in the following ways:

  • There is a timer control in it - so if you add items to the DB while on the site, it updates and shows those changes within 5 seconds.
  • The layout is slightly bigger (bigger header) and has buttons (to make it look like an email client) - these were removed because it doesn’t work at 1024x768 (aka the projector resolution). So they back in their graphical beauty.
  • There is a feed button which links to a ATOM feed for the last 10 emails - something I mentioned you could do, well now you can see it.
  • There is a database creation script, but no data. You need to create your own data.
Slide Show
07 May 2009

Some Free Posters I've Created Recently

One of the things that I do at BB&D, is produce guidance posters. So far I have produced two of them and both are publically available on the BB&D developer guidance site DRP. The first is “Outlook + Exchange = Better Together” and the second is “ADO.NET Data Services Cheat Sheet”, not two things you think about together often.

Outlook + Exchange = Better Together

The title is a bit marketing-ly, but the poster is really a nice over view of the 8 key areas of Outlook namely

  • Email
  • Contacts
  • Outlook Web Access
  • SharePoint
  • Calendars
  • Tasks
  • Outlook Features
  • RSS

The poster looks a little busy, but when printed at A3 it’s not bad at all. It also includes three areas (OWA, Mailbox size, SharePoint) where you can write in your organization details so if you print them out and put them on the wall they have some organization context.



ADO.NET Data Services Cheat Sheet

The next one I developed when learning with ADO.NET Data Services and it’s a bright and fun cheat sheet for it. It includes information on the query operators (with samples along the border), a list of functions, a list of comparison operators (like less than), query order, keys, and $value. I’ve found it very useful to print out and put up. It is designed for A3, but I have it printed at A4 (in grey scale) and it works just as well.

ADO.NET Data Services Cheat Sheet


29 Apr 2009

Glimmer - jQuery IDE


jQuery is a fairly easy to use if you understand CSS or been working with HTML for a while, but what if you are a drag & drop developer? Who never has done that? Or if you want to just whip up a nice effect quickly? Well the guys from Microsoft have recently launched Glimmer which is a free jQuery effects IDE. 


When you launch the very good looking WPF application (read:SLOW) you get four templates, which 3 of them have a wizard interface to set it up.

  • Image Sequencer (click here for the demo of this effect): This creates a rotating image box. Think of use for banners where you want to have each image go to a different URL or for a picture rotate/slide show. This one has four steps:
  1. Description (where is the “Do not show this again” checkbox)
  2. Select the images and set the URL for them  image. What is nice here is the live preview window on the right hand side.
  3. Set how long to delay between images and what the image size should be (say if you need to resize)
  4. Lastly the save page. The UI is a little odd here, since there is no finish button. You need to use the (large) save button. When you save it you get a folder with an images sub folder (containing your images), a CSS file, an image for the button (to manually switch between images), jQuery 1.3.2, a minified js file of the effect you have done and a html page for testing/usage
  • Dropdown Menu Wizard (click here for the demo of this effect): This creates a drop down menu. It has 3 steps:
    1. Description (no “Do not show this again” check box again)
    2. Set up the top level menu options image
    3. Add second level options and save. The output is pretty much the same as with image sequencer - minified js file of your effect,  jQuery and an HTML page.
  • Custom: The non-wizard of the group. This guy is powerful and will let you build a lot of effects together, but you need to have an HTML file to start with as it will give you a list of the tags/id’s available - no page, no tags, nothing to work with: . If gives you a single page to wire up the events and I think this would be useful for people needing something special. image
  • Tooltip Wizard (click here for the demo of this effect): Two step wizard to add tool tips to text/images.
    1. Description (no “Do not show this again” check box again)
    2. On step two you put in the text/image and the tool tip and save it.

    I do like the menu option “Clean Code Mode” (under the options menu) which disables the minifi-ing of the code it generates. This will be very useful for people using this to learn from.

    Overall this is a good first drop, although it should be viewed as a beta. The performance is terrible, there is so much more you could do with jQuery that it doesn’t do, it is unaware of plug-in’s which is important for jQuery and the UI has many things that aren’t logical (there is no hint that the Custom needs a HTML page to actually do anything). However as a tool to train people, or give designers a tool to work with jQuery this will be very valuable and I will be demo’ing it at my jQuery talk.

    06 Apr 2009

    VSTT Reference Guide

    If you are looking for some serious guidance on Visual Studio test, then the Rangers have come to the rescue (again) with a lovely 83 page document on VSTT. Get it from CodePlex

    It sounds like a contraction that 83 pages is a reference guide but from my use this morning of it (couldn’t of come out at a better time for me), it isn’t. It is not meant to be read from beginning to end, rather you can easily skip between parts because the material is presented in a Q&A format which is easy to manage.

    What’s in it?

    02 Apr 2009

    How to convert a VS standard class library to a Workflow activity library


    image VS. image

    If you are working in a workflow foundation project you may find you have created a class library (see image below) and want to put workflow activities into it but you may have issues with that because you didn’t create a Workflow activity library.


    Some of the issues you may find are the add item menu doesn’t have the options you expect and the workflow designer interface doesn’t work.

    image VS. image


    To solve this you need to modify the class library project file very slightly. To do that simply open the file with a text editor such as Notepad or use the options in Visual Studio to unload and edit the project.


    The first thing is to add a ProjectTypeGuids node to the base PropertyGroup node. To get there you need to navigate to the PropertyGroup node (it is under the Project node) without any Condition attributes. On my machine that is on line 3


    Next add the following line in the node:


    Example: image

    Next you need add a new project target into the Project node. You should already have one of these  around line 56


    Right below that you need to add a new one (so you will have two), the line to add is:

    <Import Project="$(MSBuildExtensionsPath)\Microsoft\Windows Workflow Foundation\v3.5\Workflow.Targets" />

    Example: image

    Now save the project file and re-open it in Visual Studio and the problems should be gone!


    You may find that the designer is not working or you get errors when adding new activities still. In such a case it could be that you are missing the workflow foundation assemblies (System.Workflow.Activities; System.Workflow.ComponentModel; System.Workflow.Runtime):


    Once you have those added everything should be working fine.

    16 Mar 2009

    ADO.NET Data Services - Weird Error

    Working on a project which uses ADO.NET data services for a small part of it. I put the ADO.NET Data Services portion together (add the files, put the EF data model together, link it up etc…) on my machine (Windows 2008 Server, SQL 2008, VS 2008) and it all worked great! Checked into source control and then went about doing something else, ignorant of the coming problem.

    Then the developer who would actually consume it (we are consuming it in C# using LINQ) came to me saying that nothing worked :( All we get is the generic ADO.NET Data Services error: “An error occurred while processing this request”. We removed the where clauses and everything worked perfectly, which hinted to me the LINQ/EF expression tree was borking out somewhere. So we went to the EF data model, told it to update and it did a change under the surface and everything worked again?!


    I love TFS because it allowed me to compare my version and the updated one easily and their was one tiny change (literally 1 byte!) in the edmx file (not the .cs backing file, the edmx file itself). Turns out that in dev they are using SQL Server 2005 and I built the model with SQL Server 2008 - now note there is ZERO 2008 special features in use, but it seems that the EF expression tree doesn’t care and try’s to use some SQL 2008 magic cause it thinks it is SQL 2008. This is controlled by the ProviderManifestToken attribute! Changing that to 2005 (which is what the update did automatically for me) solved the problem!


    23 Feb 2009

    SharePoint Developer Tools: Feb CTP Release of VSeWSS 1.3

    There is a new release of the VSeWSS (what a crap name) CTP 1.3. What are these? These are the tools you need to actually work with SharePoint from Visual Studio to create the advanced workflows or web parts etc… It also includes the documentation that is invaluable for working with any part of SharePoint from Visual Studio. What’s new in the Feb ‘09 release:

    New Features in VSeWSS 1.3
    • The extensions now install on x64 bit OS. Visual Studio 2008 and SharePoint must be already installed.
    • Command Line Build option for TFS and MSBuild integration
    • Separate WSP Package and Retract commands. You can now build the WSP without deploying it
    • SPSolGen to Support Exporting from Content Management Publishing Sites
    • New Item Template for RootFiles Deployment
    • Automatically Remove conflicting existing features on development SharePoint server
    • WSP View New Feature Dialog Improvements: scope, receiver checkbox, element checkbox
    • WSP View can now be used to merge features and it blocks site features being merged into web features
    • Allow adding separate binary files such as Workflow assemblies
    • Some refactoring allowing for Web Part renaming and removing lines from feature.xml Item Removed
    • Allow selection of GAC or BIN deployment for Web Part Project not including CAS generation
    • Increase visibility of hidden features that VSeWSS creates
    • Add fast update deploy for DLL only or file only changes to solutions
    • Numerous Bug Fixes and improvements to error messages

    You can download it on the Connect site.

    Thanks to zainnab for the heads up.