RadGrid placing footer at under header when exported to excel

[James Bridgeford]

I have an ASPX page with multiple RadGrids. All of these RadGrids have footer columns that need to be exported with the table.  Rather then exporting each grid itself, I am changing the content type of the page.  The code to do this works fine, however, when the export happens, the Grid's Footer rows are placed under the header row instead of at the bottom.  I noticed this was an issue back in 2008, but it was said to be fixed in the 2008 Q1?  release.  I am on Q1 2011 ASP.NET AJAX controls.  How can i make it so that the footer pages show up at the bottom instead of after the header?

}

protected void btnExcelExport_Click(object sender, EventArgs e)

    {

        HttpContext.Current.Response.Buffer = true;

        HttpContext.Current.Response.Clear();

        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";

        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;

        HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=Overview"

                                                + System.DateTime.Now.ToString("MMMddyyyy") + ".xls");

        HttpContext.Current.Response.Charset = string.Empty;

  

        this.Page.EnableViewState = false;

  

        StringWriter _writer = new StringWriter();

        HtmlTextWriter _textwriter = new HtmlTextWriter(_writer);

  

        btnExcelExport.Visible = false;

  

        Label1.BackColor = Color.White; 

        Label1.ForeColor = Color.Black;

  

        Label2.BackColor = Color.White;

        Label2.ForeColor = Color.Black;

  

        Label3.BackColor = Color.White;

        Label3.ForeColor = Color.Black;

            

        Master.HeaderbackColor = Color.White;

        Master.HeaderForeColor = Color.Black;

  

        Master.LinkVisiblity = false;

        Master.NavigationVisibility = false;

  

        this.Page.RenderControl(_textwriter);

  

        HttpContext.Current.Response.Write(_writer.ToString());

        HttpContext.Current.Response.Flush();

        HttpContext.Current.Response.Close();

    }



[Vasil]

Hello James,

I tried to replicate the problem using a grid with two template columns that are having footers. When I export the grid to excel, the footers are showing at the bottom of the grid, bellow all records. Please check the attached excel file.
Here is the code that I used:
ASPX:

<asp:Button ID="ExportButton" runat="server" Text="Export" OnClick="ExportButton_Click" />
<telerik:RadGrid ID="RadGrid1" runat="server" OnNeedDataSource="RadGrid1_NeedDataSource">
  <MasterTableView ShowFooter="true">
    <Columns>
      <telerik:GridTemplateColumn HeaderText="Template column 1">
        <ItemTemplate>
          some data</ItemTemplate>
        <FooterTemplate>
          Footer in template column
        </FooterTemplate>
      </telerik:GridTemplateColumn>
      <telerik:GridTemplateColumn HeaderText="Template column 2" FooterText="Text in footer">
        <ItemTemplate>
          some data</ItemTemplate>
      </telerik:GridTemplateColumn>
    </Columns>
  </MasterTableView>
</telerik:RadGrid>

C#:

protected void RadGrid1_NeedDataSource(object sender, Telerik.Web.UI.GridNeedDataSourceEventArgs e)
{
    RadGrid1.DataSource = new int[] { 1, 2, 3, 4 };
}
protected void ExportButton_Click(object sender, EventArgs e)
{
    RadGrid1.MasterTableView.ExportToExcel();
}

Could explain in more details when the problem occurs and how exactly we could reproduce it?

Best wishes,
Vasil
the Telerik team

[James Bridgeford]

I have 3 grids on a single aspx page.  Rather than calling the built in radgrid's export to excel functionality I am just Exporting the whole page to excel by changing the content type of the page on postback to "application/vnd.ms-excel". This works perfect as everything is exported properly, except for the footers.  When I open the excel document it shows the footers are at the top instead of at the bottom as they are supposed to be. 

Here is my aspx code:

<%@ Page Language="C#" AutoEventWireup="true" EnableEventValidation="false" CodeFile="OverView.aspx.cs" MasterPageFile="~/MasterPage.master"
    Inherits="OverView" %>
<%@ Register Assembly="Telerik.Web.UI" Namespace="Telerik.Web.UI" TagPrefix="telerik" %>
<%@ MasterType VirtualPath="~/MasterPage.master" %>


