[Solved] SUM formula ignores cells updated with cell.SetValue(<str>)

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
jma
Posts: 7
Joined: Sun May 27, 2018 2:26 am

[Solved] SUM formula ignores cells updated with cell.SetValue(<str>)

Post by jma »

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
Last edited by MrProgrammer on Fri Feb 17, 2023 5:11 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved] -- MrProgrammer, forum moderator
OpenOffice 4.1.1
VOID Linux
User avatar
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>)

Post by MrProgrammer »

jma wrote: Tue Feb 07, 2023 8:26 pm cell.SetValue(<str>)
=SUM(E5:E9)
The old formula does not see the new value in E5.
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).
jma
Posts: 7
Joined: Sun May 27, 2018 2:26 am

Re: Static formula ignores fields updated with cell.SetValue(<str>)

Post by jma »

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
OpenOffice 4.1.1
VOID Linux
jma
Posts: 7
Joined: Sun May 27, 2018 2:26 am

Re: Static formula ignores fields updated with cell.SetValue(<str>)

Post by jma »

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.

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
OpenOffice 4.1.1
VOID Linux
Post Reply