Sheet User Defined Attributes wipe out cell user defined attributes

English
, ,
#1

This is related to Calc Sheet Unique identifier?

I have been testing User defined attributes for Calc Cells.
At first setting the attributes for a cell seemed to work fine.
When the spreadsheet user defined attributes are set after the cell user defined attributes the cell user defined attributes get wiped.
Sorry for the long post but I want to communicate the issue clearly.

In these python examples I am using OOO Development Tools but the setting of the UDA (user defined attributes) is essentially all straight API calls.

Am I missing something here?

Example set cell UDA only

Python

from __future__ import annotations
from typing import cast, TYPE_CHECKING
from pathlib import Path
import uno
from com.sun.star.xml import AttributeData
from ooodev.loader import Lo
from ooodev.calc import CalcDoc

if TYPE_CHECKING:
    from com.sun.star.xml import AttributeContainer


def main():
    with Lo.Loader(connector=Lo.ConnectSocket()):
        doc = CalcDoc.create_doc(visible=True)
        sheet = doc.sheets[0]

        cell = sheet[0, 0]
        assert cell is not None
        cell.value = "Hello World!"
        container = cast("AttributeContainer", cell.component.UserDefinedAttributes)
        if container.hasByName("attr1"):
            container.removeByName("attr1")
        ad = AttributeData()  # onnly CDATA which is the default for Type seems to work
        ad.Value = "25"
        container.insertByName("attr1", ad)
        cell.component.UserDefinedAttributes = container
        obj = cast(AttributeData, container.getByName("attr1"))
        assert obj.Value == "25"

        fnm = Path().cwd() / "tmp" / "uda.ods"
        doc.save_doc(fnm)
        doc.close()


if __name__ == "__main__":
    main()

XML

From saved document internal content.xml

<!-- other xml -->
<office:automatic-styles>
<!-- other xml -->
  <style:style style:name="ce1" style:family="table-cell" style:parent-style-name="Default">
    <style:table-cell-properties attr1="25"/>
  </style:style>
</office:automatic-styles>

<!-- other xml -->
<table:table table:name="Sheet1" table:style-name="ta1">
  <table:table-column table:style-name="co1" table:default-cell-style-name="ce1"/>
  <table:table-row table:style-name="ro1">
    <table:table-cell office:value-type="string" calcext:value-type="string">
      <text:p>Hello World!</text:p>
    </table:table-cell>
  </table:table-row>
</table:table>

Example set Sheet UDA First (working)

Python

from __future__ import annotations
from typing import cast, TYPE_CHECKING
from pathlib import Path
import uno
from com.sun.star.xml import AttributeData
from ooodev.loader import Lo
from ooodev.calc import CalcDoc

if TYPE_CHECKING:
    from com.sun.star.xml import AttributeContainer


def main():
    with Lo.Loader(connector=Lo.ConnectSocket()):
        doc = CalcDoc.create_doc(visible=True)
        sheet = doc.sheets[0]

        sheet_uda = sheet.component.UserDefinedAttributes
        if sheet_uda.hasByName("sheet_attr"):
            sheet_uda.removeByName("sheet_attr")
        ad = AttributeData()
        ad.Value = "Sheet Attribute"
        sheet_uda.insertByName("sheet_attr", ad)
        sheet.component.UserDefinedAttributes = sheet_uda
        obj = cast(AttributeData, sheet_uda.getByName("sheet_attr"))
        assert obj is not None
        assert obj.Value == "Sheet Attribute"

        cell = sheet[0, 0]
        assert cell is not None
        cell.value = "Hello World!"
        container = cast("AttributeContainer", cell.component.UserDefinedAttributes)
        if container.hasByName("attr1"):
            container.removeByName("attr1")
        ad = AttributeData()  # onnly CDATA which is the default for Type seems to work
        ad.Value = "25"
        container.insertByName("attr1", ad)
        cell.component.UserDefinedAttributes = container
        obj = cast(AttributeData, container.getByName("attr1"))
        assert obj is not None
        assert obj.Value == "25"

        fnm = Path().cwd() / "tmp" / "uda.ods"
        doc.save_doc(fnm)
        doc.close()
        return

if __name__ == "__main__":
    main()

XML

It is interesting that the sheet UDA are assigned to each cell but still this is working.

content.xml

