Set border and data validity for columns
Set border and data validity for columns
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!
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
Re: set border and data validity for columns
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):
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.
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
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 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Set border and data validity for columns
still not work. Am I missing some thing in the following code:
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,...
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)
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
Re: Set border and data validity for columns
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 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Set border and data validity for columns
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.)
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 277 times
Last edited by Lupp on Fri Nov 24, 2017 4:26 pm, edited 1 time in total.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Set border and data validity for columns
I edit previous post to give my code and result.Villeroy wrote:Have you ever used document templates?
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
Re: Set border and data validity for columns
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.)
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 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Set border and data validity for columns
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
Re: Set border and data validity for columns
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.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
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 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice