### Recasting a STRING into a VALUE in Calc with python

Posted:

**Tue Jan 01, 2019 1:40 am**I have a python class that does some currency conversion and string formatting of numbers. It takes polymorphic input, but only spits out a stringified number.

I can push those stringified numbers up to a LibreOffice Calc in python easy enough:

This actually works nicely since the builtin currency formats in Calc work just fine with stringified numbers.

What doesn't work is formulas. Or sortof doesn't work. Calling SUM(A1:A2) will not see the stringified A1. There is a workaround (forgive me it is late and I forget it exactly but it is similar to:) =SUMRECORD(VALUE(A1:A2))

As I understand it, each cell has a memory location for a number, a string, and a formula. The formula only acts on the VALUE memory location.

Through the spreadsheet UI, I can convert one cell type to another during a copy. To do that I just put the following formula in A2, and it converts STRING(A1) to VALUE( A2):

But that only works by copying one cell to another. Obviously there is an internal recasting function within the spreadsheet that is doing the conversion during the copy.

What I want to do, is write a stringified number to the spreadsheet (as above) and then call the spreadsheets native recasting function in place from python, so that VALUE(A1) is recast from STRING(A1).

If I could call that recasting function from from python after every write, then user-side macros in the UI work like the user expects them to work. Which is what I'm trying to achieve. Can anybody show me an example on how to convert a STRING to a VALUE in place with an UNO API function call?

If you're answer is: "do type conversion python-side", I've already considered that, and it is not the solution I'm looking for.

Thanks in advance!

I can push those stringified numbers up to a LibreOffice Calc in python easy enough:

- Code: Select all Expand viewCollapse view
`stringifiednumber = str("1.01")`

cell_a1 = sheet1.getCellRange("A1")

cell_a1.String = stringifiednumber

This actually works nicely since the builtin currency formats in Calc work just fine with stringified numbers.

What doesn't work is formulas. Or sortof doesn't work. Calling SUM(A1:A2) will not see the stringified A1. There is a workaround (forgive me it is late and I forget it exactly but it is similar to:) =SUMRECORD(VALUE(A1:A2))

As I understand it, each cell has a memory location for a number, a string, and a formula. The formula only acts on the VALUE memory location.

Through the spreadsheet UI, I can convert one cell type to another during a copy. To do that I just put the following formula in A2, and it converts STRING(A1) to VALUE( A2):

- Code: Select all Expand viewCollapse view
`# formula placed in A2`

=VALUE(A1)

But that only works by copying one cell to another. Obviously there is an internal recasting function within the spreadsheet that is doing the conversion during the copy.

What I want to do, is write a stringified number to the spreadsheet (as above) and then call the spreadsheets native recasting function in place from python, so that VALUE(A1) is recast from STRING(A1).

If I could call that recasting function from from python after every write, then user-side macros in the UI work like the user expects them to work. Which is what I'm trying to achieve. Can anybody show me an example on how to convert a STRING to a VALUE in place with an UNO API function call?

If you're answer is: "do type conversion python-side", I've already considered that, and it is not the solution I'm looking for.

Thanks in advance!