Recasting a STRING into a VALUE in Calc with python

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

Recasting a STRING into a VALUE in Calc with python

Post by jma »

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:

Code: Select all

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

# 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!
OpenOffice 4.1.1
VOID Linux
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Recasting a STRING into a VALUE in Calc with python

Post by Villeroy »

This is doomed to fail as soon as you pass the file to someone with a different locale. Numeric strings (wrong data) in spreadsheets should be avoided, unless they are phone numbers, zip codes or other identifiers.
On my system =VALUE("3,141") returns pi cut down to 3 digits, =VALUE("3.141") returns 3141 and =VALUE("3.14159") returns an error. The exact same spreadsheet returns different values on your system. menu:Tools>Options>LanguageSetting>Languages>Locale lets us change the interpretation context of numeric strings. Change it to a comma locale, i.e. French, Russian, German, in order to reproduce.

Apart from errors, Calc knows doubles and strings as the only types of cell values. This is not a matter of formatting. It is a matter of data type. If you want to set a cell value, you have to use a float or integer: cell.setValue(3.14) or cell.setFormula("3.14"). The second variant always takes a string that is interpreted as English formula input (point decimal, ISO dates). cell.setFormula("3.14") [with leading apostrophe] sets a numeric string, cell.setFormula("=A1+A2") [with leading =] inserts a real formula. Every cell has a string, a formula and a value at the same time. Dates are serial day numbers, times are fractions of these day numbers, booleans are 1 and 0.

If you have wrong values in your sheet you should take care that only correct values get into it, in particular you have to import csv files and clipboard content correctly with the right import options.
In rare cases this may not be possible due to weird numeric strings. Spreadsheet users can use the formula spreadsheet's langauge to fix this. Python programmers don't need any spreadsheets, but if you write Python programs to manipulate spreadsheets, you need to know both languages. Spreadsheets are difficult because they follow their own pseudo standard which is MS Excel of the early 90ies.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply