Export the data from silverlight datagrid to Excel!

[AnhTruong]

Hello,

Here are the codes where I got data to display in silverlight datagrid as following:

            QueryTask queryTask = new QueryTask(URLService);
            queryTask.ExecuteCompleted += QueryTask_ExecuteCompleted;
            queryTask.Failed += QueryTask_Failed;

            ESRI.ArcGIS.Client.Tasks.Query query = new ESRI.ArcGIS.Client.Tasks.Query();
            query.OutFields.Add("*");
            query.Where = "1=1";
            queryTask.ExecuteAsync(query);

I like to export the data displayed in silverlight gridview to MS Excel file.

I built a export() function, but the data exported look strange
Org                                 Address                  Contact
ESRI.ArcGIS.Client.Graphic ESRI.ArcGIS.Client.Graphic ESRI.ArcGIS.Client.Graphic ESRI.ArcGIS.Client.Graphic ESRI.ArcGIS.Client.Graphic     ESRI.ArcGIS.Client.Graphic

Please help me with the export() function to export the datagrid data to Excel and PDF file. Thanks in advance.

[DominiqueBroux]

Did you look at these threads:
- http://forums.arcgis.com/threads/24558-FeatureDataGrid-Excel-Export
or - http://forums.arcgis.com/threads/13460-Export-to-Excel... ?

[AnhTruong]

Hello Dominique,

I posted my current codes for exporting to Excel file and hopefully you can help me out why the empty data result and display column headers only in the Excel file. I got stuck in how to cache current data in PagedCollectionView to export. Please take a look on my code as following:

MainPage.xaml.cs

public MainPage()
        {                 
            LoadData();
        }

private void LoadData()
        {
            QueryTask queryTask = new QueryTask(URLService);
            queryTask.ExecuteCompleted += QueryTask_ExecuteCompleted;
            queryTask.Failed += QueryTask_Failed;

            ESRI.ArcGIS.Client.Tasks.Query query = new ESRI.ArcGIS.Client.Tasks.Query();
            query.OutFields.Add("*");
            query.Where = "1=1";
            queryTask.ExecuteAsync(query);
        }

        void QueryTask_ExecuteCompleted(object sender, QueryEventArgs args)
        {
            PagedCollectionView pagedCollectionView;
            FeatureSet featureSet = args.FeatureSet;
            pagedCollectionView = new PagedCollectionView(args.FeatureSet);
           
            if (featureSet != null && featureSet.Features.Count > 0)
                QueryDetailsDataGrid.ItemsSource = pagedCollectionView;
            else
                MessageBox.Show("No features returned from query");
        }

        private void btnExcel_Click(object sender, RoutedEventArgs e)
        {
            string data = ExportDataGrid(true, QueryDetailsDataGrid);
            SaveFileDialog sfd = new SaveFileDialog()
            {
                DefaultExt = "csv",
                Filter = "CSV Files (*.csv)|*.csv|All files (*.*)|*.*",
                FilterIndex = 1
            };
            if (sfd.ShowDialog() == true)
            {
                using (Stream stream = sfd.OpenFile())
                {
                    using (StreamWriter writer = new StreamWriter(stream))
                    {
                        writer.Write(data);
                        writer.Close();
                    }
                    stream.Close();
                }
            }
        }

        private string FormatCSVField(string data)
        {
            return String.Format("\"{0}\"",
                data.Replace("\"", "\"\"\"")
                .Replace("\n", "")
                .Replace("\r", "")
                );
        }

        public string ExportDataGrid(bool withHeaders, DataGrid grid)
        {
            string colPath;
            System.Reflection.PropertyInfo propInfo;
            System.Windows.Data.Binding binding;
            System.Text.StringBuilder strBuilder = new System.Text.StringBuilder();

            PagedCollectionView source = (QueryDetailsDataGrid.ItemsSource as PagedCollectionView);

            if (source == null)
                return "";

            List<string> headers = new List<string>();
            grid.Columns.ToList().ForEach(col =>
            {
                if (col is DataGridBoundColumn)
                {
                    headers.Add(FormatCSVField(col.Header.ToString()));
                }
            });
            strBuilder
            .Append(String.Join(",", headers.ToArray()))
            .Append("\r\n");

            foreach (Object data in source)
            {
                List<string> csvRow = new List<string>();
                foreach (DataGridColumn col in grid.Columns)
                {
                    if (col is DataGridBoundColumn)
                    {
                        binding = (col as DataGridBoundColumn).Binding;
                        colPath = binding.Path.Path;
                        propInfo = data.GetType().GetProperty(colPath);
                        if (propInfo != null)
                        {
                            csvRow.Add(FormatCSVField(propInfo.GetValue(data, null).ToString()));
                        }
                    }
                }
                strBuilder
                    .Append(String.Join(",", csvRow.ToArray()))
                    .Append("\r\n");
            }


            return strBuilder.ToString();
        }


I really appreciate if you or anyone else helps me the code how to get query data from FeatureSet to pass to export function. I got the empty data in Excel files but I cannot figure out. Thanks in advance.

Did you look at these threads:
- http://forums.arcgis.com/threads/24558-FeatureDataGrid-Excel-Export
or - http://forums.arcgis.com/threads/13460-Export-to-Excel... ?

[DominiqueBroux]

Sample code given in this thread.

[AnhTruong]

I looked in your sample codes. It worked very well with the sample data on ArcGIS API for Silverlight example, but in my real data which containing comma, for example: Chen, Michael in Name column and your sample code will export any data after comma to next column, therefore it will produce more columns than original ones. Any idea how to fix it? Thanks in advance for any further help to cover my above issue.

[DominiqueBroux]

If your data contain commas you have to surround your data with quote.

So replace:

strBuilder.AppendLine(String.Join(",", lstFields.ToArray()));

by

 
strBuilder.AppendLine(String.Join(",", lstFields.Select(f => "\"" + f + "\"")));

But now you might run into issues if you data contain quotes.
So a better fix might be:

 
strBuilder.AppendLine(String.Join(",", lstFields.Select(f => "\"" + f.Replace("\"", "\"\"") + "\"")));

[AnhTruong]

Hi Dominique,

Your ArcGIS API for silverlight expertise are really help me to get starting on this new learning smoothly. Thanks for your details to walk through my problems. Best wishes to you on Happy New Year.

[DominiqueBroux]

Hi Dominique,

Your ArcGIS API for silverlight expertise are really help me to get starting on this new learning smoothly. Thanks for your details to walk through my problems. Best wishes to you on Happy New Year.

You are welcome.
I wish you an Happy New Year as well:cool:

[NathalieNeagle]

Dominique,

I've been using your code for a few years now.  It seems like it broke with the release of 3.2  I can hit the export all button it opens my dialog browse window to name and save the excel file but the file never saves and it never returns the export success dialog window.  Do you know if your example works with 3.2 or if anyone else is running into something similar?


Thanks
Nat

[NathalieNeagle]

I think my problem lies with SIlverlight 5, which is required for the new API 3.2.  I had to upgrade a few of my dll/referrences system.windows.browers, .controls .data ...etc.  This tool works with my older applicaition, which run version (runtime - 2.05727, version 2.0.5.0.) - Silverlight 4 but breaks when I upgrade to SL 5 and the newer dll.  As mentioned before I can click the export buttons and the save dialog opens; I select a location and file name - hit save and nothing happens.  Any suggestions:?