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

How to export the radgrid to excel file with footer

4 Answers 294 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Akki
Top achievements
Rank 1
Akki asked on 28 May 2012, 07:28 AM
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

4 Answers, 1 is accepted

Sort by
0
Princy
Top achievements
Rank 2
answered on 28 May 2012, 10:00 AM
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.
0
Jayesh Goyani
Top achievements
Rank 2
answered on 28 May 2012, 10:51 AM
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
0
Shubham
Top achievements
Rank 1
answered on 02 Jan 2014, 03:57 PM
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 


0
Princy
Top achievements
Rank 2
answered on 03 Jan 2014, 10:38 AM
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

Tags
Grid
Asked by
Akki
Top achievements
Rank 1
Answers by
Princy
Top achievements
Rank 2
Jayesh Goyani
Top achievements
Rank 2
Shubham
Top achievements
Rank 1
Share this question
or