Export to excel with data template

[Kevin Tran]

Hi Team,

I have been working on Export functionality in a grid which have data template like below

<telerikGridView:RadGridView.Columns>
                       <telerikGridView:GridViewDataColumn Width="125" Header=""  IsReorderable="False" IsFilterable="False">
                           <telerikGridView:GridViewDataColumn.CellTemplate>
                               <DataTemplate>
                                   <HyperlinkButton x:Name="ReportName" Grid.Row="0" Grid.Column="0" Content="{Binding Country}"  HorizontalAlignment="Left" Click="ReportName_Click" VerticalAlignment="Center">


                                   </HyperlinkButton>
                               </DataTemplate>
                           </telerikGridView:GridViewDataColumn.CellTemplate>
                       </telerikGridView:GridViewDataColumn>

                       <telerikGridView:GridViewDataColumn>
                           <telerikGridView:GridViewDataColumn.Header>
                               <Grid>
                                   <Grid.RowDefinitions>
                                       <RowDefinition Height="25"/>
                                       <RowDefinition Height="35"/>
                                   </Grid.RowDefinitions>
                                   <Grid.ColumnDefinitions>
                                       <ColumnDefinition Width="67" />
                                       <ColumnDefinition Width="67"/>
                                       <ColumnDefinition Width="87"/>
                                       <ColumnDefinition Width="80"/>
                                       <ColumnDefinition Width="67"/>
                                   </Grid.ColumnDefinitions>
                                   <TextBlock Grid.Row="0" Text="Budget"  FontWeight="Bold" Grid.ColumnSpan="5"  HorizontalAlignment="Center" VerticalAlignment="Center" />

                                   <TextBlock Grid.Row="1" Grid.Column="0" Text="Units"  HorizontalAlignment="Right" VerticalAlignment="Center"/>
                                   <TextBlock Grid.Row="1" Grid.Column="1" Text="RPL" HorizontalAlignment="Right" VerticalAlignment="Center"/>
                                   <TextBlock Grid.Row="1" Grid.Column="2" Text="Revenue (000's)" HorizontalAlignment="Right" VerticalAlignment="Center" TextWrapping="Wrap"/>
                                   <TextBlock Grid.Row="1" Grid.Column="3" Text="PC ST @ Retail   " HorizontalAlignment="Right" VerticalAlignment="Center" TextWrapping="Wrap"/>
                                   <TextBlock Grid.Row="1" Grid.Column="4" Text="Attach" HorizontalAlignment="Right" VerticalAlignment="Center"/>
                               </Grid>
                           </telerikGridView:GridViewDataColumn.Header>
                           <telerikGridView:GridViewDataColumn.CellTemplate>
                               <DataTemplate>
                                   <Grid>

                                       <Grid.RowDefinitions>
                                           <RowDefinition />
                                       </Grid.RowDefinitions>
                                       <Grid.ColumnDefinitions>
                                           <ColumnDefinition Width="67" />
                                           <ColumnDefinition Width="67"/>
                                           <ColumnDefinition Width="87"/>
                                           <ColumnDefinition Width="80"/>
                                           <ColumnDefinition Width="67"/>
                                       </Grid.ColumnDefinitions>
                                       <TextBlock Grid.Row="1" Grid.Column="0" Text="{Binding Volume_Budget}" HorizontalAlignment="Right" VerticalAlignment="Center"/>
                                       <TextBlock Grid.Row="1" Grid.Column="1" Text="{Binding RPL_Budget}" HorizontalAlignment="Right" VerticalAlignment="Center"/>
                                       <TextBlock Grid.Row="1" Grid.Column="2" Text="{Binding Revenue_Budget}" HorizontalAlignment="Right" VerticalAlignment="Center"/>
                                       <TextBlock Grid.Row="1" Grid.Column="3" Text="{Binding PCST_Budget}" HorizontalAlignment="Right" VerticalAlignment="Center"/>
                                       <TextBlock Grid.Row="1" Grid.Column="4" Text="{Binding Attach_Budget}" HorizontalAlignment="Right" VerticalAlignment="Center"/>

                                   </Grid>
                               </DataTemplate>
                           </telerikGridView:GridViewDataColumn.CellTemplate>
                       </telerikGridView:GridViewDataColumn>

                       <telerikGridView:GridViewDataColumn>
                           <telerikGridView:GridViewDataColumn.Header>
                               <Grid>
                                   <Grid.RowDefinitions>
                                       <RowDefinition Height="25"/>
                                       <RowDefinition Height="45"/>
                                   </Grid.RowDefinitions>
                                   <Grid.ColumnDefinitions>
                                       <ColumnDefinition Width="67" />
                                       <ColumnDefinition Width="67"/>
                                       <ColumnDefinition Width="87"/>
                                       <ColumnDefinition Width="80"/>
                                       <ColumnDefinition Width="67"/>
                                   </Grid.ColumnDefinitions>
                                   <TextBlock Grid.Row="0" Text="Forecast"  FontWeight="Bold" Grid.ColumnSpan="5"  HorizontalAlignment="Center" VerticalAlignment="Center" />

                                   <TextBlock Grid.Row="1" Grid.Column="0" Text="Units"  HorizontalAlignment="Right" VerticalAlignment="Center"/>
                                   <TextBlock Grid.Row="1" Grid.Column="1" Text="RPL" HorizontalAlignment="Right" VerticalAlignment="Center"/>
                                   <TextBlock Grid.Row="1" Grid.Column="2" Text="Revenue (000's)" HorizontalAlignment="Right" VerticalAlignment="Center" TextWrapping="Wrap"/>
                                   <TextBlock Grid.Row="1" Grid.Column="3" Text="PC ST @ Retail    " HorizontalAlignment="Right" VerticalAlignment="Center" TextWrapping="Wrap"/>
                                   <TextBlock Grid.Row="1" Grid.Column="4" Text="Attach" HorizontalAlignment="Right" VerticalAlignment="Center"/>
                               </Grid>
                           </telerikGridView:GridViewDataColumn.Header>
                           <telerikGridView:GridViewDataColumn.CellTemplate>
                               <DataTemplate>
                                   <Grid>
                                       <Grid.RowDefinitions>
                                           <RowDefinition />
                                       </Grid.RowDefinitions>
                                       <Grid.ColumnDefinitions>
                                           <ColumnDefinition Width="67" />
                                           <ColumnDefinition Width="67"/>
                                           <ColumnDefinition Width="87"/>
                                           <ColumnDefinition Width="80"/>
                                           <ColumnDefinition Width="67"/>
                                       </Grid.ColumnDefinitions>
                                       <TextBlock Grid.Row="1" Grid.Column="0" Text="{Binding Volume_Forecast}" HorizontalAlignment="Right" VerticalAlignment="Center"/>
                                       <TextBlock Grid.Row="1" Grid.Column="1" Text="{Binding RPL_Forecast}" HorizontalAlignment="Right" VerticalAlignment="Center"/>
                                       <TextBlock Grid.Row="1" Grid.Column="2" Text="{Binding Revenue_Forecast}" HorizontalAlignment="Right" VerticalAlignment="Center"/>
                                       <TextBlock Grid.Row="1" Grid.Column="3" Text="{Binding PCST_Forecast}" HorizontalAlignment="Right" VerticalAlignment="Center"/>
                                       <TextBlock Grid.Row="1" Grid.Column="4" Text="{Binding Attach_Forecast}" HorizontalAlignment="Right" VerticalAlignment="Center"/>
                                   </Grid>
                               </DataTemplate>
                           </telerikGridView:GridViewDataColumn.CellTemplate>
                       </telerikGridView:GridViewDataColumn>

                       <telerikGridView:GridViewDataColumn>
                           <telerikGridView:GridViewDataColumn.Header>
                               <Grid>
                                   <Grid.RowDefinitions>
                                       <RowDefinition Height="25"/>
                                       <RowDefinition Height="45"/>
                                   </Grid.RowDefinitions>
                                   <Grid.ColumnDefinitions>
                                       <ColumnDefinition Width="67" />
                                       <ColumnDefinition Width="67"/>
                                       <ColumnDefinition Width="67"/>
                                       <ColumnDefinition Width="67"/>
                                       <ColumnDefinition Width="67"/>
                                       <ColumnDefinition Width="67"/>
                                       <ColumnDefinition Width="67"/>
                                   </Grid.ColumnDefinitions>
                                   <TextBlock Grid.Row="0" Text="Variance"  FontWeight="Bold" Grid.ColumnSpan="7"  HorizontalAlignment="Center" VerticalAlignment="Center" />

                                   <Button x:Name="btnVarianceContrastf1"  Visibility="Collapsed" Background="Black" Foreground="Black"  Content="-" Grid.Row="0"  Grid.ColumnSpan="7"  HorizontalAlignment="Right" VerticalAlignment="Top" Click="btns_Click"
                                           />-->
                                   <TextBlock Grid.Row="1" Grid.Column="0" Text="Units"  HorizontalAlignment="Right" VerticalAlignment="Center"/>
                                   <TextBlock Grid.Row="1" Grid.Column="1" Text="Units %"  HorizontalAlignment="Right" VerticalAlignment="Center"/>
                                   <TextBlock Grid.Row="1" Grid.Column="2" Text="RPL" HorizontalAlignment="Right" VerticalAlignment="Center"/>
                                   <TextBlock Grid.Row="1" Grid.Column="3" Text="Revenue (000's)" HorizontalAlignment="Right" VerticalAlignment="Center" TextWrapping="Wrap"/>
                                   <TextBlock Grid.Row="1" Grid.Column="4" Text="Revenue %" HorizontalAlignment="Right" VerticalAlignment="Center" TextWrapping="Wrap"/>
                                   <TextBlock Grid.Row="1" Grid.Column="5" Text="PC ST @ Retail    " HorizontalAlignment="Right" VerticalAlignment="Center" TextWrapping="Wrap"/>
                                   <TextBlock Grid.Row="1" Grid.Column="6" Text="Attach" HorizontalAlignment="Right" VerticalAlignment="Center"/>
                               </Grid>
                           </telerikGridView:GridViewDataColumn.Header>
                           <telerikGridView:GridViewDataColumn.CellTemplate>
                               <DataTemplate>
                                   <Grid>
                                       <Grid.RowDefinitions>
                                           <RowDefinition />
                                       </Grid.RowDefinitions>
                                       <Grid.ColumnDefinitions>
                                           <ColumnDefinition Width="67" />
                                           <ColumnDefinition Width="67"/>
                                           <ColumnDefinition Width="67"/>
                                           <ColumnDefinition Width="67"/>
                                           <ColumnDefinition Width="67"/>
                                           <ColumnDefinition Width="67"/>
                                           <ColumnDefinition Width="67"/>
                                       </Grid.ColumnDefinitions>
                                       <TextBlock Grid.Row="1" Grid.Column="0" Text="{Binding Volume_Variance_f1}" HorizontalAlignment="Right" VerticalAlignment="Center"/>
                                       <TextBlock Grid.Row="1" Grid.Column="1" Text="{Binding Volume_Per_Variance_f1}" HorizontalAlignment="Right" VerticalAlignment="Center" />
                                       <TextBlock Grid.Row="1" Grid.Column="2" Text="{Binding RPL_Variance_f1}" HorizontalAlignment="Right" VerticalAlignment="Center"/>
                                       <TextBlock Grid.Row="1" Grid.Column="3" Text="{Binding Revenue_Variance_f1}" HorizontalAlignment="Right" VerticalAlignment="Center" />
                                       <TextBlock Grid.Row="1" Grid.Column="4" Text="{Binding Revenue_Per_Variance_f1}" HorizontalAlignment="Right" VerticalAlignment="Center"/>
                                       <TextBlock Grid.Row="1" Grid.Column="5" Text="{Binding PCST_Variance_f1}" HorizontalAlignment="Right" VerticalAlignment="Center" />
                                       <TextBlock Grid.Row="1" Grid.Column="6" Text="{Binding Attach_Variance_f1}" HorizontalAlignment="Right" VerticalAlignment="Center" />
                                   </Grid>
                               </DataTemplate>
                           </telerikGridView:GridViewDataColumn.CellTemplate>
                       </telerikGridView:GridViewDataColumn>

                       <telerikGridView:GridViewDataColumn>
                           <telerikGridView:GridViewDataColumn.Header>
                               <Grid>
                                   <Grid.RowDefinitions>
                                       <RowDefinition Height="25"/>
                                       <RowDefinition Height="45"/>
                                   </Grid.RowDefinitions>
                                   <Grid.ColumnDefinitions>
                                       <ColumnDefinition Width="97" />
                                       <ColumnDefinition Width="97"/>
                                       <ColumnDefinition Width="97" />
                                       <ColumnDefinition Width="97"/>
                                   </Grid.ColumnDefinitions>
                                   <TextBlock Grid.Row="0" Text="Revenue Variance Analysis"  FontWeight="Bold" Grid.ColumnSpan="4"  HorizontalAlignment="Center" VerticalAlignment="Center" />

                                   <Button x:Name="btnRVAContrastf1"  Visibility="Collapsed" Background="Black" Foreground="Black"  Content="-" Grid.Row="0"  Grid.ColumnSpan="4"  HorizontalAlignment="Right" VerticalAlignment="Top" Click="btns_Click"
                                           />-->
                                   <TextBlock Grid.Row="1" Grid.Column="0" Text="PC ST @ Retail Growth $"  HorizontalAlignment="Right" VerticalAlignment="Center" TextWrapping="Wrap"/>
                                   <TextBlock Grid.Row="1" Grid.Column="1" Text="Attach $"  HorizontalAlignment="Right" VerticalAlignment="Center"/>
                                   <TextBlock Grid.Row="1" Grid.Column="2" Text="RPL $" HorizontalAlignment="Right" VerticalAlignment="Center"/>
                                   <TextBlock Grid.Row="1" Grid.Column="3" Text="Revenue Variance" HorizontalAlignment="Right" VerticalAlignment="Center" TextWrapping="Wrap"/>

                               </Grid>
                           </telerikGridView:GridViewDataColumn.Header>
                           <telerikGridView:GridViewDataColumn.CellTemplate>
                               <DataTemplate>
                                   <Grid>
                                       <Grid.RowDefinitions>
                                           <RowDefinition />
                                       </Grid.RowDefinitions>
                                       <Grid.ColumnDefinitions>
                                           <ColumnDefinition Width="97" />
                                           <ColumnDefinition Width="97"/>
                                           <ColumnDefinition Width="97" />
                                           <ColumnDefinition Width="97"/>
                                       </Grid.ColumnDefinitions>
                                       <TextBlock Grid.Row="1" Grid.Column="0" Text="{Binding PCST_Retail_RVA_f1}" HorizontalAlignment="Right" VerticalAlignment="Center"/>
                                       <TextBlock Grid.Row="1" Grid.Column="1" Text="{Binding Attach_RVA_f1}" HorizontalAlignment="Right" VerticalAlignment="Center" />
                                       <TextBlock Grid.Row="1" Grid.Column="2" Text="{Binding RPL_RVA_f1}" HorizontalAlignment="Right" VerticalAlignment="Center"/>
                                       <TextBlock Grid.Row="1" Grid.Column="3" Text="{Binding RevenueVariance_RVA_f1}" HorizontalAlignment="Right" VerticalAlignment="Center" />

                                   </Grid>
                               </DataTemplate>
                           </telerikGridView:GridViewDataColumn.CellTemplate>
                       </telerikGridView:GridViewDataColumn>
                   </telerikGridView:RadGridView.Columns>

