<The previous article in this series | The table of contents of this series | The next article in this series>
To Know How to Handle (Read or Write) LibreOffice or Apache OpenOffice Calc Spread Sheets via Extensions from Java or Macro Programs
How Can We Get or Set Spread Sheet Cell Formats?
As we use spread sheets in the purpose of managing data, I'm not particularly interested in creating fancy visuals. However, in order to highlight some data, setting some spread sheet cell format properties may be usable.
Please explain your usage of terms, 'spread sheet cell format' and 'spread sheet cell format property'.
I call an attribute of the spread sheet cell format a 'spread sheet cell format property', and call the whole of the attributes of the spread sheet cell format the 'spread sheet cell format'. For example, the background color of the spread sheet cell is a spread sheet cell format property; the whole of the background color, the foreground color, the font name, etc is the spread cell format.
Ah-ha.
In fact, there are many spread sheet cell format properties, and I'm not interested in enumerating all of them. We will handle only these properties.
- background color
- whether background color is transparent
- font color
- font name
- font size
- font posture
- font weight
- text underline style
- whether text underline color is automatic
- text underline color
- text overline style
- whether text overline color is automatic
- text overline color
- text strikeout style
- whether text is wrapped
- text horizontal alignment
- text vertical alignment
- left borderline
- right borderline
- top borderline
- bottom borderline
Aren't some properties, for example font name, things to be set per character, not per cell?
They can be set both per character and per cell. Now, we are talking about setting them per cell; we will talk about setting them per character, in the next article.
And aren't properties like left borderline too vague? There should be borderline style, borderline color, etc.
Ah, those properties I cited are in accordance with the properties of the spread sheet cell UNO object. In fact, spread sheet cell properties are properties of the spread sheet cell UNO object. As for those borderline properties, each of them gets a struct (mapped to a class in Java) instance that contains borderline style, borderline color, etc.
I wonder why those borderline properties are gathered together in structs while text underline properties aren't.
Honestly, I wonder too. The granularity of properties doesn't seem so uniform to me.
I don't understand the meaningfulness of 'whether background color is transparent'. How is a transparent background color useful?
Honestly, I don't know. For our use, we set it 'not transparent' when we want a background color, and set it 'transparent' when we don't.
What does 'whether text underline color is automatic' mean?
When it's automatic, it is the same with that of the font.
Why can't we specify the color of the text strikeout?
Honestly, I don't know. Anyway, the color is inevitably automatic.
All right.
We can handle spread sheet cell properties using 'com.sun.star.beans.XPropertySet' UNO interface implemented by the UNO component of the cell UNO object.
I see.
These are property names and values to be set.
- background color -> CellBackColor: an integer pixel value
- whether background color is transparent -> IsCellBackgroundTransparent: a boolean true (is transparent) or false (is not transparent)
- font color -> CharColor: an integer pixel value
- font name -> CharFontName: a font name string
- font size -> CharHeight: a float font point size
- font posture -> CharPosture: an enum com.sun.star.awt.FontSlant.(NONE, OBLIQUE, ITALIC, DONTKNOW, REVERSE_OBLIQUE, or REVERSE_ITALIC)
- font weight -> CharWeight: a float com.sun.star.awt.FontWeight.(DONTKNOW, THIN, ULTRALIGHT, LIGHT, SEMILIGHT, NORMAL, SEMIBOLD, BOLD, ULTRABOLD, or BLACK)
- text underline style -> CharUnderline: a short com.sun.star.awt.FontUnderline.(NONE, SINGLE, DOUBLE, DOTTED, DONTKNOW, DASH, LONGDASH, DASHDOT, DASHDOTDOT, SMALLWAVE, WAVE, DOUBLEWAVE, BOLD, BOLDDOTTED, BOLDDASH, BOLDLONGDASH, BOLDDASHDOT, BOLDDASHDOTDOT, or BOLDWAVE)
- whether text underline color is automatic -> CharUnderlineHasColor: a boolean true (is automatic) or false (is not automatic)
- text underline color -> CharUnderlineColor: an integer pixel value
- text overline style -> CharOverline: a short com.sun.star.awt.FontUnderline.(NONE, SINGLE, DOUBLE, DOTTED, DONTKNOW, DASH, LONGDASH, DASHDOT, DASHDOTDOT, SMALLWAVE, WAVE, DOUBLEWAVE, BOLD, BOLDDOTTED, BOLDDASH, BOLDLONGDASH, BOLDDASHDOT, BOLDDASHDOTDOT, or BOLDWAVE)
- whether text overline color is automatic -> CharOverlineHasColor: a boolean true (is automatic) or false (is not automatic)
- text overline color -> CharOverlineColor: an integer pixel value
- text strikeout style -> CharStrikeout: a short com.sun.star.awt.FontStrikeout.(NONE, SINGLE, DOUBLE, DONTKNOW, BOLD, SLASH, or X)
- whether text is wrapped -> IsTextWrapped: a boolean true (is wrapped) or false (is not wrapped)
- text horizontal alignment -> HoriJustify: an enum com.sun.star.table.CellHoriJustify.(STANDARD, LEFT, CENTER, RIGHT, BLOCK, or REPEAT)
- text vertical alignment -> VertJustify: an enum com.sun.star.table.CellVertJustify.(STANDARD, TOP, CENTER, or BOTTOM)
- left borderline -> LeftBorder: an instance of com.sun.star.table.BorderLine2
- right borderline -> RightBorder: an instance of com.sun.star.table.BorderLine2
- top borderline -> TopBorder: an instance of com.sun.star.table.BorderLine2
- bottom borderline -> BottomBorder: an instance of com.sun.star.table.BorderLine2
The 'com.sun.star.table.BorderLine2' struct has these members and each member takes these values.
- LineStyle: a short com.sun.star.table.BorderLineStyle.(NONE, SOLID, DOTTED, DASHED, DOUBLE, THINTHICK_SMALLGAP, THINTHICK_MEDIUMGAP, THINTHICK_LARGEGAP, THICKTHIN_SMALLGAP, THICKTHIN_MEDIUMGAP, THICKTHIN_LARGEGAP, EMBOSSED, ENGRAVED, OUTSET, INSET, FINE_DASHED, DOUBLE_THIN, DASH_DOT, DASH_DOT_DOT, or BORDER_LINE_STYLE_MAX)
- LineWidth: an integer line width in 1/100 mm
- Color: an integer pixel value
How can we get integer pixel values?
We can get them as returns of the 'getRGB' method of 'java.awt.Color'.
All right.
After all, I have written this code after the code of the previous article.
XPropertySet l_currentSpreadSheetCellInXPropertySet = (XPropertySet) UnoRuntime.queryInterface (XPropertySet.class, l_currentSpreadSheetCell);
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("CellBackColor", new Integer ( (new Color (255, 255, 0)).getRGB ()));
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("IsCellBackgroundTransparent", new Boolean (false));
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("CharColor", new Integer ( (new Color (0, 255, 0)).getRGB ()));
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("CharFontName", "Liberation Mono");
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("CharHeight", new Float (16.0F));
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("CharPosture", FontSlant.ITALIC);
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("CharWeight", new Float (FontWeight.BOLD));
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("CharUnderline", new Short (FontUnderline.DOTTED));
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("CharUnderlineHasColor", new Boolean (true));
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("CharUnderlineColor", new Integer ( (new Color (255, 0, 0)).getRGB ()));
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("CharOverline", new Short (FontUnderline.BOLDDOTTED));
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("CharOverlineHasColor", new Boolean (true));
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("CharOverlineColor", new Integer ( (new Color (255, 0, 0)).getRGB ()));
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("CharStrikeout", new Short (FontStrikeout.DOUBLE));
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("IsTextWrapped", new Boolean (true));
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("HoriJustify", CellHoriJustify.RIGHT);
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("VertJustify", CellVertJustify.BOTTOM);
BorderLine2 l_leftBorderline = new BorderLine2 ();
l_leftBorderline.LineStyle = BorderLineStyle.DOTTED;
l_leftBorderline.LineWidth = 34;
l_leftBorderline.Color = (new Color (255, 0, 0)).getRGB ();
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("LeftBorder2", l_leftBorderline);
BorderLine2 l_rightBorderline = new BorderLine2 ();
l_rightBorderline.LineStyle = BorderLineStyle.DASHED;
l_rightBorderline.LineWidth = 26;
l_rightBorderline.Color = (new Color (0, 255, 0)).getRGB ();
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("RightBorder2", l_rightBorderline);
BorderLine2 l_topBorderline = new BorderLine2 ();
l_topBorderline.LineStyle = BorderLineStyle.FINE_DASHED;
l_topBorderline.LineWidth = 18;
l_topBorderline.Color = (new Color (0, 0, 255)).getRGB ();
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("TopBorder2", l_topBorderline);
BorderLine2 l_bottomBorderline = new BorderLine2 ();
l_bottomBorderline.LineStyle = BorderLineStyle.DASH_DOT;
l_bottomBorderline.LineWidth = 52;
l_bottomBorderline.Color = (new Color (255, 0, 255)).getRGB ();
l_currentSpreadSheetCellInXPropertySet.setPropertyValue ("BottomBorder2", l_bottomBorderline);
I also have added these import directives.
import java.awt.Color;
import com.sun.star.table.CellHoriJustify;
import com.sun.star.table.CellVertJustify;
import com.sun.star.table.BorderLine2;
import com.sun.star.table.BorderLineStyle;
import com.sun.star.awt.FontSlant;
import com.sun.star.awt.FontWeight;
import com.sun.star.awt.FontUnderline;
import com.sun.star.awt.FontStrikeout;
You only set properties, not got them. When we want to get properties, we can just call the 'getPropertyValue' method, I presume?
Yes.
<The previous article in this series | The table of contents of this series | The next article in this series>