<!-- other xml -->
<office:automatic-styles>
  <!-- other xml -->
  <style:style style:name="ce1" style:family="table-cell" style:parent-style-name="Default">
    <style:table-cell-properties sheet_attr="Sheet Attribute" attr1="25"/>
  </style:style>
  <style:style style:name="ce2" style:family="table-cell" style:parent-style-name="Default">
    <style:table-cell-properties sheet_attr="Sheet Attribute"/>
  </style:style>
</office:automatic-styles>

<!-- other xml -->
<table:table table:name="Sheet1" table:style-name="ta1">
  <table:table-column table:style-name="co1" table:number-columns-repeated="16384" table:default-cell-style-name="ce2"/>
  <table:table-row table:style-name="ro1">
    <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string">
      <text:p>Hello World!</text:p>
    </table:table-cell>
    <table:table-cell table:number-columns-repeated="16383"/>
  </table:table-row>
  <table:table-row table:style-name="ro1" table:number-rows-repeated="1048574">
    <table:table-cell table:number-columns-repeated="16384"/>
  </table:table-row>
  <table:table-row table:style-name="ro1">
    <table:table-cell table:number-columns-repeated="16384"/>
  </table:table-row>

Example NOT working, Assign sheet UDA after Cell UDA

Assign UDA for 2 cells, assign sheet UDA, assign UDA to third cell and save document.
The result is only the third cell keeps it UDA. The first two cells get UDA wiped.

Python

from __future__ import annotations
from typing import cast, TYPE_CHECKING
from pathlib import Path
import uno
from com.sun.star.xml import AttributeData
from ooodev.loader import Lo
from ooodev.calc import CalcDoc

if TYPE_CHECKING:
    from com.sun.star.xml import AttributeContainer


def main():
    with Lo.Loader(connector=Lo.ConnectSocket()):
        doc = CalcDoc.create_doc(visible=True)
        sheet = doc.sheets[0]

        cell = sheet[0, 0]
        assert cell is not None
        cell.value = "Hello World!"
        container = cast("AttributeContainer", cell.component.UserDefinedAttributes)
        if container.hasByName("attr1"):
            container.removeByName("attr1")
        ad = AttributeData()  # onnly CDATA which is the default for Type seems to work
        ad.Value = "25"
        container.insertByName("attr1", ad)
        cell.component.UserDefinedAttributes = container
        obj = cast(AttributeData, container.getByName("attr1"))
        assert obj is not None
        assert obj.Value == "25"

        cell = sheet[2, 2]
        cell.value = "Nice Day!"
        container = cast("AttributeContainer", cell.component.UserDefinedAttributes)
        if container.hasByName("attr2"):
            container.removeByName("attr2")
        ad = AttributeData()
        ad.Value = "This is It"
        container.insertByName("attr2", ad)
        cell.component.UserDefinedAttributes = container
        obj = cast(AttributeData, container.getByName("attr2"))
        assert obj is not None
        assert obj.Value == "This is It"

        sheet_uda = sheet.component.UserDefinedAttributes
        if sheet_uda.hasByName("sheet_attr"):
            sheet_uda.removeByName("sheet_attr")
        ad = AttributeData()
        ad.Value = "Sheet Attribute"
        sheet_uda.insertByName("sheet_attr", ad)
        sheet.component.UserDefinedAttributes = sheet_uda
        obj = cast(AttributeData, sheet_uda.getByName("sheet_attr"))
        assert obj is not None
        assert obj.Value == "Sheet Attribute"

        cell = sheet[4, 4]
        cell.value = "Last Chance!"
        container = cast("AttributeContainer", cell.component.UserDefinedAttributes)
        if container.hasByName("attr3"):
            container.removeByName("attr3")
        ad = AttributeData()
        ad.Value = "This should stick"
        container.insertByName("attr3", ad)
        cell.component.UserDefinedAttributes = container
        obj = cast(AttributeData, container.getByName("attr3"))
        assert obj is not None
        assert obj.Value == "This should stick"

        fnm = Path().cwd() / "tmp" / "uda.ods"
        doc.save_doc(fnm)
        doc.close()


if __name__ == "__main__":
    main()

XML

The UDA for the first two cell (“A1”, “C3” ) have no UDA when the document is saved but the last cell (“E5”) has UDA value. The value is contained in ce2 of the styles section.

