Radgrid export to excel with grouping

5 posts, 0 answers
  1. Ruby
    Ruby avatar
    23 posts
    Member since:
    Mar 2012

    Posted 15 May 2014 Link to this post

    Hi, I am able to export radgrid to excel and pdf. But for excel exporting my grouped column is not exported. Could you please let me know how that works.
    I am attaching my radgrid code. Also, i just want HA rollout as group header column and not HAtype. how to do that?
    <telerik:RadGrid ID="rgCurrentPromo" runat="server"   Skin="Windows7" OnBiffExporting="rgCurrentPromo_BiffExporting" PageSize="30"  AllowPaging="true" AllowSorting="True">
               <%-- <telerik:RadGrid ID="rgCurrentPromo" runat="server" GridLines="None" AllowSorting="true" Skin="Windows7" Width="745px"  OnItemCreated="rgCurrentPromo_ItemCreated" AutoGenerateColumns="False" AllowPaging="true"> --%>
               <ExportSettings IgnorePaging="true" OpenInNewWindow="true">
             <Pdf PageHeight="210mm" PageWidth="330mm" DefaultFontFamily="Arial Unicode MS" PageTopMargin="30mm" 
                  BorderStyle="Medium" BorderColor="#666666"
              </Pdf>
                          
          </ExportSettings>
              <PagerStyle Mode="NextPrevAndNumeric"></PagerStyle>
                  <MasterTableView AutoGenerateColumns="False"  CommandItemDisplay="Top" UseAllDataFields="true">
                      <CommandItemSettings ShowExportToExcelButton="true"   ShowExportToPdfButton="true" />
                      <CommandItemTemplate>
                           <asp:Button ID="ExporttoExcel" runat="server" Text="Export To Excel" CommandName="ExporttoExcel"   OnClick="b1_Click"/>
                         <asp:Button ID="ExportoPDF" runat="server" CommandName="ExporttoPDF" Text="Export To PDF" OnClick="b2_Click" />
                         <%--<asp:ImageButton ID="DownloadPDF" runat="server" OnClick="DownloadPDF_Click" ImageUrl="~/Grid/Examples/Functionality/Exporting/Export-Word-CSV/images/file-extension-pdf-icon.png"
          CssClass="pdfButton"></asp:ImageButton>--%>
                      </CommandItemTemplate>
                         <GroupByExpressions>
                  <telerik:GridGroupByExpression>
                      <SelectFields>
                          <telerik:GridGroupByField FieldName="HA" FieldAlias="HAType" />
                      </SelectFields>
                      <GroupByFields>
                          <telerik:GridGroupByField FieldName="HA" />
                      </GroupByFields>
                  </telerik:GridGroupByExpression>
              </GroupByExpressions>
              <GroupHeaderItemStyle Font-Bold="true" Font-Size="120%" />
                  <Columns>
                        <telerik:GridBoundColumn DataField="CUSTOMER_NAME"  FilterControlAltText="Filter CUSTOMER_NAME column" HeaderText="Customer" ReadOnly="True" SortExpression="CUSTOMER_NAME" UniqueName="CUSTOMER_NAME">
                          <ColumnValidationSettings>
                              <ModelErrorMessage Text="" />
                          </ColumnValidationSettings>
                      </telerik:GridBoundColumn>
                      <telerik:GridBoundColumn DataField="PROMO_NO" DataType="System.Int32" FilterControlAltText="Filter PROMO_NO column" HeaderText="PN" ReadOnly="True" SortExpression="PROMO_NO" UniqueName="PROMO_NO">
                          <ColumnValidationSettings>
                              <ModelErrorMessage Text="" />
                          </ColumnValidationSettings>
                      </telerik:GridBoundColumn>
                      <telerik:GridBoundColumn DataField="PROMO_NM" FilterControlAltText="Filter PROMO_NM column" HeaderText="Promo Name" SortExpression="PROMO_NM" UniqueName="PROMO_NM">
                          <ColumnValidationSettings>
                              <ModelErrorMessage Text="" />
                          </ColumnValidationSettings>
                      </telerik:GridBoundColumn>
                      
                      <telerik:GridBoundColumn DataField="PROMO_DATE" DataType="System.DateTime" FilterControlAltText="Filter PROMO_DATE column" HeaderText="Start Dt" SortExpression="PROMO_DATE"  UniqueName="PROMO_DATE"  DataFormatString="{0:dd/MM/yyyy}">
                          <ColumnValidationSettings>
                              <ModelErrorMessage Text="" />
                          </ColumnValidationSettings>
                      </telerik:GridBoundColumn
                     <telerik:GridBoundColumn DataField="HA" FilterControlAltText="Filter HA column" HeaderText="HA" SortExpression="HA" UniqueName="HA">
                          <ColumnValidationSettings>
                              <ModelErrorMessage Text="" />
                          </ColumnValidationSettings>
                      </telerik:GridBoundColumn>
                      <telerik:GridBoundColumn DataField="PROMO_DESC" FilterControlAltText="Filter PROMO_DESC column" HeaderText="Description" SortExpression="PROMO_DESC" UniqueName="PROMO_DESC">
                          <ColumnValidationSettings>
                              <ModelErrorMessage Text="" />
                          </ColumnValidationSettings>
                      </telerik:GridBoundColumn>
                      <telerik:GridBoundColumn DataField="PROMO_END_DT" DataType="System.DateTime" FilterControlAltText="Filter PROMO_END_DT column" HeaderText="End Dt" SortExpression="PROMO_END_DT" UniqueName="PROMO_END_DT" DataFormatString="{0:dd/MM/yyyy}">
                          <ColumnValidationSettings>
                              <ModelErrorMessage Text="" />
                          </ColumnValidationSettings>
                      </telerik:GridBoundColumn>
                    </Columns>
              </MasterTableView>
              <ClientSettings AllowDragToGroup="true" />
      <GroupingSettings ShowUnGroupButton="true" />
          </telerik:RadGrid>
    Protected Sub rgCurrentPromo_NeedDataSource(ByVal sender As Object, ByVal e As Telerik.Web.UI.GridNeedDataSourceEventArgs) Handles rgCurrentPromo.NeedDataSource
         Using dbContext As New AthenaModel.EntitiesModel()
             Dim firstDate As Date = New DateTime(Now.Year, Now.Month, 1)
             'Select
             'Case when AUTH_CODE is  null Then 'Non-HA Rollout' Else 'HA Rollout' End  AS HA,CUSTOMER.CUSTOMER_NAME, PLUTO.TBLPROMOTIONCODE.PROMO_NO, PLUTO.TBLPROMOTIONCODE.PROMO_NM, PLUTO.TBLPROMOTIONCODE.PROMO_DATE, PLUTO.TBLPROMOTIONCODE.PROMO_END_DT, PLUTO.TBLPROMOTIONCODE.PROMO_DESC
             'FROM PLUTO.TBLPROMOTIONCODE INNER JOIN CUSTOMER ON PLUTO.TBLPROMOTIONCODE.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID
             'WHERE PLUTO.TBLPROMOTIONCODE.PROMO_END_DT>=TRUNC (SYSDATE, 'month') Or PLUTO.TBLPROMOTIONCODE.PROMO_END_DT Is Null;
             Dim query = (From p In dbContext.TBLPROMOTIONCODEs
                     Join c In dbContext.CUSTOMERs On p.CUSTOMER_ID Equals c.CUSTOMER_ID
                     Where p.PROMO_END_DT >= firstDate Or p.PROMO_END_DT Is Nothing
                     Select c.CUSTOMER_NAME, p.PROMO_NO, p.PROMO_NM, p.PROMO_END_DT, p.PROMO_DATE, p.PROMO_DESC, HA = If(p.AUTH_CODE Is Nothing, "Non-HA Rollout", "HA Rollout")).ToList()
             ' Select c.CUSTOMER_NAME, p.PROMO_NO, p.PROMO_NM, promo_enddt = String.Format("{0:MM/dd/yyyy}", p.PROMO_END_DT), promo_dt = String.Format("{0:MM/dd/yyyy}", p.PROMO_DATE), p.PROMO_DESC, HA = IIf(p.AUTH_CODE Is Nothing, "Non-HA Rollout", "HA Rollout")).ToList()
             rgCurrentPromo.DataSource = query
             'rgCurrentPromo.DataBind()
         End Using
     End Sub
     Private isPdfExport As Boolean = False
     Protected Sub rgCurrentPromo_ItemCreated(sender As Object, e As GridItemEventArgs)
         If isPdfExport Then
             FormatGridItem(e.Item)
         End If
         'If TypeOf e.Item Is GridHeaderItem Then
         '    Dim row As RowElement = New RowElement()
         '    Dim cell As CellElement = New CellElement()
         '    cell.MergeAcross = e.Row.Cells.Count - 1
         '    cell.Data.DataItem = "VETS-100A Detail Report from " & AsofReportFromDate & " To " & AsofReportToDate & "  Reporting Time : " & Date.Now.ToString("MM-dd-yyyy H:mm:ss")
         '    cell.StyleValue = "headStyle"
         '    row.Cells.Add(cell)
         '    e.Worksheet.Table.Rows.Insert(0, row)
         '    e.Worksheet.AutoFilter.Range = e.Worksheet.AutoFilter.Range.Replace("R1", "R2")
         'End If
     End Sub
     Protected Sub FormatGridItem(item As GridItem)
         item.Style("color") = "#eeeeee"
         If TypeOf item Is GridDataItem Then
             item.Style("vertical-align") = "middle"
             item.Style("text-align") = "center"
         End If
         Select Case item.ItemType
             'Mimic RadGrid appearance for the exported PDF file 
             Case GridItemType.Item
                 item.Style("background-color") = "#4F4F4F"
                 Exit Select
             Case GridItemType.AlternatingItem
                 item.Style("background-color") = "#494949"
                 Exit Select
             Case GridItemType.Header
                 item.Style("background-color") = "#2B2B2B"
                 Exit Select
             Case GridItemType.CommandItem
                 item.Style("background-color") = "#000000"
                 Exit Select
         End Select
         If TypeOf item Is GridCommandItem Then
             'needed to span the image over the CommandItem cells 
             item.PrepareItemStyle()
         End If
     End Sub
     'Protected Sub rgCurrentPromo_ItemCommand(ByVal sender As Object, ByVal e As GridCommandEventArgs) Handles rgCurrentPromo.ItemCommand
     '    If e.CommandName = RadGrid.ExportToPdfCommandName Then
     '        isPdfExport = True
     '        rgCurrentPromo.MasterTableView.GetColumn("CUSTOMER_NAME").HeaderStyle.Width = Unit.Pixel(120)
     '        rgCurrentPromo.MasterTableView.GetColumn("PROMO_NO").HeaderStyle.Width = Unit.Pixel(100)
     '        rgCurrentPromo.MasterTableView.GetColumn("PROMO_NM").HeaderStyle.Width = Unit.Pixel(100)
     '        rgCurrentPromo.MasterTableView.GetColumn("PROMO_END_DT").HeaderStyle.Width = Unit.Pixel(100)
     '        rgCurrentPromo.MasterTableView.GetColumn("PROMO_DATE").HeaderStyle.Width = Unit.Pixel(100)
     '        rgCurrentPromo.MasterTableView.GetColumn("PROMO_DESC").HeaderStyle.Width = Unit.Pixel(100)
     '        rgCurrentPromo.MasterTableView.GetColumn("HA").HeaderStyle.Width = Unit.Pixel(120)
     '    End If
     'End Sub
     Protected Sub DownloadPDF_Click(sender As Object, e As EventArgs)
         isPdfExport = True
         rgCurrentPromo.MasterTableView.ExportToPdf()
     End Sub
     Protected Sub rgCurrentPromo_ItemCommand(sender As Object, e As Telerik.Web.UI.GridCommandEventArgs)
         If e.CommandName = RadGrid.ExportToExcelCommandName Then
             rgCurrentPromo.ExportSettings.HideStructureColumns = False
             rgCurrentPromo.Rebind()
         End If
     End Sub
     Private isExporting As Boolean = False
     Protected Sub b1_Click(ByVal sender As Object, ByVal e As EventArgs)
         isExporting = True
         rgCurrentPromo.ExportSettings.Excel.Format = GridExcelExportFormat.Biff
         rgCurrentPromo.ExportSettings.IgnorePaging = True
         rgCurrentPromo.ExportSettings.ExportOnlyData = False
         rgCurrentPromo.MasterTableView.AllowPaging = False
         ' rgCurrentPromo.ExportSettings.
         rgCurrentPromo.ExportSettings.FileName = "Current_Promotions"
         rgCurrentPromo.ExportSettings.OpenInNewWindow = True
         rgCurrentPromo.MasterTableView.CommandItemDisplay = GridCommandItemDisplay.None
         rgCurrentPromo.MasterTableView.HierarchyDefaultExpanded = True
         rgCurrentPromo.ExportSettings.HideStructureColumns = "false"
         ' rgCurrentPromo.MasterTableView.GroupHeaderTemplate.
      
         'Response.Clear()
         'Response.Buffer = True
         'Response.ContentType = "application/vnd.ms-excel"
         'Response.Charset = ""
         'Me.EnableViewState = False
         'Dim oStringWriter As New System.IO.StringWriter()
         'Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter)
         'rgCurrentPromo.RenderControl(oHtmlTextWriter)
         'Response.Write(oStringWriter.ToString())
         'Response.End()
         rgCurrentPromo.Rebind()
         rgCurrentPromo.MasterTableView.ExportToExcel()
     End Sub
     Protected Sub b2_Click(ByVal sender As Object, ByVal e As EventArgs)
         isExporting = True
         rgCurrentPromo.ExportSettings.ExportOnlyData = False
         rgCurrentPromo.ExportSettings.FileName = "Current_Promotions"
         rgCurrentPromo.ExportSettings.OpenInNewWindow = True
         rgCurrentPromo.MasterTableView.CommandItemDisplay = GridCommandItemDisplay.None
         rgCurrentPromo.MasterTableView.HierarchyDefaultExpanded = True
         rgCurrentPromo.ExportSettings.Pdf.PageHeader.LeftCell.Text = headerLeftCell
         rgCurrentPromo.ExportSettings.Pdf.PageHeader.LeftCell.TextAlign = GridPdfPageHeaderFooterCell.CellTextAlign.Center
         rgCurrentPromo.MasterTableView.GroupHeaderItemStyle.BackColor = Drawing.Color.Red
         rgCurrentPromo.MasterTableView.ExportToPdf()
     End Sub
     Protected Sub rgCurrentPromo_ItemDataBound(ByVal sender As Object, ByVal e As GridItemEventArgs) Handles rgCurrentPromo.ItemDataBound
         If isExporting Then
             If e.Item.ItemType = GridItemType.Header Then
                 e.Item.BackColor = System.Drawing.Color.White
                 e.Item.ForeColor = System.Drawing.Color.Blue
                 ' e.Item.Font.Bold = True
                 e.Item.Height = Unit.Point(20)
                 e.Item.Font.Size = 11
                 e.Item.HorizontalAlign = HorizontalAlign.Left
             End If
             If e.Item.ItemType = GridItemType.Item Then
                 e.Item.HorizontalAlign = HorizontalAlign.Left
             End If
             If e.Item.ItemType = GridItemType.AlternatingItem Then
                 e.Item.HorizontalAlign = HorizontalAlign.Left
             End If
             If e.Item.ItemType = GridItemType.GroupHeader Then
                 e.Item.ForeColor = System.Drawing.Color.Red
             End If
         End If
     End Sub
     Protected Sub rgCurrentPromo_BiffExporting(ByVal sender As Object, ByVal e As GridBiffExportingEventArgs)
  2. Princy
    Princy avatar
    17421 posts
    Member since:
    Mar 2007

    Posted 16 May 2014 in reply to Ruby Link to this post

    Hi Ruby,

    Radgrid Biff Export doesn't support Grouping. Please take a look at this article about Excel BIFF (Binary) Export. You may try HTML excel format.
    Your next requirement is not clear to me, if you want to change the Text of the GroupHeader, you may look at this article on Customizing GridGroupHeaderItem.

    Thanks,
    Princy
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Prasanth
    Prasanth avatar
    5 posts
    Member since:
    Apr 2015

    Posted 09 Apr 2015 in reply to Princy Link to this post

    Hi,

    I am using a Radgrid which have footer and groupfooter. But the excel export of the Radgrid is not working properly when it is grouped.
    The code which I used to export to Excel is below.
    private void lnkExport_Click(object sender, System.EventArgs e)
      {
     
                uwgAllocationCount.ExportSettings.FileName = "CountSummary " ;         
                uwgAllocationCount.ExportSettings.IgnorePaging = true;
                uwgAllocationCount.ExportSettings.OpenInNewWindow = true;
                uwgAllocationCount.MasterTableView.UseAllDataFields = true;
                uwgAllocationCount.ExportSettings.ExportOnlyData = true;
                uwgAllocationCount.ExportSettings.Excel.Format = GridExcelExportFormat.Biff;
                uwgAllocationCount.ExportSettings.HideStructureColumns = false;
                uwgAllocationCount.MasterTableView.HierarchyDefaultExpanded = true;                            
                uwgAllocationCount.ExportSettings.SuppressColumnDataFormatStrings = false;
                uwgAllocationCount.MasterTableView.ExportToExcel();
      }
    I have even tried GridExcelExportFormat.HTML, insted of GridExcelExportFormat.Biff, but it is showing only the grouped headers. It is not exporting the grouped data.

     

  5. Prasanth
    Prasanth avatar
    5 posts
    Member since:
    Apr 2015

    Posted 09 Apr 2015 in reply to Princy Link to this post

    I am using a Radgrid which have footer and groupfooter. But the excel export of the Radgrid is not working properly when it is grouped.
    The code which I used to export to Excel is below.
    private void lnkExport_Click(object sender, System.EventArgs e)
      {

                uwgAllocationCount.ExportSettings.FileName = "CountSummary " ;         
                uwgAllocationCount.ExportSettings.IgnorePaging = true;
                uwgAllocationCount.ExportSettings.OpenInNewWindow = true;
                uwgAllocationCount.MasterTableView.UseAllDataFields = true;
                uwgAllocationCount.ExportSettings.ExportOnlyData = true;
                uwgAllocationCount.ExportSettings.Excel.Format = GridExcelExportFormat.Biff;
                uwgAllocationCount.ExportSettings.HideStructureColumns = false;
                uwgAllocationCount.MasterTableView.HierarchyDefaultExpanded = true;                            
                uwgAllocationCount.ExportSettings.SuppressColumnDataFormatStrings = false;
                uwgAllocationCount.MasterTableView.ExportToExcel();
      }
    I have even tried GridExcelExportFormat.HTML, insted of GridExcelExportFormat.Biff, but it is showing only the grouped headers. It is not exporting the grouped data. Please help

    Thanks

    Prasanth

     

  6. Daniel
    Admin
    Daniel avatar
    4946 posts

    Posted 10 Apr 2015 Link to this post

    Hello Prasanth,

    Please don't post the same question multiple times. Let's continue our discussion here:
    Excel export of RadGrid not working when grouped by a column

    Regards,
    Daniel
    Telerik
     

    See What's Next in App Development. Register for TelerikNEXT.

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