Creating Excel like grid in Silverlight – C1FlexGridBook

If you are developing a business application, you will run into a situation where the end users want to export the data to excel and do some work there and then import the sheet back to the application. This is very normal, the main reason the users ask for it because Silverlight does not have a grid which look and behaves like Excel. There are bunch of work around for this, but I really like the C1FlexGridBook control from Component One. This gird out of the box gives excel like look and feel with less amount of code. Some of the basic features like created tabbed sheet at the bottom of the control to look like worksheets in Excel and many more.

Lets see how to create a simple excel like grid using C1FlexGridBook. What we need to create is Component One dlls for

 image

We need Excel, FlexGridBook and FlexGridBook.

So how do we go about adding the control. So in the XAML I created simple tag for C1FlexGridBook as follows

image

I am planning to create the columns in the code behind (I am trying figure out a problem so two birds with single stone). The data that I am binding is a collection of customers like the following

image

where the data structure is

image

When the page is navigated to the pages I create a customer collection like the following

image

Now all is left is to create the sheets and bind the data to the grid in each sheet. Here is where you have to be very careful. I would strongly recommend you to create skeleton sheets but do not creating individual grid with data binding, rather, you create the grid for the first and default sheet or the sheet the user wants to see. So here are the steps

Create the sheets with AddSheet and pass in the sheet name as the first parameter as shown below.

image

We also bound out collection to grid by doing that, the current grid associated with the sheet get the data from customers. We set the autogenerate to false since I have collection that I want to appear properly. So below create the dynamic columns on the fly

image

now the question is why would I need a cell template? Since the data is boolean I want to display a check box rather than textblock.

image

That’s it. Now if you would run the code you will see a result something like the following,

image

With very little code from control perspective, we got a grid which looks like excel. If you notice, it has following excel features out of the box

1. Row and Column numberings.

2. Office theme.

3. Fixed column heading for application.

4. Default Excel like filtering.

5. At the bottom, you see the sheets and also it comes with default behavior to let the user create new sheets.

6. Excel like navigation to move between sheets with forward and backward navigation button.

7. Navigate to first or last sheet with fast navigation.

8. If you have lot of sheets and want to navigate to a specific sheet, then right lick on the left or right navigation, it shows available sheets and just click on the sheet name you navigate to that sheet.

I really enjoyed working with this control. If you like it or would like to know more about this control send me a note.

Load operation failed for Query ‘X’ – Silverlight/EF

So I was given a project to debug and I started to debug and I got the infamous and more detailed error message from EF/WCF that ‘Load operation failed for Query ‘X’’. In my case this happened at the entry point at our authentication service itself. I know the code of authentication inside out so I added the existing project and start debug. I couldn’t debug and keep getting the same weird error message. So I went to our trusty friend Google and asked the question and vola there are bunch of posting on the same error message. The one really liked was from stack overflow. Even if you never got the error message but I would recommend reading the answer by John Nicholas. The reason for that is even though he did not give answer to the problem, he explained in two ways how you can tackle the problem. I took the short cut and enabled the WCF trace to see what is happening and the stack trace gave exact failing point at the server side and I was able to resolve the problem and keep moving. The sad part was the exception happened at the server side was specific bug in the code, but when we get the error message the message was not very obvious to track it down.With WCF tracing enabled it is easy to figure out what is happening at the server side fast and easy and right to the point.

Silverlight Starter Tips

I ran into two problems yesterday I thought I share it with you all and document it for future reference. I am using Silverlight 4 and VS2010.

1. I was writing a Silverlight Unit Test. When I compiled my simple test, it keep failing saying, Unable to load ‘System.Windows’ or its dependencies. It turned out, we need to make the System.Windows property to copy always.

2. I was debugging my Silverlight code and was able to step into my client code just fine. After some time, my debugging stopped working, none of my break points were hit. I was using Chrome as my default browser. I changed the browser to IE and debugging start working. So if you are a Silverlight developer do your self a favor and use IE other wise you will end up spending some time trying to figure this debugging problem.

Making Grid editable through program for C1FlexGrid

