I’d like to query the formula syntax that is set in Tools>Options>Calc>Formula syntax.
It is not org.openoffice.Calc/Formula/Syntax/StringRefAddressSyntax because that does not change with the above option.
Is there any service providing this? An instance of c.s.s.sheet.FormulaParser starts with FormulaConvention -1, which effectively means that it follows the UI setting.
(I think) It is.
It should be noted that this parameter can be specified in the settings.xml file (file archive) of the Calc document, and when viewing the parameters, we see this parameter from the current document (and not the one set in the application).
' lang:en
' Sets (optional) and returns the syntax number for cell references in Calc formulas.
' - oDoc document
' - ref target syntax number (see below). If not specified or Empty, the document syntax is not changed
'
' Returns the syntax number or Empty:
' 0 Calc A1
' 2 Excel A1
' 3 Excel R1C1
' 7 Calc A1 and Excel A1
' Empty Not set
'
' In syntax 7, both "." and "!" can be used in cell references (e.g. in the Indirect function).
Function Doc_Syntax(Byval oDoc as Object, Optional Byval ref)
Dim oDocSet as Object, v
If IsMissing(ref) Then ref=Empty
oDocSet=oDoc.createInstance("com.sun.star.sheet.DocumentSettings")
v=oDocSet.getPropertyValue("SyntaxStringRef")
If (Not IsEmpty(ref)) And ref<>v Then
v=ref
oDocSet.setPropertyValue "SyntaxStringRef", v
oDoc.calculateAll()
End If
Doc_Syntax=v
End Function
Sub Test
Msgbox Doc_Syntax(ThisComponent)
End Sub
This may set the formula syntax for a document, but not globally.
I don’t want to set anything. I do not even want to manipulate any spreadsheet. All I want to do is reading the currently active formula syntax, so I can adjust one particular address string to be displayed in the currently active syntax.
OK, I’m going to use a c.s.s.sheet.FormulaParser and feed it with SingleReference token, derived from a cell address. By default, a new parser instance uses the currently active syntax.
Formula/Syntax/Grammaris the right one.
My Formula/Syntax/StringRefAddressSyntax is something different.
I think I can solve my problem with a FormulaParser.
Thanks anyway.
Is the correct answer. I used the wrong search terms.
However, a cell address can be retrieved from a FormulaParser which works with FormulaConvention -1 (unspecified) which defaults to the current UI syntax. This way I don’t even need to know anything about the formula syntax.
Function getCellLabel(adr)
REM get an absolute cell address string with sheet name
REM in the parser's formula syntax
oParser = ThisComponent.createInstance("com.sun.star.sheet.FormulaParser")
Dim tk as new com.sun.star.sheet.FormulaToken
Dim cell as new com.sun.star.sheet.SingleReference
Dim lFlag As Long
lFlag = com.sun.star.sheet.ReferenceFlags.SHEET_3D
cell.Flags = lFlag
cell.Column = adr.Column
cell.RelativeColumn = 0
cell.Row = adr.Row
cell.RelativeRow = 0
cell.Sheet = adr.Sheet
cell.RelativeSheet = 0
tk.OpCode = 0
tk.Data = cell
s = oParser.printFormula(Array(tk), adr)
getCellLabel = s
End Function(post deleted by author)
You must consult the node:
/org.openoffice.Office.Calc/Formula/Syntax
node = '/org.openoffice.Office.Calc/Formula/Syntax'
grammar = app.get_app_config(node, 'Grammar')
app.debug(grammar)
soffice --calc
2025/06/29 11:37:51 - DEBUG - 0
Look get_app_config in:
Thank you. Actually, I asked the wrong question. I need to get a cell address string in whatever syntax is the active one. A parser does that perfectly well, because I do not even need to know anything about the current UI syntax.