I have a grid like this one now i want to export into an excel,

private void RadGridView1_ElementExported(object sender, GridViewElementExportedEventArgs e)
{
    try
    {
        if (e.Element == ExportElement.HeaderRow)
        {

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

             varianceMembers_string obj = e.Context as varianceMembers_string;
             if (obj != null)
             {
                 e.Writer.Write(String.Format(@"<table><tr><td style=""background-color:#CCC;"" colspan=""{0}"">",
                   ((IEnumerable<Telerik.Windows.Controls.GridViewColumn>)gvBudgetForecast.Columns).Count()));
                 e.Writer.Write(String.Format(@" {0} <br />", obj.Country));
             }
        }

      //  if(e.Element==ExportElement.



        if (e.Element == ExportElement.Row)
        {
            varianceMembers_string obj = e.Context as varianceMembers_string;
            if (obj != null)
            {
                e.Writer.Write(String.Format(@"<table><tr><td style=""background-color:#CCC;"" colspan=""{0}"">",
                    ((IEnumerable<Telerik.Windows.Controls.GridViewColumn>)gvBudgetForecast.Columns).Count()));
                e.Writer.Write(String.Format(@" {0} <br />", obj.77));

                e.Writer.Write(String.Format(@"<b>Volume:</b> {0} <t />", obj.77));
                e.Writer.Write(String.Format(@"<b>RPL:</b> {0} <t />", obj.77));
                e.Writer.Write(String.Format(@"<b>Revenue:</b> {0} <t />", obj.77));
                e.Writer.Write(String.Format(@"<b>PCST:</b> {0} <t />", obj.77));
                e.Writer.Write(String.Format(@"<b>Attach:</b> {0} <t />", obj.77));

                e.Writer.Write(String.Format(@"<b>Volume:</b> {0} <t />", obj.77));
                e.Writer.Write(String.Format(@"<b>RPL:</b> {0} <t />", obj.uu));
                e.Writer.Write(String.Format(@"<b>Revenue:</b> {0} <t />", obj.uu));
                e.Writer.Write(String.Format(@"<b>PCST:</b> {0} <t />", obj.uu));
                e.Writer.Write(String.Format(@"<b>Attach:</b> {0} <t />", obj.uu));

                e.Writer.Write(String.Format(@"<b>Volume:</b> {0} <t />", obj.hh));
                e.Writer.Write(String.Format(@"<b>Volume %:</b> {0} <t />", obj.Volume_Per_Variance));
                e.Writer.Write(String.Format(@"<b>RPL:</b> {0} <t />", obj.vvv));
                e.Writer.Write(String.Format(@"<b>Revenue:</b> {0} <t />", obj.Revenue_Variance));
                e.Writer.Write(String.Format(@"<b>Revenue %:</b> {0} <t />", obj.Revenue_Per_Variance));
                e.Writer.Write(String.Format(@"<b>PCST:</b> {0} <t />", obj.hhh));
                e.Writer.Write(String.Format(@"<b>Attach:</b> {0} <t />", obj.hhh));

                e.Writer.Write(String.Format(@"<b>ghg:</b> {0} <t />", obj.vvvv));
                e.Writer.Write(String.Format(@"<b>Attach:</b> {0} <t />", obj.hh));
                e.Writer.Write(String.Format(@"<b>RPL:</b> {0} <t />", obj.hhh));
                e.Writer.Write(String.Format(@"<b>Revenue Variance:</b> {0} <t />", obj.RevenueVariance_RVA));
                //e.Writer.Write(String.Format(@"<b>PCST:</b> {0} <t />", obj.PCST_Budget));


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

                e.Writer.Write("</td></tr>");
                e.Writer.Write("</td></tr>");
                e.Writer.Write("</td></tr></table>");
            }
        }
    }
    catch (Exception ex)
    {

        MessageBox.Show(ex.Message);
    }
}

