All cell styles are removed when registering new cell styles before adding the first worksheet

[mrainers]

I tried to create a workbook with the following workflow:

1. define styles for the workbook

foo_fill <- create_fill(patternType = "solid", 
                            fgColor = wb_color("blue"))
foo_font <- create_font(sz = 36, b = TRUE, color = wb_color("yellow"))

2. create a new workbook and assign the styles

wb <- wb_workbook() 
wb$styles_mgr$add(foo_fill, "foo")
wb$styles_mgr$add(foo_style, "foo")

foo_style <- create_cell_style(
  fill_id = wb$styles_mgr$get_fill_id("foo"),
  font_id = wb$styles_mgr$get_font_id("foo")
)

wb$styles_mgr$add(foo_style, "foo")

Let's see what cell styles are registered:

wb$styles_mgr$xf
#  typ id name
# 1  xf  0  foo

wb$styles_mgr$styles$cellXfs
# "<xf applyFill=\"1\" applyFont=\"1\" fillId=\"2\" fontId=\"1\"/>"
# -> unlike 'wb$styles_mgr$styles$fills' there is no default style

3. create Workbook sheets

wb$add_worksheet("test")

Check styles again:

wb$styles_mgr$xf
#   typ id name
# 1  xf  0  foo
wb$styles_mgr$styles$cellXfs
# "<xf numFmtId=\"0\" fontId=\"0\" fillId=\"0\" borderId=\"0\" xfId=\"0\"/>"
# -> already defined cell style is overwritten by the default style

So it is not possible to apply the predefined cell styles later in the workbook.

Also with this workflow it is also not possible to add additional cell styles later, after the worksheet creation. It seem that the styles_mgr is kind of messed up then for the cell styles.

Expected behavior

It should be possible to assign cell styles before adding the first worksheet.
Like the 'fills' or 'fonts' styles the cell styles should be initialized with a default before adding the first self defined style.

Version Info

R version 4.3.1 (2023-06-16 ucrt)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 19045)

attached packages: openxlsx2_1.0

[JanMarvin]

Thanks for the report @mrainers , I'll look into it. I don't really use the manual add styles approach anymore, therefore indeed there might be undetected hickups.

[mrainers]

By the way, are there other ways for defining a set of styles for later use in the workbook? By reading the vignette it didn't seem so to me.

[JanMarvin]

No, I simply apply the styles with wb_add_fill() etc where needed. Unless you require dozens of different styles it's what I find simpler to do.
But obviously in your case it fails and shouldn't, I'll look into it. Mostly an override for styles that should not happen if styles are already defined.

[JanMarvin]

A potential fix is in #805 . I'm not going to merge it without some further testing. I want to avoid breakage since this is a more delicate part of our code.

Just for reference, I'm now used to this way of styling cells. This applies the style on the cell range A1:D4. Since I usually apply the same style to header and body, this is a simpler solution for me:

library(openxlsx2)
wb <- wb_workbook()$
  add_worksheet("test")$
  add_fill(dims = "A1:D4", pattern_type = "solid", color = wb_color("blue"))$
  add_font(dims = "A1:D4", size = 36, bold = TRUE, color = wb_color("yellow"))$
  add_data(x = "Foo")

if (interactive()) wb$open()

[JanMarvin]

Should be fixed now in main. Please have a look if this solves your problem.

[mrainers]

I installed the newest version from github and can confirm that it is fixed. Thanks :-)