<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">

    <div id="export" style="width:33%;float:right;text-align:right;position:relative;padding-top:5px">
            <asp:Button runat="server" ID="btnExcelExport" Text="Export to Excel" OnClick="btnExcelExport_Click"
                            CausesValidation="False" />
    </div>
    <center>
        <div>
            <div style="width:80%;">
                <asp:Label runat="server" ID="label1" name="label1" BackColor="#0066cc"
                    ForeColor="#FFFFFF" Width="100%" BorderWidth="1px" BorderColor="#0066cc" />
                <telerik:RadGrid ID="GradGrid1" runat="server" AutoGenerateColumns="False" CellSpacing="0"
                    GridLines="None" Width="100%" RegisterWithScriptManager="False">
                    <MasterTableView showfooter="True">
                        <CommandItemSettings ExportToPdfText="Export to PDF"></CommandItemSettings>
                        <RowIndicatorColumn FilterControlAltText="Filter RowIndicator column">
                        </RowIndicatorColumn>
                        <ExpandCollapseColumn FilterControlAltText="Filter ExpandColumn column">
                        </ExpandCollapseColumn>
                        <Columns>
                            <telerik:GridBoundColumn DataField="column1" FilterControlAltText="Filter column1 column"
                                HeaderText="column1" ReadOnly="True" UniqueName=" column1">
                                <FooterStyle HorizontalAlign="Left" VerticalAlign="Bottom" />
                                <HeaderStyle HorizontalAlign="Left" VerticalAlign="Bottom" />
                                <ItemStyle HorizontalAlign="Left" VerticalAlign="Bottom" />
                            </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn DataField="column2" DataFormatString="{0:c}" FilterControlAltText="Filter column2 column"
                                HeaderText="column2" ReadOnly="True" UniqueName="column2" Aggregate="Sum">
                                <FooterStyle HorizontalAlign="Right" VerticalAlign="Bottom" />
                                <HeaderStyle HorizontalAlign="Right" VerticalAlign="Bottom" />
                                <ItemStyle HorizontalAlign="Right" VerticalAlign="Bottom" />
                            </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn DataField="column3" DataFormatString="{0:c}" FilterControlAltText="Filter column3 column"
                                HeaderText="YTD Budget" ReadOnly="True" UniqueName="column3" Aggregate="Sum">
                                <FooterStyle HorizontalAlign="Right" VerticalAlign="Bottom" />
                                <HeaderStyle HorizontalAlign="Right" VerticalAlign="Bottom" />
                                <ItemStyle HorizontalAlign="Right" VerticalAlign="Bottom" />
                            </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn DataField="column4" DataFormatString="{0:c}" FilterControlAltText="Filter column4 column"
                                HeaderText="column4" ReadOnly="True" UniqueName="column4"  Aggregate="Sum">
                                <FooterStyle HorizontalAlign="Right" VerticalAlign="Bottom" />
                                <HeaderStyle HorizontalAlign="Right" VerticalAlign="Bottom" />
                                <ItemStyle HorizontalAlign="Right" VerticalAlign="Bottom" />
                            </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn DataField="column5" DataFormatString="{0:c}" FilterControlAltText="Filter column5 column"
                                HeaderText="column5" ReadOnly="True" UniqueName="column5" Aggregate="Sum">
                                <FooterStyle HorizontalAlign="Right" VerticalAlign="Bottom" />
                                <HeaderStyle HorizontalAlign="Right" VerticalAlign="Bottom" />
                                <ItemStyle HorizontalAlign="Right" VerticalAlign="Bottom" />
                            </telerik:GridBoundColumn>
                        </Columns>
                        <EditFormSettings>
                            <EditColumn FilterControlAltText="Filter EditCommandColumn column">
                            </EditColumn>
                        </EditFormSettings>
                    </MasterTableView>
                    <FilterMenu EnableImageSprites="False">
                    </FilterMenu>
                    <HeaderContextMenu CssClass="GridContextMenu GridContextMenu_Default">
                    </HeaderContextMenu>
                </telerik:RadGrid>

            </div>
            <div style="padding-top:50px;width:80%;">
                <asp:Label runat="server" ID="Label2" name="Label2" BackColor="#0066cc"
                    ForeColor="#FFFFFF" Width="100%" BorderWidth="1px" BorderColor="#0066cc"  />
                <telerik:RadGrid ID="RadGrid2" runat="server" AutoGenerateColumns="False"
                    CellSpacing="0" GridLines="None" ShowFooter="True" Width="100%" RegisterWithScriptManager="False">
                    <MasterTableView>
                        <CommandItemSettings ExportToPdfText="Export to PDF"></CommandItemSettings>
                        <RowIndicatorColumn FilterControlAltText="Filter RowIndicator column">
                            <HeaderStyle Width="20px"></HeaderStyle>
                        </RowIndicatorColumn>
                        <ExpandCollapseColumn FilterControlAltText="Filter ExpandColumn column">
                            <HeaderStyle Width="20px"></HeaderStyle>
                        </ExpandCollapseColumn>
                        <Columns>
                            <telerik:GridBoundColumn DataField="column1" FilterControlAltText="Filter column1 column"
                                HeaderText="column1" MaxLength="30" ReadOnly="True" UniqueName="column1">
                                <FooterStyle HorizontalAlign="Left" VerticalAlign="Bottom" />
                                <HeaderStyle HorizontalAlign="Left" VerticalAlign="Bottom" Width="20%" />
                                <ItemStyle HorizontalAlign="Left" VerticalAlign="Bottom" Width="20%" />
                            </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn DataField="column2" DataFormatString="{0:c}" FilterControlAltText="Filter column2 column"
                                HeaderText="column2" MaxLength="30" ReadOnly="True"
                                UniqueName="column2" Aggregate="Sum">
                                <FooterStyle HorizontalAlign="Right" VerticalAlign="Bottom" />
                                <HeaderStyle HorizontalAlign="Right" VerticalAlign="Bottom" Width="20%" />
                                <ItemStyle HorizontalAlign="Right" VerticalAlign="Bottom" Width="20%" />
                            </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn DataField="column3" DataFormatString="{0:c}" FilterControlAltText="Filter column3 column"
                                HeaderText="column3" ReadOnly="True" UniqueName="column3" Aggregate="Sum">
                                <FooterStyle HorizontalAlign="Right" VerticalAlign="Bottom" />
                                <HeaderStyle HorizontalAlign="Right" VerticalAlign="Bottom" Width="20%" />
                                <ItemStyle HorizontalAlign="Right" VerticalAlign="Bottom" Width="20%" />
                            </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn DataField="column4" DataFormatString="{0:c}"
                                FilterControlAltText="Filter column4 column" HeaderText="column4"
                                ReadOnly="True" UniqueName="column4" Aggregate="Sum">
                                <FooterStyle HorizontalAlign="Right" VerticalAlign="Bottom" />
                                <HeaderStyle HorizontalAlign="Right" VerticalAlign="Bottom" Width="20%" />
                                <ItemStyle HorizontalAlign="Right" VerticalAlign="Bottom" Width="20%" />
                            </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn DataField="column5" DataFormatString="{0:p}"
                                FilterControlAltText="Filter column5 column" HeaderText="column5"
                                ReadOnly="True" UniqueName="column5">
                                <FooterStyle HorizontalAlign="Right" VerticalAlign="Bottom" />
                                <HeaderStyle HorizontalAlign="Right" VerticalAlign="Bottom" Width="20%" />
                                <ItemStyle HorizontalAlign="Right" VerticalAlign="Bottom" Width="20%" />
                            </telerik:GridBoundColumn>
                        </Columns>
                        <EditFormSettings>
                            <EditColumn FilterControlAltText="Filter EditCommandColumn column">
                            </EditColumn>
                        </EditFormSettings>
                    </MasterTableView>
                    <FilterMenu EnableImageSprites="False">
                    </FilterMenu>
                    <HeaderContextMenu CssClass="GridContextMenu GridContextMenu_Default">
                    </HeaderContextMenu>
                </telerik:RadGrid>
                <br />
                <telerik:RadGrid runat="server" AutoGenerateColumns="False" ID="RadGrid3"
                    CellSpacing="0" GridLines="None" Width="100%" RegisterWithScriptManager="False"
                    ShowHeader="False">
                    <MasterTableView>
                    <CommandItemSettings ExportToPdfText="Export to PDF"></CommandItemSettings>

                    <RowIndicatorColumn FilterControlAltText="Filter RowIndicator column">
                    <HeaderStyle Width="20px"></HeaderStyle>
                    </RowIndicatorColumn>

                    <ExpandCollapseColumn FilterControlAltText="Filter ExpandColumn column">
                    <HeaderStyle Width="20px"></HeaderStyle>
                    </ExpandCollapseColumn>

                        <Columns>
                            <telerik:GridBoundColumn DataField="Column1"
                                FilterControlAltText="Filter Column1 column" ReadOnly="True"
                                UniqueName="Column1">
                                <ItemStyle HorizontalAlign="Left" VerticalAlign="Bottom"  Width="20%" />
                            </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn DataField="Column2"
                                FilterControlAltText="Filter Column2 column" ReadOnly="True"
                                UniqueName="Column2">
                                <ItemStyle HorizontalAlign="Right" VerticalAlign="Bottom" Width="20%" />
                            </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn DataField="Column3"
                                FilterControlAltText="Filter Column3 column"
                                UniqueName="Column3">
                                <ItemStyle HorizontalAlign="Right" VerticalAlign="Bottom" Width="20%"  />
                            </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn DataField="Column4"
                                FilterControlAltText="Filter Column4 column" ReadOnly="True"
                                UniqueName="Column4">
                                <ItemStyle HorizontalAlign="Right" VerticalAlign="Bottom"  Width="20%" />
                            </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn DataField="Column5"
                                FilterControlAltText="Filter Column5 column" ReadOnly="True"
                                UniqueName="Column5">
                                <ItemStyle HorizontalAlign="Right" VerticalAlign="Bottom"  Width="20%" />
                            </telerik:GridBoundColumn>
                        </Columns>

                    <EditFormSettings>
                    <EditColumn FilterControlAltText="Filter EditCommandColumn column"></EditColumn>
                    </EditFormSettings>
                    </MasterTableView>

                    <FilterMenu EnableImageSprites="False"></FilterMenu>

                    <HeaderContextMenu CssClass="GridContextMenu GridContextMenu_Default"></HeaderContextMenu>
                </telerik:RadGrid>
            </div>
            <div style="padding-top:50px;width:80%;" >
                <asp:Label runat="server" ID="Label3" name="Label3"
                    BackColor="#0066cc" ForeColor="#FFFFFF" Width="100%" BorderWidth="1px" BorderColor="#0066cc"  />
                <telerik:RadGrid ID="RadGrid4" runat="server" AutoGenerateColumns="False"
                    CellSpacing="0" GridLines="None" ShowFooter="True" Width="100%" RegisterWithScriptManager="False">
                    <MasterTableView>
                        <CommandItemSettings ExportToPdfText="Export to PDF"></CommandItemSettings>

                        <RowIndicatorColumn FilterControlAltText="Filter RowIndicator column">
                        </RowIndicatorColumn>
                        <ExpandCollapseColumn FilterControlAltText="Filter ExpandColumn column">
                        </ExpandCollapseColumn>
                        <Columns>
                            <telerik:GridBoundColumn DataField="Column1" FilterControlAltText="Filter Column1 column"
                                HeaderText="Column1" MaxLength="30" ReadOnly="True" UniqueName="Column1">
                                <FooterStyle HorizontalAlign="Left" VerticalAlign="Bottom" />
                                <HeaderStyle HorizontalAlign="left" VerticalAlign="Bottom" />
                                <ItemStyle HorizontalAlign="Left" VerticalAlign="Bottom" Width="20%" />
                            </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn DataField="Column2" DataFormatString="{0:c}" FilterControlAltText="Filter Column2 column"
                                HeaderText="Column2" MaxLength="30" ReadOnly="True" UniqueName="Column2" Aggregate="Sum">
                                <FooterStyle HorizontalAlign="Right" VerticalAlign="Bottom" />
                                <HeaderStyle HorizontalAlign="Right" VerticalAlign="Bottom" />
                                <ItemStyle HorizontalAlign="Right" VerticalAlign="Bottom" Width="20%" />
                            </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn DataField="Column3" DataFormatString="{0:c}"
                                FilterControlAltText="Filter Column3 column" HeaderText="Column3"
                                MaxLength="30" ReadOnly="True" UniqueName="Column3" Aggregate="Sum">
                                <FooterStyle HorizontalAlign="Right" VerticalAlign="Bottom" />
                                <HeaderStyle HorizontalAlign="Right" VerticalAlign="Bottom" />
                                <ItemStyle HorizontalAlign="Right" VerticalAlign="Bottom" Width="20%" />
                            </telerik:GridBoundColumn>
                            <telerik:GridBoundColumn DataField="Column4" DataFormatString="{0:c}"
                                FilterControlAltText="Filter Column4 column" HeaderText="Column4"
                                MaxLength="30" ReadOnly="True" UniqueName="Column4" Aggregate="Sum">
                                <FooterStyle HorizontalAlign="Right" VerticalAlign="Bottom" />
                                <HeaderStyle HorizontalAlign="Right" VerticalAlign="Bottom" />
                                <ItemStyle HorizontalAlign="Right" VerticalAlign="Bottom" Width="20%" />
                            </telerik:GridBoundColumn>
                        </Columns>
                        <EditFormSettings>
                            <EditColumn FilterControlAltText="Filter EditCommandColumn column">
                            </EditColumn>
                        </EditFormSettings>
                    </MasterTableView>
                    <FooterStyle HorizontalAlign="Left" VerticalAlign="Bottom" />
                    <FilterMenu EnableImageSprites="False">
                    </FilterMenu>
                    <HeaderContextMenu CssClass="GridContextMenu GridContextMenu_Default">
                    </HeaderContextMenu>
                </telerik:RadGrid>
            </div>
        </div>
    </center>
