The Style Tag

Normally, JETT preserves Excel formatting and styling as much as possible during transformation. Static Excel formatting and styling can be done with Excel itself in the template spreadsheet. However, to style a cell dynamically, JETT provides the "style" tag. It alters whatever styling and formatting properties exist in the Cells in its block. This tag was designed to look similar to in-line CSS styling in HTML. Style tags must have a body.

Multiple properties may be specified, separated by semicolons, e.g. style="font-weight: bold; font-height-in-points: 20".

Attributes

  • The "style" tag supports all base tag attributes.
  • style: String Optional. This attribute works like the "style" attribute in HTML, in that one can specify one or more style elements in a property: value;property: value style. If a property is specified, then it will override whatever value is already present in the Cell. If a property value is an empty string or the property is not present, then it will be ignored and it will not override whatever value is already present in the Cell. Unrecognized property names and unrecognized values for a property are ignored and do not override whatever value is already present in the Cell. Property names and values may be specified in a case insensitive-fashion, i.e. "CENTER" = "Center" = "center".
  • class: String Optional. This attribute works like the "class" attribute in HTML, in that one can specify one or more pre-defined style classes that contain style elements to apply, in a semicolon-delimited list of class names. Style classes are defined in CSS-like files that are registered with the ExcelTransformer prior to transformation, with the addCssFile(String filename) or addCssText(String cssText) methods. Each subsequent class name overrides the previous class name. The style attribute overrides any class specified here.

Properties

The following properties control alignment, borders, colors, etc., everything but the font characteristics.

  • alignment - Controls horizontal alignment, with one of the values taken from Alignment.toString().
  • border - Controls all 4 borders for the cell, with one of the values taken from BorderType.toString().
  • border-bottom - Controls the bottom border for the cell, with one of the values taken from BorderType.toString().
  • border-left - Controls the left border for the cell, with one of the values taken from BorderType.toString().
  • border-right - Controls the right border for the cell, with one of the values taken from BorderType.toString().
  • border-top - Controls the top border for the cell, with one of the values taken from BorderType.toString().
  • border-color - Controls the color of all 4 borders for the cell, with a hex value ("#rrggbb") or one of 48 Excel-based color names defined by ExcelColor.toString(). For ".xls" files, if a hex value is supplied, then the supported color name that is closest to the given value is used.
  • bottom-border-color - Controls the color of the bottom border for the cell, with a hex value ("#rrggbb") or one of the above 48 color names mentioned above.
  • left-border-color - Controls the color of the left border for the cell, with a hex value ("#rrggbb") or one of the above 48 color names mentioned above.
  • right-border-color - Controls the color of the right border for the cell, with a hex value ("#rrggbb") or one of the above 48 color names mentioned above.
  • top-border-color - Controls the color of the top border for the cell, with a hex value ("#rrggbb") or one of the above 48 color names mentioned above.
  • column-width-in-chars - Controls the width of the cell's column, in number of characters.
  • data-format - Controls the Excel numeric or date format string.
  • fill-background-color - Controls the background color of the fill pattern, with one of the color values mentioned above.
  • fill-foreground-color - Controls the foreground color of the fill pattern, with one of the color values mentioned above.
  • fill-pattern - Controls the fill pattern, with one of the values taken from FillPattern.toString():
  • hidden - Controls the hidden property with a true or false value.
  • indention - Controls the number of characters that the text is indented.
  • locked - Controls the locked property with a true or false value.
  • rotation - Controls the number of degrees the text is rotated, from -90 to +90, or ROTATION_STACKED for stacked text.
  • row-height-in-points - Controls the height of the cell's row, in points.
  • vertical-alignment - Controls horizontal alignment, with one of the values taken from VerticalAlignment.toString():
  • wrap-text - Controls whether long text values are wrapped onto the next physical line with a cell, with a true or false value.

The following properties control the font characteristics.

  • font-weight - Controls how bold the text appears, with the values taken from FontBoldweight.toString().
  • font-charset - Controls the character set, with the values taken from Charset.toString().
  • font-color - Controls the color of the text, with a hex value ("#rrggbb") or one of the color names mentioned above.
  • font-height-in-points - Controls the font height, in points.
  • font-name - Controls the font name, e.g. "Arial".
  • font-italic - Controls whether the text is italic, with a true or false value.
  • font-strikeout - Controls whether the text is strikeout, with a true or false value.
  • font-type-offset - Controls the text offset, e.g. superscript and subscript, with the values taken from FontTypeOffset.toString().
  • font-underline - Controls whether and how the text is underlined, with the values taken from Underline.toString().

CSS Files

CSS Files in JETT work similarly to CSS files in HTML -- supply class names, along with properties and values inside braces:

.redBoxCenter
{
   border: thin;
   border-color: red;
   alignment: center;
}
.blueBoldBigText
{
   font-color: blue;
   font-weight: bold;
   font-height-in-points: 24;
}

However, JETT only recognizes class selectors, and the properties must be taken from the list of JETT style properties defined above.

