How to export the radgrid to excel file with footer

[Akki]

Hi all,

In my application, I have rad grid. I want export it into excel. For this I am using oledb connection. The grid is exporting into the excel. Now the problem is the grid contains the footer row. The footer row is not exporting in excel. Anybody please help on this how to export the radgrid data into the excel with header and footer row. Thanks in advance.

Regards,
Akki

[Princy]

Hi Akki,

Please take a look into the following code i tried to export footer and header.

ASPX:

<asp:Button ID="Button1" runat="server" Text="Export" OnClick="Button1_Click" />
<telerik:RadGrid ID="RadGrid1" runat="server" OnNeedDataSource="RadGrid1_NeedDataSource"
    AllowSorting="true" OnExcelMLExportRowCreated="RadGrid1_ExcelMLExportRowCreated"
    EnableLinqExpressions="false">
    <ExportSettings ExportOnlyData="true" Excel-Format="Html" />
    <MasterTableView AutoGenerateColumns="false" ShowFooter="true" CommandItemDisplay="Top">
    <CommandItemSettings ShowExportToExcelButton="true" />
        <Columns>
            <telerik:GridBoundColumn DataField="Manufacturer" HeaderText="Manufacturer" />
            <telerik:GridBoundColumn Aggregate="Count" DataField="ProductName" FooterAggregateFormatString="Total products: {0}"
                HeaderText="Product" />
            <telerik:GridBoundColumn DataField="InStock" DataType="System.Int32" HeaderText="Available"
                AllowSorting="false" />
            <telerik:GridBoundColumn Aggregate="Sum" DataField="Price" FooterAggregateFormatString="Total: {0}"
                HeaderText="Price" DataType="System.Double" />
            <telerik:GridCalculatedColumn DataFields="Price, InStock" Aggregate="Sum" Expression="{0}*{1}"
                UniqueName="myCalculatedColumn" HeaderText="Total" DataType="System.Double" />
        </Columns>
    </MasterTableView>
</telerik:RadGrid>

C#:

protected void RadGrid1_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
{
    DataTable table = new DataTable();
    table.Columns.Add("ProductName");
    table.Columns.Add("Price", typeof(double));
    table.Columns.Add("Manufacturer");
    table.Columns.Add("InStock", typeof(int));
    table.Rows.Add("Futo maki", 12.44, "Osaka Nihonbashi", 4);
    table.Rows.Add("Musaka", 10.61, "Sofiiski gozbi", 1);
    table.Rows.Add("Cheesezer", 9.50, "Burger mafia", 9);
    RadGrid1.DataSource = table;
}
int currentItem = 0;
protected void RadGrid1_ExcelMLExportRowCreated(object source, GridExportExcelMLRowCreatedArgs e)
{
    if (e.Worksheet.Table.Rows.Count == RadGrid1.Items.Count + 1)
    {
        GridFooterItem footerItem = RadGrid1.MasterTableView.GetItems(GridItemType.Footer)[0] as GridFooterItem;
        RowElement row = new RowElement(); //create new row for the footer aggregates
        for (int i = 2; i < footerItem.Cells.Count; i++)
        {
            TableCell fcell = footerItem.Cells[i];
            CellElement cell = new CellElement();
            cell.Data.DataItem = fcell.Text == " " ? "" : fcell.Text;
            row.Cells.Add(cell);
        }
        e.Worksheet.Table.Rows.Add(row);
        //correct the autofilter
        e.Worksheet.AutoFilter.Range = String.Format("R1C1:R1C{0}", e.Worksheet.Table.Columns.Count + 1);
    }
    //create new cell for this row (part of the calculated column)
    CellElement calculatedCell = new CellElement();
    if (e.RowType == GridExportExcelMLRowType.DataRow) //data cell
    {
        calculatedCell.Data.DataItem = double.Parse(RadGrid1.MasterTableView.Items[currentItem]["myCalculatedColumn"].Text);
        currentItem++;
    }
    if (e.RowType == GridExportExcelMLRowType.HeaderRow)
        calculatedCell.Data.DataItem = RadGrid1.MasterTableView.GetColumn("myCalculatedColumn").HeaderText; //header cell
    e.Row.Cells.Add(calculatedCell);
}

protected void Button1_Click(object sender, EventArgs e)
{
    RadGrid1.MasterTableView.ExportToExcel();
}

Thanks,
Princy.

[Jayesh Goyani]

Hello Akki,

<telerik:RadGrid ID="RadGrid1" runat="server" AutoGenerateColumns="false" OnNeedDataSource="RadGrid1_NeedDataSource"
          ShowFooter="true"
           ShowHeader="true">
           <ExportSettings ExportOnlyData="true">
           </ExportSettings>
           <MasterTableView CommandItemDisplay="Top">
               <CommandItemSettings ShowExportToExcelButton="true" />
               <Columns>
                   <telerik:GridBoundColumn DataField="ID" HeaderText="ID" UniqueName="ID" Aggregate="Count">
                   </telerik:GridBoundColumn>
               </Columns>
           </MasterTableView>
       </telerik:RadGrid>

protected void RadGrid1_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
   {
       DateTime dt = DateTime.Now;

       dynamic data = new[] {
               new { ID = 1, Name ="Name_1", customdate=dt,NO = 11.00,CustomerId="ALFKI",Stock=20,IsSysAdmin = true},
               new { ID = 2, Name = "Name2", customdate=dt,NO = 11.00,CustomerId="ALFKI2",Stock=30,IsSysAdmin = false},
               new { ID = 3, Name = "Name3", customdate=dt,NO = 11.00,CustomerId="ALFKI1",Stock=20,IsSysAdmin = true},
               new { ID = 4, Name = "Name4", customdate=dt,NO = 11.44,CustomerId="ALFKI",Stock=40,IsSysAdmin = true},
               new { ID = 55555, Name = "Name5", customdate=dt,NO = 10.4,CustomerId="ALFKI",Stock=20,IsSysAdmin = true}
           };

       RadGrid1.DataSource = data;
   }

Thanks,
Jayesh Goyani

[Shubham]

HI Princy 

when I am setting 
<ExportSettings ExportOnlyData="true" Excel-Format="Html" />

then our event
OnExcelMLExportRowCreated="RadGrid1_ExcelMLExportRowCreated"

Not get fired  .  It get fired only when I sat  Excel-Format="ExcelML"  

 but in this case  rows  of child grid moves to next cell in excel .

 Thanks 

[Princy]

Hi Shubham,

With Excel-Format="Html" you can use the OnHTMLExporting or OnGridExporting event of the RadGrid. Please have a look into this documentation on HTML-Based Export and Excel Format (HTML-Based).

Thanks,
Princy