Skip to end of metadata
Go to start of metadata
Contents Summary
 

Adding Borders to Cells

Microsoft Excel allows users to format cells by adding borders. The type of border depends on where it is added. For example, a top border is one added to the top position of a cell. Users can also modify the borders' line style and color.

With Aspose.Cells, developers can add borders and customize what they look like in the same flexible way as in Microsoft Excel.

Adding Borders to Cells

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 the Cells collection. Each item in the Cells collection represents an object of the Cell class.

Aspose.Cells provides the GetStyle method in the Cell class. The SetStyle method is used to set a cell's formatting style. The Style class provides properties for adding borders to cells.

Adding Borders to a Cell

Developers can add borders to a cell by using the Style object's Borders collection. The border type is passed as an index to the Borders collection. All border types are pre-defined in the BorderType enumeration.

Border enumeration

Border Types

Description

BottomBorder

A bottom border line

DiagonalDown

A diagonal line from top left to right bottom

DiagonalUp

A diagonal line from bottom left to right top

LeftBorder

A left border line

RightBorder

A right border line

TopBorder

A top border line

The Borders collection stores all borders. Each border in the Borders collection is represented by a Border object which provides two properties, Color and LineStyle to set a border's line color and style respectively.

To set a border's line color, select a color using the Color enumeration (part of the .NET Framework) and assign it to the Border object's Color property.

The border's line style is set by selecting a line style from the CellBorderType enumeration.

CellBorderType enumeration

Line Styles

Description

DashDot

Thin dash-dotted line

DashDotDot

Thin dash-dot-dotted line

Dashed

Dashed line

Dotted

Dotted line

Double

Double line

Hair

Hairline

MediumDashDot

Medium dash-dotted line

MediumDashDotDot

Medium dash-dot-dotted line

MediumDashed

Medium dashed line

None

No line

Medium

Medium line

SlantedDashDot

Slanted medium dash-dotted line

Thick

Thick line

Thin

Thin line

Select one of the line styles and then assign it to the Border object's LineStyle property.

 

You should set both line style and color at the same time. The two diagonal border line should have the same line style and color.

Adding Borders to a Range of Cells

It is also possible to add borders to a range of cells rather than just a single cell. To do so, first, create a range of cells by calling the Cells collection's CreateRange method. It takes the following parameters:

  • First Row, the first row of the range.
  • First Column, represents the first column of the range.
  • Number of Rows, the number of rows in the range.
  • Number of Columns, the number of columns in the range.

The CreateRange method returns a Range object, which contains the specified range of cells. The Range object provides a SetOutlineBorder method that takes the following parameters to add a border to the range of cells:

  • Border Type, the border type, selected from the BorderType enumeration.
  • Line Style, the border line style, selected from the CellBorderType enumeration.
  • Color, the line color, selected from the Color enumeration.

 

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.

 

Limitation in Excel 97-2003 format

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.

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

HorizontalStripev

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.

 

Important to Know

  • 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.
Labels
  • No labels