Export to Excel - maintaining cell color

[Richard]

In regard to your WPF grid, in some cases we set the background and/or the foreground color of certain cells, and would like to maintain these colors if and when the grid is exported to excel.

When the GridView_ElementExportingToDocument event is fired, a GridViewElementExportingToDocumentEventArgs object is passed.  How can I use this object to determine the foreground and background color of the cell?

[Richard]

Also, we are setting a CellSelectionStyle object in preparation for the export.  How should the color settings be passed to that object?

[Pawel]

private void RadGridView_ElementExportingToDocument(object sender, GridViewElementExportingToDocumentEventArgs e)
        {
            var documentVisualExportParameters = e.VisualParameters as GridViewDocumentVisualExportParameters;

            if (documentVisualExportParameters != null)
            {
                if (e.Element == ExportElement.Cell)
                {

                    documentVisualExportParameters.Style =new CellSelectionStyle()
                    {
                        Fill = new PatternFill(PatternType.Solid, Colors.Aquamarine, Colors.Aquamarine)
                    };
                }
            }
        }

 

But be aware that it will not work if you are using function ExportToXlsx and you set setting  useDeafaultstyles = true.

[Richard]

This looks like it will work for setting the excel color, but how do I detect the color of the incoming grid cell?

[Pawel]

hey,
it is not possible to get cell color when this event is fired since style is always null. What you could do.

1.You have a DataContext 'of a row' so you could reuse your converter logic which you are using to determine the color of cell in the ui.

2. You have to write your own logic to export a table, you have to get a RadGridView object take all columns and create your own WorkBook and then you can try to take the color of a cell, but when a was trying to take for ex text of an cell which was modified by converter it returned me text which was not modified by converter so it could be impossible or really hard to achieve

I recommend you first option :).

 

[Yoan]

Hi Richard,

Indeed, you can use the first approach that Pawel provided. Here is a sample code which demonstrates it:

private void ClubsGrid_ElementExportingToDocument(object sender, GridViewElementExportingToDocumentEventArgs e)
        {
            if (e.Element == ExportElement.Row)
            {
                var row = (sender as GridViewDataControl).ItemContainerGenerator.ContainerFromItem(e.DataContext) as GridViewRow;
                if (row != null)
                {
                    var cells = row.Cells;
                    foreach (var cell in cells)
                    {
                        var backColor = cell.Background;
                        var foreColor = cell.Foreground;
                    }
                }
            }
        }

@Pawel:

Thank you for providing the approach and contributing to the Telerik community.

Regards,
Yoan
Progress Telerik

[Richard]

Thanks for the answers.  Just one more question...

Row objects are presumably exported first before the cell objects, so I can save the row that applies to subsequent cells with the following logic:

            if (e.Element == ExportElement.Row)
            {
                try
                {
                    _row = (sender as GridViewDataControl).ItemContainerGenerator.ContainerFromItem(e.DataContext) as GridViewRow;
                }
                catch
                {
                    _row = null;
                }
            }