To use classes defined in a CSS file, register the CSS file with the ExcelTransformer prior to transformation.

excelTransformer.addCssFile("styles.css");

Alternatively, supply CSS text directly to the ExcelTransformer.

String cssTextString = ".redBoxCenter {border: thin; border-color: red; alignment: center;} " +
                       ".blueBoldBigText {font-color: blue; font-weight: bold; font-height-in-points: 24;}";
excelTransformer.addCssText(cssTextString);

Property Values

Each property has its own set of values that it recognizes in a case insensitive fashion. If a value is empty or it is unrecognized, then it has no effect.

The alignment property

These values control how text is aligned horizontally within the cells.

Value Example Description
center Centered Text Content is centered horizontally.
centerSelection Center Selection Text Content is centered horizontally across a selection. This has more meaning in Excel itself when one can center text across multiple cells.
distributed This sentence, long enough to cover 2 lines, is distributed text. Content is distributed horizontally. Content with multiple words yields content on the left edge and on the right edge of the cell. This works like "justify" in CSS.
fill Fill text Fill text Fill text Fill text Fill text With the "fill" value, content is repeated until it would run over the end of the cell.
general General Text Content is not explicitly aligned; text is left-aligned and numbers are right-aligned.
left Left-Aligned Text Content is aligned to the left of the cell.
right Right-Aligned text. Content is aligned to the right of the cell.

The border properties

These values control the type of cell border, for the properties "border", "border-bottom", "border-left", "border-right", and "border-top".

  • none No border.
  • thin A thin border.
  • medium A medium-sized border.
  • dashed A thin dashed border.
  • hair A "hair" border, somewhere in between "dashed" and "dotted".
  • thick A thick border.
  • double A double border.
  • dotted A thin dotted border.
  • mediumdashed A medium-sized dashed border.
  • dashdot A thin border with a dash-dot pattern.
  • mediumdashdot A medium-sized border with a dash-dot pattern.
  • dashdotdot A thin border with a dash-dot-dot pattern.
  • mediumdashdotdot A medium-sized border with a dash-dot-dot pattern.
  • slanteddashdot A medium-sized border with a slatned dash-dot pattern.

Here are examples of all the above values.

Borders Results

The border-color properties

These values control the color of the cell border, for the properties "border-color", "bottom-border-color", "left-border-color", "right-border-color", and "top-border-color".

Any colors specified in the style tag can take one of two forms:

  • One of 48 pre-defined Excel color names identifying the desired color. These color names do NOT necessarily match the color names defined in HTML.
    • aqua automatic black blue bluegrey brightgreen
    brown coral cornflowerblue darkblue darkgreen darkred
    darkteal darkyellow gold green grey25percent grey40percent
    grey50percent grey80percent indigo lavender lemonchiffon lightblue
    lightcornflowerblue lightgreen lightorange lightturquoise lightyellow lime
    maroon olivegreen orange orchid paleblue pink
    plum red rose royalblue seagreen skyblue
    tan teal turquoise violet white yellow
  • A hex-string color value, in the format "#RRGGBB", e.g. "#000000" for black, "#FFFFFF" for white, and "#0000FF" for blue. For .xls spreadsheets, the closest of the 48 pre-defined Excel colors is used.

Here are examples of some of the above values.

The column-width-in-chars property

These floating-point values control the width of the column in which the cell is found, in number of characters. This controls the width of the entire column, and all cells in it, not just the cell in which this is found.

The data-format property

These values control how the data in the cell is formatted. One can control zeroes beyond the decimal point, thousands separators, percentages, fractions, date and time formatting, and more. Any data format string that can be typed into Excel can be used here. Here are some examples that format the number "42140.629247":

  • #,###.00 => 41,240.63
  • 0.00% => 4124062.92%
  • ???.??? => 41240.629
  • [Red][<=100]General\\;[Blue][>100]General => 41240.62925
  • ## ???/??? => 41240 426/677
  • yyyy-mm-dd hh:mm:ss => 2012-11-27 15:06:07
  • mmmm d, yyyy h:mm:ss AM/PM => November 27, 2012 3:06:07 PM

The semicolon in the red/blue example above needs to be escaped, so that the StyleTag doesn't interpret that as the end of the key-value pair. Then Java requires that the backslash is itself escaped.

The fill-background-color and fill-foreground color properties

These values control the background and foreground colors that are used when a fill pattern is applied. Colors may be specified in the same way as for the "border-color" properties (see above). These only take effect in Excel when the fill pattern is not "nofill".

The fill-pattern property

