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.