Blog‎ > ‎

Export DataGrid to Excel - Silverlight 3

posted May 4, 2010, 12:16 PM by Rene Rasmussen   [ updated May 10, 2010, 1:50 PM ]
I meet a lot of requests from developers who want to export their data presented in a Silverlight grid to Excel. Well, actually it is the developers who meet a lot of request from the users to export data from data grid to Excel.  The best approach is normally to convert “the original data”. That is, use the data source when you export data, don’t use the data from found in the data grid.

But there are several arguments to use the data grid as data source. First of all, it is possible to implement an “automatic” exporter method which will export almost any data grid to Excel. This is a fast way to support “Export to Excel” from “all” data grids and it does provide an Excel document which has the same column order and sort order as the user has defined in the data grid. 

Silverlight 3 is working in a limited sandbox which does enable us to access Excel and generate a true Excel document, but we can create a simple ‘,’ separated file which Excel can parse and that’s what I will do in this demo.

There are quite a lot of pit falls to be aware of when implementing a generic method for exporting data to Excel, but first let’s have a look at the overall design .
private void ConvertDataGridToExcel(DataGrid grid)
{
  StringBuilder sb = new StringBuilder();

  // add headers
  foreach (var col in grid.Columns)
  {
    sb.Append(AddHeaderItem(col));
  }

  // add data
  foreach (var row in grid.ItemsSource)
  {
    foreach (var col in grid.Columns)
    {
      sb.Append(AddDataItem(col, row));
    }
  }

  SaveToCsvFile(sb);
}
This is pretty straight forward, so let’s look at the pitfalls.
The user is normally allowed to reorder the columns in the data grid and the exported file should present data as it organized by the user. Solution:
foreach (var column in grid.Columns.OrderBy(c => c.DisplayIndex).ToList())
The data grid is virtualized and only the data currently visible is presented found when enumerating the ItemSource. Therefore we scroll to the last item in each row before trying to load the item.Solution:
grid.ScrollIntoView(row, grid.Columns.OrderBy(c => c.DisplayIndex).Last());
Parsing the data source depends on the data source type. See implementation below for details on implementation.
The full implementation is shown here.
private void CreateCsvFile(object sender, RoutedEventArgs e)
{
  var sfd = new SaveFileDialog();
  sfd.DefaultExt = ".csv";
  sfd.Filter = "Excel-CSV Files|*.csv";
  sfd.FilterIndex = 1;
  if (true == sfd.ShowDialog())
  {
    using (var stream = sfd.OpenFile())
    using (var sw = new StreamWriter(stream))
    {
      sw.Write(ConvertDataGridToCsv(MyDataGrid, true));
    }
  }
}

private string ConvertDataGridToCsv(DataGrid grid, bool addHeaders)
{
  var sb = new StringBuilder();
  if (addHeaders)
  {
    foreach (var column in grid.Columns.OrderBy(c => c.DisplayIndex).ToList())
    {
      sb.Append("\"");
      sb.Append(column.Header.ToString().Replace("\"", "\"\""));
      sb.Append("\";");
    }
    sb.AppendLine();
  }

  foreach (object obj in grid.ItemsSource)
  {
    grid.ScrollIntoView(obj, grid.Columns.OrderBy(c => c.DisplayIndex).Last());

    foreach (DataGridColumn c in grid.Columns.OrderBy(c=>c.DisplayIndex).ToList())
    {
      sb.Append("\"");
      var el = c.GetCellContent(obj);
      if (el==null)
      {
        // load values by reflection
        string str = string.Empty;
        var propInfo = obj.GetType().GetProperty(c.Header.ToString());
        if (propInfo != null)
          str = propInfo.GetValue(obj, null).ToString();
        sb.Append(str.Replace("\"", "\"\""));
      }
      else if (el is TextBlock)
      {
        var tb = el as TextBlock;
        sb.Append(tb.Text.Replace("\"", "\"\""));
      }
      else if (el is CheckBox)
      {
        var cb = el as CheckBox;
        sb.Append(cb.IsChecked == true ? "True" : "False");
      }
      else
      {
        int i = 0;
      }
      sb.Append("\";");
    }
    sb.AppendLine();
  }

  return sb.ToString();
}
Comments