Exporting data from Silverlight

We can put a lot of time and effort figuring out what our customers want to do with data, and tailoring the UI towards making those tasks pleasant, efficient, and seamless. But there comes a point where the user wants to put the data into a spreadsheet and analyze/share from there. Or, they may just want to print the data.

Silverlight datagrids are not very helpful when it comes to exporting data. And, printer support for silverlight is non-existent. We got around this limitation by using a WebHandler that receives the export request, and returns a csv file.

The export request contains all the information about the service method and parameters that populated data in the datagrid.

The ExportRequestData is serialized by the silverlight client, passed to the handler on the query string, and deserialized by the handler. So, both the web and silverlight projects needs to understand this class. You can do this by creating a separate dll and referencing it in both the projects, but we were in tactical mode at the time and put a file in one project and just made a link to it in the other. Just make sure you don't introduce any code in it that Silverlight doesn't understand (since SL is a subset of full-fledged .net).

Here is the code for ExportRequestData class:

   1:  //This file is owned by the web project and linked to the silveright project.

   2:  //It is safer to edit this file within the context of silverlight

   3:  //because then the intellisense only shows the methods and properties you can use within Silverlight

   4:  //framework. If edited within web context, intellisense will show stuff you can do within

   5:  //full-fledged .NET, and if you end up using stuff outside of silverlight framework, the silverlight

   6:  //app will not build.

   7:   

   8:  public class ExportRequestData

   9:  {

  10:      private Dictionary<string, string> _Parameters;

  11:      public Dictionary<string, string> Parameters

  12:      {

  13:          get

  14:          {

  15:              if (_Parameters == null)

  16:                  _Parameters = new Dictionary<string, string>();

  17:              return _Parameters;

  18:          }

  19:          set { _Parameters = value; }

  20:      }

  21:      public string Method { get; set; }

  22:   

  23:      public void AddParam(string key, object value)

  24:      {

  25:          if (value != null)

  26:          {

  27:              Parameters.Add(key, value.ToString());

  28:          }

  29:      }

  30:   

  31:      //expected format : method_name;param1=value1|param2=value2|param3=value3

  32:      public string Serialize()

  33:      {

  34:          StringBuilder sb = new StringBuilder();

  35:          sb.Append(Method.ToString()).Append(";");

  36:          foreach (KeyValuePair<string, string> kv in Parameters)

  37:          {

  38:              sb.Append(kv.Key).Append("=").Append(kv.Value).Append("|");

  39:          }

  40:          string ret = sb.ToString();

  41:          return ret.Remove(ret.Length - 1);

  42:      }

  43:   

  44:   

  45:      //expected format : method_name;param1=value1|param2=value2|param3=value3

  46:      public static ExportRequestData Deserialize(string data)

  47:      {

  48:          ExportRequestData d = new ExportRequestData();

  49:          d.Parameters = new Dictionary<string, string>();

  50:   

  51:          string[] splitData = data.Split(new char[] { ';' });

  52:          d.SetMethod(splitData[0]);

  53:          if (splitData.Length > 1)

  54:          {

  55:              string[] p = splitData[1].Split(new char[] { '|' });

  56:              char[] paramSplitter = new char[] { '=' };

  57:              foreach (string s in p)

  58:              {

  59:                  string[] param = s.Split(paramSplitter);

  60:                  if (param.Length > 1)

  61:                      d.Parameters.Add(param[0], param[1]);

  62:              }

  63:          }

  64:          return d;

  65:      }

  66:   

  67:      public string GetParam(string key)

  68:      {

  69:         

  70:          if (Parameters.ContainsKey(key))

  71:              return Parameters[key];

  72:          else return null;

  73:      }

  74:   

  75:      public bool? GetBoolParam(string key)

  76:      {

  77:          object ret = GetParam(key);

  78:          if (ret != null)

  79:          {

  80:              return Convert.ToBoolean(ret);

  81:          }

  82:          else return null;

  83:      }

  84:   

  85:   

  86:      public short? GetShortParam(string key)

  87:      {

  88:          object ret = GetParam(key);

  89:          if (ret != null)

  90:          {

  91:              return Convert.ToInt16(ret);

  92:          }

  93:          else return null;

  94:      }

  95:   

  96:      public byte? GetByteParam(string key)

  97:      {

  98:          object ret = GetParam(key);

  99:          if (ret != null)

 100:          {

 101:              return Convert.ToByte(ret);

 102:          }

 103:          else return null;

 104:      }

 105:   

 106:      public int? GetIntParam(string key)

 107:      {

 108:          object ret = GetParam(key);

 109:          if (ret != null)

 110:          {

 111:              return Convert.ToInt32(ret);

 112:          }

 113:          else return null;

 114:      }

 115:   

 116:      public DateTime? GetDateTimeParam(string key)

 117:      {

 118:          object ret = GetParam(key);

 119:          if (ret != null)

 120:          {

 121:              return Convert.ToDateTime(ret);

 122:          }

 123:          else return null;

 124:      }

 125:   

 126:      public double? GetDoubleParam(string key)

 127:      {

 128:          object ret = GetParam(key);

 129:          if (ret != null)

 130:          {

 131:              return Convert.ToDouble(ret);

 132:          }

 133:          else return null;

 134:      }

 135:  }