These values define a pattern that is visible behind any text or value in a cell. The pattern is colored using the "fill-background-color" and "fill-foreground-color" properties.

  • nofill - No pattern fill. Background and forground color settings have no effect. This is the default.
  • solid - Solid foreground color.
  • gray50percent - The pixels alternate between the background and foreground colors.
  • gray75percent - In a pattern, 75% of the pixels are the foreground color; the rest are the background color.
  • gray25percent - In a pattern, 25% of the pixels are the foreground color; the rest are the background color.
  • horizontalstripe - Horizontal stripe pattern.
  • verticalstripe - Vertical stripe pattern.
  • reversediagonalstripe - Diagonal stripe pattern, going from the upper-left to the lower-right.
  • diagonalstripe - Diagonal stripe pattern, going from the lower-left to the upper-right.
  • diagonalcrosshatch - Diagonal crosshatch pattern.
  • thickdiagonalcrosshatch - Diagonal crosshatch pattern, using mostly the foreground color.
  • thinhorizontalstripe - Thin horizontal stripe pattern; mostly the background color shows.
  • thinverticalstripe - Thin vertical stripe pattern; mostly the background color shows.
  • thinreversediagonalstripe - Thin diagonal stripe pattern, going from the upper-left to the lower-right; mostly the background color shows.
  • thindiagonalstripe - Thin diagonal stripe pattern, going from the lower-left to the upper-right; mostly the background color shows.
  • thinhorizontalcrosshatch - Thin horizontal and vertical crosshatch pattern; mostly the background color shows.
  • thindiagonalcrosshatch - Thin diagonal crosshatch pattern; mostly the background color shows.
  • gray12percent - In a pattern, 12.5% of the pixels are the foreground color; the rest are the background color.
  • gray6percent - In a pattern, 6.25% of the pixels are the foreground color; the rest are the background color.

Here are examples of all the above values.

The hidden property

These values determine whether the cell is "hidden", which only has an effect if the workbook is "protected".

  • true - This cell is "hidden". Users cannot see the formula or edit the cell. This has no effect if the spreadsheet is not "protected".
  • false - This cell is not "hidden". This is the default.

The indention property

These values determine by how many characters the cell text is indented.

Value Example
0 Indented 0 characters
1 Indented 1 character
3 Indented 3 characters
10 Indented 10 characters

The locked property

These values determine whether the cell is "locked", which only has an effect if the workbook is "protected".

  • true - This cell is "locked". It appears that all cells are "locked" by default in Excel. This has no effect if the spreadsheet is not "protected".
  • false - This cell is not "locked".

The rotation property

These values (-90 to 90) represent the number of degrees to rotate the text of the cell. Normal text is represented by 0 degrees (the default). Negative numbers rotate the text clockwise, and positive numbers rotate the text counter-clockwise. Use the special value "255" to make the cell text stacked (letters oriented as normal, but one letter is on top of the next).

Here are some examples of this property:

The row-height-in-points property

These floating-point values control the height of the row in which the cell is found, in points. This controls the height of the entire row, and all cells in it, not just the cell in which this is found.

The vertical-alignment property

These values control how text is aligned vertically within the cells.

  • bottom - Align cell text at the bottom of the cell.
  • center - Align cell text at the middle of the cell.
  • distributed - Vertically distribute text in the cell.
  • justify - Vertically justify text in the cell.
  • top - Align cell text at the top of the cell.

The wrap-text property

These values control whether text is wrapped onto the next line within the cell or not. If not, then overflowing text runs on into the next cell (or is cut off if the next cell has its own content).

  • true - Wrap long content onto the next logical line in the cell.
  • false - Don't wrap long content onto the next logical line in the cell. This is the default. Content will "spill over" into the next cell, or if the next cell has its own content, then it will be cut off.

Here are some examples.

The font-weight property

These values control how bold the cell content appears.

  • bold - The text appears bold.
  • normal - The text appears normal. This is the default.

The font-charset property

These values determine the Excel character set of the content in the cell.

  • ansi
  • default
  • symbol
  • mac
  • shiftjis
  • hangeul
  • johab
  • gb2312
  • chinesebig5
  • greek
  • turkish
  • vietnamese
  • hebrew
  • arabic
  • baltic
  • russian
  • thai
  • easteurope
  • oem

The font-color property

These values control the color of the text content of the cell. Colors may be specified in the same way as for the "border-color" properties (see above).

The font-height-in-points property

These floating-point values control the size of the font being used for the text in the cell.

The font-name property

These font name string values control the font used for the text in the cell.

Here are some examples:

  • Arial - Arial
  • Courier New - Courier New
  • Tahoma - Tahoma
  • Times New Roman - Times New Roman
  • Verdana - Verdana

The font-italic property

These values control whether the text renders in italic.

  • true - Text is rendered italic.
  • false - Text is rendered normal. This is the default.

The font-strikeout property

These values control whether the text renders in strikeout.

  • true - Text is rendered strikeout.
  • false - Text is rendered normal. This is the default.

The font-type-offset property

These values control whether the text renders as subscript or superscript.

  • none - Text is rendered normal. This is the default.
  • sub - Text is rendered in subscript.
  • sup - Text is rendered in superscript.

The font-underline property

These values control whether the text is underlined.

  • single - A single underline.
  • double - A double underline.
  • singleaccounting - A single underline, accounting style.
  • doubleaccounting - A double underline, accounting style.
  • none - No underline. This is the default.

Here are some examples: