Export Silverlight DataGrid to Excel XML/CSV

• Download source code - 1.73 KB

Introduction

There are several reasons for the end user to export data from an application into an Excel compatible format (mostly for further analysis/usage or data). This article explains how to include data export capability to Silverlight DataGrid.

Background

I am working on migrating some ASP.NET code to a Silverlight application. One of the features that I felt lacking in Silverlight is the ability to export the contents of a DataGrid to the end user. To address this issue, I created an extension for DataGrid control. When the attached file (DataGridExtensions.cs) is included in a project, the "Export" extension is automatically made available to all the DataGrid controls used in the project.

Using the code

This module exposes two methods:

• Export (this DataGrid dg) - extends the DataGrid control by providing the export functionality.
• ExportDataGrid (DataGrid dGrid) - this method is internally called by the "Export" DataGrid extension. However, this method can be directly called too.

public static void Export(this DataGrid dg)
{
    ExportDataGrid(dg);
}
public static void ExportDataGrid(DataGrid dGrid)
{
    SaveFileDialog objSFD = new SaveFileDialog() { DefaultExt = "csv", 
        Filter = "CSV Files (*.csv)|*.csv|Excel XML (*.xml)|*.xml|All files (*.*)|*.*", 
        FilterIndex = 1 };
    if (objSFD.ShowDialog() == true)
    {
        string strFormat = 
          objSFD.SafeFileName.Substring(objSFD.SafeFileName.IndexOf('.') + 1).ToUpper();
        StringBuilder strBuilder = new StringBuilder();
        if (dGrid.ItemsSource == null) return;
        List<string> lstFields = new List<string>();
        if (dGrid.HeadersVisibility == DataGridHeadersVisibility.Column || 
            dGrid.HeadersVisibility == DataGridHeadersVisibility.All)
        {
            foreach (DataGridColumn dgcol in dGrid.Columns)
                lstFields.Add(FormatField(dgcol.Header.ToString(), strFormat));
            BuildStringOfRow(strBuilder, lstFields, strFormat);
        }
        foreach (object data in dGrid.ItemsSource)
        {
            lstFields.Clear();
            foreach (DataGridColumn col in dGrid.Columns)
            {
                string strValue = "";                    
                Binding objBinding = null;
                if (col is DataGridBoundColumn)
                    objBinding = (col as DataGridBoundColumn).Binding;
                if (col is DataGridTemplateColumn)
                {
                    //This is a template column...
                    //    let us see the underlying dependency object
                    DependencyObject objDO = 
                      (col as DataGridTemplateColumn).CellTemplate.LoadContent();
                    FrameworkElement oFE = (FrameworkElement)objDO;
                    FieldInfo oFI = oFE.GetType().GetField("TextProperty");
                    if (oFI != null)
                    {
                        if (oFI.GetValue(null) != null)
                        {
                            if (oFE.GetBindingExpression(
                                   (DependencyProperty)oFI.GetValue(null)) != null)
                                objBinding = 
                                  oFE.GetBindingExpression(
                                  (DependencyProperty)oFI.GetValue(null)).ParentBinding;
                        }
                    }
                }
                if (objBinding != null)
                {
                    if (objBinding.Path.Path != "")
                    {
                        PropertyInfo pi = data.GetType().GetProperty(objBinding.Path.Path);
                        if (pi != null) strValue = pi.GetValue(data, null).ToString();
                    }
                    if (objBinding.Converter != null)
                    {
                        if (strValue != "")
                            strValue = objBinding.Converter.Convert(strValue, 
                              typeof(string), objBinding.ConverterParameter, 
                              objBinding.ConverterCulture).ToString();
                        else
                            strValue = objBinding.Converter.Convert(data, 
                              typeof(string), objBinding.ConverterParameter, 
                              objBinding.ConverterCulture).ToString();
                    }
                }
                lstFields.Add(FormatField(strValue,strFormat));
            }
            BuildStringOfRow(strBuilder, lstFields, strFormat);
        }
        StreamWriter sw = new StreamWriter(objSFD.OpenFile());
        if (strFormat == "XML")
        {
            //Let us write the headers for the Excel XML
            sw.WriteLine("<?xml version=\"1.0\" " + 
                         "encoding=\"utf-8\"?>");
            sw.WriteLine("<?mso-application progid" + 
                         "=\"Excel.Sheet\"?>");
            sw.WriteLine("<Workbook xmlns=\"urn:" + 
                         "schemas-microsoft-com:office:spreadsheet\">");
            sw.WriteLine("<DocumentProperties " + 
                         "xmlns=\"urn:schemas-microsoft-com:" + 
                         "office:office\">");
            sw.WriteLine("<Author>Arasu Elango</Author>");
            sw.WriteLine("<Created>" +  
                         DateTime.Now.ToLocalTime().ToLongDateString() + 
                         "</Created>");
            sw.WriteLine("<LastSaved>" + 
                         DateTime.Now.ToLocalTime().ToLongDateString() + 
                         "</LastSaved>");
            sw.WriteLine("<Company>Atom8 IT Solutions (P) " + 
                         "Ltd.,</Company>");
            sw.WriteLine("<Version>12.00</Version>");
            sw.WriteLine("</DocumentProperties>");
            sw.WriteLine("<Worksheet ss:Name=\"Silverlight Export\" " + 
               "xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">");
            sw.WriteLine("<Table>");
        }
        sw.Write(strBuilder.ToString());
        if (strFormat == "XML")
        {
            sw.WriteLine("</Table>");
            sw.WriteLine("</Worksheet>");
            sw.WriteLine("</Workbook>");
        }
        sw.Close();
    }
}

The ExportDataGrid method prompts to the user to select the output save file name. Based on the user selection, the method determines the format to save -- XML or CSV. The Excel XML format used is not compatible with Microsoft Excel 2003 or earlier.

The row contents are built by a method named BuildStringOfRow.

private static void BuildStringOfRow(StringBuilder strBuilder, 
        List<string> lstFields, string strFormat)
{
    switch (strFormat)
    {
        case "XML":
            strBuilder.AppendLine("<Row>");
            strBuilder.AppendLine(String.Join("\r\n", lstFields.ToArray()));
            strBuilder.AppendLine("</Row>");
            break;
        case "CSV":
            strBuilder.AppendLine(String.Join(",", lstFields.ToArray()));
            break;
    }
}

The above method builds the row contents string as per the output format. Formatting of individual fields is done by a method named FormatField.

private static string FormatField(string data, string format)
{
    switch (format)
    {
        case "XML":
            return String.Format("<Cell><Data ss:Type=\"String" + 
               "\">{0}</Data></Cell>", data);
        case "CSV":
            return String.Format("\"{0}\"", 
              data.Replace("\"", "\"\"\"").Replace("\n", 
              "").Replace("\r", ""));
    }
    return data;
}

The FormatField method returns the data formatted as per the output format.

How to use

After including the attached code (DataGridExtensions.cs) to your project, you can initiate the export of the DataGrid contents by calling the Export() method of the DataGrid. For example, if the DataGrid name is objDataGrid, you will be calling objDataGrid.Export() to invoke the export.

Points of interest

This code generates the Excel file in XML format. I found exporting in XML much easier than exporting into the XLS or XLSX format.

History

• Version 1 - December 1, 2009.