The client tacks on the serialized ExportRequestData object (handler of Click event of the "Export" HyperlinkButton):

   1:                  var rd = new ExportRequestData { Method = "MyMethod" };

   2:                  rd.AddParam("FirstParam", param1Value);

   3:                  rd.AddParam("SecondParam", param2Value);

   4:                  rd.AddParam("ThirdParam", param3Value);

   5:                  MyHyperLinkButton.NavigateUri = new Uri("http://mydomain.com/handler.ashx?data=" HttpUtility.UrlEncode(data.Serialize()));

You will probably add authentication and other things to this. The caveat here is that you have to match the method and parameter names exactly to the ones in the web service. The order in which they are added, doesn't matter. Now, we are all set with the client. Onwards to the server code.

The handler takes the request, deserializes it, calls the method with the parameters described in the request, and outputs the results as a csv.

So the ProcessRequest in the handler looks like this:

   1:  //Re-construct the ExportRequestData object:

   2:  ExportRequestData rd = ExportRequestData.Deserialize(context.Request.QueryString["data"].ToString());

   3:  ExportResults(rd);

ExportResults method uses reflection call the right method and get results. It casts the results to Object[], which then gets passed to a generic CsvExporter.

   1:      private void ExportResults(ExportRequestData rd)

   2:      {

   3:          MyWebService svc = new MyWebService();

   4:          MethodInfo mi = typeof(MyWebService).GetMethod(rd.Method);

   5:          ParameterInfo[] pis = mi.GetParameters();

   6:          object[] ps = new object[pis.Length];

   7:          for (int i = 0; i < pis.Length; i++)

   8:          {

   9:              if (pis[i].ParameterType == typeof(string))

  10:                  ps[i] = rd.GetParam(pis[i].Name);

  11:              else if (pis[i].ParameterType == typeof(DateTime?))

  12:                  ps[i] = rd.GetDateTimeParam(pis[i].Name);

  13:              else if (pis[i].ParameterType == typeof(short?))

  14:                  ps[i] = rd.GetShortParam(pis[i].Name);

  15:              else if (pis[i].ParameterType == typeof(int?))

  16:                  ps[i] = rd.GetIntParam(pis[i].Name);

  17:              else if (pis[i].ParameterType == typeof(double?))

  18:                  ps[i] = rd.GetDoubleParam(pis[i].Name);

  19:              else if (pis[i].ParameterType == typeof(byte?))

  20:                  ps[i] = rd.GetByteParam(pis[i].Name);

  21:              else if (pis[i].ParameterType == typeof(bool?))

  22:                  ps[i] = rd.GetBoolParam(pis[i].Name);

  23:          }

  24:          CsvExporter.Export((Object[])mi.Invoke(svc, ps), rd.Method, true);

  25:      }

The CsvExporter examines the actual type of the object in Object[] passed to it (it assumes that all the objects in the array are of the same type). It uses this info to create the header. In addition, if a property of the object is numeric, it adds a "=" in front of the value so it shows up correctly when displayed in Excel. Here is the code for the Exporter:

   1:   

   2:      public static Regex NumericRegex;

   3:   

   4:      static CsvExporter()

   5:      {

   6:          NumericRegex = new Regex(@"^\d+$");

   7:      }

   8:     

   9:      public static void Export(Object[] source, string fileName)

  10:      {

  11:          HttpResponse response = HttpContext.Current.Response;

  12:          response.ContentType = "text/plain";

  13:          response.AddHeader("Content-Disposition", "attachment;filename=\"" + fileName + "_" + DateTime.Now.ToString("MMddyyHHmmss") + ".csv\"");

  14:          response.Clear();

  15:   

  16:          StreamWriter csvDoc = new StreamWriter(response.OutputStream);

  17:          if (source.Length > 0)

  18:          {

  19:              PropertyInfo[] props = source[0].GetType().GetProperties();

  20:              foreach (PropertyInfo prop in props)

  21:              {

  22:                  csvDoc.Write("\"" + prop.Name.Replace("_", " ") + "\",");

  23:              }

  24:              csvDoc.WriteLine();

  25:              foreach (object o in source)

  26:              {

  27:                  foreach (PropertyInfo prop in props)

  28:                  {

  29:                      object v = prop.GetValue(o, null);

  30:                      if (v != null && (prop.PropertyType == typeof(string)) && NumericRegex.IsMatch(v.ToString()))

  31:                      {

  32:                          csvDoc.Write("=");

  33:                      }

  34:                      csvDoc.Write("\"");

  35:                      csvDoc.Write(v);

  36:                      csvDoc.Write("\",");

  37:                  }

  38:                  csvDoc.WriteLine();

  39:              }

  40:          }

  41:          csvDoc.Flush();

  42:          csvDoc.Close();

  43:          response.End();

  44:      }

