Radgridview export to excel with colored call bacgrounds

1 Answer 61 Views
GridView
Peter
Top achievements
Rank 1
Peter asked on 15 Feb 2023, 12:48 PM

Dear Team,

I have a WPF app (vb.net) where i use radgridview. Virtualization is on because we have more hundreds of rows, and in the CELLLOADED event i color the cell backgrounds based on cell content.

The result is something like this: (attached: mapps.jpg)

The customer wants me to create excel output, including the colors. I browsed a lot of forums and i found multiple approaches but the result is always the same. The background is colored, but all column inherits the color of the first row. (attached: excel.jpg)

 

    Sub ExcelExport_Colored(rgv As RadGridView)

        Dim workbook As Workbook = rgv.ExportToWorkbook(New GridViewDocumentExportOptions() With {
                        .ExportDefaultStyles = True,
                        .AutoFitColumnsWidth = True,
                        .ShowColumnHeaders = True})
        Dim dialog As SaveFileDialog = New SaveFileDialog()
        dialog.DefaultExt = "xlsx"
        dialog.Filter = String.Format("{1} files (*.{0})|*.{0}|All files (*.*)|*.*", "xlsx", "Excel")
        dialog.FilterIndex = 1

        If dialog.ShowDialog() = True Then
            Dim provider = New XlsxFormatProvider()

            Using output = dialog.OpenFile()
                provider.Export(workbook, output)
            End Using
        End If

        Dim excelPath2 As String = Registry.GetValue("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\excel.exe",
"Path", "Key does not exist")
        Microsoft.VisualBasic.Interaction.Shell(excelPath2 & "EXCEL.EXE /e/min """ & dialog.FileName & """", AppWinStyle.NormalFocus)

    End Sub

 

I tried other export options as well, like radgridview.export, radgridview.exporttoxlsx.. all give the same result or worse.
Can you give any advice how to achieve it? I don't insist to the icons, if i could export the data with bgcolors i would be happy already.

Thank you !!

Peter

1 Answer, 1 is accepted

Sort by
0
Martin Ivanov
Telerik team
answered on 20 Feb 2023, 10:43 AM

Hello Peter,

The gridview exports the styles only of the first row and cell that it finds during the export. This is a performance optimization that is mentioned here: 
https://docs.telerik.com/devtools/wpf/controls/radgridview/export/excel/export-xlsx#export-default-styles

To achieve the desired effect, you will need to adjust the styles manually during the export. You can see how to do this in the following help article:https://docs.telerik.com/devtools/wpf/controls/radgridview/export/how-to/style-exported-documents

To see how to export an image column check the following example: 
https://github.com/telerik/xaml-sdk/tree/master/GridView/ExportImageColumn

I hope these resources are useful.

Regards,
Martin Ivanov
Progress Telerik

Love the Telerik and Kendo UI products and believe more people should try them? Invite a fellow developer to become a Progress customer and each of you can get a $50 Amazon gift voucher.

Peter
Top achievements
Rank 1
commented on 20 Feb 2023, 04:55 PM | edited

Dear Martin,

Yes, this looks promising, thank you.

I found how to get the current cell value, i can recolor based on it, and i found the datacontext(x) where i can check the values of the whole row. Only one important thing is missing:

I cant figure out how can  i get the column header name, or column index. 

 

 

Private Sub ElementExportingToDocument(ByVal sender As Object, ByVal e As GridViewElementExportingToDocumentEventArgs)

If e.Element = ExportElement.cell andalso e.value = "PICK" Then TryCast(e.VisualParameters, GridViewDocumentVisualExportParameters).Style = New CellSelectionStyle() With { .Fill = New PatternFill(PatternType.Solid, Colors.White, Colors.White), .ForeColor = New ThemableColor(Colors.Black) } End If

End Sub 

 

Can you please help me to find it?

 

 

Thanks

Peter

 

Martin Ivanov
Telerik team
commented on 21 Feb 2023, 11:46 AM

When the e.Element is Cell or HeaderCell, the type of the event arguments is different. In that case, the event arguments are of type GridViewCellExportingEventArgs which derives from GridViewElementExportingToDocumentEventArgs. The GridViewCellExportingEventArgs holds a reference to the cell's column, so you can use this in order to get the information you need. 

Private Sub Gridview_ElementExportingToDocument(ByVal sender As Object, ByVal e As GridViewElementExportingToDocumentEventArgs)
    If e.Element = ExportElement.Cell Then
        Dim cellArgs = CType(e, GridViewCellExportingEventArgs)
        Dim column = cellArgs.Column
    End If
End Sub

Peter
Top achievements
Rank 1
commented on 21 Feb 2023, 08:02 PM

Thanks

This was exactly what i needed. To be honest it was well hidden, so without your help i would never had found it.

My final code - if someone else needs it to:

 


    Private Sub ElementExportingToDocument(ByVal sender As Object, e As GridViewElementExportingToDocumentEventArgs) Handles rgv_Coverage.ElementExportingToDocument


        If e.Element = ExportElement.HeaderRow Then
            TryCast(e.VisualParameters, GridViewDocumentVisualExportParameters).Style = New CellSelectionStyle() With {
            .FontSize = 14,
            .IsBold = True,
            .Fill = New PatternFill(PatternType.HorizontalStripe, Colors.DarkSlateBlue, Colors.Blue),
            .ForeColor = New ThemableColor(Colors.White)
        }
        End If

        If e.Element = ExportElement.Cell Then
            Dim cellArgs = CType(e, GridViewCellExportingEventArgs)
            Dim column As Telerik.Windows.Controls.GridViewDataColumn = cellArgs.Column

            If column.UniqueName = "PRIO1_0221" Then
                If e.DataContext("CoverageState").ToString = "1G" Then
                    TryCast(e.VisualParameters, GridViewDocumentVisualExportParameters).Style = ExcelCellStyle(Colors.Pink)
                End If
            End If

            If column.UniqueName = "WH_STOCK" Then
                If Val(e.Value.ToString) = 0 Then
                    TryCast(e.VisualParameters, GridViewDocumentVisualExportParameters).Style = ExcelCellStyle(Colors.Wheat)
                End If
            End If

        End If

    End Sub

    Private Shared Function ExcelCellStyle(c As Color) As CellSelectionStyle
        Return New CellSelectionStyle() With {.FontSize = 14, .IsBold = False, .Fill = New PatternFill(PatternType.Solid, c, c), .ForeColor = New ThemableColor(Colors.Black)}
    End Function

Bye

Peter

Martin Ivanov
Telerik team
commented on 22 Feb 2023, 12:26 PM

Thanks for sharing your code, Peter. I hope everything works as expected now.
Tags
GridView
Asked by
Peter
Top achievements
Rank 1
Answers by
Martin Ivanov
Telerik team
Share this question
or