<!-- other xml -->
<office:automatic-styles>
  <!-- other xml -->
  <style:style style:name="ce1" style:family="table-cell" style:parent-style-name="Default">
    <style:table-cell-properties sheet_attr="Sheet Attribute"/>
  </style:style>
  <style:style style:name="ce2" style:family="table-cell" style:parent-style-name="Default">
    <style:table-cell-properties sheet_attr="Sheet Attribute" attr3="This should stick"/>
  </style:style>
</office:automatic-styles>

<table:table table:name="Sheet1" table:style-name="ta1">
  <table:table-column table:style-name="co1" table:number-columns-repeated="16384" table:default-cell-style-name="ce1"/>
  <table:table-row table:style-name="ro1">
    <table:table-cell office:value-type="string" calcext:value-type="string">
      <text:p>Hello World!</text:p>
    </table:table-cell>
    <table:table-cell table:number-columns-repeated="16383"/>
  </table:table-row>
  <table:table-row table:style-name="ro1">
    <table:table-cell table:number-columns-repeated="16384"/>
  </table:table-row>
  <table:table-row table:style-name="ro1">
    <table:table-cell table:number-columns-repeated="2"/>
    <table:table-cell office:value-type="string" calcext:value-type="string">
      <text:p>Nice Day!</text:p>
    </table:table-cell>
    <table:table-cell table:number-columns-repeated="16381"/>
  </table:table-row>
  <table:table-row table:style-name="ro1">
    <table:table-cell table:number-columns-repeated="16384"/>
  </table:table-row>
  <table:table-row table:style-name="ro1">
    <table:table-cell table:number-columns-repeated="4"/>
    <table:table-cell table:style-name="ce2" office:value-type="string" calcext:value-type="string">
      <text:p>Last Chance!</text:p>
    </table:table-cell>
    <table:table-cell table:number-columns-repeated="16379"/>
  </table:table-row>
  <table:table-row table:style-name="ro1" table:number-rows-repeated="1048570">
    <table:table-cell table:number-columns-repeated="16384"/>
  </table:table-row>
  <table:table-row table:style-name="ro1">
    <table:table-cell table:number-columns-repeated="16384"/>
  </table:table-row>
</table:table>
Calc Sheet Unique identifier?
Calc cell Unique Identifier, is this possible?
Calc cell Unique Identifier, is this possible?
#2

As far as I can see, you did not specify any NameSpace in your attribute data.

#3

When I tried namespaces they did not work.

#4

Try with namespace. Attribute is just ignored.


Python

from __future__ import annotations
from typing import cast, TYPE_CHECKING
from pathlib import Path
import uno
from com.sun.star.xml import AttributeData
from ooodev.loader import Lo
from ooodev.calc import CalcDoc

if TYPE_CHECKING:
    from com.sun.star.xml import AttributeContainer


def main():
    with Lo.Loader(connector=Lo.ConnectSocket()):
        doc = CalcDoc.create_doc(visible=True)
        sheet = doc.sheets[0]

        cell = sheet[0, 0]
        assert cell is not None
        cell.value = "Hello World!"
        container = cast("AttributeContainer", cell.component.UserDefinedAttributes)
        if container.hasByName("attr1"):
            container.removeByName("attr1")
        ad = AttributeData()  # onnly CDATA which is the default for Type seems to work
        ad.Value = "25"
        ad.Namespace = "com.somwhere.else"
        container.insertByName("attr1", ad)
        cell.component.UserDefinedAttributes = container

        fnm = Path().cwd() / "tmp" / "uda.ods"
        doc.save_doc(fnm)
        doc.close()


if __name__ == "__main__":
    main()

XML

<!-- other xml -->
<office:automatic-styles>
  <!-- other xml -->
  <style:style style:name="ta1" style:family="table" style:master-page-name="Default">
    <style:table-properties table:display="true" style:writing-mode="lr-tb"/>
  </style:style>
  <style:style style:name="ce1" style:family="table-cell" style:parent-style-name="Default"/>
</office:automatic-styles>

<table:table table:name="Sheet1" table:style-name="ta1">
  <table:table-column table:style-name="co1" table:default-cell-style-name="ce1"/>
  <table:table-row table:style-name="ro1">
    <table:table-cell office:value-type="string" calcext:value-type="string">
      <text:p>Hello World!</text:p>
    </table:table-cell>
  </table:table-row>
