Problem with number types in calc+python

[Roman001]

Hi There, I’m using a construction as recomended in How can I iterate through cells in range in Python macro. My problem is in the applied picture from the debug mode

I get data from a worksheet with DateArray property but some values come as float, some as string. Nevertheless I had set text format on all cells before. How can I get all values as a string

[karolus]

@Roman001

Nevertheless I had set text format on all cells before.

NO … the data from Column D is obviously a float!

If you need the String-representation of your data you may try …FormulaArray instead …DataArray ( be aware this returns the underlying Formula if there are true Formulas )

btw.

• please dont concatenate strings str(i) +':' +str(i) ⇒ f'{i}:{i}'
• I dont think its a good idea to grep the whole row with 16384 entries if you probably need only a small fraction of the row ?!

[sokol92]

In addition to @karolus 's answer.
The getDataArray method has some specific features that aren’t fully documented.
Depending on the cell type, the following values ​​are returned:

• Empty → zero-length string ("" in Python and Basic)
• Number → double (float in Python, Double in Basic)
• String → string (str in Python, String in Basic)
• Error value → void (None in Python, Empty in Basic)

[Roman001]

Thanks for the advices, it works with FormulaArray. But I still can’t understand the DataArray behaviour
All my cells have the text format, but one seems to him as float…

[sokol92]

@Roman001

All my cells have the text format

When you change the cell format, the previously entered cell value does not change.
The cell format does not reflect the value of the cell, it is only your wish for the displayed value.

[karolus]

…additional: »Text« is NOT only a question of formatting, its in the first place another DataType, which you need to reenter after applying the »Text-format«

[Roman001]

I do it with the script, this is the same as Text for columns menu in Excel

[sokol92]

@Roman001

I do it with the script, this is the same as Text for columns menu in Excel

Your text changes the number format of cells.
“Text for columns” in Excel changes cell values.
Cell values ​​and cell formats are two different worlds.
Example for independent analysis.

1. Enter any date in cell A1 and select the appropriate date format.
2. Change the format of cell A1 to text. Explain what is displayed in the cell.

[Roman001]

Yes. If you change format in dropdown menu from date to text it is would be int value. But I go to Data/Text to Columns menu and choose column type as text - profit, the same as Excel

[sokol92]

@Roman001

But I go to Data/Text to Columns menu and choose column type as text - profit, the same as Excel

The Python snippet you provided is not equivalent to the user actions mentioned above.

[karolus]

# should also convert Numbers to Text 
source_range.DataArray = source_range.FormulaArray

[sokol92]

data_array_str = [list(map(str, row)) for row in data_array]