How can a Basic macro retrieve the format string applicable to a given cell?

English
,
#1

I wanted to get the format string for a given cell of a spreadsheet (ignoring any conditional formatting), i.e. the string that appears when we do Format·Cells... in the Numbers tab under Format Code, and could be used in the Format (value, format-string) function.

I inspected the cell object in the debugger, expecting to find this in a property, but the most promising thing I could see was FormatID, and at first I had no idea what that did.

Edit 2024-04-04 22:25 CEST
My ulterior objective is to format a value in a text in a dialogue as it would appear in the given cell.

#3

I suggest you to use the Styles instead of the direct (manual) formatting method.
It is a simple task to get the applied style (name) by macro from the cell object.

#9

I understand the value of styles, but I am looking for a more general purpose function.

#11

There is no more general, more efficient, more better feature, than the Styles are. You can handle and organize all of the properties by usage of the Styles.

#7

Use

format = ThisComponent.NumberFormats.getByKey(cell.NumberFormat).FormatString
#10

Thanks, that works fine. At some point I found NumberFormat and tried it, but I must have done something wrong, as my function failed — but now it works.

#2

With a bit of searching I found Managing Number Formats - Apache OpenOffice Wiki . Using this I got the impression that the required expression is:

    format = ThisComponent.NumberFormats.getByKey (cell.FormatID).FormatString

For a cell in a new spreadsheet this returns “General”, with FormatID = 0.

Edit 1
However, this sometimes produces a RuntimeException referring to /home/buildslave/source/libo-core/svl/source/numbers/numfmuno.cxx:366. In one such case, the FormatID is 2600.

Edit 2
Moreover, when I specify Percent, with Format Code “0.00%”, the FormatID is 1, which is succesfully looked up, but the FormatString is just “0” rather than “0.00%”.

#4

Let’s explore this together.
I can’t reproduce the example from Edit 2.
Could you upload the corresponding file to demonstrate the noted effects?


See also the “14.3.1. Formatting numbers and dates” section from the famous book by A. Pitonyak OOME_4_1.odt.

#5

What gave you the idea that a cell’s FormatID property would be the number format’s key ID? It is not. It is an ID of all attributes/formats of that cell, identically formatted cells have the same ID, but any difference yields a different ID. The only thing true in your assumption is that a completely unformatted cell in a new document has a FormatID of 0.


Btw, note that (that part of) the DevGuide is also available at LibreOffice Developer's Guide: Chapter 6 - Office Development - The Document Foundation Wiki, though the linking to UNO API .idl documentation (i.e. here LibreOffice: NumberFormats Service Reference and LibreOffice: XNumberFormats Interface Reference) isn’t working yet.

#6

And please do not use the Answer or Suggest a solution button for comments that are not an answer to the original question / solution to the problem, use the Reply or Comment bubble instead, or edit your original question to provide further details. Thanks.

#8

Are you still interested, given that my actual problem has been solved by erAck? If so I can try to upload a suitable file.

Thanks for the tip. It is 14.12.1 in the version I have! I hope to find time to have a look at that, though at a first glance it does not cover my exact requirement.

It was the first thing I saw with the debugger, and only thing I noticed that seemed to fit the bill: I somehow overlooked NumberFormat :frowning: Then (after a too skimpy test :frowning: ) it seemed to work, so I thought I had understood the rather scanty documentation.

Thanks for the tip, another time I shall try it (if I remember).

I am sorry about that: I thought I had understood the answer in the documentation and thus had a genuine solution, but discovered too late that I had not.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.