BackColor for spreadsheet cells/columns/rows?

[Ian]

I can't seem to get the back color of a cell, row or column to set programatically.

The ribbon lets me choose a back (fill) color, but nothing happens. 

I can find the 'setForeColor' method, but there isn't a 'setBackColor'. Can you confirm that this is right ?

I CAN set back colors using the Styles = good/bad/neutral from the UI.

So is the only way to set the back color to create my own CellStyle, and use that?

Is there any documentation on how to do this? Using the Visual Studio debugger makes it a very slow process of software archeology!

[Dess | Tech Support Engineer, Principal]

Hello, Ian, 

In order to customize the loaded worksheet, it is necessary to apply the desired style to the specific CellSelection according to the API that the RadSpreadProcessing library offers: https://docs.telerik.com/devtools/document-processing/libraries/radspreadprocessing/working-with-cells/get-set-clear-properties

Here is a basic code snippet how to change the fore color for the cells in the specified selected range: 

Workbook workbook = this.radSpreadsheet1.Workbook;
Worksheet worksheet = workbook.Worksheets.First();
CellSelection selection = worksheet.Cells[0, 0, 5, 5];
selection.SetForeColor(ThemableColor.FromArgb(115, 125, 125, 125));

I hope this information helps. If you need any further assistance please don't hesitate to contact me. 

Regards,
Dess | Tech Support Engineer, Sr.
Progress Telerik

[Ian]

Looks like there is LOTS of good stuff here  - thanks.

(would be great to have more references to, and examples of, this kind of function. I suspect most people will be using this control (instead of a real spreadsheet) because they want to manipulate it in code, so the more examples, the easier to do that. I'm finding it a bit hard to do this manipulation without any examples)

[Dess | Tech Support Engineer, Principal]

Hello, Ian, 

RadSpreadsheet utilizes the RadSpreadProcessing library. It is a part of the Document Processing and the relevant documentation is available on a separate site. If you need to manipulate a document programatically it is suitable to use the RadSpreadProcessing library. If you face any further difficulties with it, you can submit a support ticket with the Document Processing product and thus our support staff will gladly assist you.

Should you have further questions please let me know.

Regards,
Dess | Tech Support Engineer, Sr.
Progress Telerik

[Randy]

To set the background color of one or more cells, create a CellSelection and then use the SetFill method with a solid PatternFill. See Fill Property for more details.

Workbook workbook = new Workbook(); 
Worksheet worksheet = workbook.Worksheets.Add(); 

// Set background color to Aqua
PatternFill solidPatternFill = new PatternFill(PatternType.Solid, Colors.Aqua, Colors.Transparent); 
worksheet.Cells[1, 0, 5, 5].SetFill(solidPatternFill); 

Using a fill to set the background color wasn't obvious to me either as it seems like there should be a SetBackgroundColor method so I created the extension methods below.

using Telerik.Documents.Media;
using Telerik.Windows.Documents.Spreadsheet.Model;
using Telerik.Windows.Documents.Spreadsheet.PropertySystem;

namespace Extensions
{
    public static class CellSelectionExtensions
    {
        public static CellSelection SetBackgroundColor(this CellSelection cellSelection, Color color)
        {
            var fill = new PatternFill(PatternType.Solid, color, Colors.Transparent);
            cellSelection.SetFill(fill);
            return cellSelection;
        }
    }

    public static class CellStyleExtensions
    {
        public static CellStyle SetBackgroundColor(this CellStyle cellStyle, Color color)
        {
            var fill = new PatternFill(PatternType.Solid, color, Colors.Transparent);
            cellStyle.Fill = fill;
            return cellStyle;
        }
    }
}

Example usage (sample output image attached):

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();

// Set background color to Aqua on the cell selection
worksheet.Cells[0, 1, 0, 5].SetBackgroundColor(Colors.Aqua);

// Set background color to Red on the style
var myCustomStyleName = "MyCustomStyle";
var myCustomStyle = workbook.Styles.Add(myCustomStyleName);
myCustomStyle.SetBackgroundColor(Colors.Red);

var range = new CellRange(2, 2, 2, 6);
worksheet.Cells[range].SetStyleName(myCustomStyleName);

 

[Dimitar]

Hi Randy,

Thank you for your solution and feedback. I will pass it to the team and we will consider adding such a method.

[Ian]

I agree Randy - if a SetForeColor is available, then a setBackColor method would be the kind thing to implement as well. 

[Vladislav]

Hi Ian,

I have logged a new feature request on your behalf - SpreadProcessing: Add a SetBackgroundColor() stock method. You can vote for it and subscribe to receive updates when its status changes.

I have updated your Telerik points (both yours and Randy's) in appreciation for bringing this to our attention and for providing a solution for it.