In some scenarios, you may have some user options, when they were selected, you want to take the user to a specific row and make it editable rather than asking the user to double click to enter editing. It is very easy to do it in Component One using following two lines

_flex2.Selection = new C1.Silverlight.FlexGrid.CellRange(0, 0);
_flex2.StartEditing(true);

First like set the row and cell where you want the control to go to and the second line enable editing for you.

If you still have trouble getting focus change the code to

Dispatcher.BeginInvoke(() => { this._flex2.Focus(); });.

Elegant way to create combo box in C1FlexGrid (Tip 8)

You are asked to create a Silverlight application to show a grid where one or more columns are combo boxes. This is a very common scenario. It can be very easily solved by creating a data edit template on the grid column and show a combo box, with a converter. There are lots of examples for this scenario, one in code project and another one in Manish blog. When I binged, there are lot of information in stack overflow. There is a much better solution if you are using Component One.

If you have component one, then we can remove all these data template, combo box and converters with simple property called Value Converter. Lets see the problem statement. We need to create a grid, with employee name and job title.

image

When you enter title in edit mode, it show available jobs like the following

image

Nothing special here, it is the standard combo box which shows all the available jobs for user to pick from. So how do we solve this problem using the special Value Converter?

Lets start from ViewModel this time. 

Code Snippet
  1.  
  2. public List<Person> People
  3. {
  4.     get
  5.     {
  6.         List<Person> list = new List<Person>();
  7.         list.Add(new Person { ID = 1, Name = "Ajay", JobID = 1 });
  8.         list.Add(new Person { ID = 2, Name = "Jeeem", JobID = 2 });
  9.         return list;
  10.     }
  11. }

Nothing new in the view model, it is same as you would build a collection in any other program. How about View?

Code Snippet
  1. <c1:C1FlexGrid Name="_flex" ItemsSource="{Binding People}" AutoGenerateColumns="False">
  2.     <c1:C1FlexGrid.Columns>
  3.         <c1:Column Binding="{Binding Name}"/>
  4.         <c1:Column Binding="{Binding JobID, Mode=TwoWay}" Header="Title"/>
  5.     </c1:C1FlexGrid.Columns>
  6. </c1:C1FlexGrid>

Look at that, there is nothing special here either. We have a grid with two columns, first column is name and second column is Job ID. Please note that, we are not using combo box here. Ok so far we did not do anything new and I still want a combo box in second column. Lets go to the view code behind, that’s where the magic happens.

Code Snippet
  1. public Dictionary<int, string> dict = new Dictionary<int, string>();
  2.  
  3. public MainPage()
  4. {
  5.     InitializeComponent();
  6.     dict.Add(1, "Manager");
  7.     dict.Add(2, "Team Lead");
  8.     _flex.Columns[1].ValueConverter = new ColumnValueConverter(dict);
  9. }

We first create a dictionary, where we are going to keep the options available to show in the combo box. Line 6 and 7 adds two job titles to the dictionary and finally in line 8, we say, for second column use the value converter and point to new instance Column Value Converter with dictionary collection. This informs flex grid that, this column needs to a combo box and use the dictionary to show the string value of the value bound to the column.

Look at the code, it is very simple and elegant. I really like the approach more than writing data template, converters etc.,

One things, I haven’t figured out yet is to bind the value converter directly to view model property. If we could do it, we can eliminate all code from code behind. If I find a solution, I will post it here.

ExpandoObject in Silverlight – Take 2

In my previous blog we looked how easy it to use dynamic / ExpandoObject in Silverlight. But if you notice the sample code, I cheated big time there. Even though I created a dynamic class on the fly, the binding did happen to a known properties of the ViewModel. Have a look at the XAML I used in the previous example

