topic Re: Export the data from silverlight datagrid to Excel! in ArcGIS API for Silverlight Questions

Export the data from silverlight datagrid to Excel!

AnhTruong — Thu, 08 Dec 2011 18:25:02 GMT

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.

Re: Export the data from silverlight datagrid to Excel!

DominiqueBroux — Fri, 09 Dec 2011 07:51:35 GMT

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

Re: Export the data from silverlight datagrid to Excel!

AnhTruong — Wed, 21 Dec 2011 13:58:52 GMT

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

Re: Export the data from silverlight datagrid to Excel!

DominiqueBroux — Mon, 26 Dec 2011 10:20:20 GMT

Sample code given in this thread.

Re: Export the data from silverlight datagrid to Excel!

AnhTruong — Tue, 27 Dec 2011 12:34:42 GMT

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.

Re: Export the data from silverlight datagrid to Excel!

DominiqueBroux — Sat, 11 Dec 2021 08:53:16 GMT

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

So replace:

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

 
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("\"", "\"\"") + "\"")));

Re: Export the data from silverlight datagrid to Excel!

AnhTruong — Wed, 28 Dec 2011 11:32:26 GMT

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.

Re: Export the data from silverlight datagrid to Excel!

DominiqueBroux — Fri, 30 Dec 2011 07:53:31 GMT

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:

Re: Export the data from silverlight datagrid to Excel!

NathalieNeagle — Tue, 25 Feb 2014 03:39:23 GMT

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

Re: Export the data from silverlight datagrid to Excel!

NathalieNeagle — Wed, 26 Feb 2014 15:26:01 GMT

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:?

Re: Export the data from silverlight datagrid to Excel!

NathalieNeagle — Sat, 11 Dec 2021 08:53:19 GMT

In this post

http://forums.arcgis.com/threads/13460-Export-to-Excel

Karen Blaney mentioned that the file won't export if there are null attributes.

THIS IS MY ISSUE.

The export works if every field has a value, if not it doesn't save.

I'm trying to add code to handle this but running into no love. '

I changed the simple if statement I added to the color of RED to highlight the change

 //Create the rows
                foreach (Graphic data in graphics)
                {                    
                    lstValues.Clear();
                    
                    foreach (string field in fields)
                    {
                        string strValue = string.Empty;

                        if ((data.Attributes[field]) != null)
                        {
                            strValue = data.Attributes[field].ToString();
                        }
                        lstValues.Add(FormatField(strValue, strFormat));

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

                        //lstValues.Add(FormatField(strValue, strFormat));
                    }
                    //Build the row
                    BuildStringOfRow(strBuilder, lstValues, strFormat);
                                  
                }

                WriteExcelFile(objSFD, strFormat, strBuilder);

 //Build the column headers row
                BuildStringOfRow(strBuilder, lstFields, strFormat);

                //Create the rows
                foreach (Graphic data in graphics)
                {
                    lstValues.Clear();                   
                    
                    foreach (DataGridColumn field in columns)
                    {
                        string strValue = string.Empty;

                        if ((data.Attributes[field.Header.ToString()]) != null)
                        {
                            strValue = data.Attributes[field.Header.ToString()].ToString();
                        }
                       

                        lstValues.Add(FormatField(strValue, strFormat));

                       
                    }
                    //Build the row
                    BuildStringOfRow(strBuilder, lstValues, strFormat);
                      
                }

                WriteExcelFile(objSFD, strFormat, strBuilder);
            }

Could any one point me in the right direction.

Thanks
Nathalie

Re: Export the data from silverlight datagrid to Excel!

NathalieNeagle — Thu, 27 Feb 2014 20:12:19 GMT

My last code changes worked! Thanks!