Calc Sheet Unique identifier?

English
, ,
#1

I am wondering if a sheet has a unique identifier, one that does not change when the sheet is renamed or moved in the document.

I found How to uniquely identify sheet in calc but it was not aswerred.

My current solution is add a hidden control with a unique generated name to a page as follows:

Uses OOO Development Tools

def _get_sheet_id(self) -> str:
    # need to get a unique id for the sheet.
    if not self._addr:
        raise Exception("_get_sheet_id() No cell selected")

    sheet = self._doc.sheets[self._addr.Sheet]
    if len(sheet.draw_page.forms) == 0:
        frm = sheet.draw_page.forms.add_form("Form1")
    else:
        frm = sheet.draw_page.forms[0]
    if frm.has_by_name(self._hidden_name):
        ctl = FormCtlHidden(frm.get_by_name(self._hidden_name), self._doc.lo_inst)
        sheet_id = cast(str, ctl.get_property("sheet_id"))
        return sheet_id
    ctl = frm.insert_control_hidden(name=self._hidden_name)
    ctl.hidden_value = "SheetId"
    str_id = gUtil.Util.generate_random_string(10)
    ctl.add_property("sheet_id", PropertyAttributeEnum.CONSTRAINED, str_id)
    return str_id

I don’t want to re-invent the wheel so I am wondering if there is another (existing) way?

Sheet User Defined Attributes wipe out cell user defined attributes
#2

See this topic: Codename.

#3

Thanks for this.


I was expecting the code name to be useable a variable name. But when I insert a sheet with the name of My Second Sheet the code name will contain the spaces as well. This makes it not so usable as a file name or variable name.


Inserting a sheet with the name of My (<some name>) & this! is allowed and results in a CodeName="My (<some name>) & this!"
I think will also implement the solution above as a reliable unique name that can be used in file/folder names and as a variable name.


image
image1143Ă—653 66.1 KB

#6

If this is exactly about what the subject asks, I would talk of a “life time unique identifier” in a similar sense as the StarDesktop uses the RuntimeUID but -as said- for the life time of the sheet.
Since I found the topic interesting, I made an example including some portions of the code I have on my system anyway. The “lifeTimeUID” part is new. There are two versions of which one creates the unique designators based on the position (index) the sheet has when getting it assigned. This is a bit complicated because previouslly set identifiers made for the same position need to be disambiguated.
The conceptually much simpler solution is to use an unambiguos date-time stamp. Since only UTC is sufficiently unambiguous, I included my related code.
I did not check for any features of Excel or try to get a workaround for any alien tools.
For both suggestions I used the property UserDefinedAttributes of the sheets. The syntax for the date-time stamp shall allow to also use it as an actual sheetname without complications.
See attached example. No warranty of any kind, of course.
ask106147SheetLifeTimeUID.ods (34.4 KB)

#4

I ended up adding a new property to ooodev.calc.CalcSheet of unique_id that get is value from this CalcSheetId class. Basically implements what I posted above.

I also added a code_name property that return the code name as is.

#5

Hallo
What the hell is that supposed to??
codename

#7

Althought the code name can be set. By implementing unique_id it assure the sheet id is not change by other extension or macros. I wanted an id that was not changeable ( at least not indended to be).

#8

A Spreadsheet has UserDefinedAttributes (XNameContainer) edit: – which I can’t fill with anything due to invalid argument exceptions.

#9

Have you taken a glance at the related code in my example?

#10

OMG! I did not see how this is related to UserDefinedAttributes. Yet another undocumented UNO shit.

#11

A few lines of documentation.

#12

Inspecting the UserDefinedAttributes, I land on LibreOffice: XNameContainer Interface Reference with method insertByName( [in] string aName, [in] any aElement ). There is no reference to anything xml related. The second argument is documented as “any” type. In fact it needs to be struct c.s.s.xml.AtributeData. Nothing points me to the allowed types of data.
Same with LibreOffice: XInitialization Interface Reference which could be very useful if it were documented.

#13

I am not employed as an advocate for the LibreOffice documentation as is, and I also find it occasionally very unsatisfactory.
However:

  • In the given case the “any aElement” should obviously be interpreted including something like “and probably depending on the use-case (see tree below)”. There you find the case AttributeContainer linked in my above comment. That was the way I found it using the search tool with the string “attribute”.
  • Human resources of TheDocumentFoundation are in short supply, Maintaining and enhancing the documentation competes with other tasks like bugfixing and working on enhancement requests.
  • I reported about 60 bugs and have in mind some more. About 30 were fixed, sometimes soon. Should I prefer the fixing of the others or the improvement of the documentation? I can’t do either myself because I don’t understand the source code.
  • Free OpenSource software may not survive if experienced users blame it again and again for doubtable reasons.
2 Likes
#14

The documentation for PageProperties states: :slightly_smiling_face:

UserDefinedAttributes contains user defined attributes.
This com.sun.star.container.XNameContainer supports the service com.sun.star.xml.AttributeContainer.

This note should be copied to the documentation for CellProperties.

I’ll write to the bug tracker.

#15

See Also: Sheet User Defined Attributes wipe out cell user defined attributes

#16

tdf#161347

1 Like

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