Radgridview export to excel with colored call bacgrounds

[Peter]

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

[Martin Ivanov]

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

[Peter]

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]

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]

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]

Thanks for sharing your code, Peter. I hope everything works as expected now.