Code Snippet
  1.  
  2. <TextBlock Text="First Name" FontSize="14"/>
  3. <TextBlock Text="{Binding FirstName}" FontSize="14" Grid.Column="1"/>
  4. <TextBlock Text="Last Name" FontSize="14" Grid.Row="1"/>
  5. <TextBlock Text="{Binding LastName}" FontSize="14" Grid.Row="1" Grid.Column="1"/>
  6. <TextBlock Text="Age" FontSize="14" Grid.Row="2"/>
  7. <TextBlock Text="{Binding Age}" FontSize="14" Grid.Row="2" Grid.Column="1"/>
  8. <TextBlock Text="Salary" FontSize="14" Grid.Row="3"/>
  9. <TextBlock Text="{Binding Salary}" FontSize="14" Grid.Row="3" Grid.Column="1"/>

You can see I am binding to a known fields FirstName, LastName, etc. If we were using dynamic classes these properties might not have been known during the design time so I couldn’t have bind the way I am binding here. So I cheated just to show how to create a dynamic objects but not the dynamic binding. Lets first try to bind to dynamic object itself and see what happens. To do that, I am going to change the XAML to bind to list of objects, whose properties are not known during design time (even though I am going to create them in code behind).

Code Snippet
  1. <c1:C1FlexGrid ItemsSource="{Binding People}" AutoGenerateColumns="True"/>

I am using ComponentOne FlexGrid to bind to list of Person to the grid. Now the ViewModel code which returns the People

Code Snippet
  1. List<object> _people = new List<object>();
  2.  
  3.         public List<object> People
  4.         {
  5.             get
  6.             {
  7.                 dynamic person = new ExpandoObject();
  8.                 person.FirstName = "Unni";
  9.                 person.LastName = "Nair";
  10.                 person.Age = 20;
  11.                 person.Salary = 10000;
  12.                 //_people.Add(new Person { FirstName = "Unni", LastName = "Nair", Age = 20, Salary = 10000 });
  13.                 _people.Add(person);
  14.                 return _people;
  15.             }
  16.         }

With this code, if you compile and run, you will notice, it will not display any rows. So there is something wrong here. When I search the net, I ran into a forum posting on the same subject. So as per Min-Hong,

“I am afraid, it is beacuse dynamic does not support reflection.

   Thus does not support databinding(in silverlight databinding uses reflection).”

I did couple of searches and hit a Silverlight feature list voting page on Microsoft, where lot of people requested this. It turned out, Silverlight 4 does not support this feature and I am not sure the state of Silverlight 5. But the good news is that, there were couple of work around to this problem. They are

  • Using emit to create a type using TypeBuilder. There are couple of posting on this model.One such example is here. I approached this model to create a sample code which would generate a 1000 rows and 50 columns and it took almost 2.6 seconds.
  • The second approach is using property bag model and using Silverlight’s suppport for indexer for binding. In this model, the same number of cell generation took only 1.4 seconds. So I decided to stick to this model. I am not going to explain, since Xavior’s blog explained it so good.

So lets look at the code which display our Person List in data grid.

We will add a new class called DynamicDataContext as per Xavior explained in his blog

Code Snippet
  1. public class DynamicDataContext : DynamicObject, INotifyPropertyChanged
  2. {
  3.     private readonly IDictionary<string, object> propertyBag = new Dictionary<string, object>();
  4.  
  5.     public event PropertyChangedEventHandler PropertyChanged;
  6.  
  7.     /// <summary>
  8.     /// The indexer is needed to be able to use indexer-syntax in XAML
  9.     /// to data bind to the properties available in the private property bag.
  10.     /// </summary>
  11.     /// <param name="index">The name of the property.</param>
  12.     /// <returns>The value of the property, or null if the property doesn't exist.</returns>
  13.     public object this[string index]
  14.     {
  15.         get
  16.         {
  17.             object result;
  18.             propertyBag.TryGetValue(index, out result);
  19.             return result;
  20.         }
  21.         set
  22.         {
  23.             propertyBag[index] = value;
  24.             RaisePropertyChanged(index);
  25.         }
  26.     }
  27.  
  28.     public override bool TryGetMember(GetMemberBinder binder, out object result)
  29.     {
  30.         return propertyBag.TryGetValue(binder.Name, out result);
  31.     }
  32.  
  33.     public override bool TrySetMember(SetMemberBinder binder, object value)
  34.     {
  35.         propertyBag[binder.Name] = value;
  36.         RaisePropertyChanged(binder.Name);
  37.         return true;
  38.     }
  39.  
  40.     private void RaisePropertyChanged(string propertyName)
  41.     {
  42.         if (PropertyChanged != null)
  43.             PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
  44.     }
  45. }