</table:table>
#5

From my testing:
The interference is both directions.
There seems to be only one object UserDefinedAttributes per sheet, and If it gets assigned to a cell, it’s no longer available from the sheet and vice versa. I didn’t test every detail, though.

Try a bug report.

Yes. The issue may destroy the value of the approach I suggested there.
In addition: If a sheet gets copied as one object, an identifier it had will no longer be unnique.

#6

Please clarify the question.
UserDefinedAttributes is a public attribute of the CellProperties interface, it behaves the same as other attributes (CellStyle, CellColor, …).
What do you see as the problem?

#7

image

Yes indeed. The cell properties are implemented in may other classes as the image above indicates.


In this tread I am stating that when the UserDefinedAttributes is set on a Sheet cell the expected behavour is that it would not affect the settings for the Spreadsheet UserDefinedAttributes and vis versa.


This is not what is happening. Setting one or cells UserDefinedAttributes and then setting the Sheets UserDefinedAttributes causes the previous cells UserDefinedAttributes to be wipped out. This demonstrated clearly in the above post.

#8

This is the same behavior for other CellProperties attributes.
When, for example, you assign CellColor to a range of cells, this property is assigned to all cells in the range. For a sheet, this means assigning a property to all cells in the sheet (the sheet is interpreted as a range of cells).


Essentially, UserDefinedAttributes are formatting elements, so it’s a bad idea to use this attribute to store information about specific cells - it leads to an inefficient .ods file structure.

#9

So this is a feature and not a bug?

#10

Feature (in my opinion).

#11

I wouldn’t see it this way.
Let me talk of CellBackColor which is also a property among those listed for the CellProperties service.
If you take the complete sheet as one SheetCellRange you can assign a value to that property with effect for all its cells. Nonetheless you can still set a different CellBackColor later for any smaller SheetCellRange or any single cell - as well via the UI as based on user code - without afflicting the color of other cells.
To assign a UserDefinedAttributes structure to the sheet with the effect to have assigned it to every contined cell does obviously not make sense - and is also not actually done.
If the property is a SheetCell property, but not a Spreadsheet property, an assignment to the sheet must be blocked. If it also is available as a sheet property the setting must be able to co-exist with all the settings for single cells.
I agree that this is not clearly specified in the service/interface diagram, but I would judge this to be a bug anyway. If not a bug in the implementation then one in the specification or even in the “architecture” of the application.
Completely inacceptable I find the fact (meanwhile thoroughly tested under 24.2.2.2) that the assignment of a user defined attribute to any single cell has the effect that every cell of the same sheet also shows that attribute.
I can only strictly dissuade from using this property.
:::
@vib:
If this is in any way related to Apache OpenOffice Community Forum - [Solved] UserDefinedAttributes property of Calc cell - (View topic) you should definitely abandon the usahe of attributes. There are much better ways.

#12

Let’s check it together.

Option Explicit
Sub TestProperty()
  Dim oDoc As Object, oSheet As Object, oRange as Object, oUserData As Object, propName As String
  Dim oMyAttribute As New com.sun.star.xml.AttributeData
  oDoc = StarDesktop.loadComponentFromURL( "private:factory/scalc", "_blank", 0, Array())
  oSheet=oDoc.Sheets(0)
  
  oMyAttribute.Type = "CDATA"
  oMyAttribute.Value = "Andrew Pitonyak"
  propName="Andy"  

  ' Set UserDefinedAttribute "Andy" for oSheet
  oUserData= oSheet.UserDefinedAttributes
  oUserData.insertByName(propName, oMyAttribute)
  oSheet.UserDefinedAttributes=oUserData
  ShowUDP oSheet.GetCellRangeByName("A2"), propName  ' shows "Andrew Pitonyak"
  
  ' Change UserDefinedAttribute "Andy" for cell "A3"
  oMyAttribute.Value = "Andrew Lloyd Webber"
  oRange=oSheet.GetCellRangeByName("A3")
  oUserData= oRange.UserDefinedAttributes
  oUserData.ReplaceByName(propName, oMyAttribute)
  oRange.UserDefinedAttributes=oUserData
  
  ShowUDP oSheet.GetCellRangeByName("A2"), propName   ' shows "Andrew Pitonyak"
  ShowUDP oRange, propName                            ' shows "Andrew Lloyd Webber"
End Sub

