I'm using OpenXLSX2 in R to process some data and then output a formatted workbook. I have a pivot table that I would like to connect slicers to - I'm wondering if this is possible using OpenXLSX2. All I could think to try was pulling the wb$slicers from an existing workbook with the slicers I wanted and assigning it to the workbook being built, which did not work:
wb1<- wb_load("slicergrab.xlsx") #Has the pivot table I build below and the slicers I want with it
location<- c("London", "NYC", "NYC", "Berlin", "Madrid", "London")
amount<- c(7, 5, 3, 2.5, 6, 1)
example_df<- data.frame(location, amount)
wb<- wb_workbook() %>% wb_add_worksheet() %>% wb_add_data(x = example_df)
df<- wb_data(wb)
wb<- wb %>% wb_add_pivot_table(df, dims = "A3", rows = "location", data = "amount")
wb$slicers<- wb1$slicers
wb_save(wb, "test.xlsx", overwrite = T)
I've also tried just assigning wb$slicers the name of the variable I want a slicer of : wb$slicers<- "location" and wb$slicers<- df$location, neither of which works
Time has passed and the first support for slicers has been added to the main branch of openxlsx2. The code is still a bit experimental, but it will be part of the upcoming version 1.1. Now it is possible to create pivot tables and slicers (only) for these pivot tables.
library(openxlsx2)
example_df <- data.frame(
location = c("London", "NYC", "NYC", "Berlin", "Madrid", "London"),
amount = c(7, 5, 3, 2.5, 6, 1)
)
wb <- wb_workbook() %>%
wb_add_worksheet() %>%
wb_add_data(x = example_df)
df <- wb_data(wb)
wb <- wb %>%
wb_add_pivot_table(
df, dims = "A3", rows = "location", data = "amount",
slicer = "location", pivot_table = "pt1"
) %>%
wb_add_slicer(
x = df, dims = "E2",
slicer = "location", pivot_table = "pt1"
)
if (interactive()) wb$open()