wb reverse functions

[JanMarvin]

Remove content from a worksheet

• add wb_remove_conditional_formatting(). part of #1010 #1011
• remove tables could be renamed to wb_remove_data_table()
• similar naming issues with: wb_clean_sheet() removes:
  • cell data (shared strings, inline strings or formulas)
  • styles
  • merged cells
• wb_remove_pivot_table()
• wb_remove_slicer() add remove_slicer(). part of #1010 #1020
• wb_remove_timeline() add remove_timeline(). part of #1010 #1021
• wb_remove_thread()
• wb_unfreeze_pane()

Remove images and charts to a worksheet

• wb_remove_image()
• wb_remove_plot()
• wb_remove_chartsheet() (might be handled by remove worksheet?)
• wb_remove_mschart()

Remove styling from a cell

• A simple helper to set cc$s to ""?

Other tools to interact with worksheets

• wb_reset_base_font()
• wb_reset_base_colors()
• wb_unprotect()
• wb_remove_sparklines()
• wb_remove_chart_xml()
• wb_remove_drawing()
• wb_remove_data_validation()
• wb_remove_form_control()
• wb_remove_ignore_error()
• wb_remove_page_break()
• wb_remove_mips()

[JanMarvin]

Work on wb_remove_drawing() for slicers and timelines on a sheet

# prepare data
df <- data.frame(
  AirPassengers = c(AirPassengers),
  time = seq(from = as.Date("1949-01-01"), to = as.Date("1960-12-01"), by = "month"),
  letters = letters[1:4]
)

# create workbook
wb <- wb_workbook()$
  add_worksheet("pivot")$
  add_worksheet("data")$
  add_data(x = df)

# get pivot table data source
df <- wb_data(wb, sheet = "data")

# create pivot table
wb$add_pivot_table(
  df,
  sheet = "pivot",
  rows = "time",
  cols = "letters",
  data = "AirPassengers",
  pivot_table = "airpassengers",
  params = list(
    compact = FALSE, outline = FALSE, compact_data = FALSE,
    row_grand_totals = FALSE, col_grand_totals = FALSE)
)

# add slicer
wb$add_slicer(
  df,
  dims = "E1:I7",
  sheet = "pivot",
  slicer = "letters",
  pivot_table = "airpassengers",
  params = list(choose = c(letters = 'x %in% c("a", "b")'))
)

# add timeline
wb$add_timeline(
  df,
  dims = "E9:I14",
  sheet = "pivot",
  timeline = "time",
  pivot_table = "airpassengers",
  params = list(
    beg_date = as.Date("1954-01-01"),
    end_date = as.Date("1961-01-01"),
    choose_beg = as.Date("1957-01-01"),
    choose_end = as.Date("1958-01-01"),
    level = 0,
    style = "TimeSlicerStyleLight2"
  )
)

# work on removal of slicers
get_drawing_types <- function(wb) {

  xml_names <- xml_node_name(wb$drawings[[1]], "xdr:wsDr")

  type <- vector("integer", length(xml_names))
  for (i in seq_along(xml_names)) {
    xml_name <- xml_names[i]

    xml_nds <- xml_node(wb$drawings[[1]], "xdr:wsDr", xml_name)

    # xml_node has no which
    mtchs <- match(xml_names, xml_names[i])
    mtchs[!is.na(mtchs)] <- cumsum(mtchs[!is.na(mtchs)])
    xml_nd <- xml_nds[mtchs[i]]

    if (grepl("http://schemas.microsoft.com/office/drawing/2010/slicer", xml_nd))
      type[i] <- 1
    
    if (grepl("http://schemas.microsoft.com/office/drawing/2012/timeslicer", xml_nd))
      type[i] <- 2

  }

  names(type) <- xml_names
  type
}

get_drawing_types(wb)


wb$remove_slicer(sheet = "pivot")

xml_names <- xml_node_name(wb$drawings[[1]], "xdr:wsDr")




# wb$drawings[[1]] <- xml_rm_child(wb$drawings[[1]], xml_child = xml_names[1], which = 1)

wb$open()