Sub ShowUDP(ByVal oRange As Object, Byval propName As String)
  Dim oUserData As Object, propVal
  oUserData=oRange.UserDefinedAttributes 
  If oUserData.hasByName(propName) Then propVal=oUserData.getByName(propName).Value
  Msgbox oRange.AbsoluteName & ": " & propVal,,"UserDefinedAttributes: " & propName
End Sub

Version: 7.6.6.3 (X86_64) / LibreOffice Community
Build ID: d97b2716a9a4a2ce1391dee1765565ea469b0ae7
CPU threads: 6; OS: Windows 10.0 Build 19045; UI render: default; VCL: win
Locale: ru-RU (ru_RU); UI: en-US
Calc: threaded

#13

@Lupp, here is another example of how we can work carefully with UserDefinedAttribute of cells and ranges.
Two attributes that don’t interfere with each other.

Option Explicit
Sub TestUserDefinedAttribute()
  Dim oDoc As Object, oSheet As Object, oRange1 as Object, oRange2 As Object
  oDoc = StarDesktop.loadComponentFromURL( "private:factory/scalc", "_blank", 0, Array())
  oSheet=oDoc.Sheets(0)

  oRange1=oSheet.GetCellRangeByName("A2") 
  oRange2=oSheet.GetCellRangeByName("A3")   

  SetRangesUserDefinedAttribute oSheet,  "Andy", "Andrew Pitonyak"
  SetRangesUserDefinedAttribute oRange1, "Andy", "Andrew Lloyd Webber"
  
  SetRangesUserDefinedAttribute oSheet,  "Wolfgang", "Wolfgang Amadeus Mozart"
  SetRangesUserDefinedAttribute oRange2, "Wolfgang", "Wolfgang Pauli"

  ShowUDA oRange1
  ShowUDA oRange2
End Sub

' ------------------------------------------------
' lang:en
' Set Range UserDefinedAttribute.
' - obj        object that supports any of the interfaces: SheetCellRange, SheetCellRanges. 
' - propName   attribute name.
' - propValue  attribute name.
' - bFormat    If True, then all cells in obj have the same format. 
Sub SetRangesUserDefinedAttribute(ByVal obj As Object, ByVal propName As String, ByVal propValue As String, _
                                  Optional ByVal bFormat As Boolean)
  Dim oRange, arr, oRanges
  If IsMissing(bFormat) Then bFormat=False
  If obj.supportsService("com.sun.star.sheet.SheetCell") Or bFormat Then
    SetUserDefinedAttribute obj, propName, propValue
  ElseIf obj.supportsService("com.sun.star.sheet.SheetCellRanges") Then
    For Each oRange In obj
       SetRangesUserDefinedAttribute oRange, propName, propValue
    Next oRange
  ElseIf obj.supportsService("com.sun.star.sheet.SheetCellRange") Then   
    arr=obj.getUniqueCellFormatRanges
    For Each oRanges In arr
      SetRangesUserDefinedAttribute oRanges, propName, propValue, True
    Next
  End If
End Sub

' ------------------------------------------------
' lang:en
' Set UserDefinedAttribute.
' - obj        object that supports the CellProperty interface.
' - propName   attribute name.
' - propValue  attribute name.
Sub SetUserDefinedAttribute(ByVal obj As Object, ByVal propName As String, ByVal propValue As String)
  Dim oUserData As Object
  Dim oAttribute As New com.sun.star.xml.AttributeData
  
  oAttribute.Type = "CDATA"
  oAttribute.Value = propValue
 
  oUserData= obj.UserDefinedAttributes
  If oUserData.hasByName(propName) Then
    oUserData.ReplaceByName propName, oAttribute
  Else
    oUserData.InsertByName propName, oAttribute
  End If
  obj.UserDefinedAttributes=oUserData
 End Sub

' Shows UserDefinedAttributes of cell
Sub ShowUDA(ByVal oCell As Object)
  Dim oUserData As Object, oAttribute As Object, propName As String, s As String
  oUserData=oCell.UserDefinedAttributes
  For Each propName In  oUserData.ElementNames
    s=s & PropName & ": " & oUserData.getByName(propName).Value & Chr(10)
  Next PropName  
  Msgbox s,,"UserDefinedAttributes of  " & oCell.AbsoluteName
End Sub
Calc cell Unique Identifier, is this possible?

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