I am trying to give a calc cell a uniquie identifier. This way if the cell is moved (eg rows or columns inserted) I can still identify the cell. Setting a UserDefinedAttribute will not work as seen here.
I don’t want to use a named range because it can be modified or removed by the user. Also I don’t what to clutter up the Named ranges in the sheet.
Is this possible?
I am in interactive Jupyter-noterbook: https://github.com/jupyter/notebook
doc = XSCRIPTCONTEXT.getDocument()
my_cell = doc.CurrentSelection
print( my_cell.AbsoluteName )
# > $Tabelle1.$B$8
I am leaving temporarly the notebook-frontend and do something in the doc ( eg. inserting some rows above B8… rename the sheet … etcpp. )
after these actions changing back to my notebook-frontend and enter into a new (notebook)-cell:
print( my_cell.AbsoluteName )
# > $fancy_sheet_name.$B$12
@vib: do you get the point?
I am not clear on what your pointing at here.
I can see that the cell get its absolute name changed as it changes in from another place.
This is sort of like being in debug mode of VS code, getting the cell, hitting a breakpoint and the making changes in Calc, then switching back to VS and continue running. The Cell changes would be pickup up as you are demonstrating here.
I am attempting to write a hidden control (which can hold property values) into the form of the sheet draw page. I have no issue adding the hidden control. I want the hidden control to hold custom properties for the cell. Which I have no problem doing. The issue is connecting the control to the cell. I initally was giving the hidden controls names such as cell_0_0 ( A1) and cell_0_1 (B1). Then it would be easy t look up a control that was related to the cell. Then I realized this would totally fail when cells, column or rows were added or deleted. So, I am looking for a reliable way that I can relate a cell to a hidden control.
As I stated in my original post using UserDefinedAttributes is failing. So I only know of two other ways to connect a cell with a hidden contorl at this time. Both of which I find unsatisfactory.
Any ideas?
You did claim that. But you never described why. The topic you pointed to only seems to discuss why it’s not OK to set user-defined attributes on sheet level for your task. My perception is, that just limiting your code to only ever set it using individual cells would work fine.
If I have understood correctly so far, the actual task is »to write a tool that works similar to excels ´´py-mode´´ « ? sorry for messy youtube
the point is: if a »long time process« holds a reference to some object, the reference recognizes every change on that object and you dont need tweaking workarounds to store the objects state somehow.
@karolus Likely (in the frame of a bigger picture of @vib’s recent questions). But still, I fail to see a connection between that and the “UserDefinedAttributes is failing as a cell’s unique identifier” claim.
The problem that @vib tries to solve is likely the need to point to cells from inside of the Python code in a way similar to what @karolus shows - but since that’s not the level of the Calc formula, but an inner level of the “textual” Python code argument (or similar), Calc can’t itself provide the automatic referencing. This wasn’t stated, or I missed that statement - so just guessing, but that would naturally be the problem that a native implementation would best handle. Note that holding references to all the cells referenced from inside Python formulas in the running plugin would only be possible when (1) the extension is present (so editing the file on another system would ruin the references), and (2) the formulas were recalculated at least once (so that the plugin had a chance to build the reference list).
Yet, this doesn’t make the UserDefinedAttributes not fit - in the same way as any other way of monopolizing some public APU, in the hope that nobody else would ever use it for an unrelated task, ruining any hope that this attribute holds the information relevant to this or that plugin.
Clearly setting UserDefninedAttribues is not reliable. As soon as a macro or other code runs to set the sheet UserDefninedAttribues all Cell UserDefninedAttribues are lost.
I want to add a feature to OOO Development Tools (OooDev) that allows for setting of custom properties. I have been able to do this for Writer Docs, Draw Pages, Impress Sheets, Calc Sheet. Not yet figured out Calc Sheet Cells. That is what I am trying to solve in this post. If I can figure out a way for Calc Sheet Cells then I can investigated if there is a way to extend that to Calc Sheet Ranges and elsewhere.
I am not certain that I need this feature for the up coming PY extension (expected to be named LibrePythonista) that I am working on ( if i can get past all the challenges ), see YouTube for the example idea. Although the extension would most likely benefit from this feature. The Extension will automatically install OooDev and will run on top of it. Also I think Pandas, Numpy, MatLab and Seaborn will automatically be installed.
An example use case.
The LibrePythonista python code for a sheet will be one single module that is built and rebuilt dynamically as the sheet get new python cells.
Cells are calculated across and then down.
Lets say when the sheet is first loaded I want to find all the cells in order ( across and then down) that are to be considered python code cells, ( I am not sure at this point that those cells will be formula cells, yet to be seen). If I can add the feature to OooDev then it should be easy to get all the cells that have a custom property set that contains a property with the the the name LibrePythonistaCell=True for example. I expect there would be a limited number of cells on a sheet that would have custom properties assigned. Custom properties are easily stored in a hidden control similar to my CalcSheetId class. Because only the cells with custom properties would be stored in the sheet it should be simple to parse through them and find the relevant cells. I may also want to store other data in the properties such as the python code that goes with the cell, or at least the storage location for the python code. From a developer stand point I can see there are many way that custom properties on a cell can be useful.
Yes it is within a bigger frame; However, not limited to the bigger frame (LibrePythonistaCell). The connection to UserDefinedAttributes is very simple. I want to store custom properties for a cell. I mostly know How to do this, I can not use UserDefinedAttributes, My plan is to add one hidden control per cell that has custom properties ( unless I can find a better way). A hidden control has the benefit of being saved with the document and can contain unlimited number of Key value properties.
The issued I have not solved is how to name the hidden control so that it is connected to the cell. So when I load a cell in python code I can more or less just props = cell.get_custom_props() and the get_custom_props() method should be able to get the some unique name of the cell and find the hidden control with the matching name that contains custom properties. I just don’t know how to have a reliable name that I can use for a cell. So, if custom properties are added to a cell then a hidden control name of cell_0_0 (for cell “A1”) would be fine if the cell it belong to would never be moved, but that would be naive to think that way. I am looking for a way to find the hidden control for a cell (if it exist), even after the cell has been moved, for reason such as inserting rows or columns. My thinking is If I could just find a way to give a cell a unique id such as jour46jj (random) then I could also name the hidden control jour46jj and then if the cell is moved it would not matter. The unique id would always be the same.
I don’t expect needing to hold references to all cells with custom properties. It should be that when a cell is accessed in code that the propertied can be dynamically loaded as suggest by props = cell.get_custom_props().
In my mind what I am proposing to accomplish (ability to assign custom properties to cells) is not difficult in theory. I hope I am communicating this clearly enough.
It is completely unrelated, that setting “the sheet UserDefninedAttribues all Cell UserDefninedAttribues are lost”. Because completely the same way, setting cell’s UserDefninedAttribues from another script, your UserDefninedAttribues for that cell are lost. And that also applies to the methods you invented for “Writer Docs, Draw Pages, Impress Sheets, Calc Sheet”.
No matter how hard you try: any public API is public, so you simply have no way to use it without a fear that another script ruins your data.
Also, note that UserDefninedAttribues is a container of named attributes, not a single attribute.
If, for example, you use custom attributes with your own group of names (for example, with some kind of name prefix), then you have the right to expect that another qualified programmer will not change your attributes.
Everything is similar to other properties. When you set the CellBackColor property of a sheet, you realize that you are removing all previously set cell colors.
The programmer should not overwrite whole UserDefninedAttribues of sheet cell, only individual attributes.
I am not sure if you are aware of this but even though UserDefninedAttribues can be set for a cell, which is a container and is demonstrated in Andrew Pitonyak’s book, there is a issue that when the Sheets UserDefined Attribute are set after Cell UserDefined Attributes, All Previous cells loose their UserDefninedAttribues. This what the Sheet User Defined Attributes wipe out cell user defined attributes post is all about. It seems reasonable to me that a developer may set UserDefinedAttributes for a sheet in the normal course of things. This means any macros or extension relying on cell UserDefninedAttributes would become broken if the Sheet UserDefinedAttributes were to be set after the Cell UserDefinedAttributes.
The only relation to what I am asking in this tread is that UserDefinedAttributes are not usable for this reason. So, I am essentially looking for a work around.
For my implementation for custom properties for Writer Docs, Draw Pages, Impress Pages and Calc Sheets I am not relying on UserDefinedAttributes at all.
It seems to me, that we are talking past each other. My point is: no matter which way you implement that. Since you use a public API, you are trying to monopolize the API. I.e., you expect, that no other script uses the same API, to set a different value for the same object, for an unrelated reason.
And if you try to read my replies here, you may notice that I am aware of the “sheet custom properties “overwrite” cell custom properties” topic.
The short if it is yes.
The long of it is it should not be hard to write properties that are not expected to be overwritten.
If I add a property to a cell (if that were possible) with the Name Key, Value of LibrePythonistaCell=True then I think it would be reasonable to act as if the LibrePythonista would be the only thing that set the LibrePythonistaCell property on any give cell. If another user set a property on the same cell such as MyCustomProp="I Love PY" it would not interfere with the LibrePythonistaCell property or any other property with the way I want to implement custom properties. Again currently the only issue I am having with this is how to tie a hidden control name to a cell name.
Why do you think that everything has to be installed together with OooDev? The opposite is the case: OooDev is not mandatory anywhere, except in your perception bubble !
There is e.g. a fork of Apso.oxt with which you can simply install and uninstall numpy etc.!
With your strange “numpy” extension you have to delete everything including OO_dev and its dependencies by hand afterwards! That was the point where I decided not to bother with OooDev ect. anymore.
One last hint for your upcoming LibrePythonista : I’m pretty sure excels-py_mode feature is build on top of a mostly regular python-stack with Jupyter-server (of course also with numpy and pandas) and IPython as backend. only the notebook-»FrontEnd« which runs normaly in any webbrowser has someway moved into excel365.
Good luck with resisting on OooDev!
Bye
This seems like a moot point. I it is like saying ScriptForge is not mandatory anywhere. And Indeed it is not. They are both libraries with the intention of making working with the API easier.
OooDev is open source and contribution are always welcome. The Project is built with community in mind. If you want the submit improvement they will be welcomed.
Not exactly a unique cell identifier but close enough. Using OooDev is it now possible ( since version 0.45.1 to add custom properties to cells.
Here is my source code that makes it happen for those who are interested. Basically I use a shape with a unique Id embedded in the cell to attach to hidden controls. The hidden controls are persisted with the doc.