Analyzing your prompt, please hold on...
An error occurred while retrieving the results. Please refresh the page and try again.
Cells Formats
Contents
[
Hide
]
Adding Borders to Cells
Microsoft Excel allows users to format cells by adding borders.
Borders settings in Microsoft Excel
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
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
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
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.
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) file formats.
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
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.
To set the foreground or background color of a cell, use the Style object’s setBackgroundColor or setForegroundColor properties. These properties only come into effect if the Style object’s setPattern property is configured.
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
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.
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
If you are interested in formatting a portion of Rich Text in a cell, consider using the Cell.getCharacters & Cell.setCharacters methods. The Cell.getCharacters method is to be used to access the portions of the text and then amendments can be done using the Cell.setCharacters method whereas the get method returns an array of FontSetting objects which can be manipulated to set various properties such as font name, font color, boldness, etc and set method can be used to apply the changes.