Set border and data validity for columns

Keyboard macros or custom scripts

Set border and data validity for columns

Postby strongleg » Thu Nov 23, 2017 3:14 pm

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!
OpenOffice 2.4 on Ubuntu 9.04
strongleg
 
Posts: 9
Joined: Sat Oct 28, 2017 4:41 pm

Re: set border and data validity for columns

Postby Lupp » Thu Nov 23, 2017 5:07 pm

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   Expand viewCollapse view
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.
On Windows 10: LibreOffice 5.4.2 and older versions, PortableOpenOffice 4.1.3 and older, StarOffice 5.2
---
Maybe we might! (Create a powerful UFO: United Free Office)
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 1523
Joined: Sat May 31, 2014 7:05 pm

Re: Set border and data validity for columns

Postby strongleg » Fri Nov 24, 2017 3:19 pm

still not work. Am I missing some thing in the following code:
Code: Select all   Expand viewCollapse view
         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,...
Last edited by strongleg on Fri Nov 24, 2017 4:16 pm, edited 1 time in total.
OpenOffice 2.4 on Ubuntu 9.04
strongleg
 
Posts: 9
Joined: Sat Oct 28, 2017 4:41 pm

Re: Set border and data validity for columns

Postby Villeroy » Fri Nov 24, 2017 4:12 pm

Have you ever used document templates?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24656
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Set border and data validity for columns

Postby Lupp » Fri Nov 24, 2017 4:19 pm

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.)
Attachments
aoo91320.ods
(10.37 KiB) Downloaded 8 times
Last edited by Lupp on Fri Nov 24, 2017 4:26 pm, edited 1 time in total.
On Windows 10: LibreOffice 5.4.2 and older versions, PortableOpenOffice 4.1.3 and older, StarOffice 5.2
---
Maybe we might! (Create a powerful UFO: United Free Office)
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 1523
Joined: Sat May 31, 2014 7:05 pm

Re: Set border and data validity for columns

Postby strongleg » Fri Nov 24, 2017 4:22 pm

Villeroy wrote: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?
OpenOffice 2.4 on Ubuntu 9.04
strongleg
 
Posts: 9
Joined: Sat Oct 28, 2017 4:41 pm

Re: Set border and data validity for columns

Postby Lupp » Fri Nov 24, 2017 4:24 pm

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.)
On Windows 10: LibreOffice 5.4.2 and older versions, PortableOpenOffice 4.1.3 and older, StarOffice 5.2
---
Maybe we might! (Create a powerful UFO: United Free Office)
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 1523
Joined: Sat May 31, 2014 7:05 pm

Re: Set border and data validity for columns

Postby strongleg » Fri Nov 24, 2017 4:26 pm

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

Lupp wrote: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.)
OpenOffice 2.4 on Ubuntu 9.04
strongleg
 
Posts: 9
Joined: Sat Oct 28, 2017 4:41 pm

Re: Set border and data validity for columns

Postby Villeroy » Fri Nov 24, 2017 5:30 pm

strongleg wrote: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?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24656
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 5 guests