CODEWITHSUNDEEP
rexcelopenxlsx2
Laura
Laura

Reputation: 329

Adding slicers using OpenXLSX2 in R

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

Upvotes: 0

Views: 296

Answers (1)

Jan Marvin
Jan Marvin

Reputation: 969

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()

Upvotes: 2

Related Questions