Howdy,
I have a sheet that I use as a template. It has formulas that process columns in it. I programatically copy the sheet and populate the "ditto" with external data. Writing the new data in the sheet goes like this:
dittosheet.getCellRangeByName(str("E5"))
reportcell.setValue(str("123.45"))
The value sent in setValue() shows up in the document. There is a formula in E10 that was already there. It's formula is:
=SUM(E5:E9)
The old formula does not see the new value in E5. If I manually updated E6, it SUMS E6, but not E5 and E6. If I press F2 it shows that the formula with a blue box around E5:E9 as expected.
I have tried using Document.recalculate() and Document.recalculateAll(). Does not work. F9 does not work.
Can somebody tell me how to get an old formula to see a new value? The problem is the same whether I use setValue() or setFormula() to write the cell.
Thanks in advance!
JMA
[Solved] SUM formula ignores cells updated with cell.SetValue(<str>)
[Solved] SUM formula ignores cells updated with cell.SetValue(<str>)
Last edited by MrProgrammer on Fri Feb 17, 2023 5:11 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved] -- MrProgrammer, forum moderator
Reason: Tagged ✓ [Solved] -- MrProgrammer, forum moderator
OpenOffice 4.1.1
VOID Linux
VOID Linux
- MrProgrammer
- Moderator
- Posts: 4895
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Static formula ignores fields updated with cell.SetValue(<str>)
The SUM() function ignores all cells with text values. Read section 1. Types of data in Ten concepts that every Calc user should know.
If you need any additional assistance you should attach a spreadsheet demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the spreadsheet itself). Also provide the complete text of the macro you're using. Variables like reportcell are meaningless in your post without that.
If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Static formula ignores fields updated with cell.SetValue(<str>)
In "Ten Concepts..", it specifies that a cell needs to contain a "number" for a formula to see it.
To test this I did cell.setValue(float("123.45")) to statically recast the stringified number. The API should get this as a float. But after using the menu to look at the format, it is still setting the cell to a text. Note that before the method setValue() is called, the cell was manually set to a "Currency" in the UI.
Also according to "Ten Concepts" I should be able to:
foo = str("$123.45")
cell.setValue(foo)
and this should now be a "Currency" formatted cell. But Calc throws:
com.sun.star.script.CannotConvertException:invalid STRING value!
So the method setValue() is resetting an empty "Currency" formatted cell to a "Text" formatted cell after recieving a float, and it doesn't accept the same type casting hints as direct UI input.
"Number" from the "ten concepts" isn't a primitive type, so if there is an object of Type "Number" that I an instantiate before I send the value to the API how do I do that?
I am guessing probably more correct is that the cell stores data as several diverse properties, and SUM (Formula's) can't see the one in the text property. That would then demand some kind of postwrite or prewrite recasting within the API. So I need to know whatever UNO method does that.
Unfortunately it isn't practical to upload the spreadsheet. (the document and the macro code are monsterous) At best I could make a test case, but that is a lot of work for something as simple as recasting.
There are two interfaces to fix this that I can think of. Either I have to instantiate a properly formatted type ("Number" or some such), which I would need to know the API call for, OR I need to know what method provided by the API that recasts the Text into a Number or Currency after the value is written to the cell. I am guessing this is CellStyle or something like that.
So which is it? Recast then send, or send then recast, and what are the associated methods?
Thanks in advance,
JMA
To test this I did cell.setValue(float("123.45")) to statically recast the stringified number. The API should get this as a float. But after using the menu to look at the format, it is still setting the cell to a text. Note that before the method setValue() is called, the cell was manually set to a "Currency" in the UI.
Also according to "Ten Concepts" I should be able to:
foo = str("$123.45")
cell.setValue(foo)
and this should now be a "Currency" formatted cell. But Calc throws:
com.sun.star.script.CannotConvertException:invalid STRING value!
So the method setValue() is resetting an empty "Currency" formatted cell to a "Text" formatted cell after recieving a float, and it doesn't accept the same type casting hints as direct UI input.
"Number" from the "ten concepts" isn't a primitive type, so if there is an object of Type "Number" that I an instantiate before I send the value to the API how do I do that?
I am guessing probably more correct is that the cell stores data as several diverse properties, and SUM (Formula's) can't see the one in the text property. That would then demand some kind of postwrite or prewrite recasting within the API. So I need to know whatever UNO method does that.
Unfortunately it isn't practical to upload the spreadsheet. (the document and the macro code are monsterous) At best I could make a test case, but that is a lot of work for something as simple as recasting.
There are two interfaces to fix this that I can think of. Either I have to instantiate a properly formatted type ("Number" or some such), which I would need to know the API call for, OR I need to know what method provided by the API that recasts the Text into a Number or Currency after the value is written to the cell. I am guessing this is CellStyle or something like that.
So which is it? Recast then send, or send then recast, and what are the associated methods?
Thanks in advance,
JMA
OpenOffice 4.1.1
VOID Linux
VOID Linux
Re: Static formula ignores fields updated with cell.SetValue(<str>)
I was barking up the wrong tree. Indeed I had two places that were doing copies between sheets and I was doing them in two different ways. But ultimately the answer is, read as a string, test for length, write as a formula. This can of course be amended with whetever intermediate processing may be required.
Note: "document" is the UNO API document object. All other variables are str's and should be self-evident.
Note: "document" is the UNO API document object. All other variables are str's and should be self-evident.
def sheetcell2sheetcell(self,document,srcsheetname,srccellname,dstsheetname,dstcellname): srcsheet = document.Sheets.getByName(srcheetname) dstsheet = document.Sheets.getByName(dstsheetname) srccellstr = srcsheet.getCellRangeByName(srccellname).String if (len(str(srccellstr)) > 0): dstsheet.getCellRangeByName(dstcellname).Formula = srccellstr return True return False
Last edited by MrProgrammer on Fri Feb 10, 2023 10:54 pm, edited 2 times in total.
Reason: Added formatting tags
Reason: Added formatting tags
OpenOffice 4.1.1
VOID Linux
VOID Linux