Since the dynamic object does not support reflection, to show all the properties of the dynamic class in the grid, we need do couple of things in XAML and its code behind. First stop XAML

Code Snippet
  1. <c1:C1FlexGrid Name="_flex" ItemsSource="{Binding People}" AutoGenerateColumns="False"/>

We need to make sure we are not auto generating the columns since Silverlight does not know which are the columns we are going to have in grid, we need to manually create it in the code behind and then add it to the grid. So to do that we will mark the auto generate columns to ‘False’.

Before we go into code behind and see how we create the columns, first look at the ViewModel and see the what are we doing there.

First stop we create the list of DynamicDataContext for People property which is bound to the grid as

Code Snippet
  1. List<DynamicDataContext> _people;
  2.  
  3.         public List<DynamicDataContext> People
  4.         {
  5.             get
  6.             {
  7.                 if (_people == null)
  8.                     GenerateDynamicList();
  9.                 return _people;
  10.             }
  11.         }
  12.  
  13.         private void GenerateDynamicList()
  14.         {
  15.             _people = new List<DynamicDataContext>();
  16.             var person = new DynamicDataContext();
  17.             person["FirstName"] = "Unni";
  18.             person["LastName"] = "Nair";
  19.             person["Age"] = 20;
  20.             person["Salary"] = 10000;
  21.             _people.Add(person);
  22.             EventAggregator.Publish(new List<string> { "FirstName", "LastName", "Age", "Salary" });
  23.         }

Line 3-11 we create the People which is what bound to our grid in XAML.

When we generate the person collection, at the end, we publish an event for view to create the associated columns in the grid, by passing all available column names. It is done by publishing an event with list of column names at line 22. This event publishing is Jounce model of passing information between components.

Now lets look at the view code behind.

Code Snippet
  1. [ExportAsView("MainPage", IsShell = true)]
  2.     public partial class MainPage : UserControl, IEventSink<List<string>>, IPartImportsSatisfiedNotification
  3.     {
  4.         public MainPage()
  5.         {
  6.             InitializeComponent();
  7.         }
  8.  
  9.         [Export]
  10.         public ViewModelRoute Binding
  11.         {
  12.             get { return ViewModelRoute.Create("MainViewModel", "MainPage"); }
  13.         }
  14.  
  15.         [Import]
  16.         public IEventAggregator EventAggregator { get; set; }
  17.  
  18.         public void HandleEvent(List<string> publishedEvent)
  19.         {
  20.             foreach (string name in publishedEvent)
  21.             {
  22.                 Column col = new Column();
  23.                 col.Binding = new System.Windows.Data.Binding(string.Format("[{0}]", name));
  24.                 col.Header = name;
  25.                 _flex.Columns.Add(col);
  26.             }
  27.         }
  28.  
  29.         public void OnImportsSatisfied()
  30.         {
  31.             EventAggregator.Subscribe<List<string>>(this);
  32.         }
  33.     }

This is all Jounce here. When view model send the list of string, (don’t write production code like this 🙂 ) the view event sink intercepts and perform Handle Event method. For every element in the list, we create a new column and add it to our grid to display. That’s about it. Now if you would run this, you will get the result like the following

image

Very simple huh? I think Xaviour’s blog was exceptional in explaining the DynamicDataContent and how we use Silverlight 4’s indexer to identify and bind the dynamic columns.

I want to stop here with couple of points.

1. First of all, we need to create all the columns for the gird in the code behind. So once the collection is build you can send an event over to view and build it there. But most of the cases, you will first identify all the columns that are required to build the columns, so before you start building the data collection, you send the information over to View and build the grid columns before hand.

2. I am sure you have noticed the INotifyPropertyChanged interface implementation, that means we can track the data change events.

