Export query results from Data Grid to Excel

[JoannaLaroussi]

I am displaying query results in a data grid and I would like to be able export these results to Excel. I tried to use code example from:

http://www.codeproject.com/KB/silverlight/SilverlightDataGridExport.aspx

assigning export to a button click event.  It is almost working, but in a created Excel document instead of values in my cells I see in each cell �??ESRI.ArcGIS.Client.Graphic�?�. How I can change it for my real values? Or did anybody found a better method to export data from Data Grid to Excel document?

The code from a code project is:

using System;
using System.Windows;
using System.Windows.Data;
using System.Windows.Media;
using System.Windows.Controls;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using System.IO;
using System.Reflection;
using System.Xml.Linq;

//example from http://www.codeproject.com/KB/silverlight/SilverlightDataGridExport.aspx

public static class DataGridExtensions
{
    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
                        DependencyObject objDO = (col as DataGridTemplateColumn).CellTemplate.LoadContent();
                        FrameworkElement oFE = (FrameworkElement)objDO;
                        FieldInfo oFI = oFE.GetType().GetField("Street");
                        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("</DocumentProperties>");
                sw.WriteLine("<Worksheet ss:Name=\"Distance eligibility\" 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();
        }
    }
    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;
        }
    }
    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;
    }
}

[karenvolarich]

were you able to get this working?  If so, how?  I am getting the same results of ESRI.ArcGIS.Client.Graphic

[JoannaLaroussi]

I was able to solve it in a following way:

1. Code behind:

// export to Excel - calls MyExcelExport class
        private void exportExcel_Click(object sender, RoutedEventArgs e)
        {
            Dictionary<string, string> aliases = new Dictionary<string, string>();

            // first string is the same like field name in a feature class,
            // second equals header in data grid
            aliases.Add("Street", "Street Name");
            aliases.Add("FromLeft", "From Left");
            aliases.Add("ToLeft", "To Left");
            aliases.Add("FromRight", "From Right");
            aliases.Add("ToRight", "To Right");

            MyExcelExport.ExportDataGrid(PEDQueryNewDataGrid, null, aliases);
        }

2. XAML:

<!-- PED Query Results Data Grid Stack panel-->
                <Grid x:Name="PEDQueryResultPanelGrid" HorizontalAlignment="Right" VerticalAlignment="Bottom" Margin="0,0,15,15" Visibility="Collapsed">
                    <Rectangle Stroke="Gray"  RadiusX="10" RadiusY="10" Fill="#77919191" Margin="0,0,0,5" >
                        <Rectangle.Effect>
                            <DropShadowEffect/>
                        </Rectangle.Effect>
                    </Rectangle>
                    <Rectangle Fill="#FFFFFFFF" Stroke="DarkGray" RadiusX="5" RadiusY="5" Margin="10,17,10,15" />

                    <StackPanel Orientation="Vertical" HorizontalAlignment="Center" Margin="10,2,15,15">
                        <TextBlock HorizontalAlignment="Left" Text="Streets Found" Foreground="White" FontSize="9"  FontWeight="Bold" Margin="2,0,0,5" />

                        <slData:DataGrid x:Name="PEDQueryNewDataGrid" AutoGenerateColumns="False" HeadersVisibility="Column" Background="White"
                             IsReadOnly="True" Canvas.Left="10" Canvas.Top="50" MaxHeight="150" Width="315"
                             HorizontalScrollBarVisibility="Visible" VerticalScrollBarVisibility="Visible" CanUserSortColumns="True"
                             RowStyle="{StaticResource HighLightRow}"
                             SelectionChanged="QueryHighlightDataGrid_SelectionChanged"
                             LoadingRow="QueryHighlightDataGrid_LoadingRow">
                            <slData:DataGrid.Columns>
                                <slData:DataGridTextColumn CanUserSort="True" Width="200" Binding="{Binding Attributes[Street]}" Header="Street Name"/>
                                <slData:DataGridTextColumn CanUserSort="True" Width="80" Binding="{Binding Attributes[FromLeft]}" Header="From Left"/>
                                <slData:DataGridTextColumn CanUserSort="True" Width="80" Binding="{Binding Attributes[ToLeft]}" Header="To Left"/>
                                <slData:DataGridTextColumn CanUserSort="True" Width="81" Binding="{Binding Attributes[FromRight]}" Header="From Right"/>
                                <slData:DataGridTextColumn CanUserSort="True" Width="80" Binding="{Binding Attributes[ToRight]}" Header="To Right"/>                            </slData:DataGrid.Columns>
                        </slData:DataGrid>
                        <!--Export to Excel -->
                        <StackPanel Grid.Row="5" Orientation="Horizontal">
                            <Rectangle Grid.Column="0" Width="100"/>
                            <Button x:Name="exportExcel" Click="exportExcel_Click" Content="Export To Excel" 
                                     Grid.Column="1" Height="25" Width="115" Margin="5"/>
                        </StackPanel>

                    </StackPanel>

                </Grid>

