My brother-in-law popped round a couple of weeks ago to see how I was recovering from my knee surgery. We got onto the topic of IT (as always) and how his department at a large financial company were in the process of coming up with their own adhoc financial system [more] because their IT department were:

a) To restrictive
b) To slow to respond to change requests

I shook my head as always saying they were just creating trouble for themselves later down the road, but if they were going to do it they should look into using Excel 2007 and the new Excel services it provides as they already have a MOSS installation to play with.

I touched on Excel services in Moss at a Crm training course I did at Microsoft earlier in the year and it looked pretty good, especially in an iFrame dashboard layout with a bunch of PKI showing, but that's as far as I've used them. I was going to spend some time at some point to learn some more. As luck would have it I'm subscribed to the blog of an old manager of mine from Capgemini, Joel Jeffery, and he's written an article on just this subject.

http://joelj.spaces.live.com/blog/cns!493B225BAB0E6A61!431.entry

Thanks Joel for the article nice introduction to some of the features and what you can do and some of the pain you might experience it also clarifies some of the can's and cannot's of Excel services.

Joel now runs JFDI Phoenix and is offering training that covers this as well as the rest of WSS and MOSS:

http://www.jfdiphoenix.co.uk/sharepoint+training.aspx?language=en-GB

I've forwarded the link onto my brother-in-law so hopefully they create something half decent for future dev teams to unravel!

Have you ever been given a spreadsheet to import into some system or other?

Have you then tried creating a simple dataloading program to read the data, run some dataloading rules against it before loading it into the correct fields on the target system.

Have you found this has caused problems in code because:

A) Everything is a string
B) It is hard to access the correct spreadsheet columns in code be because of a lot of column index numbers being used.
C) The Excel runtime must be installed on the computer running the data loading tool.

If you have shared this pain and have not found a better solution then please read on.
[more]

The process I now use when faced with this problem is to:

1) Define a simple xml schema per excel worksheet I need to use.
2) Map the schema elements onto the excel worksheet columns
3) Save the worksheet as ‘Xml Data’ (NOT XmlSpreadsheet)
4) Generate a .Net class using Microsoft’s Xsd tool and the same schema used on the worksheet.
5) Deserialize the ‘Xml Data’ version of the worksheet into the strongly type object created in .Net
6) Now you are in the world of .Net code and visual studio to manipulate the data in a strongly typed and intuitive way.

The attached zip file has an example of this process using data from the Northwind database.

There are two folders.

‘Step 1’ contains the source spreadsheet, schema file, example of the xml,  xsd tool, batch file to run the xsd tool, and a sample of the generated class

‘Step 2’ contains a Visual Studio 2005 solution and source code that includes the Xsd generated class, a Loader class that Deserializethe Xml file into an instance of the generated class, the Winform binds the loaded data to a DataGridView. I also included an additional partial class file to demonstrate how to extend the functionality of the generated code.

StrongTypeSpreadsheet.zip (194.80 kb)