Then when a cell reference is passed the following logic is run: 

          if (e.Element == ExportElement.Cell)
            {
                if (_row != null)
                {
                    foreach (GridViewCellBase cell in _row.Cells)
                    {
                        if (cell.Column.UniqueName == column.UniqueName)
                        {
                            if (cell.<get background color here> != <normal cell background color>)
                            {
                                ((GridViewDocumentVisualExportParameters)e.VisualParameters).Style = new Telerik.Windows.Controls.GridView.CellSelectionStyle()
                                {
                                    Fill = new PatternFill(PatternType.Solid, cell.<get background color here>, cell.<get background color here>)
                                };
                            }
                            break;
                        }
                    }
                }

How do I determine the background color for a GridViewCellBase object?

[Yoan]

Hi Richard,

You can just use the Background property of the cell object like so:

if (e.Element == ExportElement.Cell)
            {
                if (_row != null)
                {
                    foreach (GridViewCellBase cell in _row.Cells)
                    {
                        //if (cell.Column.UniqueName == column.UniqueName)
                        {
                            //if (cell.< get background color here > != < normal cell background color >)
                            if (cell.Background != Brushes.Black)
                            {
                                //  ((GridViewDocumentVisualExportParameters)e.VisualParameters).Style = new Telerik.Windows.Controls.GridView.CellSelectionStyle()
                                //  {
                                //      Fill = new PatternFill(PatternType.Solid, cell.< get background color here >, cell.< get background color here >)
                                //  };
                            }
                            break;
                        }
                    }
                }
            }

Regards,
Yoan
Progress Telerik

[Richard]

I got this working for the most part, but the strange thing is that the foreground color for a grid cell sometimes comes through correctly to the excel spreadsheet, and sometimes not (for cells that have red foreground text, even in the same row).  Here is the code - do you see any issues?

        private GridViewRow _row = null;

        private void GridView_ElementExportingToDocument(object sender, GridViewElementExportingToDocumentEventArgs e)

        {
            Telerik.Windows.Controls.GridViewColumn column = null;
            Color backColor = Colors.White;
            Color foreColor = Colors.Black;

            if (e is GridViewCellExportingEventArgs)
            {
                column = ((GridViewCellExportingEventArgs)e).Column as Telerik.Windows.Controls.GridViewColumn;
            }

            // Row
            if (e.Element == ExportElement.Row)
            {
                _row = (sender as GridViewDataControl)?.ItemContainerGenerator.ContainerFromItem(e.DataContext) as GridViewRow;
            }

            if (e.Element == ExportElement.Cell)

            {
                // Set the color of the exported cell if it has a non-default color
                if (_row != null)
                {
                    foreach (GridViewCellBase cell in _row.Cells)
                    {
                        if (cell.Column.UniqueName == column.UniqueName)
                        {
                            SolidColorBrush brush = (SolidColorBrush)cell.Foreground;
                            if (brush != null)
                            {
                                foreColor = brush.Color;
                            }

                            brush = (SolidColorBrush)cell.Background;
                            if (brush != null)
                            {
                                backColor = brush.Color;
                            }

                            break;
                        }
                    }
                }

                SetCellStyle(column, foreColor, backColor, e);
            }
        }

        private void SetCellStyle(Telerik.Windows.Controls.GridViewColumn column,
            Color foreColor,
            Color backColor,
            GridViewElementExportingToDocumentEventArgs e)
        {
            ((GridViewDocumentVisualExportParameters)e.VisualParameters).Style = new CellSelectionStyle()
            {
                ForeColor = new ThemableColor(foreColor),
                Fill = new PatternFill(PatternType.Solid, backColor, backColor)
            };
        }

 

[Richard]

We found out what the problem is.  The red color is apparently only applied when the cells containing the color are visible.  These grids are very wide, and when the grid is horizontally scroll away from the red cells, then they will be exported as default.

Therefore, we need a way to fully "materialize" the grid so that the color is correctly exported even though the cells are not visible on the screen.

[Richard]

More information on this.  The grid is from WPF, and is virtualized for performance reasons, which is why the style is not appearing until the cell is visible.  Here is an example of the xaml from one of the columns:

                    <telerik:GridViewDataColumn x:Name="Flood100YearLEAmt"
                                                UniqueName="Flood100YearLEAmt"
                                                Header="Flood 100 Year LE"
                                                ColumnGroupName="ColumnGroupName"
                                                TextAlignment="Right"
                                                Style="{StaticResource FloodStyle}"
                                                DataMemberBinding="{Binding Flood100YrLEAmt, StringFormat={}{0:N0}}"
                                                cc:ColumnSetting.CanBeHidden="False">
                        <telerik:GridViewDataColumn.ToolTipTemplate>
                            <DataTemplate>
                                <TextBlock Text="{Binding Flood100YrLEAmt, StringFormat={}{0:N0}}" />
                            </DataTemplate>
                        </telerik:GridViewDataColumn.ToolTipTemplate>
                        <telerik:GridViewDataColumn.CellStyle>
                            <Style TargetType="telerik:GridViewCell">
                                <Style.Triggers>
                                    <DataTrigger Value="False">
                                        <DataTrigger.Binding>
                                            <MultiBinding Converter="{StaticResource IsNullConverter}">
                                                <Binding Path="Flood100YrLEPDAmtTooltip.Scenario" />
                                                <Binding Path="Flood100YrLEPDAmtTooltip.Account" />
                                                <Binding Path="Flood100YrLETEAmtTooltip.Scenario" />
                                                <Binding Path="Flood100YrLETEAmtTooltip.Account" />
                                            </MultiBinding>
                                        </DataTrigger.Binding>
                                        <Setter Property="FontWeight"
                                                Value="Bold" />
                                        <Setter Property="FontStyle"
                                                Value="Italic" />
                                    </DataTrigger>
                                    <DataTrigger Value="True">
                                        <DataTrigger.Binding>
                                            <MultiBinding Converter="{StaticResource LEDerivedConverter}">
                                                <Binding Path="Flood100YrLEPDAmtTooltip" />
                                                <Binding Path="Flood100YrLEPDEngAmt" />
                                            </MultiBinding>
                                        </DataTrigger.Binding>
                                        <Setter Property="Foreground"
                                                Value="Red" />
                                    </DataTrigger>
                                    <DataTrigger Value="True">
                                        <DataTrigger.Binding>
                                            <MultiBinding Converter="{StaticResource LEDerivedConverter}">
                                                <Binding Path="Flood100YrLETEAmtTooltip" />
                                                <Binding Path="Flood100YrLETEEngAmt" />
                                            </MultiBinding>
                                        </DataTrigger.Binding>
                                        <Setter Property="Foreground"
                                                Value="Red" />
                                    </DataTrigger>
                                </Style.Triggers>
                            </Style>
                        </telerik:GridViewDataColumn.CellStyle>
                    </telerik:GridViewDataColumn>

Is there a way of determining from the grid cell contents if any of the above triggers would be fired?

[Yoan]

Hi Richard,

You are absolutely right - when RadGridView is virtualized, it loads the rows/cells that are only in the viewport. This is why it is not recommended to work with the visual elements directly(the cells in this case), not only because they are not loaded, but also they are reused on scrolling which means that you can get incorrect values. Sorry for not mentioning that at the beginning.

So, you have two options: 

   1. Disable the virtualization of the control by setting the /ColumnVirtualization property to False. This is not recommended approach because the performance may degrade.

   2. Working with the data items instead of the visual elements. I've noticed that you are using DataTriggers for manipulating the background. This is not recommended if you have cell outside of the viewport. The background of the cell should mess up if you start scrolling. The correct approach for manipulating the background of the cell is to use CellStyleSelector. It uses the data item behind the cell and in this way getting incorrect results on scrolling should be avoided. If you decide to use this approach you can still use the ElementExportingToDocument event. However, you should not use the row and its cells collection, but the Item behind the row - GridViewRow.Item property. In this way, you can check the properties and calculate what would be the color. As I can see you are depending on multiple properties for manipulating the background, so introducing a new calculated property may be of help in this scenario.

Please give this a try let me know how it goes.

Regards,
Yoan
Progress Telerik

[Richard]

Thanks for the info, but I am not sure how to use the GridViewRow.Item property to find the current column/cell, and implement or re-fire the above Data Trigger logic.

[Yoan]

Hi Richard,

I have attached a sample project which demonstrates the approach. You can note that the logic with the DataTrigger is implemented with a StyleSelector. In the exporting event, I have shown how to check if the background is red for the first column.

I hope this helps.

Regards,
Yoan
Progress Telerik