text cell to numeric conversion

[JanMarvin]

Idea from ycphs/openxlsx#195

We could provide a function that converts a range of cells or a sheet from text to numbers.

For this to work, we could convert characters to numbers, check for warnings, and if no warning is issued, replace the character contents of the cell with a numeric value.

[stees]

Hello! I would like to know which function does this conversion. I tried using wb_add_numfmt but it didn't work.

[JanMarvin]

Hi @stees , good question. We haven’t really documented this, but have a look at this.

options(“openxlsx2.string_nums“ = TRUE)

wb <- wb_workbook()$add_worksheet()$add_data(x = data.frame(v1 = as.character(1:5)))

this should convert the strings to numerics. Typing on my phone, didn’t check this … is this what you were looking for?

[stees]

Actually, I was looking for something more in the lines of wb_add_numfmt, but actually converting the cell to "Number".

That's because the input dataframe had to store these columns as character columns in the first place already, so that's why converting the columns of the dataframe itself wouldn't do, I guess. Seeing as Excel allows for cell-specific types, I would like to convert only some of them, after applying other formatting functions (font, fill, etc).

Using wb_add_numfmt, I noticed that the number format was indeed inside the properties of the cell, but its type wasn't converted to "Number", so it still returned the same "number stored as text" error.

[JanMarvin]

The thing is, strings are stored differently in the openxml format. Numbers are stored as <v=“1.2”/> and inline strings as <is><t>”1.2”</t></is>. And the number format is just applied to numbers, not to strings.
The option above simply tries to convert every text cell via as.numeric() before it is stored in the worksheet.

[edit] for the option above: If the cell can be converted to a number and no NA is thrown in the process, the numeric value is written into the cell, otherwise the string is written. That’s why I think it’s what you wanted.

[stees]

Oh, I see. I'll try using that function and binding rows afterwards to see if it works. Thanks.

[JanMarvin]

I have added a first draft of a chapter for this to the book: https://janmarvin.github.io/ox2-book/chapters/openxlsx2_number_and_string.html

[stees]

options("openxlsx2.string_nums" = TRUE) worked great! Is there any way to control the number format after it's converted in this manner?

[JanMarvin]

Glad that it works! You should be able to change the number format with wb_add_numfmt(), other number formats are not applied when the data is written to the worksheet, because the wb_add_data() function still assumes that the cell written was a string and not a number. So if you have mixed columns some manual selection is required.