[JoannaLaroussi]

And finally

3. MyExcelExport class
using System;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Ink;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;

using System.Collections.Generic;
using System.Text;
using System.IO;
using ESRI.ArcGIS.Client;

using PUTES;


// export to Excel using field aliases
// modified code from http://forums.arcgis.com/threads/13460-Export-to-Excel...?p=79906#post79906
// it is called in PUTES private void exportExcel_Click(object sender, RoutedEventArgs e)

public static class MyExcelExport
{
    public static void Export(this DataGrid dg, List<string> excludedColumns, Dictionary<string, string> aliases)
    {
        ExportDataGrid(dg, excludedColumns, aliases);
    }
  
  
    public static void ExportDataGrid(DataGrid dGrid, List<string> excludedColumns, Dictionary<string, string> aliases)
    {
      
        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)
                {
                    if (excludedColumns != null)
                    {
                        foreach (string columnName in excludedColumns)
                        {
                            if (dgcol.Header.ToString() == columnName)
                            {
                                continue;
                            }
                            else
                            {
                                lstFields.Add(FormatField(dgcol.Header.ToString(), strFormat));
                            }
                        }
                    }
                    else
                        lstFields.Add(FormatField(dgcol.Header.ToString(), strFormat));
                }
                BuildStringOfRow(strBuilder, lstFields, strFormat);
            }
            foreach (Graphic data in dGrid.ItemsSource)
            {
                lstFields.Clear();
                foreach (DataGridColumn col in dGrid.Columns)
                {
                    string strValue = string.Empty;
                    string dataField = string.Empty;

                    if (aliases == null)
                    {            
                        dataField = col.Header.ToString();
                    }
                    else
                    {
                        foreach (KeyValuePair<string, string> kv in aliases)
                        {
                            if (col.Header.ToString() != kv.Value)
                            {
                               
                                // here are hard coded names of headers in a data grid
                                // and corresponding fields in a feature class
                               
                                //export PED query results
                                if (col.Header.ToString() == "Street Name")
                                {
                                    dataField = "Street";
                                }
                                else if (col.Header.ToString() == "From Left")
                                {
                                    dataField = "FromLeft";
                                }
                                else if (col.Header.ToString() == "To Left")
                                {
                                    dataField = "ToLeft";
                                }
                                else if (col.Header.ToString() == "From Right")
                                {
                                    dataField = "FromRight";
                                }
                                else if (col.Header.ToString() == "To Right")
                                {
                                    dataField = "ToRight";
                                }
                               
                                break;
                            }
                        }
                    }
         


                    if (excludedColumns != null)
                    {
                        foreach (string columnName in excludedColumns)
                        {
                            if (col.Header.ToString() == columnName)
                                continue;
                            else
                                strValue = data.Attributes[dataField].ToString();
                        }
                    }
                    else

                        strValue = data.Attributes[dataField].ToString();

                    lstFields.Add(FormatField(strValue, strFormat));
                }
                BuildStringOfRow(strBuilder, lstFields, strFormat);
            }
            StreamWriter sw = new StreamWriter(objSFD.OpenFile());
            if (strFormat == "XML")
            {
                // 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("<Created>" + DateTime.Now.ToLocalTime().ToLongDateString() + "</Created>");
                sw.WriteLine("<LastSaved>" + DateTime.Now.ToLocalTime().ToLongDateString() + "</LastSaved>");
                sw.WriteLine("</DocumentProperties>");
                sw.WriteLine("<Worksheet ss:Name=\"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();
        }
    }
    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;
        }
    }
    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;
    }

}