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

Radgrid export to excel with grouping

4 Answers 237 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Ruby
Top achievements
Rank 1
Ruby asked on 15 May 2014, 08:29 PM
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)

4 Answers, 1 is accepted

Sort by
0
Princy
Top achievements
Rank 1
answered on 16 May 2014, 08:35 AM
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
0
Prasanth
Top achievements
Rank 1
answered on 09 Apr 2015, 07:36 AM

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.

 

0
Prasanth
Top achievements
Rank 1
answered on 09 Apr 2015, 07:37 AM

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

 

0
Daniel
Telerik team
answered on 10 Apr 2015, 04:15 PM
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.

 
Tags
Grid
Asked by
Ruby
Top achievements
Rank 1
Answers by
Princy
Top achievements
Rank 1
Prasanth
Top achievements
Rank 1
Daniel
Telerik team
Share this question
or