Export to Excel...

ducksunlimited · ‎09-22-2010

HI All, anybody would share how to export from a datagrid to a csv or excel file? thanks,

Chris

AliMirzabeigi · ‎09-22-2010

Have you looked the following? It looks promising...
http://www.codeproject.com/KB/silverlight/SilverlightDataGridExport.aspx
Here is another post: http://www.codeproject.com/KB/silverlight/SL4InteropWithExcel.aspx

Hope these help.

ducksunlimited · ‎09-22-2010

Thanks for the reply. I already checked it and made it work for a simple attribute query DataGrid, but couldn't make it work for a DataGrid from a spatial query (from ESRI). I am new to Silverlight and don't know how to customize the codes. I attached the DataGrid format below. Any further help would be appreicated.

<slData:DataGrid.Columns>
<codeBehind:GraphicAttributeColumn Width="102" SortMemberPath="POP2007" AttributeName="POP2007" Header="Population 2007"/>
</slData:DataGrid.Columns>

Have you looked the following? It looks promising...
http://www.codeproject.com/KB/silverlight/SilverlightDataGridExport.aspx
Here is another post: http://www.codeproject.com/KB/silverlight/SL4InteropWithExcel.aspx

Hope these help.

DarinaTchountcheva · ‎09-22-2010

Hi Russel,

I have used the first link to export DataGrid to Excel.
I have modified the code to work for Graphics data.