</asp:Content>

From here I click the export button which has the following code:

protected void btnExcelExport_Click(object sender, EventArgs e)
    {
        HttpContext.Current.Response.Buffer = true;
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
        HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=Overview"
                                                + System.DateTime.Now.ToString("MMMddyyyy") + ".xls");
        HttpContext.Current.Response.Charset = string.Empty;

        this.Page.EnableViewState = false;

        StringWriter _writer = new StringWriter();
        HtmlTextWriter _textwriter = new HtmlTextWriter(_writer);

        btnExcelExport.Visible = false;

        label1.BackColor = Color.White;
        label1.ForeColor = Color.Black;

        label2.BackColor = Color.White;
        label2.ForeColor = Color.Black;

        label3.BackColor = Color.White;
        label3.ForeColor = Color.Black;


        Master.HeaderbackColor = Color.White;
        Master.HeaderForeColor = Color.Black;

        Master.LinkVisiblity = false;
        Master.NavigationVisibility = false;

        this.Page.RenderControl(_textwriter);

        HttpContext.Current.Response.Write(_writer.ToString());
        HttpContext.Current.Response.Flush();
        HttpContext.Current.Response.Close();
    }

I would attach the output file, but I would have to change it enough to where it wouldn't be useful. 

I hope this clears up everything.

