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

[GreggBreton]

Hello Dominique,

Just learning the debugging tools and the basics

so in this null reference error from the previous post, the object is var path?

and the check would be

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

referencing the previous check for [att], and it would be the same concept?

Thank you

[Anonymous User]

Original User: darinatch

Hi Gregg,

I am not sure why you get a null reference error there, but try using this GetValue method. It checks for most of the objects to see if they are null:

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

You should figure out what column is giving you the error though.
Look at the image below.

[ATTACH=CONFIG]18146[/ATTACH]

Add a break point by clicking on the grey line on the left where the red dot is. You will get the same red dot and red background for the line. Hit F5 to start debugging. Click a button to export some data (use a selection with 1-2 records only), and when you hit the break point a yellow line will appear on the break line. Use F10 to step through the code inside the method. Inspect the values of the variables in the Locals window as show in the image. You can also see their values by hoovering over a variable in the code window. Use  F5 to continue. This will let you step through this code for every cell value in the grid, and will help you determine the column(s) that throw the exception. 

In the Locals window you can see the properties of the objects by clicking on the plus sign. See image. The Path object is the one you are probably interested in.

Once you know what column is the problem, you might be able to fix the problem. It could be the special characters you have as field aliases (just a guess).

Search the web and/or Visual Studio help for how to debug applications. The above steps are only to give some heads up. 😄

Good Luck!

[GreggBreton]

Hello Darina,

Servers are down for maintenance, so when they become available I will debug and let you know how I do

I would like to get the DataGridExtension you set up with Dominique's solution

Thank you for the assistance 🙂

[Anonymous User]

Original User: gregg.breton

Hello Darina,

Servers are up and running

It looks like Dissolved_Oxygen

[ATTACH=CONFIG]18237[/ATTACH]

How to trap a null reference for a particular field?

[Anonymous User]

Original User: darinatch

Hi Gregg,

Replace:

return graphic.Attributes[att].ToString();

With:

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

I am sorry, I forgot to make this change. This should fix the first error.

What version of the DataGridExtensions.cs are you using? The DataGridExtensions.cs should look like this (this is the version in the project I posted a few days ago):

public static class DataGridExtensions
    {

        public static void Export(this FeatureDataGrid dg, IEnumerable<Graphic> graphics, List<string> excludedColumns)
        {
            ExportDataGrid(dg, graphics, excludedColumns);
        }

        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 FormatField(col.Header.ToString(), strFormat);

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

                    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)
            {
                DataGridBoundColumn column = col as DataGridBoundColumn;
                if ((column.Binding != null) && (column.Binding.Path != null))
                {
                    string path = column.Binding.Path.Path;
                    if (!string.IsNullOrEmpty(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;
        }
    }

Hope, this fixes the second error.

Darina

[GreggBreton]

Hello Darina,

That fixed the error 😄 😄

Thank you for all of your help

[Anonymous User]

Original User: bcales

The XML file opens up blank, in Excel, however the file itself if opened in notepad has the data. Also, Is there a way to handle fields that are multi-line.

e.g. I customed the output like this to have the Address lumped together, but when opening in Excel, it's single

resultFeature.Attributes.Add("OWNADDRESS_FULL",
                            resultFeature.Attributes["OWNADDRESS"] + "\r" + (resultFeature.Attributes["OWNCITYST"] + " " + resultFeature.Attributes["OWNZIPCODE"]).Trim()); // + "\r" + resultFeature.Attributes["OWNCOUNTRY"]);

[BobNichols]

Sorry I zipped the wrong file :

First off, thank you for providing this code.  It works pretty well for me if I am not exporting more than 10-15 records at a time.  Anymore than that then it starts either dropping data or wont save.  Screencap of an exported csv.  Are there limits to how much data can be exported?  I have found that if I export data from a datagrid that has fewer columns I am able to export more records before it either doesn't work or stops populating columns.

[ATTACH=CONFIG]32152[/ATTACH]

Here is a screencap of the datagrid that I am exporting from.

[ATTACH=CONFIG]32153[/ATTACH]

Thanks in advance for the help!

[BobNichols]

First off, thank you for providing this code.  It works pretty well for me if I am not exporting more than 10-15 records at a time.  Anymore than that then it starts either dropping data or wont save.  Screencap of an exported csv.  Are there limits to how much data can be exported?  I have found that if I export data from a datagrid that has fewer columns I am able to export more records before it either doesn't work or stops populating columns.

Never mind everything is working like it is supposed to.  I just received a new workstation from IT and its setup to not check for new webpages every time you visit a page.  So my app is running on an old broken version.  And the best part, all of the browser settings are locked so I cant change them or even clear the cache.  Makes web development a little hard to troubleshoot... 😞