public static class DataGridExtensions
    {
        public static void Export(this DataGrid dg, List<string> excludedColumns, Dictionary<string, string> aliases)
        {
            ExportDataGrid(dg, excludedColumns, aliases);
        }

        public static void ExportDataGrid(DataGrid dGrid, List<string> excludedColumns, Dictionary<string, string> aliases)
        {
            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;
                List<string> lstFields = new List<string>();
                if (dGrid.HeadersVisibility == DataGridHeadersVisibility.Column || dGrid.HeadersVisibility == DataGridHeadersVisibility.All)
                {
                    foreach (DataGridColumn dgcol in dGrid.Columns)
                    {
                        if (excludedColumns != null)
                        {
                            foreach (string columnName in excludedColumns)
                            {
                                if (dgcol.Header.ToString() == columnName)
                                {
                                    continue;
                                }
                                else
                                {
                                    lstFields.Add(FormatField(dgcol.Header.ToString(), strFormat));
                                }
                            }
                        }
                        else
                            lstFields.Add(FormatField(dgcol.Header.ToString(), strFormat));
                    }

                    BuildStringOfRow(strBuilder, lstFields, strFormat);
                }
                foreach (Graphic data in dGrid.ItemsSource)
                {
                    lstFields.Clear();
                    foreach (DataGridColumn col in dGrid.Columns)
                    {
                        string strValue = string.Empty;
                        string dataField = string.Empty;
                        foreach (KeyValuePair<string, string> kv in aliases)
                        {
                            if (col.Header.ToString() == kv.Value)
                            {
                                dataField = kv.Key;
                                break;
                            }
                        }
                        if (excludedColumns != null)
                        {
                            foreach (string columnName in excludedColumns)
                            {
                                if (col.Header.ToString() == columnName)
                                    continue;
                                else
                                    strValue = data.Attributes[dataField].ToString();                                
                            }
                        }
                        else
                            strValue = data.Attributes[dataField].ToString();                            
                        
                        lstFields.Add(FormatField(strValue, strFormat));
                    }
                    BuildStringOfRow(strBuilder, lstFields, 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 void BuildStringOfRow(StringBuilder strBuilder, List<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 in your Export button click event handler call this method:

YourNamespace.DataGridExtensions.ExportDataGrid(theGrid, null, aliases);

Where:

- theGrid is the DataGrid with the data to be exported
- null is for excludedColumns (I just needed to not export some of the columns)
- aliases is a Dictionary<string, string>, where the key is the real Field Name, and value is the Field Alias that I have used to display in the DataGrid headers

Good Luck!

ducksunlimited · ‎09-27-2010

thanks for sharing the codes. I have been working on this for the day but could't make it work. I think the problem is with the 3rd auguments "aliases". Where and how should I declare it? and what the syntax looks like? thank again for the help.

Russel

Hi Russel,

I have used the first link to export DataGrid to Excel.
I have modified the code to work for Graphics data.

public static class DataGridExtensions
    {
        public static void Export(this DataGrid dg, List<string> excludedColumns, Dictionary<string, string> aliases)
        {
            ExportDataGrid(dg, excludedColumns, aliases);
        }

        public static void ExportDataGrid(DataGrid dGrid, List<string> excludedColumns, Dictionary<string, string> aliases)
        {
            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;
                List<string> lstFields = new List<string>();
                if (dGrid.HeadersVisibility == DataGridHeadersVisibility.Column || dGrid.HeadersVisibility == DataGridHeadersVisibility.All)
                {
                    foreach (DataGridColumn dgcol in dGrid.Columns)
                    {
                        if (excludedColumns != null)
                        {
                            foreach (string columnName in excludedColumns)
                            {
                                if (dgcol.Header.ToString() == columnName)
                                {
                                    continue;
                                }
                                else
                                {
                                    lstFields.Add(FormatField(dgcol.Header.ToString(), strFormat));
                                }
                            }
                        }
                        else
                            lstFields.Add(FormatField(dgcol.Header.ToString(), strFormat));
                    }

                    BuildStringOfRow(strBuilder, lstFields, strFormat);
                }
                foreach (Graphic data in dGrid.ItemsSource)
                {
                    lstFields.Clear();
                    foreach (DataGridColumn col in dGrid.Columns)
                    {
                        string strValue = string.Empty;
                        string dataField = string.Empty;
                        foreach (KeyValuePair<string, string> kv in aliases)
                        {
                            if (col.Header.ToString() == kv.Value)
                            {
                                dataField = kv.Key;
                                break;
                            }
                        }
                        if (excludedColumns != null)
                        {
                            foreach (string columnName in excludedColumns)
                            {
                                if (col.Header.ToString() == columnName)
                                    continue;
                                else
                                    strValue = data.Attributes[dataField].ToString();                                
                            }
                        }
                        else
                            strValue = data.Attributes[dataField].ToString();                            
                        
                        lstFields.Add(FormatField(strValue, strFormat));
                    }
                    BuildStringOfRow(strBuilder, lstFields, 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 void BuildStringOfRow(StringBuilder strBuilder, List<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 in your Export button click event handler call this method:

YourNamespace.DataGridExtensions.ExportDataGrid(theGrid, null, aliases);

Where:

- theGrid is the DataGrid with the data to be exported
- null is for excludedColumns (I just needed to not export some of the columns)
- aliases is a Dictionary<string, string>, where the key is the real Field Name, and value is the Field Alias that I have used to display in the DataGrid headers

Good Luck!

DarinaTchountcheva · ‎09-28-2010

Hi Russel,

I am using the aliases to show more meaningful field names in DataGrids, MapTips, ...

//The parameter "aliases" is a Dictonary<string, string> and you can hardcode it, 
//get the data form XML config file, or from a database.
//Here is how you can create it:
Dictionary<string, string> aliases = new Dictionary<string, string>();
aliases.Add("OriginalFieldName1", "FieldNameAlias1");
aliases.Add("OriginalFieldName2", "FieldNameAlias2"); 
aliases.Add("OriginalFieldName3", "FieldNameAlias3"); 
aliases.Add("OriginalFieldName4", "FieldNameAlias4");

This will only work though if your DataGrid has the same aliases as headers.

And here is where my code doesn't work, because I always use Aliases, and I did not cover the scenario when Aliases are not used. (Sorry, my bad).

I don't have time to do any testing, but try to add the "if statement" below, and see if it will work with no aliases (see the comments):

 foreach (Graphic data in dGrid.ItemsSource)
                {
                    lstFields.Clear();
                    foreach (DataGridColumn col in dGrid.Columns)
                    {
                        string strValue = string.Empty;
                        string dataField = string.Empty;
                        // Add this if statement 
                        if (aliases == null)
                        {
                            dataField = col.Header.ToString();
                        }
                        else
                        {
                            foreach (KeyValuePair<string, string> kv in aliases)
                            {
                                if (col.Header.ToString() == kv.Value)
                                {
                                    dataField = kv.Key;
                                    break;
                                }
                            }
                        }
                        // End if statement
                        if (excludedColumns != null)
                        {
                            foreach (string columnName in excludedColumns)
                            {
                                if (col.Header.ToString() == columnName)
                                    continue;
                                else
                                    strValue = data.Attributes[dataField].ToString();                                
                            }
                        }
                        else
                            strValue = data.Attributes[dataField].ToString();                            
                        
                        lstFields.Add(FormatField(strValue, strFormat));
                    }
                    BuildStringOfRow(strBuilder, lstFields, strFormat);
                }

Then you can change the call:

YourNamespace.DataGridExtensions.ExportDataGrid(theGrid, null, null);

And you can do this if your DataGrid displays the real field names.
If the code fix doesn't work, try creating the aliases parameter with the same OriginalFieldName and FieldNameAlias just to see if it will work.

I will go back and simplify this method when I get a chance.

Gotta go! Good Luck!

DavidAshton · ‎02-23-2011

Darina and Russell,

Thanks for the posts/thread. And thank you Darina for the code!

I was wondering if either have had the time to get the code to work without using Aliases,

Russell or Darina did you ever check Darina code to with the if statement to see if it worked?

I don't have time to do any testing, but try to add the "if statement" below, and see if it will work with no aliases (see the comments):

I was able to get all the code to compile and when I hit the excel button to export my DataGrid I see the dialog box open and I give the file a name but the file never gets created. When I go to find the saved file (cvs or excel) it is not there.

I'm not sure what to do and why this is? Can either of you help...have you experience anything similar?

I was also wondering would this code work for a featuredatagrid?

Oh one more thing for my Darina you mentioned for the call method for the button click to add this

YourNamespace.DataGridExtensions.ExportDataGrid(theGrid, null, null);

but this was erroring out for me but once I drop my Namespace the error went away so this is what mine looks like:

   private void Excel_Click(object sender, RoutedEventArgs e)
        {

            DataGridExtensions.ExportDataGrid(QueryDetailsDataGrid, null, null);
        }

Could this be my issue...everything else is the same.

Thanks
Dave

KarenBlaney · ‎01-05-2012

Hi Russel,

I am using the aliases to show more meaningful field names in DataGrids, MapTips, ...

//The parameter "aliases" is a Dictonary<string, string> and you can hardcode it, 
//get the data form XML config file, or from a database.
//Here is how you can create it:
Dictionary<string, string> aliases = new Dictionary<string, string>();
aliases.Add("OriginalFieldName1", "FieldNameAlias1");
aliases.Add("OriginalFieldName2", "FieldNameAlias2"); 
aliases.Add("OriginalFieldName3", "FieldNameAlias3"); 
aliases.Add("OriginalFieldName4", "FieldNameAlias4");

This will only work though if your DataGrid has the same aliases as headers.

And here is where my code doesn't work, because I always use Aliases, and I did not cover the scenario when Aliases are not used. (Sorry, my bad).

I don't have time to do any testing, but try to add the "if statement" below, and see if it will work with no aliases (see the comments):

 foreach (Graphic data in dGrid.ItemsSource)
                {
                    lstFields.Clear();
                    foreach (DataGridColumn col in dGrid.Columns)
                    {
                        string strValue = string.Empty;
                        string dataField = string.Empty;
                        // Add this if statement 
                        if (aliases == null)
                        {
                            dataField = col.Header.ToString();
                        }
                        else
                        {
                            foreach (KeyValuePair<string, string> kv in aliases)
                            {
                                if (col.Header.ToString() == kv.Value)
                                {
                                    dataField = kv.Key;
                                    break;
                                }
                            }
                        }
                        // End if statement
                        if (excludedColumns != null)
                        {
                            foreach (string columnName in excludedColumns)
                            {
                                if (col.Header.ToString() == columnName)
                                    continue;
                                else
                                    strValue = data.Attributes[dataField].ToString();                                
                            }
                        }
                        else
                            strValue = data.Attributes[dataField].ToString();                            
                        
                        lstFields.Add(FormatField(strValue, strFormat));
                    }
                    BuildStringOfRow(strBuilder, lstFields, strFormat);
                }

Then you can change the call:

YourNamespace.DataGridExtensions.ExportDataGrid(theGrid, null, null);

And you can do this if your DataGrid displays the real field names.
If the code fix doesn't work, try creating the aliases parameter with the same OriginalFieldName and FieldNameAlias just to see if it will work.

I will go back and simplify this method when I get a chance.

Gotta go! Good Luck!

I got this to work by adding the following if statement to check for null values:

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

ScottLarkin · ‎11-25-2013

Just wanted to say thanks to darinatch as I have been trying to work this out all day and just found your code example and it works great.

Cheers 😮