How to change tab color for existing worksheet?

[evan8daniels]

After combing through documentation for both openxlsx and openxlsx2, I can't seem to find any function that allows for changes to a tab color for an existing worksheet, though there are options for adding new worksheets and assigning colors to them. I would really appreciate any help. Thanks so much.

[JanMarvin]

Hi @evan8daniels , you can set the tab color in openxlsx2 like this:

library(openxlsx2)

# create a workbook with three sheets, set one upon creation and two afterwards
wb <- wb_workbook()$
  add_worksheet("S1")$
  add_worksheet("S2", tab_color = "blue")$
  add_worksheet()$
  set_page_setup(tab_color = wb_color("red"))$
  set_page_setup("S1", tab_color = wb_color("green"))

if (interactive()) wb$open()

[JanMarvin]

Hm, indeed, wasn't aware of the notice. There hasn't been a lot of development in this function. I should revisit it.

Could you share a few more lines? Do you run the latest openxlsx2? Is open2_wb a openxlsx2 workbook? Something that stems from this?

wb <- wb_load("filename.xlsx")
wb$set_page_setup(tab_color = wb_color("green"))

[evan8daniels]

After uninstalling/reinstalling, it looks like I am running version 0.6, which does not seem to be the latest version. I also should note that I am running R version 4.1.3, and am not able to update it -- not sure if that has anything to do with why the function is not working as intended, but I figured I would throw it out there.

open2_wb is indeed a openxlsx2 workbook. It was created using openxlsx, but I load it and run the code just as you have in your example:
open2_wb <- wb_load("Test.xlsx")
open2_wb$set_page_setup("Total", tabcolor = wb_color("yellow"))

[JanMarvin]

Puh, 0.6 is quite old. I’m in the same boat and have to run 4.1.2 at work, but openxlsx2 compiles with rtools 41.

[JanMarvin]

If - for whatever kind of reason you cannot update your openxlsx2 package - you could experiment with the following:

library(openxlsx2)

wb <- wb_load("testfile.xlsx")

tab_color <- xml_node_create("tabColor", xml_attributes = wb_color("green"))
tabColor <- sprintf('<sheetPr>%s</sheetPr>', tab_color)
wb$worksheets[[1]]$sheetPr <- tabColor
if (interactive()) wb$open()

This should work, but there might already be content in wb$worksheets[[1]]$sheetPr, so be careful and we have so many features and bugfixes, that an update to the latest release would be a huge benefit!

[evan8daniels]

That did indeed work! I'll see what I can figure out in terms of updating openxlsx2, but this should be sufficient for now. Thanks so much for your help.

[evan8daniels]

Hi @JanMarvin, sorry to reopen this issue, but it seems like hex color codes do not work with the method you suggested. I believe the correct syntax is tab_color <- xml_node_create("tabColor", xml_attributes = wb_color(hex = "#999999")), but instead of #999999, for example, the worksheet tab is black (no matter which hex code is passed through). Curious if you have any thoughts.

[JanMarvin]

Perfect gray for me with excel 2016 on windows. Please be aware that I have no influence how spreadsheet software presents the colors.

[evan8daniels]

Interesting. Thanks for checking it out.