Setting an Excel 2007 gradient background
Description:
Set a cell’s background color to a vertical gradient using two user-defined colors.
Code:
using System.Drawing;
using Microsoft.Office.Interop.Excel;
Range range =
ColorStop colorStop;
range.Interior.Pattern = XlPattern.xlPatternLinearGradient;
LinearGradient gradient = range.Interior.Gradient as LinearGradient;
// turn the gradient 90 degrees to vertical.
gradient.Degree = 90;
// clear out the defailt color settings.
gradient.ColorStops.Clear();
// set the top color.
colorStop = gradient.ColorStops.Add(0);
colorStop.Color = ColorTranslator.ToOle(Color.PaleGreen);
colorStop.TintAndShade = 0;
// set the bottom color.
colorStop = gradient.ColorStops.Add(1);
colorStop.Color = ColorTranslator.ToOle(Color.DarkRed);
colorStop.TintAndShade = 0.75;
Usage:
Use TintAndShade to handle the ‘darkness’ of the color. ( 0.0 = full strength. 0.9 is very pale). The default color picked by the users for my worksheet is LightGoldenrodYellow. So I set the top color to LightGoldenrodYellow. The bottom color is PaleVioletRed, DodgerBlue, ForestGreen, or DarkGray. The TintAndShade is set to 0.75, but it is personal preference. 0.75 sets a very pale color that doesn’t overpower the users.
Notes:
Using the Interop object for Excel 2007 has been a challenge. After about 4 months of daily effort, I think I am starting to get. If you try to set a value within an Interop object, they can throw an exception. The way around this is to pull the object into the VS context then manipulate it from there. range.Interior.Gradient doesn have a setter. But I can set a local as a ‘Tools’ copy of the Interop object, then I cna start setting values in the local/tool version.