With this, we were able to implement a generic and reusable system to export data from our Silverlight UIs. I hope the info is helpful.

Silverlight Datagrid Layout Manager: The code

In the previous post I talked about a datagrid layout manager we used in our product. There is a demo and a link to code in that post as well. By the way, one thing I forgot to mention in that post: A user can drag/drop to rearrange the columns, and then if she opens "manage layout" and hit "save", the rearranged layout will persist to a future session. An added bonus.

Here is the the full demo, including user control and consuming code: http://www.filefactory.com/file/a06da5d/n/ContextMenuDemo_zip

I will try to break down the code in this post.

The column information in the datagrid is massaged into an ObservableCollection object. The definition for OrderedItem:

   1:      public class OrderedItem : INotifyPropertyChanged

   2:      {

   3:          public event System.ComponentModel.PropertyChangedEventHandler PropertyChanged;

   4:   

   5:          private string _Name;

   6:          public string Name

   7:          {

   8:              get { return _Name; }

   9:              set

  10:              {

  11:                  _Name = value;

  12:                  if (PropertyChanged != null)

  13:                  {

  14:                      PropertyChanged(this, new PropertyChangedEventArgs("Name"));

  15:                  }

  16:              }

  17:          }

  18:   

  19:          public string DisplayName { get; set; }

  20:          private int _Position;

  21:          public int Position

  22:          {

  23:              get { return _Position; }

  24:              set

  25:              {

  26:                  _Position = value;

  27:                  if (PropertyChanged != null)

  28:                  {

  29:                      PropertyChanged(this, new PropertyChangedEventArgs("Position"));

  30:                  }

  31:              }

  32:          }

  33:   

  34:          private bool _IsFrozen;

  35:          public bool IsFrozen

  36:          {

  37:              get { return _IsFrozen; }

  38:              set

  39:              {

  40:                  _IsFrozen = value;

  41:                  if (PropertyChanged != null)

  42:                  {

  43:                      PropertyChanged(this, new PropertyChangedEventArgs("IsFrozen"));

  44:                  }

  45:              }

  46:          }

  47:   

  48:          private bool _IsIncluded;

  49:          public bool IsIncluded

  50:          {

  51:              get { return _IsIncluded; }

  52:              set

  53:              {

  54:                  _IsIncluded = value;

  55:                  if (PropertyChanged != null)

  56:                  {

  57:                      PropertyChanged(this, new PropertyChangedEventArgs("IsIncluded"));

  58:                  }

  59:              }

  60:          }

  61:   

  62:          public OrderedItem Copy()

  63:          {

  64:              return new OrderedItem

  65:              {

  66:                  DisplayName = DisplayName,

  67:                  IsIncluded = IsIncluded,

  68:                  Name = Name,

  69:                  Position = Position

  70:              };

  71:          }

  72:   

  73:      }

This method examines the datagrid and creates the ObservableCollection:

   1:  public void ShowLayoutDialog()

   2:  {

   3:  LayoutRoot.Visibility = Visibility.Visible;

   4:  List<ordereditem> ls = new List<ordereditem>();

   5:  for (int i = ZeroBasedStartIndex; i < GridToFormat.Columns.Count; i++)

   6:              {

   7:                  var oi = new OrderedItem

   8:                  {

   9:                      Name = GridToFormat.Columns[i].Header.ToString(),

  10:                      Position = GridToFormat.Columns[i].DisplayIndex + 1 - ZeroBasedStartIndex,

  11:                      IsIncluded = (GridToFormat.Columns[i].Visibility == Visibility.Visible),

  12:                      IsFrozen = GridToFormat.Columns[i].IsFrozen

  13:                  };

  14:                  oi.PropertyChanged += new PropertyChangedEventHandler(OrderedItem_PropertyChanged);

  15:                  ls.Add(oi);

  16:              }

  17:              ls.Sort(new OrderedItemComparer());

  18:              ChooserGrid.ItemsSource = ls.ToObservableCollection<OrderedItem>();

  19:  }