3. In ‘DynamicDataContext’, all we are tracking is bound object’s value change. We can extent this further to add your Entity Framework’s entity in it so that you can make changes to the underlying entity in the Data Context itself so that you can make use Entity Frame work’s change tracking for back end data update.

4. You can event extend the ‘DynamicDataContext’ to create hierarchical classes and generate tree like grid with ease. I will try to modify the ‘DynamicDataContext’ in the future blog to show this ability.

ExpandoObject in Silverlight 4

I do not think, this is a recurring Silverlight pattern. Once in a while, we run into a situation where we need to work with dynamic classes. One best example is creating pivot using Silverlight. Well, by default when I say pivot, it is supposed to be read only control. Component One has a very powerful pivot control for Silverlight called OLAP for Silverlight. Lets say, you need work on the pivot data but you need to edit it, even then, you do not need to create a run time class to solve the problem. You can get away without using run time classes. But for the fun of it, I thought I will see if we can solve the problem by creating the dynamic class.

As always I develop Silverlight MVVM application using Jounce. So is the case here as well. So what are we trying to do? Before we go into creating pivot class lets first look at a simple solution using dynamic. We are going to display a grid of text block to show first name, last name, age and salary like the following

image

Lets start with our simple XAML

Code Snippet
  1. <TextBlock Text="First Name" FontSize="14"/>
  2.         <TextBlock Text="{Binding FirstName}" FontSize="14" Grid.Column="1"/>
  3.         <TextBlock Text="Last Name" FontSize="14" Grid.Row="1"/>
  4.         <TextBlock Text="{Binding LastName}" FontSize="14" Grid.Row="1" Grid.Column="1"/>
  5.         <TextBlock Text="Age" FontSize="14" Grid.Row="2"/>
  6.         <TextBlock Text="{Binding Age}" FontSize="14" Grid.Row="2" Grid.Column="1"/>
  7.         <TextBlock Text="Salary" FontSize="14" Grid.Row="3"/>
  8.         <TextBlock Text="{Binding Salary}" FontSize="14" Grid.Row="3" Grid.Column="1"/>

In the XAML we are doing direct binding to the view model properties. Lets look at the View Model code for these properties

Code Snippet
  1.  
  2. public string FirstName
  3. {
  4.     get
  5.     {
  6.         return person.FirstName;
  7.     }
  8. }
  9.  
  10. public string LastName
  11. {
  12.     get
  13.     {
  14.         return person.LastName;
  15.     }
  16. }
  17.  
  18. public int Age
  19. {
  20.     get
  21.     {
  22.         return person.Age;
  23.     }
  24. }
  25.  
  26. public int Salary
  27. {
  28.     get
  29.     {
  30.         return person.Salary;
  31.     }
  32. }

This code basically returns each property of person instance of a class. One would think it is a our favorite ‘Person’ class but that is the not the case. person is defined as

Code Snippet
  1. dynamic person = new ExpandoObject();

look at that we did not define the Person class rather we declared as a dynamic of type ExpandoObject. The beauty of this type is that, during run time you can add and remove members. Now the person is defined, how do we add properties to the class. Here is how, you can make members on the fly.

Code Snippet
  1.  
  2. public MainViewModel()
  3. {
  4.     person.FirstName = "unni";
  5.     person.LastName = "nair";
  6.     person.Age = 20;
  7.     person.Salary = 10000;
  8. }

Please make sure you add Microsoft.CSharp.dll otherwise you will get  a compile time error.

That is it, compile and run it, you will the result as shown in the beginning of the blog.

Hopefully you read through the MSDN reference on ExpandoObject. If you read all the way towards end, there is one section that is of very much interest to us. ‘Receiving Notifications of Property Changes’. As per MSDN, “ExpandObject class implements the INotifyPropertyChanged interface and can raise a PropertyChanged event when a member is added, deleted, or modified.” That is a very good news for us. But please don’t get carried away, there is a catch, it direct dynamic binding only works in WPF not in Silverlight. We will look at the issue in the next blog and see how we can solve it.