public final class CellStyleCollection extends Object implements Iterable<Style>
Style
s.
Conceptually, cell formatting is divided into following groups:
CellStyle.setNumberFormat(java.lang.String)
.
CellStyle.setHorizontalAlignment(com.gembox.spreadsheet.HorizontalAlignmentStyle)
, CellStyle.setVerticalAlignment(com.gembox.spreadsheet.VerticalAlignmentStyle)
, CellStyle.setIndent(int)
, CellStyle.setRotation(int)
, CellStyle.setTextVertical(boolean)
, CellStyle.setWrapText(boolean)
and CellStyle.setShrinkToFit(boolean)
.
CellStyle.setFont(com.gembox.spreadsheet.ExcelFont)
.
CellStyle.setBorders(com.gembox.spreadsheet.CellBorders)
.
CellStyle.setFillPattern(com.gembox.spreadsheet.ExcelFillPattern)
.
CellStyle.setLocked(boolean)
and CellStyle.setFormulaHidden(boolean)
.
Additional CellStyle
methods not associated with any formatting group are:
CellStyle.getName()
- name of the referenced workbook style
.
CellStyle.setQuotePrefix(boolean)
- true
to store numeric value of a cell as text; otherwise, false
.
CellStyle.isDefault()
- true
if referenced
workbook style
is default (
getNormal()
) and there are no additional modifications of cell formatting; otherwise, false
.
Workbook
contains a set of master styles
which can be referenced by multiple cells.
Workbook
must always contain at least one master style
which cannot be removed and is, by default, referenced by all cells. This default style is
getNormal()
.
Workbook style
can either be built-in or user-defined. Built-in style is accessible from workbook styles
via BuiltInCellStyleName
enumeration.
Cell formatting group (Number, Alignment, Font, Border, Fill or Protection) (and its associated methods) is resolved from referenced
workbook (master) style
, unless cell formatting group or its associated method is modified.
Cell formatting is available for one or more cells through AbstractRange.setStyle(com.gembox.spreadsheet.CellStyle)
method which is available on ExcelCell
and CellRange
types.
Cell formatting specified on ExcelColumn
and ExcelRow
types through ExcelColumnRowBase.setStyle(com.gembox.spreadsheet.CellStyle)
method is simply propagated to cell formatting of its ExcelColumnRowBase.getCells()
.
For performance reasons, cell formatting on CellRange
is resolved based just on its top-left cell formatting, except borders which are resolved based on corner cells depending on border side.
Setting cell formatting method on CellRange
is propagated to each cell in a range.
To set workbook (master) style
to one or more cells, simply assign it to AbstractRange.setStyle(com.gembox.spreadsheet.CellStyle)
method.
Preferable way to modify formatting method of multiple cells is to get CellRange
to which all those cells belong, and use AbstractRange.setStyle(com.gembox.spreadsheet.CellStyle)
method of that range to make the modification.
If modifying multiple formatting properties of a CellRange
, without preserving unmodified formatting properties, preferable way is to create new instance of CellStyle
, make modifications on it, and assign it using AbstractRange.setStyle(com.gembox.spreadsheet.CellStyle)
method of that range.
GemBox.Spreadsheet internally takes care not to allocate unnecessary cells when formatting a range of cells (for example, when formatting all worksheet cells
) and to cache formatting information of equally formatted cells, at the appropriate time, to reduce memory footprint.
Following code demonstrates how to set workbook style to a range of cells:
// Set 'Good' style to all cells of the first sheet.
workbook.getWorksheet(0).getCells().setStyle(workbook.getStyle(BuiltInCellStyleName.GOOD));
Following code demonstrates when not to and when to initialize a new instance of the CellStyle class:
// To modify a single formatting property on a range of cells, simply modify it.
// Other formatting properties will remain unchanged.
sheet.getCells().getStyle().getFont().setItalic(true);
// To modify multiple formatting properties on a range of cells, create new CellStyle instance, modify it, and assign it to a range of cells.
// All formatting properties are changed.
CellStyle style = new CellStyle(sheet.getParent());
style.setNumberFormat("#,##0.00");
style.setHorizontalAlignment(HorizontalAlignmentStyle.CENTER);
style.setLocked(false);
sheet.getCells().setStyle(style);
Following code demonstrates cell formatting:
// 'Number' formatting group
sheet.getCell("B3").setValue("NumberFormat");
sheet.getCell("C3").setValue(1234);
sheet.getCell("C3").getStyle().setNumberFormat("#.##0,00 [$Krakozhian Money Units]");
// 'Alignment' formatting group
sheet.getCell("B5").setValue("HorizontalAlignment = ");
sheet.getCell("C5").setValue("HorizontalAlignmentStyle.Center");
sheet.getCell("C5").getStyle().setHorizontalAlignment(HorizontalAlignmentStyle.CENTER);
sheet.getCell("B6").setValue("VerticalAlignment = ");
sheet.getCell("C6").setValue("VerticalAlignmentStyle.Top");
sheet.getCell("C6").getStyle().setVerticalAlignment(VerticalAlignmentStyle.TOP);
// Set row height to 30 points.
sheet.getRow("6").setHeight(30 * 20);
sheet.getCell("B7").setValue("Indent");
sheet.getCell("C7").setValue("five");
sheet.getCell("C7").getStyle().setIndent(5);
sheet.getCell("C7").getStyle().setHorizontalAlignment(HorizontalAlignmentStyle.LEFT);
sheet.getCell("B8").setValue("Rotation");
sheet.getCell("C8").setValue("35 degrees up");
sheet.getCell("C8").getStyle().setRotation(35);
sheet.getCell("B9").setValue("IsTextVertical = ");
sheet.getCell("C9").setValue("true");
sheet.getCell("C9").getStyle().setTextVertical(true);
sheet.getCell("B10").setValue("WrapText");
sheet.getCell("C10").setValue("This property is set to true so this text appears broken into multiple lines.");
sheet.getCell("C10").getStyle().setWrapText(true);
sheet.getCell("B11").setValue("ShrinkToFit");
sheet.getCell("C11").setValue("This property is set to true so this text appears shrunk.");
sheet.getCell("C11").getStyle().setShrinkToFit(true);
// 'Font' formatting group
sheet.getCell("B13").setValue("Font.Name = ");
sheet.getCell("C13").setValue("Comic Sans MS");
sheet.getCell("C13").getStyle().getFont().setName("Comic Sans MS");
sheet.getCell("B14").setValue("Font.Italic = ");
sheet.getCell("C14").setValue("true");
sheet.getCell("C14").getStyle().getFont().setItalic(true);
sheet.getCell("B15").setValue("Font.Weight = ");
sheet.getCell("C15").setValue("ExcelFont.BoldWeight");
sheet.getCell("C15").getStyle().getFont().setWeight(ExcelFont.BOLD_WEIGHT);
sheet.getCell("B16").setValue("Font.Size = ");
sheet.getCell("C16").setValue("18 * 20");
sheet.getCell("C16").getStyle().getFont().setSize(18 * 20);
sheet.getCell("B17").setValue("Font.Color");
sheet.getCell("C17").setValue("Text2");
sheet.getCell("C17").getStyle().getFont().setColor(SpreadsheetColor.fromName(ColorName.TEXT_2));
sheet.getCell("B18").setValue("Font.UnderlineStyle = ");
sheet.getCell("C18").setValue("UnderlineStyle.Double");
sheet.getCell("C18").getStyle().getFont().setUnderlineStyle(UnderlineStyle.DOUBLE);
sheet.getCell("B19").setValue("Font.Strikeout = ");
sheet.getCell("C19").setValue("true");
sheet.getCell("C19").getStyle().getFont().setStrikeout(true);
sheet.getCell("B20").setValue("Font.ScriptPosition = ");
sheet.getCell("C20").setValue("ScriptPosition.Superscript");
sheet.getCell("C20").getStyle().getFont().setScriptPosition(ScriptPosition.SUBSCRIPT);
// 'Border' formatting group
sheet.getCell("B22").setValue("Borders.setBorders(...)");
sheet.getCell("C22").getStyle().getBorders().setBorders(MultipleBorders.all(), SpreadsheetColor.fromName(ColorName.ACCENT_2), LineStyle.THIN);
// 'Fill' formatting group
sheet.getCell("B24").setValue("FillPattern.setPattern(...)");
sheet.getCell("C24").getStyle().getFillPattern().setPattern(FillPatternStyle.THIN_HORIZONTAL_CROSSHATCH, SpreadsheetColor.fromName(ColorName.GREEN), SpreadsheetColor.fromName(ColorName.YELLOW));
sheet.getCell("B25").setValue("FillPattern.setGradient(...)");
sheet.getCell("C25").getStyle().getFillPattern().setGradient(GradientShadingStyle.HORIZONTAL_HIGH, SpreadsheetColor.fromName(ColorName.GREEN), SpreadsheetColor.fromName(ColorName.YELLOW));
// 'Protection' formatting group
sheet.getCell("B27").setValue("Locked = ");
sheet.getCell("C27").setValue("false");
sheet.getCell("C27").getStyle().setLocked(false);
sheet.getCell("B28").setValue("FormulaHidden = ");
sheet.getCell("C28").setValue("true");
sheet.getCell("C28").getStyle().setFormulaHidden(true);
ExcelFile.getStyles()
Modifier and Type | Method and Description |
---|---|
Style |
add(String name)
Adds an empty
Style instance to the CellStyleCollection . |
boolean |
contains(String styleName)
Determines whether the
CellStyleCollection contains a Style instance with specific name. |
Style |
get(BuiltInCellStyleName styleName)
Gets the
Style that corresponds to given BuiltInCellStyleName . |
Style |
get(String styleName)
Gets the
Style that corresponds to given name. |
Style |
getNormal()
Gets the 'Normal' style.
|
Iterator<Style> |
iterator()
Returns an iterator that iterates through the
CellStyleCollection . |
boolean |
remove(BuiltInCellStyleName name)
Removes built-in style with the specified name from the workbook.
|
boolean |
remove(String name)
Removes style with the specified name from the workbook.
|
boolean |
remove(Style style)
Removes the specified style from the workbook.
|
int |
size()
Gets the number of
Style s contained in the collection. |
equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
forEach, spliterator
public Style add(String name)
Style
instance to the CellStyleCollection
.name
- The name of new Style
instance.Style
instance that is added to this collection.IllegalArgumentException
- Parameter name
cannot be null or empty.IllegalStateException
- Style with name name
is already contained in the workbook.public boolean contains(String styleName)
CellStyleCollection
contains a Style
instance with specific name.styleName
- Name of the Style
to locate in the CellStyleCollection
.true
if Style
instance with specific name is found in the CellStyleCollection
; otherwise, false
.public Style get(BuiltInCellStyleName styleName)
Gets the Style
that corresponds to given BuiltInCellStyleName
.
If CellStyleCollection
doesn't contain correspondent style, then new style will be added and returned.
styleName
- The BuiltInCellStyleName
of the Style
.Style
that corresponds to given BuiltInCellStyleName
.public Style get(String styleName)
Gets the Style
that corresponds to given name.
If CellStyleCollection
doesn't contain correspondent style, then new style will be added and returned.
public Style getNormal()
The 'Normal' style always exists in a workbook and it cannot be removed.
The 'Normal' style stores default cell formatting properties for an entire workbook, because all cells will reference the 'Normal' style by default.
public Iterator<Style> iterator()
CellStyleCollection
.iterator
in interface Iterable<Style>
CellStyleCollection
.public boolean remove(BuiltInCellStyleName name)
name
- The name of the built-in style which should be removed from the workbook.true
if style is successfully removed; otherwise, false
. This method also returns false
if style was not found in the workbook.IllegalStateException
- 'Normal
' style cannot be removed from the workbook.public boolean remove(String name)
name
- The name of the style which should be removed from the workbook.true
if style is successfully removed; otherwise, false
. This method also returns false
if style was not found in the workbook.IllegalStateException
- 'Normal
' style cannot be removed from the workbook.public boolean remove(Style style)
style
- The style to remove from the workbook.true
if style
is successfully removed; otherwise, false
. This method also returns false
if style
was not found in the workbook.public int size()
Style
s contained in the collection.© GemBox d.o.o. — All rights reserved.