Fill Settings

Colors and Background Patterns

Microsoft Excel can set the foreground (outline) and background (fill) colors of cells and background patterns.

Aspose.Cells also supports these features in a flexible manner. In this topic, we learn to use these features using Aspose.Cells.

Setting Colors and Background Patterns

Aspose.Cells provides a class, Workbook that represents a Microsoft Excel file. The Workbook class contains a Worksheets collection that allows access to each worksheet in the Excel file. A worksheet is represented by the Worksheet class. The Worksheet class provides a Cells collection. Each item in the Cells collection represents an object of the Cell class.

The Cell has the GetStyle and SetStyle methods that are used to get and set a cell’s formatting. The Style class provides properties for setting the foreground and background colors of the cells. Aspose.Cells provides a BackgroundType enumeration that contains a set of pre-defined types of background patterns which are given below.

Background Patterns	Description
DiagonalCrosshatch	Represents diagonal crosshatch pattern
DiagonalStripe	Represents diagonal stripe pattern
Gray6	Represents 6.25% gray pattern
Gray12	Represents 12.5% gray pattern
Gray25	Represents 25% gray pattern
Gray50	Represents 50% gray pattern
Gray75	Represents 75% gray pattern
HorizontalStripe	Represents horizontal stripe pattern
None	Represents no background
ReverseDiagonalStripe	Represents reverse diagonal stripe pattern
Solid	Represents solid pattern
ThickDiagonalCrosshatch	Represents thick diagonal crosshatch pattern
ThinDiagonalCrosshatch	Represents thin diagonal crosshatch pattern
ThinDiagonalStripe	Represents thin diagonal stripe pattern
ThinHorizontalCrosshatch	Represents thin horizontal crosshatch pattern
ThinHorizontalStripe	Represents thin horizontal stripe pattern
ThinReverseDiagonalStripe	Represents thin reverse diagonal stripe pattern
ThinVerticalStripe	Represents thin vertical stripe pattern
VerticalStripe	Represents vertical stripe pattern

In the example below, the foreground color of the A1 cell is set but A2 is configured to have both foreground and background colors with a vertical stripe background pattern.

	// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
	// The path to the documents directory.
	string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

	// Create directory if it is not already present.
	bool IsExists = System.IO.Directory.Exists(dataDir);
	if (!IsExists)
	System.IO.Directory.CreateDirectory(dataDir);

	// Instantiating a Workbook object
	Workbook workbook = new Workbook();

	// Adding a new worksheet to the Workbook object
	int i = workbook.Worksheets.Add();

	// Obtaining the reference of the newly added worksheet by passing its sheet index
	Worksheet worksheet = workbook.Worksheets[i];

	// Define a Style and get the A1 cell style
	Style style = worksheet.Cells["A1"].GetStyle();

	// Setting the foreground color to yellow
	style.ForegroundColor = Color.Yellow;

	// Setting the background pattern to vertical stripe
	style.Pattern = BackgroundType.VerticalStripe;

	// Apply the style to A1 cell
	worksheet.Cells["A1"].SetStyle(style);

	// Get the A2 cell style
	style = worksheet.Cells["A2"].GetStyle();

	// Setting the foreground color to blue
	style.ForegroundColor = Color.Blue;

	// Setting the background color to yellow
	style.BackgroundColor = Color.Yellow;

	// Setting the background pattern to vertical stripe
	style.Pattern = BackgroundType.VerticalStripe;

	// Apply the style to A2 cell
	worksheet.Cells["A2"].SetStyle(style);

	// Saving the Excel file
	workbook.Save(dataDir + "book1.out.xls", SaveFormat.Excel97To2003);

view raw Examples-CSharp-Formatting-ColorsAndBackground-1.cs hosted with ❤ by GitHub

Important to Know

To set a cell’s foreground or background color, use the Style object’s ForegroundColor or BackgroundColor properties. Both properties will take effect only if the Style object’s Pattern property is configured.
The ForegroundColor property sets the cell’s shade color. The Pattern property specifies the type of background pattern used for the foreground or background color. Aspose.Cells provides an enumeration, BackgroundType. that contains a set of pre-defined types of background patterns.
If you select BackgroundType.None value from the BackgroundType enumeration, the foreground color is not applied. Likewise, the background color is not applied if you select the BackgroundType.None or BackgroundType.Solid values.
When retrieving cell’s shading/fill color, if Style.Pattern is BackgroundType.None, Style.ForegroundColor will return Color.Empty.

Applying Gradient Fill Effects

To apply your desired Gradient Fill Effects to the cell, use the Style object’s SetTwoColorGradient method accordingly.

	// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
	// The path to the documents directory.
	string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

	// Instantiate a new Workbook
	Workbook workbook = new Workbook();
	// Get the first worksheet (default) in the workbook
	Worksheet worksheet = workbook.Worksheets[0];
	// Input a value into B3 cell
	worksheet.Cells[2, 1].PutValue("test");

	// Get the Style of the cell
	Style style = worksheet.Cells["B3"].GetStyle();
	// Set Gradient pattern on
	style.IsGradient = true;
	// Specify two color gradient fill effects
	style.SetTwoColorGradient(Color.FromArgb(255, 255, 255), Color.FromArgb(79, 129, 189), GradientStyleType.Horizontal, 1);
	// Set the color of the text in the cell
	style.Font.Color = Color.Red;
	// Specify horizontal and vertical alignment settings
	style.HorizontalAlignment = TextAlignmentType.Center;
	style.VerticalAlignment = TextAlignmentType.Center;

	// Apply the style to the cell
	worksheet.Cells["B3"].SetStyle(style);

	// Set the third row height in pixels
	worksheet.Cells.SetRowHeightPixel(2, 53);

	// Merge the range of cells (B3:C3)
	worksheet.Cells.Merge(2, 1, 1, 2);

	// Save the Excel file
	workbook.Save(dataDir + "output.xlsx");

view raw Examples-CSharp-Formatting-ApproachesToFormatData-ApplyingGradientFillEffects-1.cs hosted with ❤ by GitHub

Colors and Palette

A palette is the number of colors available for use in creating an image. The use of a standardized palette in a presentation allows the user to create a consistent look. Each Microsoft Excel (97-2003) file has a palette of 56 colors that can be applied to cells, fonts, gridlines, graphic objects, fills and lines in a chart.

With Aspose.Cells it is possible not only to use the palette’s existing colors but also custom colors. Before using a custom color, add it to the palette first.

This topic discusses how to add custom colors to the palette.

Adding Custom Colors to Palette

Aspose.Cells supports Microsoft Excel’s 56 color palette. To use a custom color that is not defined in the palette, add the color to the palette.

Aspose.Cells provides a class, Workbook, that represents a Microsoft Excel file. The Workbook class provides a ChangePalette method that takes the following parameters to add a custom color to modify the palette:

Custom Color, the custom color to be added.
Index, the index of the color in the palette that the custom color will replace. Should be between 0-55.

The example below adds a custom color (Orchid) to the palette before applying it on a font.

	// Instantiating an Workbook object
	Workbook workbook = new Workbook();

	//Checks if a color is in the palette for the spreadsheet.
	Console.WriteLine(workbook.IsColorInPalette(Color.Orchid));

	// Adding Orchid color to the palette at 55th index
	workbook.ChangePalette(Color.Orchid, 55);

	Console.WriteLine(workbook.IsColorInPalette(Color.Orchid));

	// Adding a new worksheet to the Excel object
	int i = workbook.Worksheets.Add();

	// Obtaining the reference of the newly added worksheet by passing its sheet index
	Worksheet worksheet = workbook.Worksheets[i];

	// Accessing the "A1" cell from the worksheet
	Cell cell = worksheet.Cells["A1"];

	// Adding some value to the "A1" cell
	cell.PutValue("Hello Aspose!");

	// Defining new Style object
	Style styleObject = workbook.CreateStyle();
	// Setting the Orchid (custom) color to the font
	styleObject.Font.Color = workbook.Colors[55];

	// Applying the style to the cell
	cell.SetStyle(styleObject);

	// Saving the Excel file
	workbook.Save("out.xlsx");

view raw Examples-CSharp-Formatting-ColorsAndPalette-1.cs hosted with ❤ by GitHub

The palette only holds 56 colors. When you add a custom color to the palette, the palette is changed and any element in the file formatted with the previous color is changed. So, when you change the palette, please be very careful. Moreover, this is the limitation in XLS (Excel 97 - 2003) file format only as there is no such limitation for XLSX or other advanced MS Excel (2007/2010 or 2013) file formats.

Border Settings Set Conditional Formats of Excel and ODS files.