How to set number of decimal places and font for a cell in Calc by macro

[peterbe]

I am trying to find out how to set a number of decimal places and font boldness for a cell in Calc by macro, but i did not succeed yet. In Microsoft Office VBA it can be done simply by:

Cells(row index, column index).NumberFormat = “0.000” (number of zeroes behind decimal point sets the number of decimal places, “0” for no decimal places)

Cells(row index, column index).Font.Bold = False
or
Cells(row index, column index).Font.Bold = True

I am using Libre Office Version 4.2.7.2 and Ubuntu 14.04 LTS. Thanks for help.

[Lupp]

Why macro? How call it? How pass parameters? Defining some cell styles and using them should be the means of choice - and apllying a style requires a simple doubleclick in the “stylist” (F11) for any selected range and can also be done with the help of the STYLE function. Old-fashioned Excel may not offer such perfect solutions and possibly rely more on direct formats. (They also may love VBA because of its enforcing their vendor lock-in …)

[Lupp]

To avoid misunderstandings: Applying named cell styles to cells or asking the cells for their (optional) CellStyle property (the value of which which is the cell-style’s name as a string) from a subroutine is simple. Applying a direct format is less simple. I virtually never did it and cannot tell more.
Maybe you want to study http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part1.pdf#page=98&zoom=auto,129.6,415.3 and explore the implications. - Or someone else will explain.

[PYS]

Hi

LibreOffice also allows to address cells by col & index.
Properties you want are : NumberFormat and CharWeight

You can get the list of values here (SDK API Reference)

LibreOffice manages formats based on local parameters (different decimal separator for example). A good practice is to test whether the format you wish to apply exists. If yes… it is applied, otherwise we create. Below is a complete example.

option explicit

Sub PysApplyFormat

Dim oCell As Object
Dim oLocalSettings As New com.sun.star.lang.Locale
Dim oNumberFormats As Object
Dim lKey as long 

' getCellByPosition(col,row) where col & row start 0 (B3: 1, 2)'

oCell = ThisComponent.Sheets.getByName("Sheet1").getCellByPosition(1,2)

oCell.charWeight = com.sun.star.awt.FontWeight.BOLD

oLocalSettings.language = "fr"
oLocalSettings.country = "fr"

oNumberFormats = thiscomponent.numberFormats
lKey = oNumberFormats.queryKey("0,000", oLocalSettings , true)

If lKey = -1 then 
	lKey = oNumberFormats.addNew("0,000", oLocalSettings)
end if

oCell.NumberFormat = lKey

End Sub

[PYS]

@karolus - I do not see what you updated?
Regards

[karolus]

@PYS : only one single quote at the end of the ’ Comment line ’ for proper Syntaxhightlighting

[PYS]

@karolus - thank you
I am not familiar with the Ask syntax (new here) , sorry…

[peterbe]

Thank you guys, it works OK. I am new to Libre Office and so it was difficult for me to find this out.
I have couple thousands lines of code in MS Office VBA and it wolud take a lot of time to rewrite it to work under Libre Office Basic.
I think setting the properties of a cell by macro that can be set manually by right licking a cell and left clicking Format Cell should be explained in Libre Office Basic help.

[peterbe]

I am disappointed that for referencing cells and setting their properties there is a need for much longer and complicated code than in MS Office VBA, thus programming in it for automatic data processing is much less clear and efficient. This should really be made more simple. For me is unfortunately better to stay with MS Office VBA for now.