Export to excel with data template

4 posts, 0 answers
  1. Kevin Tran
    Kevin Tran avatar
    40 posts
    Member since:
    Apr 2010

    Posted 07 Dec 2010 Link to this post

    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


  2. Yavor Georgiev
    Admin
    Yavor Georgiev avatar
    982 posts

    Posted 08 Dec 2010 Link to this post

    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
    Browse the videos here>> to help you get started with RadControls for Silverlight
  3. DevCraft banner
  4. John
    John avatar
    34 posts
    Member since:
    Oct 2010

    Posted 10 Jun 2011 Link to this post

    Is it possible to do it in the ElementExporting event?
  5. Yavor Georgiev
    Admin
    Yavor Georgiev avatar
    982 posts

    Posted 11 Jun 2011 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
Back to Top