Export To Excel - Downloaded only records in dialog view

[GreggBreton]

Hello,

Working with the Export Utility previously posted:

Export.zip


Customized to perform queries and export records. Notice only records within view of the data grid are actually downloaded. When you scroll down, only records in view are able to be downloaded. Attached are snapshots of examples.

If anyone has ideas on what is transpiring please let me know. I used firebug and all records seems to be downloaded to machine (not just 5, 10, or 15 records- but the entire recordset returned from query), but only those records within view of the dialog box are actually downloaded to csv file. Need to scroll down to actually get addtional records.

XAML and C# code have the same response (used C to query and return 6 states with a C) with the sample data. The screen shots are examples from the customized version that I used for another map service. If there is addtional data scrolling to the left or right, that data is not downloaded only records within view.

Appreciate any input on what on this issue - Thank you


Code Behind:

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using ESRI.ArcGIS.Client;
using ESRI.ArcGIS.Client.Tasks;

namespace ExportTest
{
    public partial class MainPage : UserControl
    {
        public MainPage()
        {
            InitializeComponent();
        }

        void QueryButton_Click(object sender, RoutedEventArgs e)
        {
            QueryTask queryTask =
                new QueryTask("http://sampleserver1.arcgisonline.com/ArcGIS/rest/services/Demographics/ESRI_Census_USA/MapServer/5");
            queryTask.ExecuteCompleted += QueryTask_ExecuteCompleted;
            queryTask.Failed += QueryTask_Failed;

            ESRI.ArcGIS.Client.Tasks.Query query = new ESRI.ArcGIS.Client.Tasks.Query();
            query.Text = StateNameTextBox.Text;

            query.OutFields.Add("*");
            queryTask.ExecuteAsync(query);
        }
        void QueryTask_ExecuteCompleted(object sender, ESRI.ArcGIS.Client.Tasks.QueryEventArgs args)
        {
            FeatureSet featureSet = args.FeatureSet;

            if (featureSet != null && featureSet.Features.Count > 0)
            {
                QueryDetailsDataGrid.ItemsSource = args.FeatureSet;
            }
            else
                MessageBox.Show("No features returned from query");
        }

        private void QueryTask_Failed(object sender, TaskFailedEventArgs args)
        {
            MessageBox.Show("Query execute error: " + args.Error);
        }
        private void Excel_Click(object sender, RoutedEventArgs e)
        {

            DataGridExtensions.ExportDataGrid(QueryDetailsDataGrid, null);
        }

    }


    public static class DataGridExtensions
    {
        public static void Export(this DataGrid dg, List<string> excludedColumns)
        {
            ExportDataGrid(dg, excludedColumns);
        }

        public static void ExportDataGrid(DataGrid dGrid, List<string> excludedColumns)
        {
            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;
                if (dGrid.HeadersVisibility == DataGridHeadersVisibility.Column || dGrid.HeadersVisibility == DataGridHeadersVisibility.All)
                {
                    var listFields = from col in dGrid.Columns
                                     where excludedColumns == null || !excludedColumns.Contains(col.Header.ToString())
                                     select col.Header.ToString();

                    BuildStringOfRow(strBuilder, listFields, strFormat);
                }
                foreach (Graphic data in dGrid.ItemsSource)
                {
                    var listFields = from col in dGrid.Columns
                                     where excludedColumns == null || !excludedColumns.Contains(col.Header.ToString())
                                     select GetValue(col.GetCellContent(data));

                    BuildStringOfRow(strBuilder, listFields, 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();
            }
        }

        private static string GetValue(FrameworkElement frameworkElement)
        {
            return frameworkElement == null ? null
       : (frameworkElement is TextBlock ? (frameworkElement as TextBlock).Text : frameworkElement.ToString());

        }

        private static void BuildStringOfRow(StringBuilder strBuilder, IEnumerable<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;
        }
    }
}

[Anonymous User]

Original User: darinatch

Hi Gregg,

It seems like the col.GetCellContent(data) in

var listFields = from col in dGrid.Columns
                                     where excludedColumns == null || !excludedColumns.Contains(col.Header.ToString())
                                     select GetValue(col.GetCellContent(data));

might be the problem.

The GetCellContent method returns a TextBlock, but the Text property of this TextBlock is an empty string once it hits cells that are not visible.
Try using the Graphic object, which represents each row of the grid to create a row for the export document.

[DarinaTchountcheva]

Gregg,

Here is my version of that portion of the code (Not as clean and elegant as yours, but works for me. Feel free to modify it and make it better using Linq if you would like. But this is the idea. ;-))  :

