This is a migrated thread and some comments may be shown as answers.

[Solved] Problem exporting a Grid to Excel

3 Answers 154 Views

Grid

This is a migrated thread and some comments may be shown as answers.

Jaime asked on 24 Sep 2009, 02:29 PM

Hello...

I 'm using RadControls for ASP.NET Q3 2007 and I have problems when exporting a grid to Excel. A strange gray square embracing some cells of the generated Excel is shown.

This is the Grid definition code:

    
                                <rad:RadGrid ID="grdUsuariosSinConexion" runat="server" AllowFilteringByColumn="True" AllowPaging="True" 
        
                                    AllowSorting="True" AutoGenerateColumns="False" 
        
                                    GridLines="None" Skin="WebBlue" Width="95%" EnableAJAX="True" EnableAJAXLoadingTemplate="True" OnNeedDataSource="grdUsuariosSinConexion_NeedDataSource" OnItemDataBound="grdSinUso_ItemDataBound" OnItemCommand="grd_ItemCommand" OnItemCreated="grd_ItemCreated">  
        
                                    <ExportSettings OpenInNewWindow="True" FileName="ExportUsuariosSinConexion" IgnorePaging="True">  
        
                                    </ExportSettings> 
        
                                    <MasterTableView NoMasterRecordsText="No hay registros que mostrar." CommandItemDisplay="Top">  
        
                                        <RowIndicatorColumn Visible="False">  
        
                                            <HeaderStyle Width="20px" /> 
        
                                        </RowIndicatorColumn> 
        
                                        <ExpandCollapseColumn Resizable="False" Visible="False">  
        
                                            <HeaderStyle Width="20px" /> 
        
                                        </ExpandCollapseColumn> 
        
                                        <Columns> 
        
                                            <rad:GridBoundColumn DataField="Nombre" HeaderText="Nombre" UniqueName="column">  
        
                                                <ItemStyle Width="75%" /> 
        
                                            </rad:GridBoundColumn> 
        
                                            <rad:GridBoundColumn DataField="UltimaFecha" HeaderText="Ultima Fecha" UniqueName="colUltimaFecha" DataFormatString="{0:dd/MM/yyyy}" DataType="System.DateTime">  
        
                                                <ItemStyle Width="25%" /> 
        
                                            </rad:GridBoundColumn> 
        
                                        </Columns> 
        
                                        <SortExpressions> 
        
                                            <rad:GridSortExpression FieldName="Nombre" /> 
        
                                        </SortExpressions> 
        
                                        <CommandItemTemplate> 
        
                                            <asp:ImageButton ID="btnExportUsoPorUsuario" runat="server" ImageUrl="~/Private/images/small_xls.gif" CommandName="ExportToExcel" ImageAlign="AbsMiddle" /> 
        
                                        </CommandItemTemplate>                              
        
                                    </MasterTableView> 
        
                                    <HeaderStyle ForeColor="Gainsboro" HorizontalAlign="Center" /> 
        
                                    <PagerStyle HorizontalAlign="Left" Mode="NextPrevAndNumeric" NextPagesToolTip="P&#225;ginas Siguientes" 
        
                                        NextPageToolTip="P&#225;gina Siguiente" PagerTextFormat="Cambiar p&#225;gina: {4} &amp;nbsp;|&amp;nbsp; Mostrando p&#225;gina {0} de {1}, usuarios {2} al {3} de {5}." 
        
                                        PrevPagesToolTip="P&#225;ginas Anteriores" PrevPageToolTip="P&#225;gina Anterior" /> 
        
                                    <ItemStyle HorizontalAlign="Left" /> 
        
                                    <FilterItemStyle HorizontalAlign="Left" /> 
        
                                    <AlternatingItemStyle HorizontalAlign="Left" /> 
        
                                    <ClientSettings> 
        
                                        <ClientEvents OnRequestStart="mngRequestStarted" /> 
        
                                    </ClientSettings> 
        
                                    <CommandItemStyle HorizontalAlign="Right" /> 
        
                                </rad:RadGrid>

And since the grid is controlled by RadAjaxManager, I placed the following code in HEAD section:

    
                <script type="text/javascript">  
        
                function mngRequestStarted(ajaxManager, eventArgs)    
        
                {    
        
                    eventArgs.set_enableAjax(false);    
        
                }    
        
                </script>

I have followed a document in this site that shows how to export to Excel in these scenarios, so I created a field named _exportExcel that is true when I press a export button placed in CommandItem of the grid. Therefore, these are the events I needed to implement:

    
                protected void grd_ItemCreated(object sender, GridItemEventArgs e)  
        
                {  
        
                    e.Item.Visible = !_exportExcel || e.Item.ItemType == GridItemType.Header || e.Item.ItemType == GridItemType.AlternatingItem || e.Item.ItemType == GridItemType.Item;  
        
                }  
        
                protected void grd_ItemCommand(object source, GridCommandEventArgs e)  
        
                {  
        
                    _exportExcel = e.CommandName == Telerik.WebControls.RadGrid.ExportToExcelCommandName;  
        
                }

ItemCreated event makes invisible filter row and command item row and other rows that I don't want the grid to export.

Any help will be greatly appreciated,

Thanks
Jaime

3 Answers, 1 is accepted

answered on 25 Sep 2009, 04:26 AM

Hello Jaime,

Inorder to remove images from the excel output on exporting, you can try setting the ExportOnlyData property to true as shown below:
c#:

    
            protected void grd_ItemCommand(object source, GridCommandEventArgs e)   
        
                {   
        
                    if( e.CommandName == Telerik.WebControls.RadGrid.ExportToExcelCommandName) 
        
                    { 
        
                          RadGrid1.ExportSettings.OpenInNewWindow = true; 
        
                          RadGrid1.ExportSettings.ExportOnlyData = true; 
        
                          RadGrid1.ExportSettings.IgnorePaging = true; 
        
                          RadGrid1.MasterTableView.ExportToExcel(); 
        
                    } 
        
               }

Hope this helps...
Princy.

answered on 25 Sep 2009, 01:50 PM

Hello Princy... I tried that but strangely RadGrid skin is modified. For example, I am using WebBlue. In that skin, all row items are left aligned and the color alternate for each row. When I use ExportOnlyData = true, row items change alignment to center and all rows have the same color. I verified Alternate Row Item Style and still has left alignment so I don't know why it is actually center aligned. I tried by changing Item style alignment but it seems that it is not taken into account.

Why is that?
Thanks

Jaime