Here is the method that applies the layout to the grid. InitLayout() and the Save-click handler method calls this method to do their heavy lifting.

   1:          private void UpdateGridLayout(OrderedItem[] layouts)

   2:          {

   3:              try

   4:              {

   5:                  if (layouts != null)

   6:                  {

   7:                      int fc = (from l in layouts where l.IsFrozen select 1).Count();

   8:                      GridToFormat.FrozenColumnCount = (fc > 0) ? fc + ZeroBasedStartIndex : 0;

   9:                      foreach (OrderedItem layout in layouts)

  10:                      {

  11:                          foreach (DataGridColumn c in GridToFormat.Columns)

  12:                          {

  13:                              if (c.Header.ToString() == layout.Name)

  14:                              {

  15:                                  c.Visibility = layout.IsIncluded ? Visibility.Visible : Visibility.Collapsed;

  16:                                  c.DisplayIndex = layout.Position - 1 + ZeroBasedStartIndex;

  17:                                  break;

  18:                              }

  19:                          }

  20:                      }

  21:                  }

  22:                  else

  23:                  {

  24:                      for (int i = ZeroBasedStartIndex; i < GridToFormat.Columns.Count; i++)

  25:                      {

  26:                          GridToFormat.Columns[i].DisplayIndex = i;

  27:                          GridToFormat.Columns[i].Visibility = Visibility.Visible;

  28:                      }

  29:                  }

  30:              }

  31:              catch

  32:              {

  33:                  UpdateGridLayout(null);

  34:                  _AppSettings[AppKey] = null;

  35:                  _AppSettings.Save();

  36:              }

  37:          }

There are other methods that handle PropertyChanged event for the OrderedItem, and to handle user actions to movver the item up or down. I just want to point out what each method did, and I think the xaml and cs code (that can be downloaded from link in previous post) are pretty self-explanatory. I hope this gives you enough info about the the guts of the user-control to tweak it to your liking.

Silverlight DataGrid: Letting Users Manage Layout

We have a few datagrids that contain a lot of columns, and different columns are important to different users. They want to see only a subset of what is available, and use different fields as keys to the dataset. So, we had to come up with a way for the users to:
1. Rearrrange the columns
2. Make columns visibile/invisible
3. Freeze some columns in the beginning (keys) so they remain visible during horizontal scrolling.
4. Carry over the user preferences to future sessions.

We managed to come up with a generic user control to do this for any datagrid, and I think it is worth sharing here. Here is an application that demonstrates the capability:



It is just the same datagrid from previous posts, with some extraneous fields to play around with. You get the idea.

The code for the user control can be found here

To use the DataGridLayoutChooser, you would write some xaml like this:

   1:  <lcl:DataGridLayoutChooser Name="dgLayoutChooser" AppKey="dgLayout" Grid.RowSpan="2"/>

The property AppKey is the key that it uses to save the preferences in isolated storage, for use in future sessions.

When "Loaded" event handler for your app/usercontrol, you want to specify the DataGrid that the Chooser is formatting:

   1:  void Home_Loaded(object sender, RoutedEventArgs e)

   2:  {

   3:      dgLayoutChooser.GridToFormat = dg;

   4:      dgLayoutChooser.InitLayout();

   5:  }

InitLayout() applies any layout that the user may have saved from a previous session.
Now, when the user wants to manage the layout (Click event in the demo, for instance), you call the ShowLayoutDialog() method on the Chooser:

   1:  void ManageLayout(object sender, RoutedEventArgs e)

   2:  {

   3:      dgLayoutChooser.ShowLayoutDialog();

   4:  }

We will take a deeper look at the user-control code in the next post. The DataGridLayoutChooser is a very good candidate to be made into a real control, by the way. I will make that available in a few weeks, when I get around to it. In the meantime, I hope you find this UserControl as useful as we did.

Copying Datagrid data to Clipboard through Context Menu

In my last post, I talked about adding context menu to Silverlight DataGrid. One thing that always frustrated our users was that they could not copy contents of a datagrid in our application to the clipboard.

Here is the example (clipboard is accessible through javascript only in IE, mind you):



I will make the full code available on Monday. Below, is a runthrough of how this was done.

There are plenty of examples on how to access the clipboard in Silverlight through javascript (for IE, anyways). Here is the code that I used, from Jeff Wilcox's example:

