From ed5640d8b587fbcfed7dd7967f3de04b37a76f26 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sun, 7 Apr 2024 11:06:44 +0200 Subject: Adding upstream version 4:7.4.7. Signed-off-by: Daniel Baumann --- .../source/text/sbasic/guide/calc_borders.xhp | 248 +++++++++++++++++++++ 1 file changed, 248 insertions(+) create mode 100644 helpcontent2/source/text/sbasic/guide/calc_borders.xhp (limited to 'helpcontent2/source/text/sbasic/guide/calc_borders.xhp') diff --git a/helpcontent2/source/text/sbasic/guide/calc_borders.xhp b/helpcontent2/source/text/sbasic/guide/calc_borders.xhp new file mode 100644 index 000000000..23fa7b885 --- /dev/null +++ b/helpcontent2/source/text/sbasic/guide/calc_borders.xhp @@ -0,0 +1,248 @@ + + + + + + + Formatting Borders in Calc with Macros + /text/sbasic/guide/calc_borders.xhp + + + + + + macros;format borders + +

Formatting Borders in Calc with Macros

+ By using Basic or Python programming languages it is possible to write macros that apply formats to ranges of cells in Calc. + +

Formatting Borders in Ranges of Cells

+ The code snippet below creates a Sub called FormatCellBorder that applies new border formats to a given range address in the current Calc sheet. + + Sub FormatCellBorder(cellAddress as String, newStyle as Byte, newWidth as Long, Optional newColor as Long) + ' Creates the UNO struct that will store the new line format + Dim lineFormat as New com.sun.star.table.BorderLine2 + lineFormat.LineStyle = newStyle + lineFormat.LineWidth = newWidth + If Not IsMissing(newColor) Then lineFormat.Color = newColor + ' Gets the target cell + Dim oCell as Object + Set oCell = ThisComponent.CurrentController.ActiveSheet.getCellRangeByName(cellAddress) + ' Applies the new format to all borders + oCell.TopBorder = lineFormat + oCell.RightBorder = lineFormat + oCell.LeftBorder = lineFormat + oCell.BottomBorder = lineFormat + End Sub + + The Sub described above takes in four arguments: + + + cellAddress is a string denoting the range to be formatted in the format "A1". + + + newStyle is an integer value that corresponds to the border line style (see Line Styles below). + + + newWidth is an integer value that defines the line width. + + + newColor is an integer value corresponding to a color defined using the RGB function. + + + To call FormatCellBorder create a new macro and pass the desired arguments, as shown below: + + Sub MyMacro + ' Gives access to the line style constants + Dim cStyle as Object + Set cStyle = com.sun.star.table.BorderLineStyle + ' Formats "B5" with solid blue borders + FormatCellBorder("B5", cStyle.SOLID, 20, RGB(0, 0, 255)) + ' Formats all borders in the range "D2:F6" with red dotted borders + FormatCellBorder("D2:F6", cStyle.DOTTED, 20, RGB(255, 0, 0)) + End Sub + + It is possible to implement the same functionality in Python: + + from uno import createUnoStruct + from scriptforge import CreateScriptService + + def formatCellBorder(cellAddress, newStyle, newWidth, newColor=0): + # Defines the new line format + line_format = createUnoStruct("com.sun.star.table.BorderLine2") + line_format.LineStyle = newStyle + line_format.LineWidth = newWidth + line_format.Color = newColor + # Scriptforge service to access cell ranges + doc = CreateScriptService("Calc") + cell = doc.XCellRange(cellAddress) + cell.TopBorder = line_format + cell.RightBorder = line_format + cell.LeftBorder = line_format + cell.BottomBorder = line_format + + The code snippet below implements a macro named myMacro that calls formatCellBorder: + + from com.sun.star.table import BorderLineStyle as cStyle + + def myMacro(): + bas = CreateScriptService("Basic") + formatCellBorder("B5", cStyle.SOLID, 20, bas.RGB(0, 0, 255)) + formatCellBorder("D2:F6", cStyle.DOTTED, 20, bas.RGB(255, 0, 0)) + + The Python code presented above uses the ScriptForge library that is available since %PRODUCTNAME 7.2. + +

Line Styles

+ Line styles are defined as integer constants. The table below lists the constants for the line styles available in Format - Cells - Borders: + + + + Constant name + + + Integer value + + + Line style name + + + + + SOLID + + + 0 + + + Solid + + + + + DOTTED + + + 1 + + + Dotted + + + + + DASHED + + + 2 + + + Dashed + + + + + FINE_DASHED + + + 14 + + + Fine dashed + + + + + DOUBLE_THIN + + + 15 + + + Double thin + + + + + DASH_DOT + + + 16 + + + Dash dot + + + + + DASH_DOT_DOT + + + 17 + + + Dash dot dot + + +

+ Refer to the BorderLineStyle Constant Reference in the LibreOffice API documentation to learn more about line style constants. + +

Formatting Borders Using TableBorder2

+ Range objects have a property named TableBorder2 that can be used to format range borders as it is done in the Format - Cells - Borders dialog in the Line Arrangement section. + In addition to top, bottom, left and right borders, TableBorder2 also defines vertical and horizontal borders. The macro below applies only the top and bottom borders to the range "B2:E5". + + Sub TableBorder2Example + Dim cStyle as Object + Set cStyle = com.sun.star.table.BorderLineStyle + ' Defines the new line format + Dim lineFormat as New com.sun.star.table.BorderLine2 + lineFormat.LineStyle = cStyle.SOLID + lineFormat.LineWidth = 15 + lineFormat.Color = RGB(0, 0, 0) + ' Struct that stores the new TableBorder2 definition + Dim tableFormat as New com.sun.star.table.TableBorder2 + tableFormat.TopLine = lineFormat + tableFormat.BottomLine = lineFormat + tableFormat.IsTopLineValid = True + tableFormat.IsBottomLineValid = True + ' Applies the table format to the range "B2:E5" + Dim oCell as Object + oCell = ThisComponent.CurrentController.ActiveSheet.getCellRangeByName("B2:E5") + oCell.TableBorder2 = tableFormat + End Sub + + The macro can be implemented in Python as follows: + + from com.sun.star.table import BorderLineStyle as cStyle + from scriptforge import CreateScriptService + + def tableBorder2Example(): + bas = CreateScriptService("Basic") + line_format = createUnoStruct("com.sun.star.table.BorderLine2") + line_format.LineStyle = cStyle.SOLID + line_format.LineWidth = 18 + line_format.Color = bas.RGB(0, 0, 0) + table_format = createUnoStruct("com.sun.star.table.TableBorder2") + table_format.TopLine = line_format + table_format.BottomLine = line_format + table_format.IsTopLineValid = True + table_format.IsBottomLineValid = True + doc = CreateScriptService("Calc") + cell = doc.XCellRange("B2:E5") + cell.TableBorder2 = table_format + + Refer to the TableBorder2 Struct Reference in the LibreOffice API documentation to learn more about its attributes. + +

+ + -- cgit v1.2.3