Cells Formatting

Adding Borders to Cells

Microsoft Excel allows users to format cells by adding borders.

Borders settings in Microsoft Excel

todo:image_alt_text

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 they can in Microsoft Excel.

Adding Borders to Cells

Aspose.Cells provides a class, Workbook that represents a Microsoft Excel file. The Workbook class contains a WorksheetCollection 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.

Aspose.Cells provides the setStyle method in the Cell class used to set a cell’s formatting style. Also, the object of the Style class is used and provides properties for configuring font settings.

Adding Borders to a Cell

Add borders to a cell with the Style object’s setBorder method. The border type is passed as a parameter. All border types are pre-defined in the BorderType enumeration.

Border Types Description
BOTTOM_BORDER The bottom border line
DIAGONAL_DOWN A diagonal line from top left to right bottom
DIAGONAL_UP A diagonal line from bottom left to right top
LEFT_BORDER The left border line
RIGHT_BORDER The right border line
TOP_BORDER The top border line
HORIZONTAL Only for dynamic style, such as conditional formatting.
VERTICAL Only for dynamic style, such as conditional formatting.
To set the line color, select a color using the Color enumeration and pass it to the Style object’s setBorder method’s Color parameter. The line styles are pre-defined in the CellBorderType enumeration.
Line Styles Description
DASH_DOT Represents thin dash-dotted line
DASH_DOT_DOT Represents thin dash-dot-dotted line
DASHED Represents dashed line
DOTTED Represents dotted line
DOUBLE Represents double line
HAIR Represents hair line
MEDIUM_DASH_DOT Represents medium dash-dotted line
MEDIUM_DASH_DOT_DOT Represents medium dash-dot-dotted line
MEDIUM_DASHED Represents medium dashed line
NONE Represents no line
MEDIUM Represents medium line
SLANTED_DASH_DOT Represents slanted medium dash-dotted line
THICK Represents thick line
THIN Represents thin line
Select one of the above line styles and then assign it to the Style object’s setBorder method.

The following output is generated when executing the code below.

Borders applied on all sides of a cell

todo:image_alt_text

Adding Borders to a Range of Cells

It is possible to add borders to a range of cells rather than just a single cell. First, create a range of cells by calling the Cells collection’s createRange method, which takes the following parameters:

  • First Row, the first row of the range.
  • First Column, 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. The Range object provides a setOutlineBorders method that takes the following parameters:

  • CellBorderType, the border line style, selected from the CellBorderType enumeration.
  • Color, the border line color, selected from the Color enumeration.

The following output is generated when executing the code below.

Borders applied on a range of cells

todo:image_alt_text

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.

Palette settings in Microsoft Excel

todo:image_alt_text

With Aspose.Cells it is not only possible to use existing colors but also custom colors. Before using a custom color, add it to the palette. This topic explains how to add custom colors to the palette.

Adding Custom Colors to Palette

Aspose.Cells also supports a palette of 56 colors. A standard color palette is shown above. If you want to use a custom color that is not defined in the palette then you will need to add that color to the palette before usage.

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

  • Custom color, the custom color to be added to the palette.
  • Index, the index of the color that will be replaced with the custom color. Should be between 0-55.

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

Colors and Background Patterns

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

Setting colors and background Patterns in Microsoft Excel

todo:image_alt_text

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

Setting Colors & Background Patterns

Aspose.Cells provides a class, Workbook, that represents a Microsoft Excel file. The Workbook class contains a WorksheetCollection 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.

Aspose.Cells provides the setStyle method in the Cell class that is used to set a cell’s formatting. Also, the object of the Style class can be used to configure font settings.

The setForegroundColor  property sets the cell’s shading color.

The setPattern   property specifies the background pattern used for the foreground or background color. Aspose.Cells provides the BackgroundType enumeration which contains a set of pre-defined types of background patterns.

Pattern Type Description
DIAGONAL_CROSSHATCH Represents diagonal crosshatch pattern
DIAGONAL_STRIPE Represents diagonal stripe pattern
GRAY_6 Represents 6.25% gray pattern
GRAY_12 Represents 12.5% gray pattern
GRAY_25 Represents 25% gray pattern
GRAY_50 Represents 50% gray pattern
GRAY_75 Represents 75% gray pattern
HORIZONTAL_STRIPE Represents horizontal stripe pattern
NONE Represents no background
REVERSE_DIAGONAL_STRIPE Represents reverse diagonal stripe pattern
SOLID Represents solid pattern
THICK_DIAGONAL_CROSSHATCH Represents thick diagonal crosshatch pattern
THIN_DIAGONAL_CROSSHATCH Represents thin diagonal crosshatch pattern
THIN_DIAGONAL_STRIPE Represents thin diagonal stripe pattern
THIN_HORIZONTAL_CROSSHATCH Represents thin horizontal crosshatch pattern
THIN_HORIZONTAL_STRIPE Represents thin horizontal stripe pattern
THIN_REVERSE_DIAGONAL_STRIPE Represents thin reverse diagonal stripe pattern
THIN_VERTICAL_STRIPE Represents thin vertical stripe pattern
VERTICAL_STRIPE 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.

The following output is generated when executing the code.

Foreground and background colors applied on cells with background patterns

todo:image_alt_text

Important to Know

Formatting Selected Characters in a Cell

Dealing with Font Settings explained how to format cells but only how to format the content of the entire cells. What if you want to format only selected characters?

Aspose.Cells supports this feature. This topic explains how to use this feature.

Formatting Selected Characters

Aspose.Cells provides a class, Workbook, that represents a Microsoft Excel file. The Workbook class contains a WorksheetCollection 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 class provides characters method that takes the following parameters to select a range of characters in a cell:

  • Start Index, the index of the character to start the selection from.
  • Number of Characters, the number of characters to select.

In the output file, in the A1" cell, the word ‘Visit’ is formatted with the default font but ‘Aspose!’ is bold and blue.

Formatting selected characters

todo:image_alt_text