1:          public static void SetText(string text)

2:          {

3:              var clipboardData = (ScriptObject)HtmlPage.Window.GetProperty("clipboardData");

4:              if (clipboardData != null)

5:              {

6:                  bool success = (bool)clipboardData.Invoke("setData", "text", text);

7:                  if (!success)

8:                  {

9:                      HtmlPage.Window.Alert(ClipboardFailure);

10:                  }

11:              }

12:              else

13:              {

14:                  HtmlPage.Window.Alert("clipboard not available");

15:              }

16:          }

You can always ctrl+C to copy contents of a textbox or textblock to clipboard, but DataGrid does not allow that. To do this, I take the cell in context, loop through it's children recursively, and write out the contents of the child if it is TextBlock or TextBox:

1:          private static string GetText(UIElement uie)

2:          {

3:              string ret = string.Empty;

4:              if (uie != null)

5:              {

6:                  if (uie is TextBlock)

7:                      ret = (uie as TextBlock).Text;

8:                  else if (uie is TextBox)

9:                      ret = (uie as TextBox).Text;

10:                  else if (uie is Panel)

11:                  {

12:                      foreach (var element in (uie as Panel).Children)

13:                          ret += GetText(element);

14:                  }

15:              }

16:              return ret;

17:          }

Now, what if we want to have a menu command that copies whole row data? I have a generic serializer that takes the datacontext of the row as object and, using reflection, recursively creates a string representation:

   1:          private static string Serialize(object data)

   2:          {

   3:              if (data != null)

   4:              {

   5:                  var sb = new StringBuilder();

   6:                  var props = data.GetType().GetProperties();

   7:                  sb.Append(data.GetType().Name);

   8:                  foreach (var prop in props)

   9:                  {

  10:                      var o = prop.GetValue(data, null);

  11:                      sb.Append(Environment.NewLine).Append(prop.Name).Append(" = ");

  12:                      if (o == null || o is string || o is DateTime || o.GetType().IsPrimitive)

  13:                          sb.Append(o);

  14:                      else

  15:                          sb.Append(Serialize(o).Replace(Environment.NewLine, Environment.NewLine + "\t"));

  16:                  }

  17:                  return sb.ToString();

  18:              }

  19:              else return string.Empty;

  20:          }

How do we get to a reference to the cell? When context menu is requested, the DataGrid finds the cell where the right-click occurred, and keeps a reference to in a private field, to be used when the menu selection occurs. So the GetContextMenuContent method changes like so:

   1:          public virtual List<ContextMenuItemInfo> GetContextMenuContent(System.Windows.Browser.HtmlEventArgs e)

   2:          {

   3:              Point curr = new Point(e.OffsetX, e.OffsetY);

   4:              _CellInContext = null;

   5:              for (var i = 0; i < presenter.Children.Count; i++)

   6:              {

   7:                  var row = (DataGridRow)presenter.Children[i];

   8:                  if (ContextMenuInterceptor.IsPointWithinBounds(curr, row))

   9:                  {

  10:                      foreach (var col in Columns)

  11:                      {

  12:                          var cell = col.GetCellContent(row.DataContext).Parent as DataGridCell;

  13:                          if (cell != null && ContextMenuInterceptor.IsPointWithinBounds(curr, cell))

  14:                          {

  15:                              _CellInContext = cell;

  16:                              break;

  17:                          }

  18:                      }

  19:   

  20:                      foreach (ContextMenuItemInfo m in ContextMenuList)

  21:                          m.Tag = row;

  22:                      return ContextMenuList;

  23:                  }

  24:              }

  25:              return null;

  26:          }

And the HandleContextMenu actually handles some stuff instead of just raising an event:

   1:          public virtual void HandleContextMenuClick(object sender, ContextMenuClickEventArgs e)

   2:          {

   3:              switch (e.ClickedItem.Key)

   4:              {

   5:                  case CMD_COPY_ROW:

   6:                      Clipboard.SetText(Serialize(((DataGridRow)e.ClickedItem.Value).DataContext));

   7:                      break;

   8:                  case CMD_COPY_CELL:

   9:                      if (_CellInContext != null)

  10:                          Clipboard.SetText(GetText(_CellInContext.Content as UIElement));

  11:                      break;

  12:                  default:

  13:                      if (ContextMenuClicked != null)

  14:                          ContextMenuClicked(sender, e);

  15:                      break;

  16:              }

  17:          }

Both of these have worked very well for our application. Both "Copy Cell Content" and "Copy Row Data" are context menu commands in all our datagrids.