Still in my excel my data is not well formatted,
         System.Windows.Controls.Grid System.Windows.Controls.Grid System.Windows.Controls.Grid System.Windows.Controls.Grid Header CoverFlow.UserControls.varianceMembers_string CoverFlow.UserControls.varianceMembers_string CoverFlow.UserControls.varianceMembers_string CoverFlow.UserControls.varianceMembers_string CoverFlow.UserControls.varianceMembers_string   dd      Volume: 11 RPL: $ 11 Revenue: $ 000 PCST: 00 Attach: 00 Volume: 1, RPL: $ d100,

Even though i am able to write actual numbers still i am getting object names as sown above any help?

Thanks

[Yavor Georgiev]

Hello Kevin Tran,

 If you use the ElementExported event to append data to the export stream, the original values that the RadGridView would normally export will still be written to the stream. Could you please try handling the ElementExporting event and modifying the Value property of the event handler arguments?

Kind regards,
Yavor Georgiev
the Telerik team

[John]

Is it possible to do it in the ElementExporting event?

[Yavor Georgiev]

Hello John,

 The ExportWriter is only exposed in the ElementExported event. The trick here is to handle the ElementExporting event and set the Value in the event args object to null, so that the RadGridView does not try to export the complex object. Then, in ElementExported, you can write your custom output in the ExportWriter.

All the best,
Yavor Georgiev
the Telerik team