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

[DominiqueBroux]

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.

[Anonymous User]

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.

[DarinaTchountcheva · Accepted solution]

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

[Anonymous User]

Original User: gregg.breton

Yes, that fixed the null reference error Darina!


Dominique thank you 😄 for the Export solution

[GreggBreton]

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?

[Anonymous User]

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!

[GreggBreton]

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

[Anonymous User]

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

[GreggBreton]

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]

[Anonymous User]

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.