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 thickness.
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 Collabora Office 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.

Бібліотека ScriptForge