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

Radgrid export to excel with grouping

4 Answers 914 Views

Grid

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

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

0

0

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

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

Tags

Grid

Asked by

Answers by