Export to Excel

7 posts, 2 answers
  1. Lokesh
    Lokesh avatar
    89 posts
    Member since:
    Jul 2012

    Posted 05 Sep 2013 Link to this post

    Hi Team,
    I have an editable radgrid and I want to export it to excel.

    This is my aspx:
     

    <telerik:RadAjaxPanel ID="rjxPanel" runat="server" LoadingPanelID="RadAjaxLoadingPanel1">

            <asp:UpdatePanel ID="updtPanel" runat="server">

    <ContentTemplate>

    <telerik:RadButton ID="btnExport" runat="server" OnClick="btnExport_Click" Text="Export" />


    <telerik:RadGrid ID="rgCostAllocation" runat="server"  OnNeedDataSource="rgCostAllocation_NeedDataSource"

                                    OnItemDataBound="rgCostAllocation_ItemDataBound" OnDeleteCommand="rgCostAllocation_DeleteCommand"

                                    ShowFooter="true">

                                    <ExportSettings Excel-Format="ExcelML" ExportOnlyData="true" IgnorePaging="true" HideStructureColumns="true"></ExportSettings>

                                    <MasterTableView CommandItemDisplay="Top">

                                        <CommandItemSettings ExportToExcelText="Export" ShowExportToExcelButton="true" />

                                        <Columns>

    <telerik:GridBoundColumn DataField="CostAllocationId" HeaderText="CostAllocationId"

                                                UniqueName="CostAllocationId" Display="false">

                                            </telerik:GridBoundColumn>

                                            <telerik:GridTemplateColumn DataField="AllocationLevel" HeaderText="Level" UniqueName="AllocationLevel"

                                                AutoPostBackOnFilter="true" CurrentFilterFunction="EqualTo" Display="false">

                                                <ItemTemplate>

                                                    <telerik:RadNumericTextBox ID="txtLevel" runat="server" Text='<%# DataBinder.Eval(Container,"DataItem.AllocationLevel") %>'

                                                        NumberFormat-AllowRounding="false" NumberFormat-DecimalDigits="0" />

                                                </ItemTemplate>

                                                <FooterTemplate>

                                                    <telerik:RadNumericTextBox ID="txtFLevel" runat="server" NumberFormat-AllowRounding="false"

                                                        NumberFormat-DecimalDigits="0" />

                                                </FooterTemplate>

                                            </telerik:GridTemplateColumn>

                                            <telerik:GridTemplateColumn DataField="SenderDescription" HeaderText="Sender" UniqueName="SenderCostCenterId" AllowFiltering="true"

                                                AutoPostBackOnFilter="true" CurrentFilterFunction="Contains">

                                                <ItemTemplate>

                                                     <telerik:RadComboBox ID="rcbSender" runat="server"

                                                                  MarkFirstMatch="True" AutoPostBack="true"

                                                                  Width="170px" OnItemsRequested ="rcbSender_ItemsRequested"

                                                                  height="200px" EnableLoadOnDemand="True" 

                                                                  ItemRequestTimeout ="500"   DropDownWidth="280px"

                                                                  DataTextField="CostCentreDescription"

                                                                  DataValueField="CostCentreID"  ExpandEffect="pixelate">

                                                          <headertemplate>

                                                            <table width="92%">

                                                                <tr>                                                                

                                                                    <td style="font-weight:bold; width:50%;">&nbsp; Cost Centre Description</td>

                                                                    <td style="font-weight:bold; width:50%;">Cost Centre Code</td>

                                                                   

                                                              </tr>

                                                            </table>                                

                                                          </headertemplate>

                                                          <itemtemplate>

                                                            <table width="100%">

                                                                <tr>

                                                                    <td style="width: 50%; font-size: 8pt; font-family: Arial;" align="left">

                                                                        &nbsp;

                                                                        <%# DataBinder.Eval(Container, "Attributes['DataValue']") %>

                                                                    </td>

                                                                    <td align="left" style="font-size: 8pt; width: 50%; font-family: Arial;">

                                                                        <%# DataBinder.Eval(Container, "Attributes['DataText']")%>

                                                                    </td>

                                                                </tr>

                                                            </table>

                                                        </itemtemplate>

                                                </telerik:RadComboBox>

                                                </ItemTemplate>

                                                <FooterTemplate>

                                                  

                                                 <telerik:RadComboBox ID="rcbFSender" runat="server"

                                                                  MarkFirstMatch="True" AutoPostBack="true"

                                                                  Width="170px" OnItemsRequested ="rcbFSender_ItemsRequested"

                                                                  height="200px" EnableLoadOnDemand="True"  ItemRequestTimeout ="500"

                                                                  DataTextField="CostCentreDescription"

                                                                  DataValueField="CostCentreID"

                                                                  DropDownWidth="280px"

                                                                  ExpandEffect="pixelate" >

                                                          <headertemplate>

                                                            <table width="92%">

                                                                <tr>                                                               

                                                                    <td style="font-weight:bold; width:50%;">&nbsp; Cost Centre Description</td>

                                                                    <td style="font-weight:bold; width:50%;">Cost Centre Code</td>

                                                                   

                                                              </tr>

                                                            </table>                               

                                                          </headertemplate>

                                                          <itemtemplate>

                                                            <table width="100%">

                                                                 <tr>

                                                                    <td style="width: 50%; font-size: 8pt; font-family: Arial;" align="left">

                                                                        &nbsp;

                                                                        <%# DataBinder.Eval(Container, "Attributes['DataValue']") %>

                                                                    </td>

                                                                    <td align="left" style="font-size: 8pt; width: 50%; font-family: Arial;">

                                                                        <%# DataBinder.Eval(Container, "Attributes['DataText']")%>

                                                                    </td>

                                                                </tr>

                                                                </tr>

                                                            </table>

                                                        </itemtemplate>

                                                </telerik:RadComboBox>

                                               

                                                </FooterTemplate>

                                            </telerik:GridTemplateColumn>

    <telerik:GridTemplateColumn DataField="AllocationPercentage" HeaderText="Percentage"

                                                UniqueName="AllocationPercentage" AutoPostBackOnFilter="true" CurrentFilterFunction="Contains">

                                                <ItemTemplate>

                                                    <telerik:RadNumericTextBox Width="60px" ID="txtPercentage" runat="server" Text='<%# DataBinder.Eval(Container,"DataItem.AllocationPercentage") %>'

                                                        Type="Percent" />

                                                </ItemTemplate>

                                                <FooterTemplate>

                                                    <asp:Label ID="lblSumPercentage" runat="server" />

                                                 

                                                    <telerik:RadNumericTextBox Width="60px" ID="txtFPercentage" runat="server" Type="Percent" />

                                                    <telerik:RadButton ID="btnAddNew" runat="server" OnClick="btnAddNew_Click" Text="Add New" />

                                                </FooterTemplate>

                                                <ItemStyle Width="15%" />

                                            </telerik:GridTemplateColumn>

                                            <telerik:GridButtonColumn CommandName="Delete" ButtonType="ImageButton" UniqueName="DeleteCommandColumn"

                                                HeaderText="Delete" ConfirmText="Are you sure you want to delete?" Text="Delete">

                                                <ItemStyle HorizontalAlign="Center" Width="3%" />

                                                <HeaderStyle HorizontalAlign="Center" Width="3%" />

                                            </telerik:GridButtonColumn>

                                        </Columns>

                                    </MasterTableView>

                                </telerik:RadGrid>

     

         

    </ContentTemplate>

            </asp:UpdatePanel>

    </telerik:RadAjaxPanel>


     This is my .cs

    protected void btnExport_Click(object sender, EventArgs e)

    {

                try

                {

                    rgCostAllocation.MasterTableView.ExportToExcel();

                }

                catch (Exception ex)

                {

                    XITingExceptionProcessor.ProcessException(this, ex);

                }

    }


    I have a master page with RadAjaxManager.
    When I try to export to excel, it doesn't work because of RadAjaxPanel.
    So when I try to export by commenting the RadAjaxPanel, it exports to excel but with no data, a totally blank sheet.

    Could you please help me with this?

    Thanks,
    Lok..
  2. Answer
    Princy
    Princy avatar
    17421 posts
    Member since:
    Mar 2007

    Posted 05 Sep 2013 Link to this post

    Hi Lokesh,

    Please try adding the following to your code.Please have a look at this documentation on Export from Ajaxified Grid.

    ASPX:
    <telerik:RadAjaxPanel ID="RadAjaxPanel1" runat="server" ClientEvents-OnRequestStart="requestStart">
        . . . . . . . .
    </telerik:RadAjaxPanel>

    JS:
    <script type="text/javascript">
        function requestStart(sender, args)
        {
            if (args.get_eventTarget().indexOf("btnExport") >= 0)
                args.set_enableAjax(false);
        }
    </script>

    Thanks,
    Princy
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Lokesh
    Lokesh avatar
    89 posts
    Member since:
    Jul 2012

    Posted 05 Sep 2013 Link to this post

    Hi Princy,
    Thanks for your quick response.
    It solved my first problem of RadAjaxPanel. Now I can see the excel popup.
    But my second problem is still as it is. It shows me the blank excel sheet.

    Could you please help me with this.?

    Thanks,
    Lok..
  5. Princy
    Princy avatar
    17421 posts
    Member since:
    Mar 2007

    Posted 06 Sep 2013 Link to this post

    Hi Lokesh,

    I have tried your code and it works fine at my end. I see that you have set display="false" for some column, make sure that you haven't set it to false for  all the columns.Then try setting UseAllDataFields="true",property of the master table. If you have set ExportOnlyData property to True,the value in the ItemTemplate is passed to a control and this value along with the control won't be exported. But in your code I can see you have used DataBinder inside ItemTemplate. So the data should show in excel. Can you please make sure the data is showing in RadGrid ? Below is the code snippet that I tried.

    ASPX:
    <telerik:RadAjaxPanel ID="rjxPanel" runat="server" LoadingPanelID="RadAjaxLoadingPanel1"
        ClientEvents-OnRequestStart="requestStart">
        <asp:UpdatePanel ID="updtPanel" runat="server">
            <ContentTemplate>
                <telerik:RadButton ID="btnExport" runat="server" OnClick="btnExport_Click" Text="Export" />
                <telerik:RadGrid ID="RadGrid1" runat="server" DataSourceID="SqlDataSource1" AutoGenerateColumns="false"
                    AllowPaging="true">
                    <ExportSettings Excel-Format="ExcelML" ExportOnlyData="true" IgnorePaging="true"
                        HideStructureColumns="true">
                    </ExportSettings>
                    <MasterTableView CommandItemDisplay="Top">
                        <CommandItemSettings ExportToExcelText="Export" ShowExportToExcelButton="true" />
                        <Columns>
                            <telerik:GridTemplateColumn DataField="OrderID" HeaderText="OrderID" UniqueName="OrderID"
                                AllowFiltering="true" AutoPostBackOnFilter="true" CurrentFilterFunction="EqualTo">
                                <ItemTemplate>
                                    <telerik:RadComboBox ID="rcbSender" runat="server" MarkFirstMatch="True" AutoPostBack="true"
                                        Width="170px" Height="200px" EnableLoadOnDemand="True"
                                        ItemRequestTimeout="500" DropDownWidth="280px" DataTextField="OrderID"
                                        DataValueField="OrderID" ExpandEffect="pixelate">
                                        <HeaderTemplate>
                                            <table width="92%">
                                                <tr>
                                                    <td style="font-weight: bold; width: 50%;">
                                                          OrderID
                                                    </td>                                                      
                                                </tr>
                                            </table>
                                        </HeaderTemplate>
                                        <ItemTemplate>
                                            <table width="100%">
                                                <tr>
                                                    <td style="width: 50%; font-size: 8pt; font-family: Arial;" align="left">
                                                          
                                                        <%# DataBinder.Eval(Container, "Attributes['OrderID']")%>
                                                    </td>                                                     
                                                </tr>
                                            </table>
                                        </ItemTemplate>
                                    </telerik:RadComboBox>
                                </ItemTemplate>
                                <FooterTemplate>
                                    <telerik:RadComboBox ID="rcbFSender" runat="server" MarkFirstMatch="True" AutoPostBack="true"
                                        Width="170px" Height="200px" EnableLoadOnDemand="True"
                                        ItemRequestTimeout="500" DataTextField="OrderID" DataValueField="OrderID"
                                        DropDownWidth="280px" ExpandEffect="pixelate">
                                        <HeaderTemplate>
                                            <table width="92%">
                                                <tr>
                                                    <td style="font-weight: bold; width: 50%;">
                                                          OrderID
                                                    </td>                                                      
                                                </tr>
                                            </table>
                                        </HeaderTemplate>
                                        <ItemTemplate>
                                            <table width="100%">
                                                <tr>
                                                    <td style="width: 50%; font-size: 8pt; font-family: Arial;" align="left">
                                                          
                                                        <%# DataBinder.Eval(Container, "Attributes['OrderID']")%>
                                                    </td>                                                      
                                                </tr>
                                                </tr>
                                            </table>
                                        </ItemTemplate>
                                    </telerik:RadComboBox>
                                </FooterTemplate>
                            </telerik:GridTemplateColumn>
                        </Columns>
                    </MasterTableView>
                </telerik:RadGrid>
            </ContentTemplate>
        </asp:UpdatePanel>
    </telerik:RadAjaxPanel>

    Thanks,
    Princy
  6. Lokesh
    Lokesh avatar
    89 posts
    Member since:
    Jul 2012

    Posted 10 Sep 2013 Link to this post

    Hi Princy,
    Thanks for your reply but your code is just not working at my end. I don't know why.
    So I tried for a work-around. I added 4 BoundColumns with the fields I want to export. Set them "Display = false"  and on a button click, while exporting, I set them "Display=true"
    Now I can see the grid data getting exported.
    But here, the excel structure is totally distorted. Please find the attached image and help me with this.

    Thanks,
    Lok..
  7. Answer
    Kostadin
    Admin
    Kostadin avatar
    1708 posts

    Posted 12 Sep 2013 Link to this post

    Hello Lokesh,

    I noticed than in the code which you provided you have a UpdatePanel nested in RadAjaxPanel. Note that this is unsupported scenario and even if it works is better to avoid nesting ajaxified elements(RadAjaxManagerm RadAjaxPanel, UpdatePanel). As far as I can see you are using a HTML Export format in your project. Could you please verify that you did not set any width of the column? You could check out the following help article which describes how to change the column width. It would be best if you could provide us with a small runnable sample in order to pinpoint the reason for that behavior.

    Regards,
    Kostadin
    Telerik
    If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to the blog feed now.
  8. Lokesh
    Lokesh avatar
    89 posts
    Member since:
    Jul 2012

    Posted 13 Sep 2013 Link to this post

    Hi Kostadin,
    Thanks for your reply.
    I tried your solution. I removed the asp:UpdatePanel.  help article is also very useful.
    I set the column width in the code behind and it worked. 
    Actually adding/removing asp:UpdatePanel made no change in the exported excel format. But as you said  it is unsupported scenario, I removed it.

    Thanks,
    Lok..



Back to Top
UI for ASP.NET Ajax is Ready for VS 2017