Monday, 21 April 2008

Importing an Excel spreadsheet using C#

Here is a simple snippet which shows how to import data from an excel spreadsheet into your domains objects using c#. 

The following code snippets form part of a static helper class called ExcelImport.cs.  We make use of generics here, in that we assume the developer is aware of the type of object they are trying to populate from the excel spreadsheet. 

Snippet 1 - Static import Class, Parse<K> Method.

Our static class will have a generic method called "Parse<K>".  This method, as you can see, is a generic method which takes two parameters (fileName and workSheetName). The method makes use of the OleDbConnection and OleDbDataAdapter ADO classes to in effect "query" the spreadsheets data.

public static IEnumerable<K> Parse<K>(string fileName, string workSheetName) where K : class
{
IEnumerable<K> list = new List<K>();
string connectionString = string.Format("provider=Microsoft.Jet.OLEDB.4.0; data source={0};Extended Properties=Excel 8.0;", fileName);
string query = string.Format("SELECT * FROM [{0}$]", workSheetName);

DataSet data = new DataSet();
using (OleDbConnection con = new OleDbConnection(connectionString))
{
con.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(query, con);
adapter.Fill(data);
list = PopulateData<K>(data);
}

return list;
}



As you can see, this method hands off the actual population logic to a private static method called "PopulateData".  Parse<K> hands the type of 'K' down to the PopulateData method along with the new DataSet which has been read from the spreadsheet.



Snippet 2 -PopulateData<T> method.




private static List<T> PopulateData<T>(DataSet data) where T : class
{
List<T> dtos = new List<T>();

foreach (DataRow row in data.Tables[0].Rows)
{
T dto = Activator.CreateInstance<T>();

PopulateFieldsFromDataRows(row, dto);
dtos.Add(dto);
}
return dtos;
}



The PopulateData method instantiates a List<T> ('K' from the Parse<K> method) and iterates through each row in the DataSet.  It then uses the Activator.CreateInstance<>() to create a new dto (Of type T).  We then hand off to yet another method which uses reflection to map the data to the business object, called PopulateFieldsFromDataRows().



Snippet 2 -PopulateFieldsFromDataRows()  method.




private static void PopulateFieldsFromDataRows(DataRow row, object o)
{
foreach (DataColumn col in row.Table.Columns)
{

string name = col.ColumnName;
System.Reflection.FieldInfo field = o.GetType().GetField(name);
if (field == null)
{
PropertyInfo prop = o.GetType().GetProperty(name);
if (prop != null)
{
if (prop.CanWrite)
{
if (prop.PropertyType.Equals(typeof(DateTime)))
{
DateTime d = (DateTime)row[name];
if (d.Equals(new DateTime(1900, 1, 1)))
{
d = DateTime.MinValue;
}

prop.SetValue(o, d, null);
}
else if (prop.PropertyType.Equals(typeof(int)))
{
prop.SetValue(o, Convert.ToInt32(row[name]), null);
}
else if (prop.PropertyType.Equals(typeof(decimal)))
{
prop.SetValue(o, Convert.ToDecimal(row[name]), null);
}
else
{
string value = row[name].ToString();
prop.SetValue(o, value, null);
}
}
}
}
else
{
if (field.FieldType.Equals(typeof(DateTime)))
{
DateTime d = (DateTime)row[name];
if (d.Equals(new DateTime(1900, 1, 1)))
{
d = DateTime.MinValue;
}

field.SetValue(o, d);
}
else if (field.FieldType.Equals(typeof(int)))
{
field.SetValue(o, Convert.ToInt32(row[name]));
}
else if (field.FieldType.Equals(typeof(decimal)))
{
field.SetValue(o, Convert.ToDecimal(row[name]));
}
else if (field.FieldType.Equals(typeof(bool)))
{
if (row[name] == DBNull.Value)
{
field.SetValue(o, false);
}
else
{
field.SetValue(o, Convert.ToBoolean(row[name]));
}
}
else
{
field.SetValue(o, row[name]);
}
}
}
}



Here, we simply reflect over the object and access any public fields, or properties.  We then try and populate the objects property/field based on its name.



Below is an example of how the static class is used.  I have implemented a CreatenewBusinessObject<> static class here, just to because I think that handing object creation off to a factory is a good thing ;).




class Program
{
static void Main(string[] args)
{
IGenericObject o = BusinessObjectFactory.CreateNewBusinessObject<GenericObject>();
GenericObject obj = new GenericObject();
obj.Records = new List<GenericObjectDto>(ExcelImport.Parse<GenericObjectDto>("d:\\test.xls", "test"));

foreach (GenericObjectDto dto in obj.Records)
{
Console.WriteLine(string.Format("FirstName: {0}, LastName:{1}", dto.FirstName, dto.LastName));
}
}
}



GenericObject classes:




using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ExcelImportSample
{
public interface IGenericObject
{
string Name { get; }
Guid Id { get; }
List<GenericObjectDto> Records { get; set; }
}

public class GenericObject : IGenericObject
{
public GenericObject()
{
this.Id = new Guid();
}

public List<GenericObjectDto> Records
{
get;
set;
}

public string Name
{
get
{
return "Test Object";
}
}

public Guid Id
{
get;
private set;
}
}

public class GenericObjectDto
{
public string FirstName { get; set; }
public string LastName { get; set; }
}

public static class BusinessObjectFactory
{
public static T CreateNewBusinessObject<T>() where T : class
{
return Activator.CreateInstance<T>();
}
}
}



