openxlsx uses different tablestyles from my normal Excel application

Question

I wanted to create an excel-file containing a table with a specific formatting. I used the openxlsx-package and looked in another excel-file for the name of the table style I wanted to use. After saving the new file, the table style was different than I expected. Furthermore all table styles in that document were different from what I normally have (different order and slightly different colors) and there is no way to change it to the style I want to use.

Can someone explain where the difference come from and how I can use the normal table styles? I am using Excel 2016.

example code for creating a table:

library(openxlsx)
df <- data.frame(V1 = 1:2, V2 = letters[1:2])

wb <- createWorkbook()
addWorksheet(wb, 1)
writeDataTable(wb, 1, x = df, tableStyle = "TableStyleMedium7")

saveWorkbook(wb, file = "test.xlsx", overwrite = TRUE)

My normal table styles:

openxlsx table styles:

Answer 1

If creating a new workbook from scratch, openxlsx has the default colours set to Office 2007-2010.

You can change the colours manually after crating you excel sheet by clicking through the ribbon (Page Layout->Colors->Office).

For a regularly run workbook, instead of creating a new file, pull in a template where you have set the desired default colors. Below I opened a new excel, saved it with the default modern colors and use it as the template:

library(openxlsx)

Data <- data.frame(
    X = sample(1:100),
    Y = sample(c("YES", "NO"), 10, replace = TRUE)
    )

#Save sheet to Excel
  # Load template
    wb <- loadWorkbook("blank_template.xlsx")
  # Add data to worksheet & write as table
     addWorksheet(wb, "rawdata")
     writeDataTable(wb, "rawdata", Data, tableStyle = "TablestyleMedium6")   
  # Remove the blank sheet from the template
     removeWorksheet(wb, "Sheet1")
  # Save workbook
     saveWorkbook(wb, file = paste0("output_file.xlsx"))

I have this template saved and pull it for several run reports as people can be funny about the old colors. I have a function saved that deletes the blank sheet and saves the file to save me time, but depending on how often you run a workbook its up to you how to trade off the efficiencies.