Contenido
Índice 🔎︎

# Dar formato a bordes en Calc con macros

Es posible utilizar los lenguajes de programación BASIC y Python para escribir macros que apliquen formatos a intervalos de celdas en Calc.

## Dar formato a bordes en intervalos de celdas

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)
            ' Crea la estructura UNO que almacenará el formato de línea nuevo
            Dim lineFormat as New com.sun.star.table.BorderLine2
            lineFormat.LineStyle = newStyle
            lineFormat.LineWidth = newWidth
            If Not IsMissing(newColor) Then lineFormat.Color = newColor
            ' Obtiene la celda de destino
            Dim oCell as Object
            Set oCell = ThisComponent.CurrentController.ActiveSheet.getCellRangeByName(cellAddress)
            ' Aplica el formato nuevo a todos los bordes
            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 es un valor entero que define la anchura de la línea.

- 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
            ' Da acceso a las constantes de estilo de línea
            Dim cStyle as Object
            Set cStyle = com.sun.star.table.BorderLineStyle
            ' Da formato a «B5» de bordes azules sólidos
            FormatCellBorder("B5", cStyle.SOLID, 20, RGB(0, 0, 255))
            ' Formatea todos los bordes en el intervalo «D2:F6» con líneas punteadas rojas
            FormatCellBorder("D2:F6", cStyle.DOTTED, 20, RGB(255, 0, 0))
        End Sub

Es posible implementar la misma funcionalidad en Python:

        from uno import createUnoStruct
        from scriptforge import CreateScriptService

        def formatCellBorder(cellAddress, newStyle, newWidth, newColor=0):
            # Define el formato de línea nuevo
            line_format = createUnoStruct("com.sun.star.table.BorderLine2")
            line_format.LineStyle = newStyle
            line_format.LineWidth = newWidth
            line_format.Color = newColor
            # Servicio de ScriptForge para acceder a los intervalos de celdas
            doc = CreateScriptService("Calc")
            cell = doc.XCellRange(cellAddress)
            cell.TopBorder = line_format
            cell.RightBorder = line_format
            cell.LeftBorder = line_format
            cell.BottomBorder = line_format

El fragmento de código siguiente implementa una macro llamada myMacro que llama a 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))

El código Python presentado anteriormente utiliza la biblioteca ScriptForge que está disponible desde LibreOffice 7.2.

## Estilos de línea

Line styles are defined as integer constants. The table below lists the constants for the line styles available in Format - Cells - Borders:

Nombre de constante | Valor de entero | Nombre de estilo de línea
SOLID | 0 | Sólido
DOTTED | 1 | Punteado
DASHED | 2 | Discontinuo
FINE_DASHED | 14 | Fine dashed
DOUBLE_THIN | 15 | Double thin
DASH_DOT | 16 | Trazo-punto
DASH_DOT_DOT | 17 | Trazo-punto-punto

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

## Formato de bordes mediante 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
            ' Define el nuevo formato de línea
            Dim lineFormat as New com.sun.star.table.BorderLine2
            lineFormat.LineStyle = cStyle.SOLID
            lineFormat.LineWidth = 15
            lineFormat.Color = RGB(0, 0, 0)
            ' Estructura que almacena la nueva definición TableBorder2
            Dim tableFormat as New com.sun.star.table.TableBorder2
            tableFormat.TopLine = lineFormat
            tableFormat.BottomLine = lineFormat
            tableFormat.IsTopLineValid = True
            tableFormat.IsBottomLineValid = True
            ' Aplica el formato de tabla al intervalo «B2:E5»
            Dim oCell as Object
            oCell = ThisComponent.CurrentController.ActiveSheet.getCellRangeByName("B2:E5")
            oCell.TableBorder2 = tableFormat
        End Sub

La macro se puede implementar en Python de la manera siguiente:

        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.

Temas relacionados

La biblioteca ScriptForge