How to clear cell o range with Python.

English
,
#1

Hi!
I need help with some taks:

  1. Clearing a cell or range with python.
    I am using:
    DOC.Sheets[sheet][cell].setString('thank you')
    or
    DOC.Sheets[sheet][cell].String = 'thank you'

However, I can´t clear a cell by using this, as it simply does nothing to the cell:
DOC.Sheets[sheet][cell].String = ''

  1. I would also thank you if you could help me by assigning a list or tuple to a range:
    This does not work, but this is kind of what I am looking for:
    DOC.Sheets[sheet][range].setValues(list)

  2. In case you could help me, please could you provide a link to any documentation that helped you to help me? I just can´t find any content online with this info!

Thank you very much!

#2

…clearContents(…) https://api.libreoffice.org/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1sheet_1_1XSheetOperation.html
with Argument sum of flags https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1sheet_1_1CellFlags.html

#3

Unfortunately this does not work for me:

doc = XSCRIPTCONTEXT.getDocument()
doc.Sheets['Sheet1']['A1'].clearContents(1) # flags tested: 1, 4, 5

Nothing happens. I can only change the value of the cell :frowning:

#4

All this works for me:
setString('')
setFormula('')
clearContents(4)
clearContents(23) dates + numbers + strings + formulas
FormulaLocal = ''

pyClear.ods (20.8 KB)

#5

Aw! I see…I see that it works only if the script is executed through the Macros menu or by assigning it to a button. But if the script is executed through a cell formula (BASIC function), it doesn’t work, it behaves as I said:
Cell values can only be assigned, not cleared.

Here is an example. It executes a python macro when a basic function is called. To test it, insert the following formula in cell B1 (for example).

=CLEAR_CONTENTS()

It will clearly add a new value to A2, but A1 won´t be cleared.
However, if the python function is executed through Macros menu, it will work…

My intention is to make a relatively complex and flexible sheet where scripts can do this kind of tasks through formulas.
Am I doing something wrong or missing something?

Thank you very much for your help!
TEST.ods (15.4 KB)

#6

A cell formula must never ever manipulate cell contents. In fact, the cells are locked while the calculation cascade is running.
You can not write cell functions as Python macros (within XSCRIPTCONTEXT). Cell functions written in Python, Java, C++ are distributed as “add-ins” (extensions with sheet functions). Add-in functions appear in the formula wizard but not in the macro dialog.
https://forum.openoffice.org/en/forum/viewtopic.php?t=83856

#7

Well, If you could take a look at my file, you can see that cells are not fully locked, as their value can be modified or set.
I am doing a relatively complex and flexible doc that can eval and execute python code written in cells. And it definitively works!!! Except that cells can´t be cleared…(again, they can be set, just not cleared) If there is a way to do that, I would like to know it!
Thank you!

#8

The application itself locks all cell contents while formulas are calculated.

#9

But why their values can be modified then?

#10
  1. You modify cell content that is referenced by some formulas.
  2. If automatic calculation is enabled or if you hit F9 for manual calculation, the application locks the document, performs the calculation of all dependent formulas and finally unlocks the document, so you can modify cells again.
#11

Look, this is not true.
Please take a look at my file.
You will see the following:

  1. A python function executed by a basic function used as a cell formula (no need for extension).
  2. Cells that have their content assigned without being referenced by any formula.
#12

While your formula is calculating, no cell can be modified.

#13

Again…if you do what I said in my project you will see that cell A2 will be modified (and was never referenced)…I don´t know why you say that…but YES they can.

And by the way, my second point was about a setValues() method because I have been writing arrays to cells with python through formulas perfectly fine…dozens of cells…no problem…but I do it with two loops (row, file), so I wanted a better method. So yes…and I am sorry…Cells can be modified while the formula is calculating…

#14

They can but they shouldn’t!
Here, Microsoft provides a reasoned explanation of the limitations of UDF functions in Excel. These limitations also apply to custom functions used in Calc formulas.

#15

I understand that it may not be the best routine, and there is an overwriting risk. But in my case, I just wan´t to do it (if it is possible, this is just what I am looking for).
It is fun for me to control cells by writting python code directly into cells. And all that just works perfectly fine right now! Except clearing cells…
For example:
This works perfectly fine (assigning values to cells):

doc = XSCRIPTCONTEXT.getDocument()
doc.Sheets['Sheet1']['A1'].String = 'welcome'

This works perfectly fine (reading values from cells):

doc = XSCRIPTCONTEXT.getDocument()
cell_content = doc.Sheets['Sheet1']['A1'].String
return cell_content

But this does not work (clearing cell value)

doc = XSCRIPTCONTEXT.getDocument()
doc.Sheets['Sheet1']['A1'].String = ''

I just want to know if technically I can do that, as it is only changing a value like in the first example.
The reason I am doing that, honestly, is because formulas that return an array with (CTRL+SHIF+ENTER), can´t be automatically & dinamically resized, and this is bad, this gives you no flexibility in some scenarios.
So, even if that is not recommended, I want to know if it is possible to set a null value to a cell, the same as any other value can be assigned. This will work for my working style and it is fun for me :frowning:
Thank you guys!
By the way, my temporary solution is this:

doc = XSCRIPTCONTEXT.getDocument()
doc.Sheets['Sheet1']['A1'].Formula = '=""'

It visually works!, but I just don´t like it…

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