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.
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)