Ask Your Question
0

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

asked 2015-03-05 16:59:38 +0200

peterbe gravatar image

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.

edit retag flag offensive close merge delete

Comments

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 gravatar imageLupp ( 2015-03-05 19:09:10 +0200 )edit

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.... and explore the implications. - Or someone else will explain.

Lupp gravatar imageLupp ( 2015-03-05 21:04:47 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2015-03-06 10:31:58 +0200

pierre-yves samyn gravatar image

updated 2015-03-06 10:49:43 +0200

karolus gravatar image

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
edit flag offensive delete link more

Comments

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

pierre-yves samyn gravatar imagepierre-yves samyn ( 2015-03-06 11:44:16 +0200 )edit

@pierre-yves samyn : only one single quote at the end of the ' Comment line ' for proper Syntaxhightlighting

karolus gravatar imagekarolus ( 2015-03-06 13:16:00 +0200 )edit

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

pierre-yves samyn gravatar imagepierre-yves samyn ( 2015-03-06 13:20:26 +0200 )edit

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 gravatar imagepeterbe ( 2015-03-06 15:00:27 +0200 )edit

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.

peterbe gravatar imagepeterbe ( 2015-03-06 15:09:17 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-03-05 16:59:38 +0200

Seen: 2,519 times

Last updated: Mar 06 '15