topic Re: Export To Excel - Downloaded only records in dialog view in ArcGIS API for Silverlight Questions

Export To Excel - Downloaded only records in dialog view

GreggBreton — Wed, 26 Sep 2012 17:00:52 GMT

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;
        }
    }
}

Re: Export To Excel - Downloaded only records in dialog view

Anonymous User — Sat, 11 Dec 2021 21:10:10 GMT

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.

Re: Export To Excel - Downloaded only records in dialog view

DarinaTchountcheva — Sat, 11 Dec 2021 21:10:14 GMT

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!

Re: Export To Excel - Downloaded only records in dialog view

Anonymous User — Wed, 26 Sep 2012 20:30:01 GMT

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

Re: Export To Excel - Downloaded only records in dialog view

DarinaTchountcheva — Thu, 27 Sep 2012 13:04:32 GMT

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. 😉

Re: Export To Excel - Downloaded only records in dialog view

Anonymous User — Fri, 28 Sep 2012 13:59:03 GMT

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.

Re: Export To Excel - Downloaded only records in dialog view

DominiqueBroux — Fri, 28 Sep 2012 14:13:20 GMT

I updated the sample to fix the issue :

Re: Export To Excel - Downloaded only records in dialog view

Anonymous User — Fri, 28 Sep 2012 14:41:48 GMT

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

Re: Export To Excel - Downloaded only records in dialog view

DominiqueBroux — Fri, 28 Sep 2012 14:59:24 GMT

Sorry I zipped the wrong file :

Re: Export To Excel - Downloaded only records in dialog view

Anonymous User — Sat, 11 Dec 2021 21:10:17 GMT

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());
}

Re: Export To Excel - Downloaded only records in dialog view

DominiqueBroux — Sat, 11 Dec 2021 21:10:20 GMT

In my latter version, I changed GetValue, it's now:

private static string GetValue(DataGridColumn col, Graphic graphic)
{
 if (col is DataGridBoundColumn)
 {
  var path = (col as DataGridBoundColumn).Binding.Path.Path;
  var att = Regex.Replace(path, ".*\\[(.*)\\]", "$1");
  return graphic.Attributes[att].ToString();
 }
 return string.Empty;
}

so it should solve your issue.

Re: Export To Excel - Downloaded only records in dialog view

Anonymous User — Sat, 11 Dec 2021 21:10:23 GMT

Original User: gregg.breton

Dominque,

I utilized the updated sample and is works, and when I implement to try another application (Have null value?)

[ATTACH=CONFIG]18080[/ATTACH]

I just downloaded to make sure I am using the updated example

 if (col is DataGridBoundColumn)
            {
                var path = (col as DataGridBoundColumn).Binding.Path.Path;
                var att = Regex.Replace(path, ".*\\[(.*)\\]", "$1");
                return graphic.Attributes[att].ToString();
            }
            return string.Empty;

Still receiving that null reference error

If you need the reference webservice need more information just let me know. I have your previous sample working, but again just was not able to download all records, only those that were displayed in the window.

Re: Export To Excel - Downloaded only records in dialog view

DarinaTchountcheva — Fri, 28 Sep 2012 17:36:38 GMT

Dominique, thanks for the solution! 😄
I think Gregg is getting the error when the attribute object is null (see image).

Greg, something like this should fix the problem (make sure you are using the new GetValue method that Dominique provided):

private static string GetValue(DataGridColumn col, Graphic graphic) {  if (col is DataGridBoundColumn)  {   var path = (col as DataGridBoundColumn).Binding.Path.Path;   var att = Regex.Replace(path, ".*\\[(.*)\\]", "$1");   return graphic.Attributes[att] == null ? string.Empty : graphic.Attributes[att].ToString();  }  return string.Empty; }

Re: Export To Excel - Downloaded only records in dialog view

Anonymous User — Fri, 28 Sep 2012 18:04:41 GMT

Original User: gregg.breton

Yes, that fixed the null reference error Darina!

Dominique thank you 😄 for the Export solution

Re: Export To Excel - Downloaded only records in dialog view

GreggBreton — Sat, 29 Sep 2012 13:39:08 GMT

Darina,

Now that I got the DataGrid to work with the query, I wanted to do the same with the FeatureDataGrid

On this export to excel issue - "null reference error" you posted a solution file for a feature data grid using california.

Downloaded and replaced the dll's got it working with the sample data you used (California), customized to the same data I used above, and of course received a null reference error.

[ATTACH=CONFIG]18090[/ATTACH]

Would you be able to comment on how you would trap the null references in your original code?

Re: Export To Excel - Downloaded only records in dialog view

Anonymous User — Sat, 11 Dec 2021 21:10:27 GMT

Original User: darinatch

Hi Gregg,

Looking at the code, I believe you need to change these lines in "public void ExportData(List<Graphic> graphics, string[] fields, string[] aliases)":

string strValue = string.Empty;
strValue = data.Attributes[field].ToString();

with this line:

string strValue = data.Attributes[field] != null ? data.Attributes[field].ToString() : string.Empty;

And change these lines in "public void ExportData(List<Graphic> graphics, ObservableCollection<DataGridColumn> columns)":

string strValue = string.Empty;
strValue = data.Attributes[field.Header.ToString()].ToString();

with this line:

string strValue = data.Attributes[field.Header.ToString()] != null ? data.Attributes[field.Header.ToString()].ToString() : string.Empty;

You could use Dominiques code for the FeatureDataGrid too with some little changes.
This is how your class would look like. The extra parameter "graphics" is added so you can export the selected graphics if this is what you want.

public static class DataGridExtensions
    {
        //added the extra parameter
        public static void Export(this FeatureDataGrid dg, IEnumerable<Graphic> graphics, List<string> excludedColumns)
        {
            ExportDataGrid(dg, graphics, excludedColumns);
        }

        //added the extra parameter
        public static void ExportDataGrid(FeatureDataGrid dGrid, IEnumerable<Graphic> graphics, 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);
                }
                //this line changed a little
                foreach (Graphic graphic in graphics)
                {
                    var listValues = from col in dGrid.Columns
                                     where excludedColumns == null || !excludedColumns.Contains(col.Header.ToString())
                                     select GetValue(col, graphic);

                    BuildStringOfRow(strBuilder, listValues, 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(DataGridColumn col, Graphic graphic)
        {
            if (col is DataGridBoundColumn)
            {
                var path = (col as DataGridBoundColumn).Binding.Path.Path;
                var att = Regex.Replace(path, ".*\\[(.*)\\]", "$1");
                return graphic.Attributes[att].ToString();
            }
            return string.Empty;
        }

        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;
        }
    }

And then make these chanes in the event handlers to do the export:

//handle the Export All To Excel click
        private void exportAll_Click(object sender, RoutedEventArgs e)
        {
            FeatureLayer fe = (FeatureLayer)MyMap.Layers["California"];
            //ete.ExportData(fe.Graphics.ToList(), MyDataGrid.Columns);

            MyDataGrid.Export(fe.Graphics, null);
        }

        private void exportSelected_Click(object sender, RoutedEventArgs e)
        {
            FeatureLayer fe = (FeatureLayer)MyMap.Layers["California"];
            //ete.ExportData(fe.SelectedGraphics.ToList(), MyDataGrid.Columns);

            MyDataGrid.Export(fe.SelectedGraphics.ToList(), null);
        }

Good Luck!

Re: Export To Excel - Downloaded only records in dialog view

GreggBreton — Mon, 01 Oct 2012 19:44:39 GMT

Hello Darina,

Entered those lines to change your existing code and it fixed the error and allowed me to download the excel file. Tried all three exports (Export All To Excel, Export Selected To Excel, Export Alias to Excel)

The downloaded excel files were empty with the field headings?

[ATTACH=CONFIG]18116[/ATTACH]

[ATTACH=CONFIG]18117[/ATTACH]

[ATTACH=CONFIG]18118[/ATTACH]

Records were downloaded to Feature Data Grid, and I know the code works with your sample from California. What is next?

Keep working on it and let you know Thank you Darina

Re: Export To Excel - Downloaded only records in dialog view

Anonymous User — Mon, 01 Oct 2012 20:57:21 GMT

Original User: darinatch

Gregg,

You probably have aliases set for the fields in your ArcMap document. And that is great.
Just the sample is old, and doesn't cover this scenario.

That's why you should use Dominique's solution. It is better!

I changed the sample to use Dominique's code. I commented out the code that you don't need anymore. Delete it.
Dominique's code is in the DataGridExtensions class. I made 2 changes to it. I used the FormatField method in 2 places to cover the scenario when a comma exists in the field value, and the user is exporting to CSV.

There is no more button to export fields with Aliases, because you can set your aliases in ArcMap and they get exported. Instead there is a button Export Only Some Columns to show you how to exclude some columns from the export if you don't want all of them.

The ExportToExcel class is still there, but you don't need it anymore. You can delete it.

I hope all works for you. If not, just post back. I will look at it tomorrow.

Good Luck!

Darina

Re: Export To Excel - Downloaded only records in dialog view

GreggBreton — Mon, 01 Oct 2012 21:55:49 GMT

Darina,

Ok, downloaded sample got it working with the California data

Customized to the my new feature service and came up with that nasty null reference error again

[ATTACH=CONFIG]18122[/ATTACH]

Made the edit from the previous post to ty and fix the null reference error

return graphic.Attributes[att] == null ? string.Empty : graphic.Attributes[att].ToString();

ran again and still come up with that error. - getting real close though samples load, sln file compiles just that nasty null reference error.

[ATTACH=CONFIG]18123[/ATTACH]

Re: Export To Excel - Downloaded only records in dialog view

Anonymous User — Tue, 02 Oct 2012 06:36:40 GMT

Original User: dbroux

Null reference error should usually by very simple to fix with a debugger.

Look at the object that is null and add a check to avoid that case.