foreach (Graphic data in dGrid.ItemsSource)
                {
                    lstFields.Clear();
                    foreach (DataGridColumn col in dGrid.Columns)
                    {
                        string tempString = null;
                        if (col is DataGridTextColumn)
                        {
                            tempString = ((DataGridTextColumn)col).Binding.Path.Path.ToString();
                        }
                        else if (col is DataGridTemplateColumn)
                        {
                            //This is the case when the column has a hyperlink in the cell template
                            HyperlinkButton innerControl = ((DataGridTemplateColumn)col).CellTemplate.LoadContent() as HyperlinkButton;
                            if (innerControl != null)
                                tempString = innerControl.GetBindingExpression(HyperlinkButton.ContentProperty).ParentBinding.Path.Path.ToString();
                            else
                                tempString = string.Empty;
                        }

                        string attributeName = tempString.Replace("Attributes[", "").Replace("]", "");
                        string strValue = string.Empty;

                        if (excludedColumns != null)
                        {
                            foreach (string columnName in excludedColumns)
                            {
                                if (col.Header.ToString() == columnName)
                                {
                                    continue;
                                }
                                else
                                {
                                    if (data.Attributes[attributeName] != null)
                                        strValue = data.Attributes[attributeName].ToString();
                                    lstFields.Add(FormatField(strValue, strFormat));
                                }
                            }
                        }
                        else
                        {
                            if (data.Attributes[attributeName] != null)
                                strValue = data.Attributes[attributeName].ToString();
                            lstFields.Add(FormatField(strValue, strFormat));
                        }
                    }                   

                    BuildStringOfRow(strBuilder, lstFields, strFormat);
                }

Good Luck!

[Anonymous User]

Original User: gregg.breton

Hello Darina

I will give that a try - I am new to Silverlight, so the pretty code is not mine 🙂

I retrieved it from Export.zip

Dominique BROUX was nice enough to have customized the code and provide it to the Silverlight forum.

New to the forum- How were you able to post the code with the scroll bars? Much nicer than the code tags I used

I will try your idea and let you know how i do - thank you

[DarinaTchountcheva]

Gregg,

When you write your post, you will see a set of toolbars on the top. Hover over each tool to see what it does.
To post C# code, use the "#" tool. You will see the CODE tag. Paste your code in between the opening and closing tag. That's all.

Dominique is great! He is probably already looking into this. 😉

[Anonymous User]

Original User: gregg.breton

Thank you Darina,

I was not able to integrate your example into the Export solultion provided by Dominque. I will keep trying and like you said maybe Dominque will provide some insight to this issue.

[DominiqueBroux]

I updated the sample to fix the issue :

[Anonymous User]

Original User: gregg.breton

Hello Dominque,

Opened up the QueryWithoutMap.zip and it contained the XAML(QueryWithoutMap.XAML), but the code behind was code for QueryRelatedRecords example (QueryRelatedRecords.xaml.cs).

Gregg

[DominiqueBroux]

Sorry I zipped the wrong file :

[Anonymous User]

Original User: gregg.breton

Hello Dominque,

I get that null reference error. Tried your your solution from the previous post

private static string GetValue(FrameworkElement frameworkElement)
{
    return frameworkElement == null ? null
         :( frameworkElement is TextBlock ? (frameworkElement as TextBlock).Text : frameworkElement.ToString());
}