Again, a very simple and elegant solution to reading an Excel spreadsheet into your c# application, but one step further.  By utilizing the power of Generics and Reflection we can simply hand off the processing to a static helper.



 



Have Fun !



 




 


 

Tuesday, 15 April 2008

Hosting Windows Forms Controls in WPF (Threading)

The threading model in WPF differs somewhat from the conventional Win32 application model.  WPF objects belong to the thread that created them, and cannot be accessed directly by any other thread.  As most objects in WPF inherit from the DispatcherObject class, we can get at an objects Dispatcher property to update any properties on the object from another thread.

So how do we allow access to our object from a Windows form control?  Well, the answer is relatively simple.  Windows forms controls MUST be embedded inside a WindowsFormsHost object (A WPF wrapper for Windows Forms controls).  As the WindowsFormsHost object is  a WPF object, it therefore has a Dispatcher property which can be accessed.

The below example is not a full code sample, what I am trying to highlight here is the use of the Dispatcher to gain access to a Windows Form Control's thread.

 

Example

Below is a simple Windows Forms User control, which is hosting the Windows Media Player ActiveX control.

Create a new Windows Forms Library project within your WPF solution.  Here we will create our custom Windows Forms Control.

 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using AxWMPLib;

namespace WmpAxLib
{
public partial class WmpAxControl : UserControl
{
public AxWindowsMediaPlayer MediaPlayer
{
get
{
return this.axWindowsMediaPlayer1;
}
}
public WmpAxControl()
{
InitializeComponent();
}
}
}



As you can see, I have exposed the AxWindowsMediaPlayer object as a property Called MediaPlayer.  This is so we can access the Media Player directly in our example from the WPF code.  For now, we will not do anything else with this control but it is worth noting that we could expose events on this object for any Media Player events (for example we could just expose the Ctlcontrols property directly for controlling Play, Stop etc.



Now that we have our simple media player, we can host it inside our WPF application. 



<Window x:Class="MediaPlayer.MainPlayer"
xmlns
="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x
="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:wfi
="clr-namespace:System.Windows.Forms.Integration;assembly=WindowsFormsIntegration"
xmlns:wf
="clr-namespace:System.Windows.Forms;assembly=System.Windows.Forms"
xmlns:AxWMPLib
="clr-namespace:WmpAxLib;assembly=WmpAxLib" Loaded="Window_Loaded"
Title
="MainPlayer">
<Grid>
</Grid>
</Window>


The XAML markup shown above has references to the Windows.Forms.Integration and the Windows.Forms assemblies.  These assemblies are needed in order to host a Windows Form control within your WPF application.  There is also a reference to the Windows Forms Library assembly we created earlier, called AxWMPLib.



Now, personally I prefer to programmatically create a WindowsFormsHost control in the codebehind as a private field.  It is perfectly valid to create a control in your XAML if you wish, either way will work fine.



The next step is to add your Windows Forms control to the newly created WindowsFormsHost control.  To do this, in XAML, you simply embed your control inside the WindowsFormsControl control.  This example will show how to do it in codebehind.



1. Create two private fields ;



WmpAxLib.WmpAxControl _axWmp = new WmpAxLib.WmpAxControl();
WindowsFormsHost _host
= null;


2. In the Window_Loaded event handler, initialise the _host and _axWmp objects and add the _host control to the Windows Children collection;




_host = new System.Windows.Forms.Integration.WindowsFormsHost();
_host.Width
= 0;
_host.Height
= 0;
_host.Child
= _axWmp;
this.MainGrid.Children.Add(_host);



3. Setup a handler for the _axWmp controls "OpenStateChanged" event.




_axWmp.MediaPlayer.OpenStateChange += new AxWMPLib._WMPOCXEvents_OpenStateChangeEventHandler(MediaPlayer_OpenStateChange);



4. In the newly created handler, we want to set the _host width and height to full screen.



void MediaPlayer_OpenStateChange(object sender, AxWMPLib._WMPOCXEvents_OpenStateChangeEvent e)
{
string name = string.Empty;

_host.Dispatcher.Invoke(DispatcherPriority.Send,
new ThreadStart(delegate
{
_host.Margin
= new Thickness(0, -90, 0, 0);
_host.Height
= this.Height;
_host.Width
= this.Width;
name
= string.Format("Now playing {0}", _axWmp.MediaPlayer.currentMedia.name);
}));
}


In order to access the ActiveX control which is hosted inside our Windows Forms Control, we need to access its thread.  As the thread is not the same as the current UI Thread, we need to ask the dispatcher object to post a message to the control.  We do this by creating a new ThreadStart() object and rather than pass a delegate , we use an anonymous delegate to do the work.  We are also settings the DispatcherPriority to "Send" which tells the dispatcher to process BEFORE other asynchronous operations. 



The delegate then sets the _host's Margin, Height and Width properties.  It also accesses the MediaPlayer property we set on our Windows control, in order to get the name of the media we are playing and sets it to our locally declared string.



Further Reading



http://channel9.msdn.com/ShowPost.aspx?PostID=283473



http://msdn2.microsoft.com/en-us/library/system.windows.threading.dispatcher.invoke.aspx