[Vasil]

Hi James,

I am glad that you use own method of exporting the whole page.
Basically you are facing this problem because when the grid is rendered like an html table, the <tfoot> tag is written before the <tbody>. And this is exactly like the HTML specification requires.
We are aware that MS Excel is not following the specification exactly. And if you use our grid export it will work fine because tbody and tfoot are swapped internal when exporting. However if you use your own export you will have to swap these tags yourself. You could use regular expressions to find the elements in the rendered HTML and swap them.

Kind regards,
Vasil
the Telerik team

[Robert]

Hi Vasil -

I am experiencing identical behavior to that of the OP, but I am using the built in export functionality.  I have a single radgrid on the page and no other controls besides the RadScriptManager.

I verified in the GridExporting event that the e.ExportOuput property did swap the  <tfoot> and <tbody> tags.

I am simply using the ShowExportToExcelButton property to enable the built-in functionality.

I am attempting to open the export in Excel 2007 in case that is of relevance.

Is there something extra I must do here?

Thanks

EDIT: I realize my project was still using an older version of the Telerik Suite.  I upgraded to the latest version and it has resolved the issue :)

[G]

Hi Telerik Team,
I have three grid within a parent RadGrid on a single aspx page as mentioned in the above example by James Bridgeford and also utilizing the export functionality of Telerik Rad grid but still , Footer is coming above than data and below header but it worked fine for PDF. I saw the ExportOutput data of the GridExportingItems class while generating the report where it takes tfoot tag before tbody.

Once, tried the same thing for single grid without parent Grid. It worked fine so getting an issue in the case of Parent Grid and three child grid within this.

Also, ExportSetting tag is placed in the parent Grid so it covers all three child grids to export. Apart from Footer location issue, everything worked fine.

We have been using Telerik Q2 2010. Can you please advice the solution for the same or let me know, if need any further information.

[Vasil]

Hi,

If you have hierarchy you will need to manually swap the footer with the body. I am attaching sample page that shows how to do this.

Regards,
Vasil
Telerik

 

 

[G]

Hi Vasil,
Thanks for the code but ItemDataBound method is not being called on Export button click. It does run while loading the page and grids.

[Vasil]

Hi,

Depends on your binding, but in general case you can call Rebind(). Or to set IgnorePaging="true" in the export settings of your grid. When the grid rebinds, the ItemDataBound event will fire.

Regards,
Vasil
Telerik