1.234, once formated as currency is imported as numeric 1.23 instead of numeric 1.234

[lucasmation]

I have a situation in which preserving the full digits that were typed in a monetary field is important, even though Excel will only display two decimal digits. However, xlsx_cells will import such data only preserving the two decimal digits. Is there a workaround this?

Btw, read_excel does not do that, it preserves the detail in the original underlying value of the cell.

[nacnudus]

Please can you provide an example file?

[nacnudus]

Actually I'll provide a file.

temp.xlsx

I created this file (using LibreOffice Calc, because I don't have Excel any more) by typing 12345678987654321 into cell A1 and formatting it as currency. The value exceeds the limits of precision, so LibreOffice rounds it down to £12345678987654300.00.

When the file is read with tidyxl, the value £12345678987654300.00 is preserved.

library(tidyxl)
cells <- xlsx_cells("./temp/temp.xlsx")
sprintf("%17.2f", cells$numeric)
# [1] "12345678987654300.00"

[lucasmation]

@nacnudus, thank you. Nut I meant the loss of precision of digits to the right of the decimal point, not to the left.

[nacnudus]

@lucasmation Please provide a minimal reproducible example (AKA a reprex). If you've never heard of a reprex before, start by reading https://www.tidyverse.org/help/#reprex.

[lucasmation]

Sorry for the lack of a reprex. Here you go: test_1_234_currency.xlsx Content:

• A1 has the decimal 1.234.
• A2 has the decimal 1.234 formated as currency

f <- 'b:/temp/test_1_234_currency.xlsx'
library(tidyxl)
cells <- xlsx_cells(f)
sprintf("%17.2f", cells$numeric)
sprintf("%17.2f", cells$numeric)
sprintf("%17.3f", cells$numeric)
[1] "            1.234" "            1.234"

however the error is no longer repreoduced, the above is correct. So I will close the issue. Sorry to bother.

I will double check my actual aplication to see, and reopen this issue with an updated reprex if I can create one.