Set border and data validity for columns

[strongleg]

Q1: how to set border for all the cells of some columns? I try some code like:
border = BorderLine()
border.Color=0xffffff
...
cellRange.TopBorder=border
...
But get no luck, the border doesn't appear

Q2: how to set data validity for a column? eg, cells value in the column can only select from a predefine list, the list can hard code in the marco

sample in python would be preferred, thanks!

[Lupp]

You cannot set the border properties one by one.
For a CellRange object, say cR, you need to copy the structure cR.Table Border2, then to edit its fields consistently as needed and to assign the edited structure to cR.TableBorders2 again. So, at least I read from a text by Andrew Pitonyak - and got it to work in a few examples. Things are somehow convoluted as I see it, and I would shy back from writing code for the kind of task.
As an example of what I mean by "convoluted" you may look into https://api.libreoffice.org/docs/idl/re ... Style.html and into https://api.libreoffice.org/docs/idl/re ... 267aa5c300 (also valid for AOO). Dont't be afraid to get too much information. To the contrary you may have a lot of fun with "reverse engineering".
A simple example in BASIC to set a dashed top border of width 100 (1mm) for the current selection (if a single CellRange):

Code: Select all

Sub Test
theSel=ThisComponent.CurrentSelection
sTB=theSel.TableBorder2
With sTB.TopLine
 .LineStyle=2
 .LineWidth=100
End With
theSel.TableBorder2=sTB
End Sub

Sorry. I don't know anything about how to handle Data Validity by user code. (I rarely use it at all.)

Editing - just tested: Though the .Validation property of a CellRange is described as an object (instead of a structure) you can (and have to) do it in the same way as with table borders.
To guess the meaning of the subordinated properties reasonably should be easier.

[strongleg]

still not work. Am I missing some thing in the following code:

Code: Select all

			border = inst_sheet.getCellRangeByName('H3:H10').getPropertyValue("TableBorder2")
			logf.write('border: {}\n'.format(border))

			border.TopLine.Color = 0xffffff
			border.TopLine.InnerLineWidth=100
			border.TopLine.OuterLineWidth=100
			border.TopLine.LineDistance = 0
			border.TopLine.LineStyle = 0
			border.TopLine.LineWidth = 100
			border.IsTopLineValid = True

			border.Distance = 0
			border.IsDistanceValid = True

			logf.write('border: {}\n'.format(border))
			inst_sheet.getCellRangeByName('H3:H10').setPropertyValue("TableBorder2", border)

The two logf.write output same content.
border: (com.sun.star.table.TableBorder2){ TopLine = (com.sun.star.table.BorderLine2){ (com.sun.star.table.BorderLine){ Color = (long)0x0, InnerLineWidth = (short)0x0, OuterLineWidth = (short)0x0, LineDistance = (short)0x0 }, LineStyle = (short)0x0, LineWidth = (unsigned long)0x0 }, IsTopLineValid = (boolean)true,...

border: (com.sun.star.table.TableBorder2){ TopLine = (com.sun.star.table.BorderLine2){ (com.sun.star.table.BorderLine){ Color = (long)0x0, InnerLineWidth = (short)0x0, OuterLineWidth = (short)0x0, LineDistance = (short)0x0 }, LineStyle = (short)0x0, LineWidth = (unsigned long)0x0 }, IsTopLineValid = (boolean)true,...

[Villeroy]

Have you ever used document templates?

[Lupp]

I made a demo (see attachment) for you. Simply try it running the BASIC code.
I won't advise you how to do it in Java or in Python or in C++...
The very few user programming I do in LibO, I use BASIC as the native scripting language. Of course the BASIC itself is a poor programming language. The embedding is rather good, however, imo.

In fact I think your real problems are related to the poor documentation of the API. You will need to find out a lot about the sub-properties of the structures used for .TableBorders2 and .Validation by experiments ("reverse engineering" I called it) due to missing documentation. Bad. The bit needed for my demo cost me already half an hour at least. I can't help it. How to get or set a property is simple: Do it as the used scriping tool requires. If no success there at all: Change the tool.

(For your testing in the attached example: Select the respective columns and press Ctrl+M to clear tzhe results of the previous run. Also Validation is treated as a kind of direct formatting.)

[strongleg]

Have you ever used document templates?

I edit previous post to give my code and result.
what is document templates? is that something created by my macro or something created when I create the .ods file?

[Lupp]

A template is used to create a new document. There are default templates for each document type, and you can define user templates as you like ...
There are no editable templates for subordinate objects like shapes as far as I know...

(Try to learn about the fundamentals first.)

[strongleg]

yes, you're right. I'm not familiar with the API documentaion nor the python binding to the API. thanks , I'll try your attachment later

I made a demo (see attachment) for you. Simply try it running the BASIC code.
I won't advise you how to do it in Java or in Python or in C++...
The very few user programming I do in LibO, I use BASIC as the native scripting language. Of course the BASIC itself is a poor programming language. The embedding is rather good, however, imo.

In fact I think your real problems are related to the poor documentation of the API. You will need to find out a lot about the sub-properties of the structures used for .TableBorders2 and .Validation by experiments ("reverse engineering" I called it) due to missing documentation. Bad. The bit needed for my demo cost me already half an hour at least. I can't help it. How to get or set a property is simple: Do it as the used scriping tool requires. If not success there at all: Change the tool.

(For your testing in the attached example: Select the respective columns and press Ctrl+M to clear tzhe results of the previous run. Also Validation is treated as a kind of direct formatting.)

[Villeroy]

yes, you're right. I'm not familiar with the API documentaion nor the python binding to the API. thanks , I'll try your attachment later

You don't know the office application or how to work productively with any office application. Tempates are the most important feature of any office suite. A template is a document with prepared customizations such as validations, formattings, charts, predefined formulas, column widths, row heights, settings, anything you can declare in a document.
When you open a template (ott, ots, dot(x), xlt(x), ...), you get a new, unsaved document instead of the editable file. Then you add/edit the content of the new document and save it as a normal document (odt, ods, doc(x), xls(x),...)
Since 30 years office workers use office suites in highly sophisticated, customized and productive ways without writing any macros.

What do you know at all? Python? Spreadsheets?