Hierarchical grid export to excel

[Ravi]

Hi,

I am using Q1,2011, let me know the possible ways to export Hierarchical grid to excel.

Thanks

[Dimitrina]

Hello David,

If you need to export a Hierarchal grid to Excel, you should use the Export method of the RadGridView. The closest sample that I can suggest to you is this online demo. 

There are two ways to do this:

1. In the demo inside ElementExported event handler, you check if a row has been imported. If so you design how to export the details and you specify what details to be written in the Excel. For example if your table contains Clubs(clubsGrid) and they contain Players in the Hierarchy table, you could use:

private void clubsGrid_ElementExported(...) {

if (e.Element == ExportElement.Row)

{

 Club obj = e.Context as Club;

 if (obj != null)

 {

 

e.Writer.Write(String.Format(@"<tr><td style=""background-color:#CCC;"" colspan=""{0}"">",((IEnumerable<Telerik.Windows.Controls.GridViewColumn>)clubsGrid.Columns).Count()));

   foreach (Player player in obj.Players)

   {

   e.Writer.Write(String.Format(@"<b>Name:</b> {0} <br />", player.Name));

                        ...

   }

   e.Writer.Write("</td></tr>");

        }
}

2.You could define a resource data template for the child grid(the way want it to look like), for example "RowDetailsTemplate". You apply this template in HierarchyChildTemplate property of the grid:

private void clubsGrid_ElementExported(...)
{
     if (e.Element == ExportElement.Row)
          { var template = this.LayoutRoot.Resources["RowDetailsTemplate"] as DataTemplate;
                var grid = template.LoadContent() as RadGridView;
                grid.DataContext = e.Context;
                var subExport = grid.ToHtml();
                e.Writer.Write(subExport);
           }
 }
 

You could as well read this help article about more information regarding exporting.

Regards,
Didie
the Telerik team

[Umang]

Hi,

Above solution is not worked for me don't know what I am missing so please guide me.

In my current project, we are doing some POC work for WPF project, so I am trying to prove the concept in dev express, as well as Telerik grid and I, found one issue for hierarchical sub-grid export.

Here are my and ViewModel as well as code behind file part of the code.

<Grid.Resources>
            <DataTemplate x:Key="RowDetailsTemplate">
                <telerik:RadGridView x:Name="productsGrid" 
                                                         ItemsSource="{Binding Orders}" 
                                                         AutoGenerateColumns="False"
                                                         ShowSearchPanel="True">
                    <telerik:RadGridView.Columns>
                        <telerik:GridViewDataColumn DataMemberBinding="{Binding OrderDate}" Header="Order Date"/>
                        <telerik:GridViewDataColumn DataMemberBinding="{Binding OrderAmount}" Header="Order Amount"/>
                        <telerik:GridViewDataColumn DataMemberBinding="{Binding FirmName}" Header="Firm Name"/>
                    </telerik:RadGridView.Columns>
                </telerik:RadGridView>
            </DataTemplate>
        </Grid.Resources>

<telerik:RadGridView Grid.Row="0" x:Name="radGridView" 
                             AutoGenerateColumns="False"
                             ItemsSource="{Binding ProductCollection}" ElementExported="radGridView_ElementExported"
                         HierarchyChildTemplate="{StaticResource RowDetailsTemplate }"> 
        <telerik:RadGridView.Columns>
                <telerik:GridViewDataColumn DataMemberBinding="{Binding Date}" Header="Date"/>
                <telerik:GridViewDataColumn DataMemberBinding="{Binding TotalPurchase}" Header="Total Purchase"/>
            <telerik:GridViewDataColumn DataMemberBinding="{Binding TotalSales}" Header="Total Sales"/>
        </telerik:RadGridView.Columns>
        <telerik:RadGridView.ChildTableDefinitions>
                <telerik:GridViewTableDefinition>
                <telerik:GridViewTableDefinition.Relation>
                        <telerik:PropertyRelation ParentPropertyName="Orders" />
                    </telerik:GridViewTableDefinition.Relation>
                </telerik:GridViewTableDefinition>
        </telerik:RadGridView.ChildTableDefinitions>         
    </telerik:RadGridView>

Here is my C# code 

MVVM 

public void ExcelExportAllData(object parameter)
        {
            UserControl wnd = parameter as UserControl;
            RadGridView grid = (RadGridView)wnd.FindName("radGridView");
            RadGridView childGrid = (RadGridView)wnd.FindName("productsGrid");

            string extension = "xls";
            SaveFileDialog dialog = new SaveFileDialog()
            {
                DefaultExt = extension,
                Filter = String.Format("{1} files (.{0})|.{0}|All files (.)|.", extension, "Excel"),
                FilterIndex = 1
            };
            
            if (dialog.ShowDialog() == true)
            {
                var stream = dialog.OpenFile();
                var streamWriter = new StreamWriter(stream);
                var html = grid.ToHtml();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append(html);
                stringBuilder.Replace("style=\"border-collapse:collapse\"", "");
                stringBuilder.Replace("border = 1", "");
                streamWriter.Write(stringBuilder);
                streamWriter.Close();
            }            
        }

I tried with mvvm pattern but I am not able to find subgrid data into excel after that I tried to achieve same things in code behide and here is a code  

private void radGridView_ElementExported(object sender, GridViewElementExportedEventArgs e)
        {
            if (e.Element == ExportElement.Row)
            {
                var template = this.LayoutRoot.Resources["RowDetailsTemplate"] as DataTemplate;
                var grid = template.LoadContent() as RadGridView;
                grid.ItemsSource =e.Context;
                grid.DataContext = e.Context;
                var subExport = grid.ToHtml();
                e.Writer.Write(subExport);
            }
        }

grid.ToHtml();  This method only returns the column header but not returning any data.

 

 

 

[Stefan]

Hello Umang,

Thanks for the provided snippet.

From it I get the impression that you are not using the built-in exporting mechanisms provided by RadGridView. In such case, benefiting from the exporting events would not be possible as well. I recommend you reviewing the Export section of RadGridView's help. I also suggest you taking a look at the previously referred Exporting RowDetails online demo, as well at the other export demos of RadGridView.

I hope this helps.

Regards,
Stefan
Progress Telerik