Global options are ignored

[tpsteiner]

Hello!

After moving from v1.0 to the development branch, I noticed the global options have stopped working. Is the code below working for anyone?

library(openxlsx2)

options("openxlsx2.datetimeFormat" = "yyyy/mm/dd")
options("openxlsx2.dateFormat" = "mm/dd/yyyy")
test_data <- tibble::tribble( ~datetime_col, ~date_col,
                              "2023-12-31 00:00:00", "2023-12-31")
temp_path <- temp_xlsx()
write_xlsx(test_data, temp_path)
xl_open(temp_path)

maxWidth is also not working for me.

[JanMarvin]

Hmm are options now snake case too?
I cannot check right now, but I’m unaware of any intentional changes to options.

[JanMarvin]

This looks like it should work. Could use getOption() but that should not impact the functionality of the code. GitHub could not show me any code reference for max widths option, this might be an overlooked defunct openxlsx option. It’s also not in our options reference.

openxlsx2/R/write.R

Lines 503 to 534 in 53aef14

	if (any(dc == openxlsx2_celltype[["short_date"]])) { # Date
	if (is.null(unlist(options("openxlsx2.dateFormat")))) {
	numfmt_dt <- 14
	} else {
	numfmt_dt <- unlist(options("openxlsx2.dateFormat"))
	}
	dim_sel <- get_data_class_dims("short_date")
	# message("short_date: ", dim_sel)
	wb$add_numfmt(
	sheet = sheetno,
	dim = dim_sel,
	numfmt = numfmt_dt
	)
	}
	if (any(dc == openxlsx2_celltype[["long_date"]])) {
	if (is.null(unlist(options("openxlsx2.datetimeFormat")))) {
	numfmt_posix <- 22
	} else {
	numfmt_posix <- unlist(options("openxlsx2.datetimeFormat"))
	}
	dim_sel <- get_data_class_dims("long_date")
	# message("long_date: ", dim_sel)
	wb$add_numfmt(
	sheet = sheetno,
	dim = dim_sel,
	numfmt = numfmt_posix
	)
	}

[JanMarvin]

Oh what could be an issue is the tribble above. Both dates are probably detected as strings. We don’t convert this for you. Therefore please make sure that the columns are of type date or posix.

[JanMarvin]

This looks fine:

library(openxlsx2)

options("openxlsx2.datetimeFormat" = "yyyy/mm/dd")
options("openxlsx2.dateFormat" = "mm/dd/yyyy")
test_data <- 
  tibble::tribble(
    ~datetime_col, ~date_col,
    "2023-12-31 00:00:00", "2023-12-31"
  ) %>% 
  dplyr::mutate(
    dplyr::across(datetime_col, as.POSIXct),
    dplyr::across(date_col, as.Date)
  )

temp_path <- temp_xlsx()
write_xlsx(test_data, temp_path)

wb <- wb_load(temp_path)
# these cells have
wb$worksheets[[1]]$sheet_data$cc[3:4,]
#>    r row_r c_r c_s c_t c_cm c_ph c_vm     v f f_t f_ref f_ca f_si is
#> 3 A2     2   A   2                    45291                         
#> 4 B2     2   B   1                    45291
# these styles and these styles
wb$styles_mgr$styles$cellXfs
#> [1] "<xf numFmtId=\"0\" fontId=\"0\" fillId=\"0\" borderId=\"0\" xfId=\"0\"/>"                          
#> [2] "<xf applyNumberFormat=\"1\" borderId=\"0\" fillId=\"0\" fontId=\"0\" numFmtId=\"165\" xfId=\"0\"/>"
#> [3] "<xf applyNumberFormat=\"1\" borderId=\"0\" fillId=\"0\" fontId=\"0\" numFmtId=\"166\" xfId=\"0\"/>"
# use these numfmts
wb$styles_mgr$styles$numFmts
#> [1] "<numFmt numFmtId=\"165\" formatCode=\"mm/dd/yyyy\"/>"
#> [2] "<numFmt numFmtId=\"166\" formatCode=\"yyyy/mm/dd\"/>"

But if you want "yyyy/mm/dd" you might have to type "yyyy\\/mm\\/dd".

Maybe we can improve this, but no clue why MS requires this to create a forward slash, it is not required by the xml standard. Therefore blame MS, not us :)

[tpsteiner]

This looks like it should work. Could use getOption() but that should not impact the functionality of the code. GitHub could not show me any code reference for max widths option, this might be an overlooked defunct openxlsx option. It’s also not in our options reference.

openxlsx2/R/write.R

Lines 503 to 534 in 53aef14

	if (any(dc == openxlsx2_celltype[["short_date"]])) { # Date
	if (is.null(unlist(options("openxlsx2.dateFormat")))) {
	numfmt_dt <- 14
	} else {
	numfmt_dt <- unlist(options("openxlsx2.dateFormat"))
	}
	dim_sel <- get_data_class_dims("short_date")
	# message("short_date: ", dim_sel)
	wb$add_numfmt(
	sheet = sheetno,
	dim = dim_sel,
	numfmt = numfmt_dt
	)
	}
	if (any(dc == openxlsx2_celltype[["long_date"]])) {
	if (is.null(unlist(options("openxlsx2.datetimeFormat")))) {
	numfmt_posix <- 22
	} else {
	numfmt_posix <- unlist(options("openxlsx2.datetimeFormat"))
	}
	dim_sel <- get_data_class_dims("long_date")
	# message("long_date: ", dim_sel)
	wb$add_numfmt(
	sheet = sheetno,
	dim = dim_sel,
	numfmt = numfmt_posix
	)
	}

Thanks for the details in the latter responses. My mistake was misunderstanding what the date formatting options apply to, and you pointed out that it needs to apply to date/posix columns. Sorry about that.

As for the max width, it is included in the openxlsx2 Details reference of the col_widths section. I would like to learn more about this package so that I could maybe help you in the future. I'd also like to help add more parameters to the write_xlsx function.

Again, I'm really happy to see the effort you're putting into this. I'd wanted to use openxlsx in the past but thought it was lacking. You're making it so much better :)

[JanMarvin]

Thanks for the kind words! And I'm always interested in helping hands, especially with write_xlsx() as I don't use that function and so am always afraid I've unknowingly broken something 😄.

I always try to be friendly, open-minded, and quick to respond, and even if I don't want something, I try to give a good reason. Therefore, if there is something small to contribute, feel free to simply open a pull request and, if in doubt about larger things, simply ask beforehand whether it fits into the (current) concept.

random mumbling of an aging dude

Sharing my two cents of wisdom ... There is no direct path to becoming an open source contributor/developer. I guess the most important qualities are a desire to learn, a willingness to invest some time, and a desire to develop things that aren't currently possible or improve something in code or documentation. I never learned C++ (and I guess it shows in my code), but I got it to work by studying other people's code. And I used openxlsx, but I too felt it was missing some features, and then I invested a lot of time to develop the package I wanted/needed.[1] But that took a long time and wouldn't have been possible without the help of Jordan, Oliver and all the previous maintainers, contributors and bug reporters.

[1] And that is a very important thing in the open source world. There are always a lot of people telling you what's wrong with your project and what you should do differently, but at the end of the day you're developing for yourself and putting your free time into the project and it's okay if my project doesn't meet someone else's expectations. (No, this is not a tidyverse package and nobody has to come to me with pure benchmarks without suggestions for improvement, I'm not on the run.)