Have a macro put a border around a range of cells

[mapurves]

Using Open Office Macros Explained 3.0, I wrote a macro to draw a border around a range of cells, like C5:H15. I wanted just to set the outside edges of the range, but all the lines inside the range got borders. So, I changed the macro to set the borders just for each of the outside edges of the range, and then for the four corners. On the Format/Cell/Borders, there is an option for highlighting just the outside borders of a range. I’m hoping there is a variable something like OutsideRightBorder etc, that I can use to this.

Range.OutsideRightBorder = Border

I have hunted around but found nothing. As it is, my work-around does what I need, but it would be nice to have something more efficient.

[Hrbrgr]

Why do you need a macro for something like this?

[Villeroy]

My answer: Because the user interface is rubbish and styles don’t help here.

[Villeroy]

@mapurves

I have hunted around but found nothing.

You don’t find macros out in the wilderness. Macros need to be written by someone.

[Villeroy]

OK, here is my first try. It almost works. May someone else find the problem. For some reason, it resets a previously set border when setting the next border of a corner cell.
Fixed by "overpainting"

EDIT: Second try. Replaced yesterday’s file with a working version in Python.**

The macro applies 1, 2, 3 or 4 outer borders according to your definitions in a cell style named “Outer Borders” to arbitrary selections of ranges.

1. Download the attached text document to a trusted directory where document macros are allowed to be executed and click the install button
2. Open any spreadsheet document and define a cell style named “Outer Borders” with all the line styles, weight, colors etc for up to 4 borders.
3. Select arbitrary cells, columns, rows, ranges and call MyMacros>pyCalc>OuterBorders>applyOuterBorders.

OuterBorders2.py.odt (32.5 KB)
second version of OuterBorders.py.odt with massive code simplification thanks to @karolus

[schiavinatto]

Made with the macro recorder…

sub mapurves
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$Plan1.$C$5:$H$15"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
rem ----------------------------------------------------------------------
dim args2(12) as new com.sun.star.beans.PropertyValue
args2(0).Name = "OuterBorder.LeftBorder" : args2(0).Value = Array(0,0,26,0,0,26)
args2(1).Name = "OuterBorder.LeftDistance" : args2(1).Value = 0
args2(2).Name = "OuterBorder.RightBorder" : args2(2).Value = Array(0,0,26,0,0,26)
args2(3).Name = "OuterBorder.RightDistance" : args2(3).Value = 0
args2(4).Name = "OuterBorder.TopBorder" : args2(4).Value = Array(0,0,26,0,0,26)
args2(5).Name = "OuterBorder.TopDistance" : args2(5).Value = 0
args2(6).Name = "OuterBorder.BottomBorder" : args2(6).Value = Array(0,0,26,0,0,26)
args2(7).Name = "OuterBorder.BottomDistance" : args2(7).Value = 0
args2(8).Name = "InnerBorder.Horizontal" : args2(8).Value = Array(0,0,0,0,32767,0)
args2(9).Name = "InnerBorder.Vertical" : args2(9).Value = Array(0,0,0,0,32767,0)
args2(10).Name = "InnerBorder.Flags" : args2(10).Value = 0
args2(11).Name = "InnerBorder.ValidFlags" : args2(11).Value = 79
args2(12).Name = "InnerBorder.DefaultDistance" : args2(12).Value = 0
dispatcher.executeDispatch(document, ".uno:SetBorderStyle", "", 0, args2())
end sub

[Villeroy]

the following lines should be removed, so the recorded code works with any selection in any spreadsheet.

dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$Plan1.$C$5:$H$15"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

[schiavinatto]

Yes, but I fulfilled mapurves request

[mapurves]

That looks fine, but for readability of the code, I will stay with doing the four edges and then the four corners individually. I should have thought of using the macro recorder. I used it a few days ago to solve another question I had. As both Villeroy and I noted, you have to do the edges first, and then fill in the corners next.

Thank you, all

[mapurves]

We use LIbreCalc as a method for transcribing historic weather data from U.S. Coast Guard and Navy ships. We import an image of a log page from a ship, line up the spreadsheet grids with the grids on the log page and enter the data. The data are saved in an XML format, sent to the climate scientists and we move to the next page. We highlight various parts of the spreadsheets with different coloured fonts and borders to outline various parts of the logbook image. Currently we have 27 active spreadsheets. When we get a new set of logs, we set up a new spreadsheet using a previous when as a guide. Sometimes we forget to set fonts or borders properly, and this macro does that. We’re just making the setup of a new spreadsheet less labour intensive and less prone to errors. The logbook formats change every few years and sometimes from ship to ship.

[Villeroy]

Have a look at my new macro. I’ve replaced yesterday’s first try.

[karolus]

Hallo
Basically it is:

def create_table_border(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    global TABLEBORDER
    TABLEBORDER = doc.CurrentSelection.TableBorder2
### Todo: remove ugly global, and serialize the Struct TABLEBORDER into …json ###
    
def assign_table_border(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    doc.CurrentSelection.TableBorder2 = TABLEBORDER

first: run »create_table_border()« on a proper (outerBorder)-formatted selected Cellrange

later: run »assign_table_border()« on (optional multi-) selections in the same or other Calcdocs

[Villeroy]

Thank you very much. I overlooked the “TableBorder2” property. This boils down my code which still reads the border properties from a user defined cell style:

import uno
gStyleName = "Outer Borders"

def applyOuterBorders(*args):
    ThisComponent = XSCRIPTCONTEXT.getDocument()
    sel = ThisComponent.getCurrentSelection()
    fam = ThisComponent.StyleFamilies.getByName("CellStyles")
    if not fam.hasByName(gStyleName):
        return
    st = fam.getByName(gStyleName)
    sel.TableBorder = st.TableBorder

I replaced the attachment in yesterday’s posting again.
For those who want Basic code:

Const gStyleName = "Outer Borders"

Sub applyOuterBorders()
    sel = ThisComponent.getCurrentSelection()
    fam = ThisComponent.StyleFamilies.getByName("CellStyles")
    if not fam.hasByName(gStyleName) then exit sub
    st = fam.getByName(gStyleName)
    sel.TableBorder = st.TableBorder
End Sub

simple as that.

[vib]

Python answer.

Borders around cells are cell ranges is super simple using OOO Development Tools (OooDev)

See Help Calc Direct Cell Borders

Just a few lines of code will do the job.

rng_obj = Calc.get_range_obj("B2:F6")
cr = Calc.get_cell_range(sheet, rng_obj)
borders = Borders(
    border_side=Side(color=CommonColor.BLUE),
    horizontal=Side(line=BorderLineKind.DASH_DOT_DOT, color=CommonColor.GREEN),
    vertical=Side(line=BorderLineKind.DOUBLE, color=CommonColor.RED),
)
Styler.apply(cr, borders)