ycphs
Inf and NaN handling
Describe the bug Inf and NaN print as #NUM! in spreadsheets. Can we have some option for handling? For example, some might want NaN to just print blank. For Inf, it might print as the literal "INF".
To Reproduce
library(tidyverse)
library(openxlsx)
as.character.na <- function(x){
if_else(is.na(x) & !is.nan(x), "NA", as.character(x))
}
j <- tibble(
x=c(-1:1, NA),
y=1/x,
z=0/x,
) %>%
mutate(
Row=row_number(),
across(c(x,y,z), as.character.na, .names="{.col}_chr")) %>%
select(Row, everything())
# Create a new workbook and add a sheet
wb <- createWorkbook()
addWorksheet(wb, "Sheet 1")
# Write data to the sheet
writeData(wb, "Sheet 1", j)
# Save the workbook
saveWorkbook(wb, "tryna.xlsx", overwrite = TRUE)
Created on 2025-03-19 with reprex v2.1.1
Session info
sessioninfo::session_info()
#> ─ Session info ───────────────────────────────────────────────────────────────
#> setting value
#> version R version 4.4.2 (2024-10-31 ucrt)
#> os Windows 10 x64 (build 19045)
#> system x86_64, mingw32
#> ui RTerm
#> language (EN)
#> collate English_United States.utf8
#> ctype English_United States.utf8
#> tz America/New_York
#> date 2025-03-19
#> pandoc 3.2 @ C:/Program Files/RStudio/resources/app/bin/quarto/bin/tools/ (via rmarkdown)
#>
#> ─ Packages ───────────────────────────────────────────────────────────────────
#> package * version date (UTC) lib source
#> cli 3.6.3 2024-06-21 [1] CRAN (R 4.4.2)
#> colorspace 2.1-1 2024-07-26 [1] CRAN (R 4.4.2)
#> digest 0.6.37 2024-08-19 [1] CRAN (R 4.4.2)
#> dplyr * 1.1.4 2023-11-17 [1] CRAN (R 4.4.2)
#> evaluate 1.0.1 2024-10-10 [1] CRAN (R 4.4.2)
#> fastmap 1.2.0 2024-05-15 [1] CRAN (R 4.4.2)
#> forcats * 1.0.0 2023-01-29 [1] CRAN (R 4.4.2)
#> fs 1.6.5 2024-10-30 [1] CRAN (R 4.4.2)
#> generics 0.1.3 2022-07-05 [1] CRAN (R 4.4.2)
#> ggplot2 * 3.5.1 2024-04-23 [1] CRAN (R 4.4.2)
#> glue 1.8.0 2024-09-30 [1] CRAN (R 4.4.2)
#> gtable 0.3.6 2024-10-25 [1] CRAN (R 4.4.2)
#> hms 1.1.3 2023-03-21 [1] CRAN (R 4.4.2)
#> htmltools 0.5.8.1 2024-04-04 [1] CRAN (R 4.4.2)
#> knitr 1.49 2024-11-08 [1] CRAN (R 4.4.2)
#> lifecycle 1.0.4 2023-11-07 [1] CRAN (R 4.4.2)
#> lubridate * 1.9.4 2024-12-08 [1] CRAN (R 4.4.2)
#> magrittr 2.0.3 2022-03-30 [1] CRAN (R 4.4.2)
#> munsell 0.5.1 2024-04-01 [1] CRAN (R 4.4.2)
#> openxlsx * 4.2.7.1 2024-09-20 [1] CRAN (R 4.4.2)
#> pillar 1.10.0 2024-12-17 [1] CRAN (R 4.4.2)
#> pkgconfig 2.0.3 2019-09-22 [1] CRAN (R 4.4.2)
#> purrr * 1.0.2 2023-08-10 [1] CRAN (R 4.4.2)
#> R6 2.5.1 2021-08-19 [1] CRAN (R 4.4.2)
#> Rcpp 1.0.13-1 2024-11-02 [1] CRAN (R 4.4.2)
#> readr * 2.1.5 2024-01-10 [1] CRAN (R 4.4.2)
#> reprex 2.1.1 2024-07-06 [1] CRAN (R 4.4.2)
#> rlang 1.1.4 2024-06-04 [1] CRAN (R 4.4.2)
#> rmarkdown 2.29 2024-11-04 [1] CRAN (R 4.4.2)
#> rstudioapi 0.17.1 2024-10-22 [1] CRAN (R 4.4.2)
#> scales 1.3.0 2023-11-28 [1] CRAN (R 4.4.2)
#> sessioninfo 1.2.2 2021-12-06 [1] CRAN (R 4.4.2)
#> stringi 1.8.4 2024-05-06 [1] CRAN (R 4.4.0)
#> stringr * 1.5.1 2023-11-14 [1] CRAN (R 4.4.2)
#> tibble * 3.2.1 2023-03-20 [1] CRAN (R 4.4.2)
#> tidyr * 1.3.1 2024-01-24 [1] CRAN (R 4.4.2)
#> tidyselect 1.2.1 2024-03-11 [1] CRAN (R 4.4.2)
#> tidyverse * 2.0.0 2023-02-22 [1] CRAN (R 4.4.2)
#> timechange 0.3.0 2024-01-18 [1] CRAN (R 4.4.2)
#> tzdb 0.4.0 2023-05-12 [1] CRAN (R 4.4.2)
#> vctrs 0.6.5 2023-12-01 [1] CRAN (R 4.4.2)
#> withr 3.0.2 2024-10-28 [1] CRAN (R 4.4.2)
#> xfun 0.49 2024-10-31 [1] CRAN (R 4.4.2)
#> yaml 2.3.10 2024-07-26 [1] CRAN (R 4.4.1)
#> zip 2.3.1 2024-01-27 [1] CRAN (R 4.4.2)
#>
#> [1] C:/Users/{username}/AppData/Local/R/win-library/4.4
#> [2] C:/Program Files/R/R-4.4.2/library
#>
#> ──────────────────────────────────────────────────────────────────────────────
Output:
| Row | x | y | z | x_chr | y_chr | z_chr |
|---|---|---|---|---|---|---|
| 1 | -1 | -1 | 0 | -1 | -1 | 0 |
| 2 | 0 | #NUM! | #NUM! | 0 | Inf | NaN |
| 3 | 1 | 1 | 0 | 1 | 1 | 0 |
| 4 | NA | NA | NA |
Expected behavior
I expect values for z on row 2 to be blank. I'm not sure the best handling for Infinity.
Screenshots
Hi @szimmer ,
this probably not going to happen, because openxlsx is not actively developed anymore. But I took this feature request as an inspiration for some development in openxlsx2. The following snippet requires to build openxlsx2 from the development branch. Using the labelled package it is possible to create labels for Inf, -Inf, and NaN. I don't want to add a feature like this as a default argument, but if needed the user can create custom functions to modify the labels.
library(dplyr)
library(openxlsx2)
as.character.na <- function(x){
if_else(is.na(x) & !is.nan(x), "NA", as.character(x))
}
j <- tibble(
x = c(-1:1, NA),
y = 1 / x,
z = 0 / x,
) %>%
mutate(
Row = row_number(),
across(c(x, y, z), as.character.na, .names = "{.col}_chr")) %>%
select(Row, everything())
tab <- j
tab$y <- labelled::labelled(tab$y, c("INF" = Inf, " " = NaN))
tab$z <- labelled::labelled(tab$z, c("INF" = Inf, " " = NaN))
wb <- write_xlsx(list(j = j, k = tab), na.strings = NULL)
wb$to_df(sheet = 2)
#> Row x y z x_chr y_chr z_chr
#> 2 1 -1 -1 0 -1 -1 0
#> 3 2 0 INF 0 Inf NaN
#> 4 3 1 1 0 1 1 0
#> 5 4 NA <NA> <NA> NA NA NA
Thanks! I recently started transitioning to using openxlsx2 for new work! This was Issue by an older report originally done in openxlsx. We found a workaround in R before going to xlsx for the time being but I'm grateful for the new method in openxlsx2!