R/class-workbook-wrappers.R

In openxlsx2: Read, Write and Edit 'xlsx' Files

#' Create a new Workbook object
#'
#' This function initializes and returns a [wbWorkbook] object,
#' which is the core structure for building and modifying openxml files
#' (`.xlsx`) in `openxlsx2`.
#'
#' You can define various metadata properties at creation, such as the
#' `creator`, `title`, `subject`, and timestamps. You can also
#' specify a workbook theme.
#'
#' The returned [wb_workbook()] object is an [R6::R6Class()] instance.
#' Once created, the standard workflow is to immediately add a worksheet
#' using [wb_add_worksheet()]. From there, you can populate the sheet with data
#' ([wb_add_data()]), or formulas ([wb_add_formula()]), and apply styling
#' or other elements.
#'
#' `theme` can be one of
#' "Atlas", "Badge", "Berlin", "Celestial", "Crop", "Depth", "Droplet",
#' "Facet", "Feathered", "Gallery", "Headlines", "Integral", "Ion",
#' "Ion Boardroom", "LibreOffice", "Madison", "Main Event", "Mesh",
#' "Office 2007 - 2010 Theme", "Office 2013 - 2022 Theme", "Office Theme",
#' "Old Office Theme", "Organic", "Parallax", "Parcel", "Retrospect",
#' "Savon", "Slice", "Vapor Trail", "View", "Wisp", "Wood Type"
#'
#' @param creator Creator of the workbook (your name). Defaults to login username or `options("openxlsx2.creator")` if set.
#' @param title,subject,category,keywords,comments,manager,company Workbook property, a string.
#' @param datetime_created The time of the workbook is created
#' @param datetime_modified The time of the workbook was last modified
#' @param theme Optional theme identified by string or number.
#'   See **Details** for options.
#' @param ... additional arguments
#' @return A `wbWorkbook` object
#'
#' @export
#' @family workbook wrappers
#'
#' @examples
#' ## Create a new workbook
#' wb <- wb_workbook()
#'
#' ## Set Workbook properties
#' wb <- wb_workbook(
#'   creator  = "Me",
#'   title    = "Expense Report",
#'   subject  = "Expense Report - 2022 Q1",
#'   category = "sales"
#' )
#'
#' ## Cloning a workbook
#' wb1 <- wb_workbook()
#' wb2 <- wb1$clone(deep = TRUE)
wb_workbook <- function(
  creator           = NULL,
  title             = NULL,
  subject           = NULL,
  category          = NULL,
  datetime_created  = Sys.time(),
  datetime_modified = NULL,
  theme             = NULL,
  keywords          = NULL,
  comments          = NULL,
  manager           = NULL,
  company           = NULL,
  ...
) {
  wbWorkbook$new(
    creator           = creator,
    title             = title,
    subject           = subject,
    category          = category,
    datetime_created  = datetime_created,
    datetime_modified = datetime_modified,
    theme             = theme,
    keywords          = keywords,
    comments          = comments,
    manager           = manager,
    company           = company,
    ...               = ...
  )
}


#' Save a workbook to file
#'
#' @details When saving a `wbWorkbook` to a file, memory usage may spike
#' depending on the worksheet size. This happens because the entire XML
#' structure is created in memory before writing to disk. The memory
#' required depends on worksheet size, as XML files consist of character
#' data and include additional overhead for validity checks.
#'
#' The `flush` argument streams worksheet XML data directly to disk,
#' avoiding the need to build the full XML tree in memory. This reduces
#' memory usage but skips some XML validity checks. It also bypasses
#' the `pugixml` functions that `openxlsx2` uses, omitting certain
#' preliminary sanity checks before writing. As the name suggests,
#' the output is simply flushed to disk.
#'
#' By default, the [utils::zip()] function is used to create output files.
#' This requires a working zip utility to be available on the system. A valid
#' zip program must be found either via `Sys.which("zip")` or through the
#' `R_ZIPCMD` environment variable.
#'
#' On Windows, a suitable zip tool is typically provided by Rtools. If
#' `R_ZIPCMD` is not set, `openxlsx2` will automatically use the first detected
#' Rtools installation. If no zip utility is available, `bsdtar` can be used as
#' an alternative. On Windows this is shipped as `tar.exe`; on Mac and Linux
#' it is usually available as `bsdtar` (often requiring installation of the
#' `archive` package).
#'
#' A further fallback—primarily for older Windows systems—is to point
#' `R_ZIPCMD` to `7z.exe`. This approach has not been extensively tested and is
#' not reliable with 7-Zip on macOS.
#'
#' As an additional fallback, the `zip` package can be used. It is no longer
#' listed in `Imports` and must be installed separately if needed.
#'
#' @param wb A `wbWorkbook` object to write to file
#' @param file A path to save the workbook to
#' @param overwrite If `FALSE`, will not overwrite when `file` already exists.
#' @param path Deprecated argument. Please use `file` in new code.
#' @param flush Experimental, streams the worksheet file to disk
#'
#' @export
#' @family workbook wrappers
#'
#' @returns the `wbWorkbook` object, invisibly
#'
#' @examples
#' ## Create a new workbook and add a worksheet
#' wb <- wb_workbook("Creator of workbook")
#' wb$add_worksheet(sheet = "My first worksheet")
#'
#' ## Save workbook to working directory
#' \donttest{
#' wb_save(wb, file = temp_xlsx(), overwrite = TRUE)
#'
#' ## do not try to find zip in Rtools
#' # options("openxlsx2.no_maybe_zip" = TRUE)
#'
#' ## do not use utils::zip, will try to use bsdtar
#' # options("openxlsx2.no_utils_zip" = TRUE)
#'
#' ## if the above is set as well, do not use bsdtar
#' # options("openxlsx2.no_bsdtar" = TRUE)
#'
#' ## use 7zip on Windows this works, on Mac not
#' # Sys.setenv("R_ZIPCMD" = "C:\\Program Files\\7-Zip\\7z.exe")
#'
#' # if the last one is left blank the fallback is zip::zip
#' openxlsx2::write_xlsx(x = cars, temp_xlsx())
#' }
wb_save <- function(wb, file = NULL, overwrite = TRUE, path = NULL, flush = FALSE) {
  assert_workbook(wb)
  wb$clone()$save(file = file, overwrite = overwrite, path = path, flush = flush)
}

# add data ----------------------------------------------------------------


#' Add data to a worksheet
#'
#' Add data to worksheet with optional styling.
#'
#' @param wb A Workbook object containing a worksheet.
#' @param sheet The worksheet to write to. Can be the worksheet index or name.
#' @param x Object to be written. For classes supported look at the examples.
#' @param dims Spreadsheet cell range that will determine `start_col` and `start_row`: "A1", "A1:B2", "A:B"
#' @param start_col A vector specifying the starting column to write `x` to.
#' @param start_row A vector specifying the starting row to write `x` to.
#' @param array A bool if the function written is of type array
#' @param col_names If `TRUE`, column names of `x` are written.
#' @param row_names If `TRUE`, the row names of `x` are written.
#' @param with_filter If `TRUE`, add filters to the column name row.
#'   NOTE: can only have one filter per worksheet.
#' @param name The name of a named region if specified.
#' @param sep Only applies to list columns. The separator used to collapse list
#'   columns to a character vector e.g. `sapply(x$list_column, paste, collapse = sep)`.
#' @param apply_cell_style Should we write cell styles to the workbook
#' @param remove_cell_style keep the cell style?
#' @param na Value used for replacing `NA` values from `x`. Default
#'   looks if `options("openxlsx2.na")` is set. Otherwise [na_strings()]
#'   uses the special `#N/A` value within the workbook.
#' @param inline_strings write characters as inline strings
#' @param enforce enforce that selected dims is filled. For this to work, `dims` must match `x`
#' @param ... additional arguments
#' @export
#' @details Formulae written using [wb_add_formula()] to a Workbook object will
#' not get picked up by `read_xlsx()`. This is because only the formula is written
#' into the worksheet and it will be evaluated once the file is opened in
#' spreadsheet software.
#' The string `"_openxlsx_NA"` is reserved for `openxlsx2`.
#' If the data frame contains this string, the output will be broken. Similar
#' factor labels `"_openxlsx_Inf"`, `"_openxlsx_nInf"`, and `"_openxlsx_NaN"`
#' are reserved.
#' The `na` string `"_openxlsx_NULL"` is a special that will be treated as NULL.
#' So that setting the option `options("openxlsx2.na" = "_openxlsx_NULL")` will
#' behave similar to `na = NULL`.
#'
#' Supported classes are data frames, matrices and vectors of various types and
#' everything that can be converted into a data frame with `as.data.frame()`.
#' Everything else that the user wants to write should either be converted into
#' a vector or data frame or written in vector or data frame segments. This
#' includes base classes such as `table`, which were coerced internally in the
#' predecessor of this package.
#'
#' Even vectors and data frames can consist of different classes. Many base
#' classes are covered, though not all and far from all third-party classes.
#' When data of an unknown class is written, it is handled with `as.character()`.
#' It is not possible to write character nodes beginning with `<r>` or `<r/>`. Both
#' are reserved for internal functions. If you need these. You have to wrap
#' the input string in `fmt_txt()`.
#'
#' The columns of `x` with class Date/POSIXt, currency, accounting, hyperlink,
#' percentage are automatically styled as dates, currency, accounting,
#' hyperlinks, percentages respectively. When writing POSIXt, the users local
#' timezone should not matter. The openxml standard does not have a timezone
#' and the conversion from the local timezone should happen internally, so that
#' date and time are converted, but the timezone is dropped. This conversion
#' could cause a minor precision loss. The datetime in R and in spreadsheets
#' might differ by 1 second, caused by floating point precision. When read from
#' the worksheet, starting with `openxlsx2` release `1.15` the datetime is
#' returned in `"UTC"`.
#'
#' Functions [wb_add_data()] and [wb_add_data_table()] behave quite similar. The
#' distinction is that the latter creates a table in the worksheet that can be
#' used for different kind of formulas and can be sorted independently, though
#' is less flexible than basic cell regions.
#' @family workbook wrappers
#' @family worksheet content functions
#' @return A `wbWorkbook`, invisibly.
#' @examples
#' ## See formatting vignette for further examples.
#'
#' ## Options for default styling (These are the defaults)
#' options("openxlsx2.dateFormat" = "mm/dd/yyyy")
#' options("openxlsx2.datetimeFormat" = "yyyy-mm-dd hh:mm:ss")
#' options("openxlsx2.numFmt" = NULL)
#'
#' #############################################################################
#' ## Create Workbook object and add worksheets
#' wb <- wb_workbook()
#'
#' ## Add worksheets
#' wb$add_worksheet("Cars")
#' wb$add_worksheet("Formula")
#'
#' x <- mtcars[1:6, ]
#' wb$add_data("Cars", x, start_col = 2, start_row = 3, row_names = TRUE)
#'
#' #############################################################################
#' ## Hyperlinks
#' ## - vectors/columns with class 'hyperlink' are written as hyperlinks'
#'
#' v <- rep("https://CRAN.R-project.org/", 4)
#' names(v) <- paste0("Hyperlink", 1:4) # Optional: names will be used as display text
#' class(v) <- "hyperlink"
#' wb$add_data("Cars", x = v, dims = "B32")
#'
#' #############################################################################
#' ## Formulas
#' ## - vectors/columns with class 'formula' are written as formulas'
#'
#' df <- data.frame(
#'   x = 1:3, y = 1:3,
#'   z = paste(paste0("A", 1:3 + 1L), paste0("B", 1:3 + 1L), sep = "+"),
#'   stringsAsFactors = FALSE
#' )
#'
#' class(df$z) <- c(class(df$z), "formula")
#'
#' wb$add_data(sheet = "Formula", x = df)
#'
#' #############################################################################
#' # update cell range and add mtcars
#' xlsxFile <- system.file("extdata", "openxlsx2_example.xlsx", package = "openxlsx2")
#' wb2 <- wb_load(xlsxFile)
#'
#' # read dataset with inlinestr
#' wb_to_df(wb2)
#' wb2 <- wb_add_data(wb2, sheet = 1, mtcars, dims = wb_dims(4, 4))
#' wb_to_df(wb2)
wb_add_data <- function(
    wb,
    sheet             = current_sheet(),
    x,
    dims              = wb_dims(start_row, start_col),
    start_col         = 1,
    start_row         = 1,
    array             = FALSE,
    col_names         = TRUE,
    row_names         = FALSE,
    with_filter       = FALSE,
    name              = NULL,
    sep               = ", ",
    apply_cell_style  = TRUE,
    remove_cell_style = FALSE,
    na                = na_strings(),
    inline_strings    = TRUE,
    enforce           = FALSE,
    ...
) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$add_data(
    sheet             = sheet,
    x                 = x,
    dims              = dims,
    start_col         = start_col,
    start_row         = start_row,
    array             = array,
    col_names         = col_names,
    row_names         = row_names,
    with_filter       = with_filter,
    name              = name,
    sep               = sep,
    apply_cell_style  = apply_cell_style,
    remove_cell_style = remove_cell_style,
    na                = na,
    inline_strings    = inline_strings,
    enforce           = enforce,
    ...               = ...
  )
}

#' Add a data table to a worksheet
#'
#' Add data to a worksheet and format as an spreadsheet table.
#'
#' @inherit wb_add_data details
#' @inheritParams wb_add_data
#' @param x A data frame
#' @param table_style Any table style name or "none" (see `vignette("openxlsx2_style_manual")`)
#' @param table_name Name of table in workbook. The table name must be unique.
#' @param with_filter If `TRUE`, columns with have filters in the first row.
#' @param sep Only applies to list columns. The separator used to collapse list
#'   columns to a character vector e.g.
#'   `sapply(x$list_column, paste, collapse = sep)`.
#' @param first_column logical. If `TRUE`, the first column is bold.
#' @param last_column logical. If `TRUE`, the last column is bold.
#' @param banded_rows logical. If `TRUE`, rows are color banded.
#' @param banded_cols logical. If `TRUE`, the columns are color banded.
#' @param total_row logical. With the default `FALSE` no total row is added.
#' @param params list. Optional arguments passed to the data table creation.
#' @param ... additional arguments
#'
#' @details # Modify total row argument
#' It is possible to further tweak the total row. In addition to the default
#' `FALSE` possible values are `TRUE` (the xlsx file will create column sums
#' each variable).
#'
#' In addition it is possible to tweak this further using a character string
#' with one of the following functions for each variable: `"average"`,
#' `"count"`, `"countNums"`, `"max"`, `"min"`, `"stdDev"`, `"sum"`, `"var"`.
#' It is possible to leave the cell empty `"none"` or to create a text input
#' using a named character with name `text` like: `c(text = "Total")`.
#' It's also possible to pass other spreadsheet software functions if they
#' return a single value and hence `"SUM"` would work too.
#'
#' @family worksheet content functions
#' @family workbook wrappers
#' @examples
#' wb <- wb_workbook()$add_worksheet()$
#'   add_data_table(
#'     x = as.data.frame(USPersonalExpenditure),
#'     row_names = TRUE,
#'     total_row = c(text = "Total", "none", "sum", "sum", "sum", "SUM"),
#'     stringsAsFactors = FALSE
#'   )
#' @export
wb_add_data_table <- function(
    wb,
    sheet             = current_sheet(),
    x,
    dims              = wb_dims(start_row, start_col),
    start_col         = 1,
    start_row         = 1,
    col_names         = TRUE,
    row_names         = FALSE,
    table_style       = "TableStyleLight9",
    table_name        = NULL,
    with_filter       = TRUE,
    sep               = ", ",
    first_column      = FALSE,
    last_column       = FALSE,
    banded_rows       = TRUE,
    banded_cols       = FALSE,
    apply_cell_style  = TRUE,
    remove_cell_style = FALSE,
    na                = na_strings(),
    inline_strings    = TRUE,
    total_row         = FALSE,
    params            = NULL,
    ...
) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$add_data_table(
    sheet             = sheet,
    x                 = x,
    dims              = dims,
    start_col         = start_col,
    start_row         = start_row,
    col_names         = col_names,
    row_names         = row_names,
    table_style       = table_style,
    table_name        = table_name,
    with_filter       = with_filter,
    sep               = sep,
    first_column      = first_column,
    last_column       = last_column,
    banded_rows       = banded_rows,
    banded_cols       = banded_cols,
    apply_cell_style  = apply_cell_style,
    remove_cell_style = remove_cell_style,
    na                = na,
    inline_strings    = inline_strings,
    total_row         = total_row,
    params            = params,
    ...               = ...
  )
}

#' Add a pivot table to a worksheet
#'
#' The data must be specified using [wb_data()] to ensure the function works.
#' The sheet will be empty unless it is opened in spreadsheet software. Find
#' more details in the [section about pivot tables](https://janmarvin.github.io/ox2-book/chapters/openxlsx2_pivot_tables.html)
#' in the openxlsx2 book.
#'
#' @details
#' The pivot table is not actually written to the worksheet, therefore the cell
#' region has to remain empty. What is written to the workbook is something
#' like a recipe how the spreadsheet software has to construct the pivot table
#' when opening the file.
#'
#' It is possible to add slicers to the pivot table. For this the pivot
#' table has to be named and the variable used as slicer, must be part
#' of the selected pivot table names (`cols`, `rows`, `filter`, or
#' `slicer`). If these criteria are matched, a slicer can be added
#' using [wb_add_slicer()].
#'
#' Be aware that you should always test on a copy if a `param` argument works
#' with a pivot table. Not only to check if the desired effect appears, but
#' first and foremost if the file loads. Wildly mixing params might brick the
#' output file and cause spreadsheet software to crash.
#'
#' `fun` can be any of `AVERAGE`, `COUNT`, `COUNTA`, `MAX`, `MIN`,
#' `PRODUCT`, `STDEV`, `STDEVP`, `SUM`, `VAR`, `VARP`.
#'
#' `show_data_as` can be any of `normal`, `difference`, `percent`, `percentDiff`,
#' `runTotal`, `percentOfRow`, `percentOfCol`, `percentOfTotal`, `index`.
#'
#' It is possible to calculate data fields if the formula is assigned as a
#' variable name for the field to calculate. This would look like this:
#' `data = c("am", "disp/cyl" = "New")`
#'
#' Possible `params` arguments are listed below. Pivot tables accepts more
#' parameters, but they were either not tested or misbehaved (probably because
#' we misunderstood how the parameter should be used).
#'
#' Boolean arguments:
#' * apply_alignment_formats
#' * apply_number_formats
#' * apply_border_formats
#' * apply_font_formats
#' * apply_pattern_formats
#' * apply_width_height_formats
#' * no_style
#' * compact
#' * outline
#' * compact_data
#' * row_grand_totals
#' * col_grand_totals
#'
#' Table styles accepting character strings:
#' * auto_format_id: style id as character in the range of 4096 to 4117
#' * table_style: a predefined (pivot) table style `"TableStyleMedium23"`
#' * show_data_as: accepts character strings as listed above
#'
#' Miscellaneous:
#' * numfmt: accepts vectors of the form `c(formatCode = "0.0%")`
#' * choose: select variables in the form of a named logical vector like
#'  `c(agegp = 'x > "25-34"')` for the `esoph` dataset.
#' * sort_item: named list of index or character vectors
#'
#' @param wb A Workbook object containing a #' worksheet.
#' @param x A `data.frame` that inherits the [`wb_data`][wb_data()] class.
#' @param sheet A worksheet containing a #'
#' @param dims The worksheet cell where the pivot table is placed
#' @param filter The column name(s) of `x` used for filter.
#' @param rows The column name(s) of `x` used as rows
#' @param cols The column names(s) of `x` used as cols
#' @param data The column name(s) of `x` used as data
#' @param fun A vector of functions to be used with `data`. See **Details** for the list of available options.
#' @param params A list of parameters to modify pivot table creation. See **Details** for available options.
#' @param pivot_table An optional name for the pivot table
#' @param slicer,timeline Any additional column name(s) of `x` used as slicer/timeline
#' @seealso [wb_data()]
#' @examples
#' wb <- wb_workbook()
#' wb <- wb_add_worksheet(wb)
#' wb <- wb_add_data(wb, x = mtcars)
#'
#' df <- wb_data(wb, sheet = 1)
#'
#' # default pivot table
#' wb <- wb_add_pivot_table(wb, x = df, dims = "A3",
#'     filter = "am", rows = "cyl", cols = "gear", data = "disp"
#'   )
#'   # with parameters
#' wb <- wb_add_pivot_table(wb, x = df,
#'     filter = "am", rows = "cyl", cols = "gear", data = "disp",
#'     params = list(no_style = TRUE, numfmts = c(formatCode = "##0.0"))
#'   )
#' @family workbook wrappers
#' @family worksheet content functions
#' @export
wb_add_pivot_table <- function(
    wb,
    x,
    sheet = next_sheet(),
    dims = "A3",
    filter,
    rows,
    cols,
    data,
    fun,
    params,
    pivot_table,
    slicer,
    timeline
) {
  assert_workbook(wb)
  if (missing(filter))      filter      <- substitute()
  if (missing(rows))        rows        <- substitute()
  if (missing(cols))        cols        <- substitute()
  if (missing(data))        data        <- substitute()
  if (missing(fun))         fun         <- substitute()
  if (missing(params))      params      <- substitute()
  if (missing(pivot_table)) pivot_table <- substitute()
  if (missing(slicer))      slicer      <- substitute()
  if (missing(timeline))    timeline    <- substitute()

  wb$clone()$add_pivot_table(
    x           = x,
    sheet       = sheet,
    dims        = dims,
    filter      = filter,
    rows        = rows,
    cols        = cols,
    data        = data,
    fun         = fun,
    params      = params,
    pivot_table = pivot_table,
    slicer      = slicer,
    timeline    = timeline
  )

}

#' Add a slicer/timeline to a pivot table
#'
#' Add a slicer/timeline to a previously created pivot table. This function is still experimental and might be changed/improved in upcoming releases.
#'
#' @details
#' This assumes that the slicer/timeline variable initialization has happened before. Unfortunately, it is unlikely that we can guarantee this for loaded workbooks, and we *strictly* discourage users from attempting this. If the variable has not been initialized properly, this may cause the spreadsheet software to crash.
#' Although it is documented that slicers should use "TimelineStyleLight\[1-6\]" and "TimelineStyleDark\[1-6\]" they use slicer styles.
#'
#' Possible `params` arguments for slicers are listed below.
#' * edit_as: "twoCell" to place the slicer into the cells
#' * column_count: integer used as column count
#' * sort_order: "descending" / "ascending"
#' * choose: select variables in the form of a named logical vector like
#'  `c(agegp = 'x > "25-34"')` for the `esoph` dataset.
#' * locked_position
#' * start_item
#' * hide_no_data_items
#'
#' Possible `params` arguments for timelines are listed below.
#' * beg_date/end_date: dates when the timeline should begin or end
#' * choose_beg/choose_end: dates when the selection should begin or end
#' * scroll_position
#' * show_selection_label
#' * show_time_level
#' * show_horizontal_scrollbar
#'
#' Possible common `params`:
#' * caption: string used for a caption
#' * style: "SlicerStyleLight\[1-6\]", "SlicerStyleDark\[1-6\]" only for slicer "SlicerStyleOther\[1-2\]"
#' * level: the granularity of the slicer (for timeline 0 = year, 1 = quarter, 2 = month)
#' * show_caption: logical if caption should be shown or not
#'
#' Removing works on the spreadsheet level. Therefore all slicers/timelines are removed from a worksheet. At the moment the drawing reference remains on the spreadsheet. Therefore spreadsheet software that does not handle slicers/timelines will still show the drawing.
#'
#' @param wb A Workbook object containing a worksheet.
#' @param x A `data.frame` that inherits the [`wb_data`][wb_data()] class.
#' @param sheet A worksheet
#' @param dims The worksheet cell where the pivot table is placed
#' @param pivot_table The name of a pivot table
#' @param slicer,timeline A variable used as slicer/timeline for the pivot table
#' @param params A list of parameters to modify pivot table creation. See **Details** for available options.
#' @family workbook wrappers
#' @family worksheet content functions
#' @examples
#' # 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],
#'   stringsAsFactors = FALSE
#' )
#'
#' # 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"
#'   )
#' )
#' @export
wb_add_slicer <- function(
    wb,
    x,
    dims        = "A1",
    sheet       = current_sheet(),
    pivot_table,
    slicer,
    params
) {
  assert_workbook(wb)
  if (missing(params)) params <- substitute()

  wb$clone(deep = TRUE)$add_slicer(
    x           = x,
    sheet       = sheet,
    dims        = dims,
    pivot_table = pivot_table,
    slicer      = slicer,
    params      = params
  )

}

#' @rdname wb_add_slicer
#' @export
wb_remove_slicer <- function(
    wb,
    sheet       = current_sheet()
) {
  assert_workbook(wb)
  wb$clone()$remove_slicer(
    sheet       = sheet
  )
}

#' @rdname wb_add_slicer
#' @export
wb_add_timeline <- function(
    wb,
    x,
    dims        = "A1",
    sheet       = current_sheet(),
    pivot_table,
    timeline,
    params
) {
  assert_workbook(wb)
  if (missing(params)) params <- substitute()

  wb$clone(deep = TRUE)$add_timeline(
    x           = x,
    sheet       = sheet,
    dims        = dims,
    pivot_table = pivot_table,
    timeline    = timeline,
    params      = params
  )

}

#' @rdname wb_add_slicer
#' @export
wb_remove_timeline <- function(
    wb,
    sheet       = current_sheet()
) {
  assert_workbook(wb)
  wb$clone()$remove_timeline(
    sheet       = sheet
  )
}

#' Add a formula to a cell range in a worksheet
#'
#' This function can be used to add a formula to a worksheet.
#' In `wb_add_formula()`, you can provide the formula as a character vector.
#'
#' @details
#' Currently, the local translations of formulas are not supported.
#' Only the English functions work.
#'
#' The examples below show a small list of possible formulas:
#'
#' * SUM(B2:B4)
#' * AVERAGE(B2:B4)
#' * MIN(B2:B4)
#' * MAX(B2:B4)
#' * ...
#'
#' It is possible to pass vectors to `x`. If `x` is an array formula, it will
#' take `dims` as a reference. For some formulas, the result will span multiple
#' cells (see the `MMULT()` example below). For this type of formula, the
#' output range must be known a priori and passed to `dims`, otherwise only the
#' value of the first cell will be returned. This type of formula, whose result
#' extends over several cells, is only possible with single strings. If a vector
#' is passed, it is only possible to return individual cells.
#'
#' Custom functions can be registered as lambda functions in the workbook. For
#' this you take the function you want to add `"LAMBDA(x, y, x + y)"` and escape
#' it as follows. `LAMBDA()` is a future function and needs a prefix `_xlfn`. The
#' arguments need a prefix `_xlpm.`. So the full function looks like this:
#' `"_xlfn.LAMBDA(_xlpm.x, _xlpm.y, _xlpm.x + _xlpm.y)"`. These custom formulas
#' are accessible via the named region manager and can be removed with
#' [wb_remove_named_region()]. Contrary to other formulas, custom formulas must
#' be registered with the workbook before they can be used (see the example
#' below).
#'
#' If a function that normally works in spreadsheet software does not behave
#' as expected when written using [wb_add_formula()], e.g., if spurious
#' `@` symbols appear in the formula, it is likely that the formula is either
#' an array formula or requires a future function prefix. In modern spreadsheet
#' software, it is no longer straightforward to detect whether a formula is an
#' array formula, since this hidden in cell metadata (cm). Therefore, a formula
#' like `SUM(1+(A1:A2))` will not be displayed as `{SUM(1+(A1:A2))}`.
#'
#' @param wb A Workbook object containing a worksheet.
#' @param sheet The worksheet to write to. (either as index or name)
#' @param x A formula as character vector.
#' @param dims Spreadsheet dimensions that will determine where `x` spans: "A1", "A1:B2", "A:B"
#' @param start_col A vector specifying the starting column to write to.
#' @param start_row A vector specifying the starting row to write to.
#' @param array A bool if the function written is of type array
#' @param cm A special kind of array function that hides the curly braces in the cell.
#'   Add this, if you see "@" inserted into your formulas.
#' @param apply_cell_style Should we write cell styles to the workbook?
#' @param remove_cell_style Should we keep the cell style?
#' @param enforce enforce dims
#' @param shared shared formula
#' @param name The name of a named region if specified.
#' @param ... additional arguments
#' @return The workbook, invisibly.
#' @family workbook wrappers
#' @family worksheet content functions
#' @export
#' @examples
#' wb <- wb_workbook()$add_worksheet()
#' wb$add_data(dims = wb_dims(rows = 1, cols = 1:3), x = c(4, 5, 8))
#'
#' # calculate the sum of elements.
#' wb$add_formula(dims = "D1", x = "SUM(A1:C1)")
#'
#' # array formula with result spanning over multiple cells
#' mm <- matrix(1:4, 2, 2)
#'
#' wb$add_worksheet()$
#'  add_data(x = mm, dims = "A1:B2", col_names = FALSE)$
#'  add_data(x = mm, dims = "A4:B5", col_names = FALSE)$
#'  add_formula(x = "MMULT(A1:B2, A4:B5)", dims = "A7:B8", array = TRUE)
#'
#' # add shared formula
#' wb$add_worksheet()$
#'  add_data(x = matrix(1:25, ncol = 5, nrow = 5))$
#'  add_formula(x = "SUM($A2:A2)", dims = "A8:E12", shared = TRUE)
#'
#' # add a custom formula, first define it, then use it
#' wb$add_formula(x = c(YESTERDAY = "_xlfn.LAMBDA(TODAY() - 1)"))
#' wb$add_formula(x = "=YESTERDAY()", dims = "A1", cm = TRUE)
#'
wb_add_formula <- function(
    wb,
    sheet             = current_sheet(),
    x,
    dims              = wb_dims(start_row, start_col),
    start_col         = 1,
    start_row         = 1,
    array             = FALSE,
    cm                = FALSE,
    apply_cell_style  = TRUE,
    remove_cell_style = FALSE,
    enforce           = FALSE,
    shared            = FALSE,
    name              = NULL,
    ...
) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$add_formula(
    sheet             = sheet,
    x                 = x,
    dims              = dims,
    start_col         = start_col,
    start_row         = start_row,
    array             = array,
    cm                = cm,
    apply_cell_style  = apply_cell_style,
    remove_cell_style = remove_cell_style,
    enforce           = enforce,
    shared            = shared,
    name              = name,
    ...               = ...
  )
}

#' wb_add_hyperlink
#'
#' Helper to add shared hyperlinks into a worksheet or remove shared hyperlinks from a worksheet
#'
#' @details
#' There are multiple ways to add hyperlinks into a worksheet. One way is to construct a formula with [create_hyperlink()] another is to assign a class `hyperlink` to a column of a data frame.
#' Contrary to the previous method, shared hyperlinks are not cell formulas in the worksheet, but references in the worksheet relationship and hyperlinks in the worksheet xml structure.
#' These shared hyperlinks can be reused and they are not visible to spreadsheet users as `HYPERLINK()` formulas.
#'
#' @param wb A Workbook object containing a worksheet.
#' @param sheet The worksheet to write to. (either as index or name)
#' @param dims Spreadsheet dimensions that will determine where the hyperlink reference spans: "A1", "A1:B2", "A:B"
#' @param target An optional target, if no target is specified, it is assumed that the cell already contains a reference (the cell could be a url or a filename)
#' @param tooltip An optional description for a variable that will be visible when hovering over the link text in the spreadsheet
#' @param is_external A logical indicating if the hyperlink is external (a url, a mail address, a file) or internal (a reference to worksheet cells)
#' @param col_names Whether or not the object contains column names. If yes the first column of the dimension will be ignored
#' @export
#' @family workbook wrappers
#' @family worksheet content functions
#' @examples
#' wb <- wb_workbook()$add_worksheet()$
#'   add_data(x = "openxlsx2 on CRAN")$
#'   add_hyperlink(target = "https://cran.r-project.org/package=openxlsx2",
#'                 tooltip = "The canonical form to link to our CRAN page.")
#'
#' wb$remove_hyperlink()
wb_add_hyperlink <- function(
    wb,
    sheet       = current_sheet(),
    dims        = "A1",
    target      = NULL,
    tooltip     = NULL,
    is_external = TRUE,
    col_names   = FALSE
  ) {

  assert_workbook(wb)

  wb$clone(deep = TRUE)$add_hyperlink(
    sheet       = sheet,
    dims        = dims,
    target      = target,
    tooltip     = tooltip,
    is_external = is_external,
    col_names   = col_names
  )
}

#' @rdname wb_add_hyperlink
#' @export
wb_remove_hyperlink <- function(wb, sheet = current_sheet(), dims = NULL) {
  assert_workbook(wb)
  wb$clone()$remove_hyperlink(sheet = sheet, dims = dims)
}

#' Update a data table position in a worksheet
#'
#' Update the position of a data table, possibly written using [wb_add_data_table()]
#' @param wb A workbook
#' @param sheet A worksheet
#' @param dims Cell range used for new data table.
#' @param tabname A table name
#'
#' @details
#' Be aware that this function does not alter any filter.
#' Excluding or adding rows does not make rows appear nor will it hide them.
#' @examples
#' wb <- wb_workbook()$add_worksheet()$add_data_table(x = mtcars)
#' wb$update_table(tabname = "Table1", dims = "A1:J4")
#' @export
wb_update_table <- function(wb, sheet = current_sheet(), dims = "A1", tabname) {
  assert_workbook(wb)
  wb$clone()$update_table(sheet = sheet, dims = dims, tabname = tabname)
}

#' Copy cells around within a worksheet
#'
#' @param wb A workbook
#' @param sheet a worksheet
#' @param dims A cell where to place the copy
#' @param data A [`wb_data`][wb_data()] object containing cells to copy
#' @param as_value Should a copy of the value be written?
#' @param as_ref Should references to the cell be written?
#' @param transpose Should the data be written transposed?
#' @param ... additional arguments passed to add_data() if used with `as_value`
#' @examples
#' wb <- wb_workbook()$
#' add_worksheet()$
#'   add_data(x = mtcars)$
#'   add_fill(dims = "A1:F1", color = wb_color("yellow"))
#'
#' dat <- wb_data(wb, dims = "A1:D4", col_names = FALSE)
#' # 1:1 copy to M2
#' wb$
#'   clone_worksheet(old = 1, new = "Clone1")$
#'   copy_cells(data = dat, dims = "M2")
#' @family workbook wrappers
#' @seealso [wb_data()]
#' @export
#' @returns the `wbWorkbook` invisibly
wb_copy_cells <- function(
    wb,
    sheet     = current_sheet(),
    dims      = "A1",
    data,
    as_value  = FALSE,
    as_ref    = FALSE,
    transpose = FALSE,
    ...
) {
  assert_workbook(wb)
  wb$
    clone(deep = TRUE)$
    copy_cells(
      sheet     = sheet,
      dims      = dims,
      data      = data,
      as_value  = as_value,
      as_ref    = as_ref,
      transpose = transpose,
      ...       = ...
    )
}

# merge cells ------------------------------------------------------------------

#'
#' Merge cells within a worksheet
#'
#' Worksheet cell merging
#'
#' @details
#' If using the deprecated arguments `rows` and `cols` with a merged region must be rectangular,
#' only min and max of `cols` and `rows` are used.
#'
#' @param wb A Workbook object
#' @param sheet A name or index of a worksheet
#' @param dims worksheet cells
#' @param solve logical if intersecting merges should be solved
#' @param direction direction in which to split the cell merging. Allows "row" or "col"
#' @param ... additional arguments
#'
#' @examples
#' # Create a new workbook
#' wb <- wb_workbook()$add_worksheet()
#'
#' # Merge cells: Row 2 column C to F (3:6)
#' wb <- wb_merge_cells(wb, dims = "C3:F6")
#'
#' # Merge cells:Rows 10 to 20 columns A to J (1:10)
#' wb <- wb_merge_cells(wb, dims = wb_dims(rows = 10:20, cols = 1:10))
#'
#' wb$add_worksheet()
#'
#' ## Intersecting merges
#' wb <- wb_merge_cells(wb, dims = wb_dims(cols = 1:10, rows = 1))
#' wb <- wb_merge_cells(wb, dims = wb_dims(cols = 5:10, rows = 2))
#' wb <- wb_merge_cells(wb, dims = wb_dims(cols = 1:10, rows = 12))
#' try(wb_merge_cells(wb, dims = "A1:A10"))
#'
#' ## remove merged cells
#' # removes any intersecting merges
#' wb <- wb_unmerge_cells(wb, dims = wb_dims(cols = 1, rows = 1))
#' wb <- wb_merge_cells(wb, dims = "A1:A10")
#'
#' # or let us decide how to solve this
#' wb <- wb_merge_cells(wb, dims = "A1:A10", solve = TRUE)
#'
#' @family workbook wrappers
#' @family worksheet content functions
#' @export
wb_merge_cells <- function(wb, sheet = current_sheet(), dims = NULL, solve = FALSE, direction = NULL, ...) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$merge_cells(sheet = sheet, dims = dims, solve = solve, direction = direction, ... = ...)
}

#' @export
#' @rdname wb_merge_cells
wb_unmerge_cells <- function(wb, sheet = current_sheet(), dims = NULL, ...) {
  assert_workbook(wb)
  wb$clone()$unmerge_cells(sheet = sheet, dims = dims, ... = ...)
}


# sheets ------------------------------------------------------------------

#' Add a chartsheet to a workbook
#'
#' @description
#' The `wb_add_chartsheet()` function appends a specialized chartsheet to a
#' `wbWorkbook` object. Unlike standard worksheets, which contain a grid of cells,
#' a chartsheet is dedicated exclusively to the display of a single, full-page
#' chart.
#'
#' @details
#' A chartsheet is a distinct sheet type in the OpenXML specification. It
#' does not support standard cell data, grid lines, or typical worksheet
#' features. Its primary purpose is to provide a high-level, focused view of a
#' graphical representation.
#'
#' **Important:** A chartsheet must contain a chart object to be
#' valid. Adding a chartsheet without subsequently attaching a chart via
#' [wb_add_mschart()] will result in a corrupt workbook
#' that may fail to open in spreadsheet software.
#'
#' Like standard worksheets, chartsheets support visual customization such as
#' `tab_color`, `zoom` levels, and various `visible` states.
#'
#' @param wb A [wbWorkbook] object to which the new chartsheet will be attached.
#' @param sheet A character string for the chartsheet name. Defaults to a
#'   sequentially generated name (e.g., "Sheet 1").
#' @param tab_color The color of the sheet tab. Accepts a [wb_color()] object,
#'   a standard R color name, or a hex color code.
#' @param zoom The zoom level as a percentage; a numeric value between 10 and 400.
#' @param visible The visibility state of the sheet. Options include "visible",
#'   "hidden", or "veryHidden".
#' @param ... Additional arguments passed to internal configuration methods.
#'
#' @family workbook wrappers
#' @seealso [wb_add_mschart()], [wb_add_worksheet()]
#' @export
wb_add_chartsheet <- function(
  wb,
  sheet     = next_sheet(),
  tab_color = NULL,
  zoom      = 100,
  visible   = c("true", "false", "hidden", "visible", "veryhidden"),
  ...
) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$add_chartsheet(
    sheet       = sheet,
    tab_color   = tab_color,
    zoom        = zoom,
    visible     = visible,
    ...         = ...
  )
}

#' Add a worksheet to a workbook
#'
#' @description
#' The `wb_add_worksheet()` function is a fundamental step in workbook construction,
#' appending a new worksheet to a `wbWorkbook` object. It provides extensive
#' parameters for configuring the sheet's initial state, including visibility,
#' visual cues like grid lines, and metadata such as tab colors and page setup
#' properties.
#'
#' @details
#' Worksheets can be configured with complex headers and footers that adapt to
#' document layout requirements. The function supports distinct definitions for
#' odd pages, even pages, and the first page of a document. Headers and footers
#' are defined as character vectors of length three, representing the left,
#' center, and right sections respectively.
#'
#' Within these sections, special dynamic tags can be utilized to include
#' automatic metadata:
#' * `&[Page]`: The current page number
#' * `&[Pages]`: The total number of pages
#' * `&[Date]`: The current system date
#' * `&[Time]`: The current system time
#' * `&[Path]`: The file path of the workbook
#' * `&[File]`: The name of the file
#' * `&[Tab]`: The name of the worksheet
#'
#' The function also initializes the sheet view. Parameters like `zoom` and
#' `grid_lines` determine how the sheet is presented upon opening the file in
#' spreadsheet software. For advanced page configuration, such as DPI settings
#' and paper sizes, the function integrates with the package-wide options system
#' but allows for per-sheet overrides.
#'
#' @param wb A [wbWorkbook] object to which the new worksheet will be attached.
#' @param sheet A character string for the worksheet name. Defaults to a
#'   sequentially generated name (e.g., "Sheet 1").
#' @param grid_lines Logical; if `FALSE`, the worksheet grid lines are hidden.
#' @param row_col_headers Logical; if `FALSE`, row numbers and column letters are hidden.
#' @param tab_color The color of the worksheet tab. Accepts a [wb_color()] object,
#'   a standard R color name, or a hex color code (e.g., "#4F81BD").
#' @param zoom The sheet zoom level as a percentage; a numeric value between
#'   10 and 400. Values below 10 default to 10.
#' @param header,footer Default character vectors of length three for the left,
#'   center, and right sections of the header or footer.
#' @param odd_header,odd_footer Specific definitions for odd-numbered pages.
#'   Defaults to the values provided in `header` and `footer`.
#' @param even_header,even_footer Specific definitions for even-numbered pages.
#'   Defaults to the values provided in `header` and `footer`.
#' @param first_header,first_footer Specific definitions for the first page
#'   of the worksheet. Defaults to the values provided in `header` and `footer`.
#' @param visible The visibility state of the sheet. One of "visible", "hidden",
#'   or "veryHidden".
#' @param has_drawing _defunct_
#' @param paper_size An integer code representing a standard paper size.
#'   Refer to [wb_page_setup()] for a complete list of codes.
#' @param orientation The page orientation, either "portrait" or "landscape".
#' @param hdpi,vdpi The horizontal and vertical DPI (dots per inch) for
#'   printing and rendering. Can be set globally via `options("openxlsx2.hdpi")`.
#' @param ... Additional arguments passed to internal sheet configuration methods.
#'
#' @return The [wbWorkbook] object, invisibly.
#'
#' @section Notes:
#' * As of recent versions, the `has_drawing` argument has been removed and
#'     is no longer part of the public API.
#' * If `zoom` is provided outside the 10–400 range, it is automatically
#'     clamped to the nearest boundary.
#' * The `sheet` name is validated against a set of illegal characters
#'     prohibited by spreadsheet software standards.
#'
#' @family workbook wrappers
#' @examples
#' ## Create a new workbook
#' wb <- wb_workbook()
#'
#' ## Add a worksheet
#' wb$add_worksheet("Sheet 1")
#' ## No grid lines
#' wb$add_worksheet("Sheet 2", grid_lines = FALSE)
#' ## A red tab color
#' wb$add_worksheet("Sheet 3", tab_color = wb_color("red"))
#' ## All options combined with a zoom of 40%
#' wb$add_worksheet("Sheet 4", grid_lines = FALSE, tab_color = wb_color(hex = "#4F81BD"), zoom = 40)
#'
# TODO maybe leave the special cases to wb_set_header_footer?
#' ## Headers and Footers
#' wb$add_worksheet("Sheet 5",
#'   header = c("ODD HEAD LEFT", "ODD HEAD CENTER", "ODD HEAD RIGHT"),
#'   footer = c("ODD FOOT RIGHT", "ODD FOOT CENTER", "ODD FOOT RIGHT"),
#'   even_header = c("EVEN HEAD LEFT", "EVEN HEAD CENTER", "EVEN HEAD RIGHT"),
#'   even_footer = c("EVEN FOOT RIGHT", "EVEN FOOT CENTER", "EVEN FOOT RIGHT"),
#'   first_header = c("TOP", "OF FIRST", "PAGE"),
#'   first_footer = c("BOTTOM", "OF FIRST", "PAGE")
#' )
#'
#' wb$add_worksheet("Sheet 6",
#'   header = c("&[Date]", "ALL HEAD CENTER 2", "&[Page] / &[Pages]"),
#'   footer = c("&[Path]&[File]", NA, "&[Tab]"),
#'   first_header = c(NA, "Center Header of First Page", NA),
#'   first_footer = c(NA, "Center Footer of First Page", NA)
#' )
#'
#' wb$add_worksheet("Sheet 7",
#'   header = c("ALL HEAD LEFT 2", "ALL HEAD CENTER 2", "ALL HEAD RIGHT 2"),
#'   footer = c("ALL FOOT RIGHT 2", "ALL FOOT CENTER 2", "ALL FOOT RIGHT 2")
#' )
#'
#' wb$add_worksheet("Sheet 8",
#'   first_header = c("FIRST ONLY L", NA, "FIRST ONLY R"),
#'   first_footer = c("FIRST ONLY L", NA, "FIRST ONLY R")
#' )
#'
#' ## Need data on worksheet to see all headers and footers
#' wb$add_data(sheet = 5, 1:400)
#' wb$add_data(sheet = 6, 1:400)
#' wb$add_data(sheet = 7, 1:400)
#' wb$add_data(sheet = 8, 1:400)
#' @export
wb_add_worksheet <- function(
  wb,
  sheet           = next_sheet(),
  grid_lines      = TRUE,
  row_col_headers = TRUE,
  tab_color       = NULL,
  zoom            = 100,
  header          = NULL,
  footer          = NULL,
  odd_header      = header,
  odd_footer      = footer,
  even_header     = header,
  even_footer     = footer,
  first_header    = header,
  first_footer    = footer,
  visible         = c("true", "false", "hidden", "visible", "veryhidden"),
  has_drawing     = FALSE,
  paper_size      = getOption("openxlsx2.paperSize", default = 9),
  orientation     = getOption("openxlsx2.orientation", default = "portrait"),
  hdpi            = getOption("openxlsx2.hdpi", default = getOption("openxlsx2.dpi", default = 300)),
  vdpi            = getOption("openxlsx2.vdpi", default = getOption("openxlsx2.dpi", default = 300)),
  ...
) {

  assert_workbook(wb)
  wb$clone()$add_worksheet(
    sheet           = sheet,
    grid_lines      = grid_lines,
    row_col_headers = row_col_headers,
    tab_color       = tab_color,
    zoom            = zoom,
    odd_header      = headerFooterSub(odd_header),
    odd_footer      = headerFooterSub(odd_footer),
    even_header     = headerFooterSub(even_header),
    even_footer     = headerFooterSub(even_footer),
    first_header    = headerFooterSub(first_header),
    first_footer    = headerFooterSub(first_footer),
    visible         = visible,
    paper_size      = paper_size,
    orientation     = orientation,
    vdpi            = vdpi,
    hdpi            = hdpi,
    ...             = ...
  )
}


#' Create copies of a worksheet within a workbook
#'
#' @description
#' Create a copy of a worksheet in the same `wbWorkbook` object.
#'
#' Cloning is possible only to a limited extent. References to sheet names in
#' formulas, charts, pivot tables, etc. may not be updated. Some elements like
#' named ranges and slicers cannot be cloned yet.
#'
#' Cloning from another workbook is still an experimental feature and might not
#' work reliably. Cloning data, media, charts and tables should work. Slicers
#' and pivot tables as well as everything everything relying on dxfs styles
#' (e.g. custom table styles and conditional formatting) is currently not
#' implemented.
#' Formula references are not updated to reflect interactions between workbooks.
#'
#' @param wb A `wbWorkbook` object
#' @param old Name of existing worksheet to copy
#' @param new Name of the new worksheet to create
#' @param from (optional) Workbook to clone old from
#' @return The `wbWorkbook` object, invisibly.
#'
#' @export
#' @family workbook wrappers
#'
#' @examples
#' # Create a new workbook
#' wb <- wb_workbook()
#'
#' # Add worksheets
#' wb$add_worksheet("Sheet 1")
#' wb$clone_worksheet("Sheet 1", new = "Sheet 2")
#' # Take advantage of waiver functions
#' wb$clone_worksheet(old = "Sheet 1")
#'
#' ## cloning from another workbook
#'
#' # create a workbook
#' wb <- wb_workbook()$
#' add_worksheet("NOT_SUM")$
#'   add_data(x = head(iris))$
#'   add_fill(dims = "A1:B2", color = wb_color("yellow"))$
#'   add_border(dims = "B2:C3")
#'
#' # we will clone this styled chart into another workbook
#' fl <- system.file("extdata", "oxlsx2_sheet.xlsx", package = "openxlsx2")
#' wb_from <- wb_load(fl)
#'
#' # clone styles and shared strings
#' wb$clone_worksheet(old = "SUM", new = "SUM", from = wb_from)
#'
wb_clone_worksheet <- function(wb, old = current_sheet(), new = next_sheet(), from = NULL) {
  assert_workbook(wb)
  wb$clone()$clone_worksheet(old = old, new = new, from = from)
}

# worksheets --------------------------------------------------------------

#' Freeze panes of a worksheet
#'
#' @description
#' The `wb_freeze_pane()` function locks a specific area of a worksheet to keep
#' rows or columns visible while scrolling through other parts of the data. This
#' is achieved by defining a "split" point, where all content above or to the
#' left of the designated active region remains fixed.
#'
#' @details
#' The function operates by calculating `xSplit` and `ySplit` values based on
#' the provided active region coordinates. The `first_active_row` and
#' `first_active_col` parameters define the first cell that remains scrollable;
#' consequently, the frozen area consists of all rows and columns preceding
#' these indices.
#'
#' For common use cases, the `first_row` and `first_col` logical flags provide
#' optimized shortcuts. Enabling `first_row` locks the top row (equivalent to
#' setting the active region at row 2), while `first_col` locks the leftmost
#' column (equivalent to setting the active region at column 2). If both are
#' enabled, the function automatically freezes the intersection at cell "B2".
#'
#' The internal logic translates these coordinates into a `<pane />` XML node,
#' which specifies the `topLeftCell` of the scrollable region and assigns the
#' `activePane` (e.g., "bottomLeft", "topRight", or "bottomRight") to ensure
#' correct cursor behavior within the spreadsheet software.
#'
#' @param wb A [wbWorkbook] object.
#' @param sheet The name or index of the worksheet to modify. Defaults to
#'   the current sheet.
#' @param first_active_row The index of the first row that should remain
#'   scrollable. Rows above this will be frozen.
#' @param first_active_col The index or character label of the first column
#'   that should remain scrollable. Columns to the left will be frozen.
#' @param first_row Logical; if `TRUE`, freezes the first row of the worksheet.
#' @param first_col Logical; if `TRUE`, freezes the first column of the worksheet.
#' @param ... Additional arguments for internal case standardization.
#'
#' @section Notes:
#' * If `first_active_row` and `first_active_col` are both set to 1, or if all
#'     arguments are omitted, the function returns the workbook unchanged as
#'     there is no region to freeze.
#' * This function overwrites any existing pane configuration for the
#'     specified worksheet.
#'
#' @examples
#' wb <- wb_workbook()
#' ## Add some worksheets
#' wb$add_worksheet("Sheet 1")
#' wb$add_worksheet("Sheet 2")
#' wb$add_worksheet("Sheet 3")
#' wb$add_worksheet("Sheet 4")
#'
#' ## Freeze Panes
#' wb$freeze_pane("Sheet 1", first_active_row = 5, first_active_col = 3)
#' wb$freeze_pane("Sheet 2", first_col = TRUE) ## shortcut to first_active_col = 2
#' wb$freeze_pane(3, first_row = TRUE) ## shortcut to first_active_row = 2
#' wb$freeze_pane(4, first_active_row = 1, first_active_col = "D")
#'
#' @family workbook wrappers
#' @family worksheet content functions
#' @export
wb_freeze_pane <- function(
  wb,
  sheet            = current_sheet(),
  first_active_row = NULL,
  first_active_col = NULL,
  first_row        = FALSE,
  first_col        = FALSE,
  ...
) {
  assert_workbook(wb)
  wb$clone()$freeze_pane(
    sheet            = sheet,
    first_active_row = first_active_row,
    first_active_col = first_active_col,
    first_row        = first_row,
    first_col        = first_col
  )
}


# heights and columns -----------------------------------------------------


#' Modify row heights of a worksheet
#'
#' Set / remove custom worksheet row heights
#'
#' @param wb A [wbWorkbook] object
#' @param sheet A name or index of a worksheet. (A vector is accepted for `remove_row_heights()`)
#' @param rows Indices of rows to set / remove (if any) custom height.
#' @param heights Heights to set `rows` to specified in a spreadsheet column height units.
#' @param hidden Option to hide rows. A logical vector of length 1 or length of `rows`
#' @param hide_blanks Option to hide blank (uninitialized) rows. These rows are not only empty, they must not be part of the worksheet.
#' @name row_heights-wb
#' @family workbook wrappers
#' @family worksheet content functions
#'
#' @examples
#' ## Create a new workbook
#' wb <- wb_workbook()
#'
#' ## Add a worksheet
#' wb$add_worksheet("Sheet 1")
#'
#' ## set row heights
#' wb <- wb_set_row_heights(
#'   wb, 1,
#'   rows = c(1, 4, 22, 2, 19),
#'   heights = c(24, 28, 32, 42, 33)
#' )
#'
#' ## overwrite row 1 height
#' wb <- wb_set_row_heights(wb, 1, rows = 1, heights = 40)
#' ## remove any custom row heights in row 1
#' wb$remove_row_heights(sheet = 1, rows = 1)
NULL
#' @rdname row_heights-wb
#' @export
wb_set_row_heights <- function(wb, sheet = current_sheet(), rows, heights = NULL, hidden = FALSE, hide_blanks = NULL) {
  assert_workbook(wb)
  if (missing(rows)) rows <- substitute()
  wb$clone(deep = TRUE)$set_row_heights(sheet = sheet, rows = rows, heights = heights, hidden = hidden, hide_blanks = hide_blanks)
}
#' @rdname row_heights-wb
#' @export
wb_remove_row_heights <- function(wb, sheet = current_sheet(), rows) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$remove_row_heights(sheet = sheet, rows = rows)
}

#' Modify column widths of a worksheet
#'
#' Remove / set worksheet column widths to specified width or "auto".
#'
#' @details
#' The global minimum and maximum column width for "auto" columns are controlled by:
#' * `options("openxlsx2.minWidth" = 3)`
#' * `options("openxlsx2.maxWidth" = 250)` (the maximum width allowed in OOXML)
#'
#' Automatic column width calculation is a heuristic that may not be accurate
#' in all scenarios. Known limitations include issues with wrapped text,
#' merged cells, and font styles with variable kerning. The underlying logic
#' primarily assumes a monospace font and provides limited support for specific
#' number formats. As a safeguard to avoid very narrow columns, widths
#' calculated below the `openxlsx2.minWidth` (or if unset, below 4) threshold
#' are slightly increased.
#'
#' Be aware that calculating widths can be computationally slow for large
#' worksheets. Additionally, the `hidden` parameter is linked with settings in
#' [wb_group_cols()], so changing one will update the other. Because default
#' column widths are influenced by the specific spreadsheet software, operating
#' system, and DPI settings, even providing specific values for `widths` does
#' not guarantee perfectly consistent output across all environments.
#'
#' For automatic text wrapping of columns use
#' [wb_add_cell_style(wrap_text = TRUE)][wb_add_cell_style()]
#'
#' @param wb A `wbWorkbook` object.
#' @param sheet A name or index of a worksheet, a vector in the case of `remove_`
#' @param cols Indices of cols to set/remove column widths.
#' @param widths Width to set `cols` to specified column width or `"auto"` for
#'   automatic sizing. `widths` is recycled to the length of `cols`. openxlsx2
#'   sets the default width is 8.43, as this is the standard in some spreadsheet
#'   software. See **Details** for general information on column widths.
#' @param hidden Logical vector recycled to the length of `cols`.
#'   If `TRUE`, the columns are hidden.
#'
#' @family workbook wrappers
#' @family worksheet content functions
#'
#' @examples
#' ## Create a new workbook
#' wb <- wb_workbook()
#'
#' ## Add a worksheet
#' wb$add_worksheet("Sheet 1")
#'
#' ## set col widths
#' wb$set_col_widths(cols = c(1, 4, 6, 7, 9), widths = c(16, 15, 12, 18, 33))
#'
#' ## auto columns
#' wb$add_worksheet("Sheet 2")
#' wb$add_data(sheet = 2, x = iris)
#' wb$set_col_widths(sheet = 2, cols = 1:5, widths = "auto")
#'
#' ## removing column widths
#' ## Create a new workbook
#' wb <- wb_load(file = system.file("extdata", "openxlsx2_example.xlsx", package = "openxlsx2"))
#'
#' ## remove column widths in columns 1 to 20
#' wb_remove_col_widths(wb, 1, cols = 1:20)
#' @name col_widths-wb
NULL

#' @rdname col_widths-wb
#' @export
wb_set_col_widths <- function(wb, sheet = current_sheet(), cols, widths = 8.43, hidden = FALSE) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$set_col_widths(
    sheet  = sheet,
    cols   = cols,
    widths = widths,
    # TODO allow either 1 or length(cols)
    hidden = hidden
  )
}
#' @rdname col_widths-wb
#' @export
wb_remove_col_widths <- function(wb, sheet = current_sheet(), cols) {
  assert_workbook(wb)
  wb$clone()$remove_col_widths(sheet = sheet, cols = cols)
}


# images ------------------------------------------------------------------


#' Insert the current R plot into a worksheet
#'
#' @description
#' The `wb_add_worksheet()` function captures the active R graphics device and
#' embeds the displayed plot into a worksheet. This is achieved by copying the
#' current plot to a temporary image file via [grDevices::dev.copy()] and
#' subsequently invoking [wb_add_image()] to handle the workbook integration.
#'
#' @details
#' Because this function relies on the active graphics device, a plot must be
#' currently displayed in the R session (e.g., in the Plots pane or a separate
#' window) for the capture to succeed. The function supports various file
#' formats for the intermediate transition, including `"png"`, `"jpeg"`,
#' `"tiff"`, and `"bmp"`.
#'
#' Positioning is managed through the spreadsheet coordinate system. Using a
#' single cell in `dims` (e.g., "A1") establishes a one-cell anchor where the
#' plot maintains its absolute dimensions. Providing a range (e.g., "A1:E10")
#' creates a two-cell anchor, which may result in the plot resizing if columns
#' or rows within that range are adjusted in spreadsheet software.
#'
#' For programmatic control over the output quality, the `dpi` argument
#' influences the resolution of the captured device. Users working with
#' high-resolution displays or requiring print-quality outputs should adjust
#' the `dpi` and `units` accordingly.
#'
#' @param wb A [wbWorkbook] object.
#' @param sheet The name or index of the worksheet where the plot will be
#'   inserted. Defaults to the current sheet.
#' @param dims A character string defining the anchor point or range (e.g.,
#'   "A1" or "A1:D4").
#' @param width,height The numeric dimensions of the exported plot. Defaults
#'   to 6x4 inches.
#' @param row_offset,col_offset Numeric vectors for sub-cell positioning
#'   offsets.
#' @param file_type The image format for the temporary capture. Supported
#'   types include `"png"`, `"jpeg"`, `"tiff"`, and `"bmp"`.
#' @param units The measurement units for `width` and `height`. Must be
#'   one of `"in"`, `"cm"`, or `"px"`.
#' @param dpi The resolution in dots per inch for the image conversion.
#' @param ... Additional arguments. Supports the deprecated `start_row`
#'   and `start_col` parameters for backward compatibility.
#'
#' @examples
#' if (requireNamespace("ggplot2") && interactive()) {
#' ## Create a new workbook
#' wb <- wb_workbook()
#'
#' ## Add a worksheet
#' wb$add_worksheet("Sheet 1", grid_lines = FALSE)
#'
#' ## create plot objects
#' require(ggplot2)
#' p1 <- ggplot(mtcars, aes(x = mpg, fill = as.factor(gear))) +
#'   ggtitle("Distribution of Gas Mileage") +
#'   geom_density(alpha = 0.5)
#' p2 <- ggplot(Orange, aes(x = age, y = circumference, color = Tree)) +
#'   geom_point() + geom_line()
#'
#' ## Insert currently displayed plot to sheet 1, row 1, column 1
#' print(p1) # plot needs to be showing
#' wb$add_plot(1, width = 5, height = 3.5, file_type = "png", units = "in")
#'
#' ## Insert plot 2
#' print(p2)
#' wb$add_plot(1, dims = "J2", width = 16, height = 10, file_type = "png", units = "cm")
#'
#' }
#' @seealso [wb_add_chart_xml()] [wb_add_drawing()] [wb_add_image()] [wb_add_mschart()]
#' @export
wb_add_plot <- function(
    wb,
    sheet      = current_sheet(),
    dims       = "A1",
    width      = 6,
    height     = 4,
    row_offset = 0,
    col_offset = 0,
    file_type  = "png",
    units      = "in",
    dpi        = 300,
    ...
) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$add_plot(
    sheet      = sheet,
    dims       = dims,
    width      = width,
    height     = height,
    row_offset = row_offset,
    col_offset = col_offset,
    file_type  = file_type,
    units      = units,
    dpi        = dpi,
    ...        = ...
  )
}

#' Add drawings to a worksheet
#'
#' Add drawings to a worksheet. This requires the `rvg` package.
#' @param wb A `wbWorkbook`
#' @param sheet A sheet in the workbook
#' @param dims The dimension where the drawing is added.
#' @param xml the drawing xml as character or file
#' @param col_offset,row_offset offsets for column and row
#' @param ... additional arguments
#' @examples
#' if (requireNamespace("rvg") && interactive()) {
#'
#' ## rvg example
#' require(rvg)
#' tmp <- tempfile(fileext = ".xml")
#' dml_xlsx(file =  tmp)
#' plot(1,1)
#' dev.off()
#'
#' wb <- wb_workbook()$
#'   add_worksheet()$
#'   add_drawing(xml = tmp)$
#'   add_drawing(xml = tmp, dims = NULL)
#' }
#' @seealso [wb_add_chart_xml()] [wb_add_image()] [wb_add_mschart()] [wb_add_plot()]
#' @export
wb_add_drawing <- function(
  wb,
  sheet      = current_sheet(),
  dims       = "A1",
  xml,
  col_offset = 0,
  row_offset = 0,
  ...
) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$add_drawing(
    sheet      = sheet,
    dims       = dims,
    xml        = xml,
    col_offset = col_offset,
    row_offset = row_offset,
    ...        = ...
  )
}

#' Add a Chart object to a workbook sheet
#'
#' @description
#' Renders a `encharter` R6 object and injects the resulting XML into an
#' `openxlsx2` workbook at the specified location.
#'
#' @param wb An `openxlsx2` workbook object.
#' @param sheet Sheet name or index where the chart will be placed.
#' @param dims Character string defining the cell range (e.g., "E2:M20").
#' @param graph An initialized `encharter` R6 object.
#'
#' @return The workbook object, invisibly.
#' @export
wb_add_encharter <- function(wb, sheet = current_sheet(), dims = NULL, graph) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$add_encharter(
    sheet = sheet,
    dims  = dims,
    graph = graph
  )
}

#' Add an mschart object to a worksheet
#'
#' @description
#' The `wb_add_mschart()` function allows for the seamless integration of native
#' charts created via the `mschart` package into a worksheet. Unlike static
#' images or plots, these are dynamic, native spreadsheet charts that remain
#' editable and can utilize data already present in the workbook or data
#' provided directly at creation.
#'
#' @details
#' The function acts as a bridge between the `ms_chart` objects and the
#' spreadsheet's internal XML drawing structure. It interprets the chart settings
#' and data series to generate the necessary DrawingML.
#'
#' There are two primary workflows for adding charts:
#' 1. External Data: If the `graph` object contains a standard data frame,
#'    `wb_add_mschart()` automatically writes this data to the worksheet
#'    before rendering the chart.
#' 2. Internal Data: If the `graph` object is initialized using a `wb_data`
#'    object (created via [wb_data()]), the chart will directly reference the
#'    existing cell ranges in the worksheet. This is the preferred method for
#'    maintaining a single source of truth for your data.
#'
#' The chart is positioned using the `dims` argument. A single cell anchor
#' (e.g., "A1") will place the top-left corner of the chart, while a range
#' (e.g., "E5:L20") will scale the chart to fit that specific area.
#'
#' @param wb A [wbWorkbook] object.
#' @param sheet The name or index of the worksheet where the chart will be
#'   placed. Defaults to the current sheet.
#' @param dims A character string defining the chart's position or range
#'   (e.g., "A1" or "F4:L20").
#' @param graph An `ms_chart` object created with the `mschart` package.
#' @param col_offset,row_offset Numeric values for fine-tuning the chart's
#'   displacement from its anchor point.
#' @param ... Additional arguments.
#'
#' @section Notes:
#' * This function requires the `mschart` package to be installed.
#' * Native charts are highly dependent on the calculation engine of the
#'     spreadsheet software; if the underlying data changes, the chart will
#'     update automatically when the file is opened.
#' * The function generates unique internal IDs for the chart axes to ensure
#'     compliance with the OpenXML specification.
#'
#' @examples
#' if (requireNamespace("mschart")) {
#' require(mschart)
#'
#' ## Add mschart to worksheet (adds data and chart)
#' scatter <- ms_scatterchart(data = iris, x = "Sepal.Length", y = "Sepal.Width", group = "Species")
#' scatter <- chart_settings(scatter, scatterstyle = "marker")
#'
#' wb <- wb_workbook()
#' wb <- wb_add_worksheet(wb)
#' wb <- wb_add_mschart(wb, dims = "F4:L20", graph = scatter)
#'
#' ## Add mschart to worksheet and use available data
#' wb <- wb_workbook()
#' wb <- wb_add_worksheet(wb)
#' wb <- wb_add_data(wb, x = mtcars, dims = "B2")
#'
#' # create wb_data object
#' dat <- wb_data(wb, 1, dims = "B2:E6")
#'
#' # call ms_scatterplot
#' data_plot <- ms_scatterchart(
#'   data = dat,
#'   x = "mpg",
#'   y = c("disp", "hp"),
#'   labels = c("disp", "hp")
#' )
#'
#' # add the scatterplot to the data
#' wb <- wb_add_mschart(wb, dims = "F4:L20", graph = data_plot)
#' }
#' @seealso [wb_data()] [wb_add_chart_xml()] [wb_add_image] [wb_add_mschart()] [wb_add_plot]
#' @export
wb_add_mschart <- function(
    wb,
    sheet      = current_sheet(),
    dims       = NULL,
    graph,
    col_offset = 0,
    row_offset = 0,
    ...
) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$add_mschart(
    sheet      = sheet,
    dims       = dims,
    graph      = graph,
    col_offset = col_offset,
    row_offset = row_offset,
    ...        = ...
  )
}

#' Remove a worksheet from a workbook
#' @param wb A wbWorkbook object
#' @param sheet The sheet name or index to remove
#' @returns The `wbWorkbook` object, invisibly.
#' @export
#' @examples
#' ## load a workbook
#' wb <- wb_load(file = system.file("extdata", "openxlsx2_example.xlsx", package = "openxlsx2"))
#'
#' ## Remove sheet 2
#' wb <- wb_remove_worksheet(wb, 2)
wb_remove_worksheet <- function(wb, sheet = current_sheet()) {
  assert_workbook(wb)
  wb$clone()$remove_worksheet(sheet = sheet)
}


# base font ---------------------------------------------------------------

#' Set the default font in a workbook
#'
#' Modify / get the default font for the workbook. This will alter the latin
#' major and minor font in the workbooks theme.
#'
#' The font name is not validated in anyway. Spreadsheet software replaces
#' unknown font names with system defaults.
#'
#' The default base font is Aptos Narrow, black, size 11. If `font_name` differs
#' from the name in [wb_get_base_font()], the theme is updated to use the newly
#' selected font name.
#'
#' @param wb A workbook object
#' @param font_size Font size
#' @param font_color Font color
#' @param font_name Name of a font
#' @param ... Additional arguments
#' @family workbook styling functions
#' @family workbook wrappers
#' @name base_font-wb
#' @examples
#' ## create a workbook
#' wb <- wb_workbook(theme = "Office 2013 - 2022 Theme")
#' wb$add_worksheet("S1")
#' ## modify base font to size 10 Aptos Narrow in red
#' wb$set_base_font(font_size = 10, font_color = wb_color("red"), font_name = "Aptos Narrow")
#'
#' wb$add_data(x = iris)
#'
#' ## font color does not affect tables
#' wb$add_data_table(x = iris, dims = wb_dims(from_col = 10))
#'
#' ## get the base font
#' wb_get_base_font(wb)
#'
NULL
#' @export
#' @rdname base_font-wb
wb_set_base_font <- function(
  wb,
  font_size  = 11,
  font_color = wb_color(theme = "1"),
  font_name  = "Aptos Narrow",
  ...
) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$set_base_font(
    font_size   = font_size,
    font_color  = font_color,
    font_name   = font_name,
    ...         = ...
  )
}
#' @export
#' @rdname base_font-wb
wb_get_base_font <- function(wb) {
  # TODO all of these class checks need to be cleaned up
  assert_workbook(wb)
  wb$get_base_font()
}

#' Set the default colors in a workbook
#'
#' Modify / get the default colors of the workbook.
#'
#' @details Theme must be any of the following:
#' "Aspect", "Blue", "Blue II", "Blue Green", "Blue Warm", "Greyscale",
#' "Green", "Green Yellow", "Marquee", "Median", "Office", "Office 2007 - 2010",
#' "Office 2013 - 2022", "Orange", "Orange Red", "Paper", "Red",
#' "Red Orange", "Red Violet", "Slipstream", "Violet", "Violet II",
#' "Yellow", "Yellow Orange"
#'
#' @name wb_base_colors
#' @param wb A workbook object
#' @param theme a predefined color theme
#' @param ... optional parameters
#' @param xml Logical if xml string should be returned
#' @param plot Logical if a barplot of the colors should be returned
#' @family workbook styling functions
#' @family workbook wrappers
#' @examples
#' wb <- wb_workbook()
#' wb$get_base_colors()
#' wb$set_base_colors(theme = 3)
#' wb$set_base_colors(theme = "Violet II")
#' wb$get_base_colours()
NULL
#' @export
#' @rdname wb_base_colors
wb_set_base_colors <- function(
  wb,
  theme = "Office",
  ...
) {
  assert_workbook(wb)
  wb$clone()$set_base_colors(
    theme = theme,
    ...   = ...
  )
}
#' @export
#' @rdname wb_base_colors
wb_get_base_colors <- function(wb, xml = FALSE, plot = TRUE) {
  assert_workbook(wb)
  wb$get_base_colors(xml = xml, plot = plot)
}
#' @export
#' @rdname wb_base_colors
#' @usage NULL
wb_set_base_colours <- wb_set_base_colors
#' @export
#' @rdname wb_base_colors
#' @usage NULL
wb_get_base_colours <- wb_get_base_colors


#' Get and Set the workbook position, size and filter
#' @name wb_set_bookview
#' @return A data frame with the bookview properties
#' @export
wb_get_bookview <- function(wb) {
  assert_workbook(wb)
  wb$get_bookview()
}

#' @name wb_set_bookview
#' @param view You can remove views using index positions. This will only remove the view wont apply modifications.
#' @return The Workbook object
#' @export
wb_remove_bookview <- function(wb, view = NULL) {
  assert_workbook(wb)
  wb$clone()$remove_bookview(view = view)
}

#' @rdname wb_set_bookview
#' @param wb A [wbWorkbook] object
#' @param active_tab activeTab
#' @param auto_filter_date_grouping autoFilterDateGrouping
#' @param first_sheet The first sheet to be displayed
#' @param minimized minimized
#' @param show_horizontal_scroll showHorizontalScroll
#' @param show_sheet_tabs showSheetTabs
#' @param show_vertical_scroll showVerticalScroll
#' @param tab_ratio tabRatio
#' @param visibility visibility
#' @param window_height windowHeight
#' @param window_width windowWidth
#' @param x_window xWindow
#' @param y_window yWindow
#' @param view Which view to modify. Default is `1` (the first view).
#' @param ... additional arguments
#' @return The Workbook object
#' @examples
#'  wb <- wb_workbook()
#'  wb <- wb_add_worksheet(wb)
#'
#'  # set the first and second bookview (horizontal split)
#'  wb <- wb_set_bookview(wb,
#'      window_height = 17600, window_width = 15120,
#'      x_window = 15120, y_window = 760)
#'  wb <- wb_set_bookview(wb,
#'      window_height = 17600, window_width = 15040,
#'      x_window = 0, y_window = 760, view = 2
#'    )
#'
#'  wb_get_bookview(wb)
#'
#'  # remove the first view
#'  wb <- wb_remove_bookview(wb, view = 1)
#'  wb_get_bookview(wb)
#'
#'  # keep only the first view
#'  wb <- wb_remove_bookview(wb, view = -1)
#'  wb_get_bookview(wb)
#' @export
wb_set_bookview <- function(
    wb,
    active_tab                = NULL,
    auto_filter_date_grouping = NULL,
    first_sheet               = NULL,
    minimized                 = NULL,
    show_horizontal_scroll    = NULL,
    show_sheet_tabs           = NULL,
    show_vertical_scroll      = NULL,
    tab_ratio                 = NULL,
    visibility                = NULL,
    window_height             = NULL,
    window_width              = NULL,
    x_window                  = NULL,
    y_window                  = NULL,
    view                      = 1L,
    ...
) {
  assert_workbook(wb)
  wb$clone()$set_bookview(
    active_tab                = active_tab,
    auto_filter_date_grouping = auto_filter_date_grouping,
    first_sheet               = first_sheet,
    minimized                 = minimized,
    show_horizontal_scroll    = show_horizontal_scroll,
    show_sheet_tabs           = show_sheet_tabs,
    show_vertical_scroll      = show_vertical_scroll,
    tab_ratio                 = tab_ratio,
    visibility                = visibility,
    window_height             = window_height,
    window_width              = window_width,
    x_window                  = x_window,
    y_window                  = y_window,
    view                      = view,
    ...                       = ...
  )
}

#' Set headers and footers of a worksheet
#'
#' Set document headers and footers. You can also do this when adding a worksheet
#' with [wb_add_worksheet()] with the `header`, `footer` arguments and friends.
#' These will show up when printing an xlsx file.
#'
#' Headers and footers can contain special tags
#' * **&\[Page\]** Page number
#' * **&\[Pages\]** Number of pages
#' * **&\[Date\]** Current date
#' * **&\[Time\]** Current time
#' * **&\[Path\]** File path
#' * **&\[File\]** File name
#' * **&\[Tab\]** Worksheet name
#'
#' @param wb A Workbook object
#' @param sheet A name or index of a worksheet
#' @param header,even_header,first_header,footer,even_footer,first_footer
#'   Character vector of length 3 corresponding to positions left, center,
#'   right.  `header` and `footer` are used to default additional arguments.
#'   Setting `even`, `odd`, or `first`, overrides `header`/`footer`. Use `NA` to
#'   skip a position.
#' @param align_with_margins Align header/footer with margins
#' @param scale_with_doc Scale header/footer with document
# #' @inheritParams wb_add_worksheet
#' @param ... additional arguments
#' @export
#' @examples
#' wb <- wb_workbook()
#'
#' # Add example data
#' wb$add_worksheet("S1")$add_data(x = 1:400)
#' wb$add_worksheet("S2")$add_data(x = 1:400)
#' wb$add_worksheet("S3")$add_data(x = 3:400)
#' wb$add_worksheet("S4")$add_data(x = 3:400)
#'
#' wb$set_header_footer(
#'   sheet = "S1",
#'   header = c("ODD HEAD LEFT", "ODD HEAD CENTER", "ODD HEAD RIGHT"),
#'   footer = c("ODD FOOT RIGHT", "ODD FOOT CENTER", "ODD FOOT RIGHT"),
#'   even_header = c("EVEN HEAD LEFT", "EVEN HEAD CENTER", "EVEN HEAD RIGHT"),
#'   even_footer = c("EVEN FOOT RIGHT", "EVEN FOOT CENTER", "EVEN FOOT RIGHT"),
#'   first_header = c("TOP", "OF FIRST", "PAGE"),
#'   first_footer = c("BOTTOM", "OF FIRST", "PAGE")
#' )
#'
#' wb$set_header_footer(
#'   sheet = 2,
#'   header = c("&[Date]", "ALL HEAD CENTER 2", "&[Page] / &[Pages]"),
#'   footer = c("&[Path]&[File]", NA, "&[Tab]"),
#'   first_header = c(NA, "Center Header of First Page", NA),
#'   first_footer = c(NA, "Center Footer of First Page", NA)
#' )
#'
#' wb$set_header_footer(
#'   sheet = 3,
#'   header = c("ALL HEAD LEFT 2", "ALL HEAD CENTER 2", "ALL HEAD RIGHT 2"),
#'   footer = c("ALL FOOT RIGHT 2", "ALL FOOT CENTER 2", "ALL FOOT RIGHT 2")
#' )
#'
#' wb$set_header_footer(
#'   sheet = 4,
#'   first_header = c("FIRST ONLY L", NA, "FIRST ONLY R"),
#'   first_footer = c("FIRST ONLY L", NA, "FIRST ONLY R")
#' )
#'
#' # ---- Updating the header ----
#' ## Variant a
#' ## this will keep the odd and even header / footer from the original header /
#' ## footerkeep the first header / footer and will set the first page header /
#' ## footer and will use the original header / footer for the missing element
#' wb$set_header_footer(
#'   header = NA,
#'   footer = NA,
#'   even_header = NA,
#'   even_footer = NA,
#'   first_header = c("FIRST ONLY L", NA, "FIRST ONLY R"),
#'   first_footer = c("FIRST ONLY L", NA, "FIRST ONLY R")
#' )
#'
#' ## Variant b
#' ## this will keep the first header / footer only and will use the missing
#' ## element from the original header / footer
#' wb$set_header_footer(
#'   first_header = c("FIRST ONLY L", NA, "FIRST ONLY R"),
#'   first_footer = c("FIRST ONLY L", NA, "FIRST ONLY R")
#' )
#'
wb_set_header_footer <- function(
    wb,
    sheet              = current_sheet(),
    header             = NULL,
    footer             = NULL,
    even_header        = NULL,
    even_footer        = NULL,
    first_header       = NULL,
    first_footer       = NULL,
    align_with_margins = NULL,
    scale_with_doc     = NULL,
    ...
) {
  assert_workbook(wb)
  wb$clone()$set_header_footer(
    sheet              = sheet,
    header             = header,
    footer             = footer,
    even_header        = even_header,
    even_footer        = even_footer,
    first_header       = first_header,
    first_footer       = first_footer,
    align_with_margins = align_with_margins,
    scale_with_doc     = scale_with_doc,
    ...                = ...
  )
}


#' Set page margins, orientation and print scaling of a worksheet
#'
#' Set page margins, orientation and print scaling.
#' @param wb A workbook object
#' @param sheet A name or index of a worksheet
#' @param orientation Page orientation. One of "portrait" or "landscape"
#' @param scale Print scaling. Numeric value between 10 and 400
#' @param left,right,top,bottom  Page margin in inches
#' @param header,footer  Margin in inches
#' @param fit_to_width,fit_to_height An integer that tells the spreadsheet software on how many pages the scaling should fit. This does not actually scale the sheet.
#' @param paper_size See details. Default value is 9 (A4 paper).
#' @param print_title_rows,print_title_cols Rows / columns to repeat at top of page when printing. Integer vector.
#' @param summary_row Location of summary rows in groupings. One of "Above" or "Below".
#' @param summary_col Location of summary columns in groupings. One of "Right" or "Left".
#' @param black_and_white black and white mode
#' @param cell_comments show cell comments
#' @param copies Amount of copies
#' @param draft Draft mode
#' @param errors Show errors
#' @param first_page_number The first page number
#' @param id id (unknown)
#' @param page_order Page order
#' @param paper_height,paper_width paper size
#' @param use_first_page_number Number on first page
#' @param use_printer_defaults Use printer defaults
#' @param hdpi,vdpi horizontal and vertical dpi
#' @param tab_color The tab color
#' @param horizontal_centered,vertical_centered center print output vertical or horizontal
#' @param print_headings print headings
#' @param ... additional arguments
#' @details
#'  When adding fitting to width and height manual adjustment of the scaling factor is required. Setting `fit_to_width` and `fit_to_height` only tells spreadsheet software that the scaling was applied, but not which scaling was applied.
#'
#' [wb_page_setup()] provides a subset of [wb_set_page_setup()]. The former will soon become deprecated.
#'
#' `paper_size` is an integer corresponding to:
#'
#'  | size   | "paper type"                                                                         |
#'  |--------|--------------------------------------------------------------------------------------|
#'  | 1      | Letter paper (8.5 in. by 11 in.)                                                     |
#'  | 2      |  Letter small paper (8.5 in. by 11 in.)                                              |
#'  | 3      |  Tabloid paper (11 in. by 17 in.)                                                    |
#'  | 4      |  Ledger paper (17 in. by 11 in.)                                                     |
#'  | 5      |  Legal paper (8.5 in. by 14 in.)                                                     |
#'  | 6      |  Statement paper (5.5 in. by 8.5 in.)                                                |
#'  | 7      |  Executive paper (7.25 in. by 10.5 in.)                                              |
#'  | 8      |  A3 paper (297 mm by 420 mm)                                                         |
#'  | 9      |  A4 paper (210 mm by 297 mm)                                                         |
#'  | 10     |  A4 small paper (210 mm by 297 mm)                                                   |
#'  | 11     |  A5 paper (148 mm by 210 mm)                                                         |
#'  | 12     |  B4 paper (250 mm by 353 mm)                                                         |
#'  | 13     |  B5 paper (176 mm by 250 mm)                                                         |
#'  | 14     |  Folio paper (8.5 in. by 13 in.)                                                     |
#'  | 15     |  Quarto paper (215 mm by 275 mm)                                                     |
#'  | 16     |  Standard paper (10 in. by 14 in.)                                                   |
#'  | 17     |  Standard paper (11 in. by 17 in.)                                                   |
#'  | 18     |  Note paper (8.5 in. by 11 in.)                                                      |
#'  | 19     |  #9 envelope (3.875 in. by 8.875 in.)                                                |
#'  | 20     |  #10 envelope (4.125 in. by 9.5 in.)                                                 |
#'  | 21     |  #11 envelope (4.5 in. by 10.375 in.)                                                |
#'  | 22     |  #12 envelope (4.75 in. by 11 in.)                                                   |
#'  | 23     |  #14 envelope (5 in. by 11.5 in.)                                                    |
#'  | 24     |  C paper (17 in. by 22 in.)                                                          |
#'  | 25     |  D paper (22 in. by 34 in.)                                                          |
#'  | 26     |  E paper (34 in. by 44 in.)                                                          |
#'  | 27     |  DL envelope (110 mm by 220 mm)                                                      |
#'  | 28     |  C5 envelope (162 mm by 229 mm)                                                      |
#'  | 29     |  C3 envelope (324 mm by 458 mm)                                                      |
#'  | 30     |  C4 envelope (229 mm by 324 mm)                                                      |
#'  | 31     |  C6 envelope (114 mm by 162 mm)                                                      |
#'  | 32     |  C65 envelope (114 mm by 229 mm)                                                     |
#'  | 33     |  B4 envelope (250 mm by 353 mm)                                                      |
#'  | 34     |  B5 envelope (176 mm by 250 mm)                                                      |
#'  | 35     |  B6 envelope (176 mm by 125 mm)                                                      |
#'  | 36     |  Italy envelope (110 mm by 230 mm)                                                   |
#'  | 37     |  Monarch envelope (3.875 in. by 7.5 in.)                                             |
#'  | 38     |  6 3/4 envelope (3.625 in. by 6.5 in.)                                               |
#'  | 39     |  US standard fanfold (14.875 in. by 11 in.)                                          |
#'  | 40     |  German standard fanfold (8.5 in. by 12 in.)                                         |
#'  | 41     |  German legal fanfold (8.5 in. by 13 in.)                                            |
#'  | 42     |  ISO B4 (250 mm by 353 mm)                                                           |
#'  | 43     |  Japanese double postcard (200 mm by 148 mm)                                         |
#'  | 44     |  Standard paper (9 in. by 11 in.)                                                    |
#'  | 45     |  Standard paper (10 in. by 11 in.)                                                   |
#'  | 46     |  Standard paper (15 in. by 11 in.)                                                   |
#'  | 47     |  Invite envelope (220 mm by 220 mm)                                                  |
#'  | 50     |  Letter extra paper (9.275 in. by 12 in.)                                            |
#'  | 51     |  Legal extra paper (9.275 in. by 15 in.)                                             |
#'  | 52     |  Tabloid extra paper (11.69 in. by 18 in.)                                           |
#'  | 53     |  A4 extra paper (236 mm by 322 mm)                                                   |
#'  | 54     |  Letter transverse paper (8.275 in. by 11 in.)                                       |
#'  | 55     |  A4 transverse paper (210 mm by 297 mm)                                              |
#'  | 56     |  Letter extra transverse paper (9.275 in. by 12 in.)                                 |
#'  | 57     |  SuperA/SuperA/A4 paper (227 mm by 356 mm)                                           |
#'  | 58     |  SuperB/SuperB/A3 paper (305 mm by 487 mm)                                           |
#'  | 59     |  Letter plus paper (8.5 in. by 12.69 in.)                                            |
#'  | 60     |  A4 plus paper (210 mm by 330 mm)                                                    |
#'  | 61     |  A5 transverse paper (148 mm by 210 mm)                                              |
#'  | 62     |  JIS B5 transverse paper (182 mm by 257 mm)                                          |
#'  | 63     |  A3 extra paper (322 mm by 445 mm)                                                   |
#'  | 64     |  A5 extra paper (174 mm by 235 mm)                                                   |
#'  | 65     |  ISO B5 extra paper (201 mm by 276 mm)                                               |
#'  | 66     |  A2 paper (420 mm by 594 mm)                                                         |
#'  | 67     |  A3 transverse paper (297 mm by 420 mm)                                              |
#'  | 68     |  A3 extra transverse paper (322 mm by 445 mm)                                        |
#'  | 69     | Japanese Double Postcard (200 mm x 148 mm) 70=A6(105mm x 148mm)                      |
#'  | 71     | Japanese Envelope Kaku #2                                                            |
#'  | 72     | Japanese Envelope Kaku #3                                                            |
#'  | 73     | Japanese Envelope Chou #3                                                            |
#'  | 74     | Japanese Envelope Chou #4                                                            |
#'  | 75     | Letter Rotated (11in x 8 1/2 11 in)                                                  |
#'  | 76     | A3 Rotated (420 mm x 297 mm)                                                         |
#'  | 77     | A4 Rotated (297 mm x 210 mm)                                                         |
#'  | 78     | A5 Rotated (210 mm x 148 mm)                                                         |
#'  | 79     | B4 (JIS) Rotated (364 mm x 257 mm)                                                   |
#'  | 80     | B5 (JIS) Rotated (257 mm x 182 mm)                                                   |
#'  | 81     | Japanese Postcard Rotated (148 mm x 100 mm)                                          |
#'  | 82     | Double Japanese Postcard Rotated (148 mm x 200 mm) 83 = A6 Rotated (148 mm x 105 mm) |
#'  | 84     | Japanese Envelope Kaku #2 Rotated                                                    |
#'  | 85     | Japanese Envelope Kaku #3 Rotated                                                    |
#'  | 86     | Japanese Envelope Chou #3 Rotated                                                    |
#'  | 87     | Japanese Envelope Chou #4 Rotated 88=B6(JIS)(128mm x 182mm)                          |
#'  | 89     | B6 (JIS) Rotated (182 mm x 128 mm)                                                   |
#'  | 90     | (12 in x 11 in)                                                                      |
#'  | 91     | Japanese Envelope You #4                                                             |
#'  | 92     | Japanese Envelope You #4 Rotated 93=PRC16K(146mm x 215mm) 94=PRC32K(97mm x 151mm)    |
#'  | 95     | PRC 32K(Big) (97 mm x 151 mm)                                                        |
#'  | 96     | PRC Envelope #1 (102 mm x 165 mm)                                                    |
#'  | 97     | PRC Envelope #2 (102 mm x 176 mm)                                                    |
#'  | 98     | PRC Envelope #3 (125 mm x 176 mm)                                                    |
#'  | 99     | PRC Envelope #4 (110 mm x 208 mm)                                                    |
#'  | 100    | PRC Envelope #5 (110 mm x 220 mm)                                                    |
#'  | 101    | PRC Envelope #6 (120 mm x 230 mm)                                                    |
#'  | 102    | PRC Envelope #7 (160 mm x 230 mm)                                                    |
#'  | 103    | PRC Envelope #8 (120 mm x 309 mm)                                                    |
#'  | 104    | PRC Envelope #9 (229 mm x 324 mm)                                                    |
#'  | 105    | PRC Envelope #10 (324 mm x 458 mm)                                                   |
#'  | 106    | PRC 16K Rotated                                                                      |
#'  | 107    | PRC 32K Rotated                                                                      |
#'  | 108    | PRC 32K(Big) Rotated                                                                 |
#'  | 109    | PRC Envelope #1 Rotated (165 mm x 102 mm)                                            |
#'  | 110    | PRC Envelope #2 Rotated (176 mm x 102 mm)                                            |
#'  | 111    | PRC Envelope #3 Rotated (176 mm x 125 mm)                                            |
#'  | 112    | PRC Envelope #4 Rotated (208 mm x 110 mm)                                            |
#'  | 113    | PRC Envelope #5 Rotated (220 mm x 110 mm)                                            |
#'  | 114    | PRC Envelope #6 Rotated (230 mm x 120 mm)                                            |
#'  | 115    | PRC Envelope #7 Rotated (230 mm x 160 mm)                                            |
#'  | 116    | PRC Envelope #8 Rotated (309 mm x 120 mm)                                            |
#'  | 117    | PRC Envelope #9 Rotated (324 mm x 229 mm)                                            |
#'  | 118    | PRC Envelope #10 Rotated (458 mm x 324 mm)                                           |
#'
#' @examples
#' wb <- wb_workbook()
#' wb$add_worksheet("S1")
#' wb$add_worksheet("S2")
#' wb$add_data_table(1, x = iris[1:30, ])
#' wb$add_data_table(2, x = iris[1:30, ], dims = c("C5"))
#'
#' ## landscape page scaled to 50%
#' wb$set_page_setup(sheet = 1, orientation = "landscape", scale = 50)
#'
#' ## portrait page scales to 300% with 0.5in left and right margins
#' wb$set_page_setup(sheet = 2, orientation = "portrait", scale = 300, left = 0.5, right = 0.5)
#'
#'
#' ## print titles
#' wb$add_worksheet("print_title_rows")
#' wb$add_worksheet("print_title_cols")
#'
#' wb$add_data("print_title_rows", rbind(iris, iris, iris, iris))
#' wb$add_data("print_title_cols", x = rbind(mtcars, mtcars, mtcars), row_names = TRUE)
#'
#' wb$set_page_setup(sheet = "print_title_rows", print_title_rows = 1) ## first row
#' wb$set_page_setup(sheet = "print_title_cols", print_title_cols = 1, print_title_rows = 1)
#' @name wb_page_setup
#' @export
wb_set_page_setup <- function(
      wb,
      sheet                 = current_sheet(),
      # page properties
      black_and_white       = NULL,
      cell_comments         = NULL,
      copies                = NULL,
      draft                 = NULL,
      errors                = NULL,
      first_page_number     = NULL,
      id                    = NULL, # useful and should the user be able to set this by accident?
      page_order            = NULL,
      paper_height          = NULL,
      paper_width           = NULL,
      hdpi                  = NULL,
      vdpi                  = NULL,
      use_first_page_number = NULL,
      use_printer_defaults  = NULL,
      orientation           = NULL,
      scale                 = NULL,
      left                  = 0.7,
      right                 = 0.7,
      top                   = 0.75,
      bottom                = 0.75,
      header                = 0.3,
      footer                = 0.3,
      fit_to_width          = FALSE,
      fit_to_height         = FALSE,
      paper_size            = NULL,
      # outline properties
      print_title_rows      = NULL,
      print_title_cols      = NULL,
      summary_row           = NULL,
      summary_col           = NULL,
      # tabColor properties
      tab_color             = NULL,
      # print options
      horizontal_centered   = NULL,
      vertical_centered     = NULL,
      print_headings        = NULL,
      ...
) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$set_page_setup(
      sheet                 = sheet,
      # page properties
      black_and_white       = black_and_white,
      cell_comments         = cell_comments,
      copies                = copies,
      draft                 = draft,
      errors                = errors,
      first_page_number     = first_page_number,
      id                    = id, # useful and should the user be able to set this by accident?
      page_order            = page_order,
      paper_height          = paper_height,
      paper_width           = paper_width,
      hdpi                  = hdpi,
      vdpi                  = vdpi,
      use_first_page_number = use_first_page_number,
      use_printer_defaults  = use_printer_defaults,
      orientation           = orientation,
      scale                 = scale,
      left                  = left,
      right                 = right,
      top                   = top,
      bottom                = bottom,
      header                = header,
      footer                = footer,
      fit_to_width          = fit_to_width,
      fit_to_height         = fit_to_height,
      paper_size            = paper_size,
      # outline properties
      print_title_rows      = print_title_rows,
      print_title_cols      = print_title_cols,
      summary_row           = summary_row,
      summary_col           = summary_col,
      # tabColor properties
      tab_color             = tab_color,
      # print options
      horizontal_centered   = horizontal_centered,
      vertical_centered     = vertical_centered,
      print_headings        = print_headings,
      ...
  )
}

#' @rdname wb_page_setup
#' @export
wb_page_setup <- function(
    wb,
    sheet            = current_sheet(),
    orientation      = NULL,
    scale            = 100,
    left             = 0.7,
    right            = 0.7,
    top              = 0.75,
    bottom           = 0.75,
    header           = 0.3,
    footer           = 0.3,
    fit_to_width     = FALSE,
    fit_to_height    = FALSE,
    paper_size       = NULL,
    print_title_rows = NULL,
    print_title_cols = NULL,
    summary_row      = NULL,
    summary_col      = NULL,
    ...
) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$page_setup(
    sheet            = sheet,
    orientation      = orientation,
    scale            = scale,
    left             = left,
    right            = right,
    top              = top,
    bottom           = bottom,
    header           = header,
    footer           = footer,
    fit_to_width     = fit_to_width,
    fit_to_height    = fit_to_height,
    paper_size       = paper_size,
    print_title_rows = print_title_rows,
    print_title_cols = print_title_cols,
    summary_row      = summary_row,
    summary_col      = summary_col,
    ...              = ...
  )
}


# protect ----------------------------------------------------------------------

#' Protect a worksheet from modifications
#'
#' Protect or unprotect a worksheet from modifications by the user in the
#' graphical user interface. Replaces an existing protection. Certain features
#' require applying unlocking of initialized cells in the worksheet and across
#' columns and/or rows. While passwords can be unicode characters, spreadsheet
#' software is often unable to process these. Therefore using ascii characters
#' is recommended.
#'
#' This protection only adds XML strings to the workbook. It will not encrypt
#' the file. For a full file encryption have a look at the `msoc` package.
#'
#' If the `openssl` package is installed, a SHA based password hash will be
#' used. The legacy implementation not using `openssl` is prune to collisions.
#' @note
#' The cryptographic hashing implementation used here has not been
#' independently reviewed for security. It should not be used for
#' production-level security or sensitive data without formal auditing.
#'
#' @param wb A workbook object
#' @param sheet A name or index of a worksheet
#' @param protect Whether to protect or unprotect the sheet (default=TRUE)
#' @param password (optional) password required to unprotect the worksheet
#' @param properties A character vector of properties to lock.  Can be one or
#'   more of the following: `"selectLockedCells"`, `"selectUnlockedCells"`,
#'   `"formatCells"`, `"formatColumns"`, `"formatRows"`, `"insertColumns"`,
#'   `"insertRows"`, `"insertHyperlinks"`, `"deleteColumns"`, `"deleteRows"`,
#'   `"sort"`, `"autoFilter"`, `"pivotTables"`, `"objects"`, `"scenarios"`
#' @seealso [wb_protect]
#' @export
#' @examples
#' wb <- wb_workbook()
#' wb$add_worksheet("S1")
#' wb$add_data_table(1, x = iris[1:30, ])
#'
#' wb$protect_worksheet(
#'   "S1",
#'   protect = TRUE,
#'   properties = c("formatCells", "formatColumns", "insertColumns", "deleteColumns")
#' )
#'
#' # Formatting cells / columns is allowed , but inserting / deleting columns is protected:
#' wb$protect_worksheet(
#'   "S1",
#'   protect = TRUE,
#'   properties = c(formatCells = FALSE, formatColumns = FALSE,
#'                  insertColumns = TRUE, deleteColumns = TRUE)
#' )
#'
#' # Remove the protection
#' wb$protect_worksheet("S1", protect = FALSE)
wb_protect_worksheet <- function(
    wb,
    sheet      = current_sheet(),
    protect    = TRUE,
    password   = NULL,
    properties = NULL
) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$protect_worksheet(
    sheet      = sheet,
    protect    = protect,
    password   = password,
    properties = properties
  )
}


#' Protect a workbook from modifications
#'
#' Protect or unprotect a workbook from modifications by the user in the
#' graphical user interface. Replaces an existing protection. While
#' passwords can be unicode characters, spreadsheet software is often
#' unable to process these. Therefore using ascii characters is recommended.
#'
#' This protection only adds XML strings to the workbook. It will not encrypt
#' the file. For a full file encryption have a look at the `msoc` package.
#'
#' If the `openssl` package is installed, a SHA based password hash will be
#' used. The legacy implementation not using `openssl` is prune to collisions.
#' @note
#' The cryptographic hashing implementation used here has not been
#' independently reviewed for security. It should not be used for
#' production-level security or sensitive data without formal auditing.
#'
#' @param wb A Workbook object
#' @param protect Whether to protect or unprotect the sheet (default `TRUE`)
#' @param password (optional) password required to unprotect the workbook
#' @param lock_structure Whether the workbook structure should be locked
#' @param lock_windows Whether the window position of the spreadsheet should be
#'   locked
#' @param type Lock type (see **Details**)
#' @param file_sharing Whether to enable a popup requesting the unlock password
#'   is prompted
#' @param username The username for the `file_sharing` popup
#' @param read_only_recommended Whether or not a post unlock message appears
#'   stating that the workbook is recommended to be opened in read-only mode.
#' @param ... additional arguments
#'
#' @details
#' Lock types:
#'
#' * `1`  xlsx with password (default)
#' * `2` xlsx recommends read-only
#' * `4` xlsx enforces read-only
#' * `8` xlsx is locked for annotation
#'
#' @seealso [wb_protect_worksheet]
#' @export
#' @examples
#' \donttest{
#' wb <- wb_workbook()
#' wb$add_worksheet("S1")
#' wb_protect(wb, protect = TRUE, password = "Password", lock_structure = TRUE)
#'
#' # Remove the protection
#' wb_protect(wb, protect = FALSE)
#'
#' wb <- wb_protect(
#'   wb,
#'   protect = TRUE,
#'   password = "Password",
#'   lock_structure = TRUE,
#'   type = 2L,
#'   file_sharing = TRUE,
#'   username = "Test",
#'   read_only_recommended = TRUE
#' )
#' }
wb_protect <- function(
    wb,
    protect               = TRUE,
    password              = NULL,
    lock_structure        = FALSE,
    lock_windows          = FALSE,
    type                  = 1,
    file_sharing          = FALSE,
    username              = unname(Sys.info()["user"]),
    read_only_recommended = FALSE,
    ...
) {
  assert_workbook(wb)
  wb$clone()$protect(
    protect               = protect,
    password              = password,
    lock_structure        = lock_structure,
    lock_windows          = lock_windows,
    type                  = type,
    file_sharing          = file_sharing,
    username              = username,
    read_only_recommended = read_only_recommended,
    ...                   = ...
  )
}


# grid lines --------------------------------------------------------------

#' Modify grid lines visibility in a worksheet
#'
#' Set worksheet grid lines to show or hide.
#' You can also add / remove grid lines when creating a worksheet with
#' [`wb_add_worksheet(grid_lines = FALSE)`][wb_add_worksheet()]
#'
#' @param wb A workbook object
#' @param sheet A name or index of a worksheet
#' @param show A logical. If `FALSE`, grid lines are hidden.
#' @param print A logical. If `FALSE`, grid lines are not printed.
#' @export
#' @examples
#' wb <- wb_workbook()$add_worksheet()$add_worksheet()
#' wb$get_sheet_names() ## list worksheets in workbook
#' wb$set_grid_lines(1, show = FALSE)
#' wb$set_grid_lines("Sheet 2", show = FALSE)
wb_set_grid_lines <- function(wb, sheet = current_sheet(), show = FALSE, print = show) {
  assert_workbook(wb)
  wb$clone()$set_grid_lines(sheet = sheet, show = show, print = print)
}

# TODO hide gridlines?

# worksheet order ---------------------------------------------------------

#' Order worksheets in a workbook
#'
#' Get/set order of worksheets in a Workbook object
#'
#' @param wb A `wbWorkbook` object
#'
#' @details This function does not reorder the worksheets within the workbook
#'   object, it simply shuffles the order when writing to file.
#' @export
#' @examples
#' ## setup a workbook with 3 worksheets
#' wb <- wb_workbook()
#' wb$add_worksheet("Sheet 1", grid_lines = FALSE)
#' wb$add_data_table(x = iris)
#'
#' wb$add_worksheet("mtcars (Sheet 2)", grid_lines = FALSE)
#' wb$add_data(x = mtcars)
#'
#' wb$add_worksheet("Sheet 3", grid_lines = FALSE)
#' wb$add_data(x = Formaldehyde)
#'
#' wb_get_order(wb)
#' wb$get_sheet_na
#' wb$set_order(c(1, 3, 2)) # switch position of sheets 2 & 3
#' wb$add_data(2, 'This is still the "mtcars" worksheet', start_col = 15)
#' wb_get_order(wb)
#' wb$get_sheet_names() ## ordering within workbook is not changed
#' wb$set_order(3:1)
#' @name wb_order
wb_get_order <- function(wb) {
  assert_workbook(wb)
  wb$sheetOrder
}

#' @rdname wb_order
#' @param sheets Sheet order
#' @export
wb_set_order <- function(wb, sheets) {
  assert_workbook(wb)
  wb$clone()$set_order(sheets = sheets)
}


# named region ------------------------------------------------------------


#' Modify named regions in a worksheet
#'
#' Create / delete a named region. You can also specify a named region by using
#' the `name` argument in `wb_add_data(x = iris, name = "my-region")`.
#' It is important to note that named regions are not case-sensitive and must be unique.
#'
#' You can use the [wb_dims()] helper to specify the cell range of the named region
#'
#' @param wb A Workbook object
#' @param sheet A name or index of a worksheet
#' @param dims Worksheet cell range of the region ("A1:D4").
#' @param name Name for region. A character vector of length 1. Note that region
#'   names must be case-insensitive unique.
#' @param overwrite Boolean. Overwrite if exists? Default to `FALSE`.
#' @param local_sheet If `TRUE` the named region will be local for this sheet
#' @param comment description text for named region
#' @param hidden Should the named region be hidden?
#' @param custom_menu,description,is_function,function_group_id,help,local_name,publish_to_server,status_bar,vb_procedure,workbook_parameter,xml Unknown XML feature
#' @param ... additional arguments
#' @returns A workbook, invisibly.
#' @family worksheet content functions
#' @examples
#' ## create named regions
#' wb <- wb_workbook()
#' wb$add_worksheet("Sheet 1")
#'
#' ## specify region
#' wb$add_data(x = iris, start_col = 1, start_row = 1)
#' wb$add_named_region(
#'   name = "iris",
#'   dims = wb_dims(x = iris)
#' )
#'
#' ## using add_data 'name' argument
#' wb$add_data(sheet = 1, x = iris, name = "iris2", start_col = 10)
#'
#' ## delete one
#' wb$remove_named_region(name = "iris2")
#' wb$get_named_regions()
#' ## read named regions
#' df <- wb_to_df(wb, named_region = "iris")
#' head(df)
#'
#' @name named_region-wb
NULL

#' @rdname named_region-wb
#' @export
wb_add_named_region <- function(
  wb,
  sheet             = current_sheet(),
  dims              = "A1",
  name,
  local_sheet        = FALSE,
  overwrite          = FALSE,
  comment            = NULL,
  hidden             = NULL,
  custom_menu        = NULL,
  description        = NULL,
  is_function        = NULL,
  function_group_id  = NULL,
  help               = NULL,
  local_name         = NULL,
  publish_to_server  = NULL,
  status_bar         = NULL,
  vb_procedure       = NULL,
  workbook_parameter = NULL,
  xml                = NULL,
  ...
) {
  assert_workbook(wb)
  wb$clone()$add_named_region(
    sheet              = sheet,
    dims               = dims,
    name               = name,
    local_sheet        = local_sheet,
    overwrite          = overwrite,
    comment            = comment,
    custom_menu        = custom_menu,
    description        = description,
    is_function        = is_function,
    function_group_id  = function_group_id,
    help               = help,
    hidden             = hidden,
    local_name         = local_name,
    publish_to_server  = publish_to_server,
    status_bar         = status_bar,
    vb_procedure       = vb_procedure,
    workbook_parameter = workbook_parameter,
    xml                = xml,
    ...                = ...
  )
}

#' @rdname named_region-wb
#' @export
wb_remove_named_region <- function(wb, sheet = current_sheet(), name = NULL) {
  assert_workbook(wb)
  wb$clone()$remove_named_region(sheet = sheet, name = name)
}

# filters -----------------------------------------------------------------

#' Add/remove column filters in a worksheet
#'
#' Add or remove spreadsheet column filters to a worksheet
#'
#' Adds filters to worksheet columns, same as `with_filter = TRUE` in [wb_add_data()]
#' [wb_add_data_table()] automatically adds filters to first row of a table.
#'
#' NOTE Can only have a single filter per worksheet unless using tables.
#'
#' @param wb A workbook object
#' @param sheet A worksheet name or index.
#'   In `wb_remove_filter()`, you may supply a vector of worksheets.
#' @param cols columns to add filter to.
#' @param rows A row number.
#' @seealso [wb_add_data()], [wb_add_data_table()]
#' @examples
#' wb <- wb_workbook()
#' wb$add_worksheet("Sheet 1")
#' wb$add_worksheet("Sheet 2")
#' wb$add_worksheet("Sheet 3")
#'
#' wb$add_data(1, iris)
#' wb$add_filter(1, row = 1, cols = seq_along(iris))
#'
#' ## Equivalently
#' wb$add_data(2, x = iris, with_filter = TRUE)
#'
#' ## Similarly
#' wb$add_data_table(3, iris)
#' wb <- wb_workbook()
#' wb$add_worksheet("Sheet 1")
#' wb$add_worksheet("Sheet 2")
#' wb$add_worksheet("Sheet 3")
#'
#' wb$add_data(1, iris)
#' wb_add_filter(wb, 1, row = 1, cols = seq_along(iris))
#'
#' ## Equivalently
#' wb$add_data(2, x = iris, with_filter = TRUE)
#'
#' ## Similarly
#' wb$add_data_table(3, iris)
#'
#' ## remove filters
#' wb_remove_filter(wb, 1:2) ## remove filters
#' wb_remove_filter(wb, 3) ## Does not affect tables!
#' @name filter-wb
#' @family worksheet content functions
NULL
#' @rdname filter-wb
#' @export
wb_add_filter <- function(wb, sheet = current_sheet(), rows, cols) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$add_filter(sheet = sheet, rows = rows, cols = cols)
}
#' @rdname filter-wb
#' @export
wb_remove_filter <- function(wb, sheet = current_sheet()) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$remove_filter(sheet = sheet)
}


# validations ------------------------------------------------------------------

#' Add data validation to cells in a worksheet
#'
#' Add spreadsheet data validation to cells
#'
#' @param wb A Workbook object
#' @param sheet A name or index of a worksheet
#' @param dims A cell dimension ("A1" or "A1:B2")
#' @param type One of 'whole', 'decimal', 'date', 'time', 'textLength', 'list'
#'   (see examples)
#' @param operator One of 'between', 'notBetween', 'equal',
#'  'notEqual', 'greaterThan', 'lessThan', 'greaterThanOrEqual', 'lessThanOrEqual'
#' @param value a vector of length 1 or 2 depending on operator (see examples)
#' @param allow_blank logical
#' @param show_input_msg logical
#' @param show_error_msg logical
#' @param error_style The icon shown and the options how to deal with such inputs.
#'   Default "stop" (cancel), else "information" (prompt popup) or
#'   "warning" (prompt accept or change input)
#' @param error_title The error title
#' @param error The error text
#' @param prompt_title The prompt title
#' @param prompt The prompt text
#' @param ... additional arguments
#' @export
#' @examples
#' wb <- wb_workbook()
#' wb$add_worksheet("Sheet 1")
#' wb$add_worksheet("Sheet 2")
#'
#' wb$add_data_table(1, x = iris[1:30, ])
#' wb$add_data_validation(1,
#'   dims = "A2:C31", type = "whole",
#'   operator = "between", value = c(1, 9)
#' )
#' wb$add_data_validation(1,
#'   dims = "E2:E31", type = "textLength",
#'   operator = "between", value = c(4, 6)
#' )
#'
#' ## Date and Time cell validation
#' df <- data.frame(
#'   "d" = as.Date("2016-01-01") + -5:5,
#'   "t" = as.POSIXct("2016-01-01") + -5:5 * 10000
#' )
#' wb$add_data_table(2, x = df)
#' wb$add_data_validation(2, dims = "A2:A12", type = "date",
#'   operator = "greaterThanOrEqual", value = as.Date("2016-01-01")
#' )
#' wb$add_data_validation(2,
#'   dims = "B2:B12", type = "time",
#'   operator = "between", value = df$t[c(4, 8)]
#' )
#'
#'
#' ######################################################################
#' ## If type == 'list'
#' # operator argument is ignored.
#'
#' wb <- wb_workbook()
#' wb$add_worksheet("Sheet 1")
#' wb$add_worksheet("Sheet 2")
#'
#' wb$add_data_table(sheet = 1, x = iris[1:30, ])
#' wb$add_data(sheet = 2, x = sample(iris$Sepal.Length, 10))
#'
#' wb$add_data_validation(1, dims = "A2:A31", type = "list", value = "'Sheet 2'!$A$1:$A$10")
wb_add_data_validation <- function(
    wb,
    sheet          = current_sheet(),
    dims           = "A1",
    type,
    operator,
    value,
    allow_blank    = TRUE,
    show_input_msg = TRUE,
    show_error_msg = TRUE,
    error_style    = NULL,
    error_title    = NULL,
    error          = NULL,
    prompt_title   = NULL,
    prompt         = NULL,
    ...
) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$add_data_validation(
    sheet          = sheet,
    dims           = dims,
    type           = type,
    operator       = operator,
    value          = value,
    allow_blank    = allow_blank,
    show_input_msg = show_input_msg,
    show_error_msg = show_error_msg,
    error_style    = error_style,
    error_title    = error_title,
    error          = error,
    prompt_title   = prompt_title,
    prompt         = prompt,
    ...            = ...
  )
}


# visibility --------------------------------------------------------------

#' Get/set worksheet visible state in a workbook
#'
#' Get and set worksheet visible state. This allows to hide worksheets from the workbook.
#' The visibility of a worksheet can either be  "visible", "hidden", or "veryHidden".
#' You can set this when creating a worksheet with `wb_add_worksheet(visible = FALSE)`
#'
#' @return
#' * `wb_set_sheet_visibility`: The Workbook object, invisibly.
#' * `wb_get_sheet_visibility()`: A character vector of the worksheet visibility value
#' @examples
#'
#' wb <- wb_workbook()
#' wb$add_worksheet(sheet = "S1", visible = FALSE)
#' wb$add_worksheet(sheet = "S2", visible = TRUE)
#' wb$add_worksheet(sheet = "S3", visible = FALSE)
#'
#' wb$get_sheet_visibility()
#' wb$set_sheet_visibility(1, TRUE)         ## show sheet 1
#' wb$set_sheet_visibility(2, FALSE)        ## hide sheet 2
#' wb$set_sheet_visibility(3, "hidden")     ## hide sheet 3
#' wb$set_sheet_visibility(3, "veryHidden") ## hide sheet 3 from UI
#' @name sheet_visibility-wb
NULL

#' @rdname sheet_visibility-wb
#' @param wb A `wbWorkbook` object
#' @export
wb_get_sheet_visibility <- function(wb) {
  assert_workbook(wb)
  wb$get_sheet_visibility()
}

#' @rdname sheet_visibility-wb
#' @param sheet Worksheet identifier
#' @param value a logical/character vector the same length as sheet,
#'   if providing a character vector, you can provide any of "hidden", "visible", or "veryHidden"
#' @export
wb_set_sheet_visibility <- function(wb, sheet = current_sheet(), value) {
  assert_workbook(wb)
  wb$clone()$set_sheet_visibility(sheet = sheet, value = value)
}


#' Add a page break to a worksheet
#'
#' @description
#' The `wb_add_page_break()` function allows you to manually insert horizontal
#' or vertical page breaks into a worksheet. These breaks determine where the
#' spreadsheet software starts a new page when printing or generating a PDF.
#'
#' @details
#' Manual page breaks override the automatic breaks calculated by the software
#' based on margins and paper size.
#'
#' * Row Breaks: When a `row` is specified, the horizontal break is placed
#'     *above* the specified row. For example, setting `row = 10` ensures that
#'     Row 10 starts on a new page.
#' * Column Breaks: When a `col` is specified, the vertical break is placed
#'     to the *left* of that column. For example, `col = "B"` (or `2`) ensures
#'     Column B is the first column on the next vertical page.
#'
#' You must provide either a `row` or a `col` index, but not both in a single
#' call. To create a page intersection (both horizontal and vertical), call
#' the function twice.
#'
#' @param wb A [wbWorkbook] object.
#' @param sheet The name or index of the worksheet. Defaults to the current sheet.
#' @param row Integer; the row number where the horizontal page break should
#'   be inserted.
#' @param col Integer or character; the column number or name (e.g., "B")
#'   where the vertical page break should be inserted.
#'
#' @return The [wbWorkbook] object, invisibly.
#'
#' @section Notes:
#' * Manual breaks are visible in "Page Break Preview" mode within most
#'     spreadsheet applications.
#'
#' @seealso [wb_add_worksheet()]
#' @examples
#' wb <- wb_workbook()
#' wb$add_worksheet("Sheet 1")
#' wb$add_data(sheet = 1, x = iris)
#'
#' wb$add_page_break(sheet = 1, row = 10)
#' wb$add_page_break(sheet = 1, row = 20)
#' wb$add_page_break(sheet = 1, col = 2)
#' @export
wb_add_page_break <- function(wb, sheet = current_sheet(), row = NULL, col = NULL) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$add_page_break(sheet = sheet, row = row, col = col)
}


#' List tables in a worksheet
#'
#' @param wb A workbook object
#' @param sheet A name or index of a worksheet
#' @return A character vector of table names on the specified sheet
#' @examples
#'
#' wb <- wb_workbook()
#' wb$add_worksheet(sheet = "Sheet 1")
#' wb$add_data_table(x = iris)
#' wb$add_data_table(x = mtcars, table_name = "mtcars", start_col = 10)
#'
#' wb$get_tables(sheet = "Sheet 1")
#' @export
wb_get_tables <- function(wb, sheet = current_sheet()) {
  assert_workbook(wb)
  wb$clone()$get_tables(sheet = sheet)
}


#' Remove a data table from a worksheet
#'
#' Remove tables in a workbook using its name.
#'
#' @param wb A Workbook object
#' @param sheet A name or index of a worksheet
#' @param table Name of table to remove. Use [wb_get_tables()] to view the
#'   tables present in the worksheet.
#' @param remove_data Default `TRUE`. If `FALSE`, will only remove the data table attributes
#'   but will keep the data in the worksheet.
#' @return The `wbWorkbook`, invisibly
#' @examples
#' wb <- wb_workbook()
#' wb$add_worksheet(sheet = "Sheet 1")
#' wb$add_worksheet(sheet = "Sheet 2")
#' wb$add_data_table(sheet = "Sheet 1", x = iris, table_name = "iris")
#' wb$add_data_table(sheet = 1, x = mtcars, table_name = "mtcars", start_col = 10)
#'
#' ## delete worksheet removes table objects
#' wb <- wb_remove_worksheet(wb, sheet = 1)
#'
#' wb$add_data_table(sheet = 1, x = iris, table_name = "iris")
#' wb$add_data_table(sheet = 1, x = mtcars, table_name = "mtcars", start_col = 10)
#'
#' ## wb_remove_tables() deletes table object and all data
#' wb_get_tables(wb, sheet = 1)
#' wb$remove_tables(sheet = 1, table = "iris")
#' wb$add_data_table(sheet = 1, x = iris, table_name = "iris")
#'
#' wb_get_tables(wb, sheet = 1)
#' wb$remove_tables(sheet = 1, table = "iris")
#' @export
wb_remove_tables <- function(wb, sheet = current_sheet(), table, remove_data = TRUE) {
  assert_workbook(wb)
  if (missing(table)) table <- substitute()
  wb$clone(deep = TRUE)$remove_tables(sheet = sheet, table = table, remove_data = remove_data)
}


# grouping ----------------------------------------------------------------

#' Group rows and columns in a worksheet
#'
#' Group a selection of rows or cols
#'
#' @details If row was previously hidden, it will now be shown. Columns can be
#' added using A1 notion, so `cols = 2:3` is similar to `cols = "B:C"`. It is
#' possible to add nested groups, so `cols = list("3" = list(1:2, 3:4)` is also
#' possible. Depending on the selected summary column either left or right will
#' be selected for grouping, this can be changed in `wb_set_page_setup()`.
#'
#' @param wb A `wbWorkbook` object
#' @param sheet A name or index of a worksheet
#' @param rows,cols Indices or for columns also characters of rows and columns to group
#' @param collapsed If `TRUE` the grouped columns are collapsed
#' @param levels levels
#' @family workbook wrappers
#' @family worksheet content functions
#' @examples
#' # create matrix
#' t1 <- AirPassengers
#' t2 <- do.call(cbind, split(t1, cycle(t1)))
#' dimnames(t2) <- dimnames(.preformat.ts(t1))
#'
#' wb <- wb_workbook()
#' wb$add_worksheet("AirPass")
#' wb$add_data("AirPass", t2, row_names = TRUE)
#'
#' # groups will always end on/show the last row. in the example 1950, 1955, and 1960
#' wb <- wb_group_rows(wb, "AirPass", 2:3, collapsed = TRUE) # group years < 1950
#' wb <- wb_group_rows(wb, "AirPass", 4:8, collapsed = TRUE) # group years 1951-1955
#' wb <- wb_group_rows(wb, "AirPass", 9:13)                  # group years 1956-1960
#'
#' wb <- wb_group_cols(wb, "AirPass", 2:4, collapsed = TRUE)
#' wb <- wb_group_cols(wb, "AirPass", 5:7, collapsed = TRUE)
#' wb <- wb_group_cols(wb, "AirPass", 8:10, collapsed = TRUE)
#' wb <- wb_group_cols(wb, "AirPass", 11:13)
#'
#' @name grouping-wb
#' @family workbook wrappers
NULL

#' @export
#' @rdname grouping-wb
wb_group_cols <- function(wb, sheet = current_sheet(), cols, collapsed = FALSE, levels = NULL) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$group_cols(
    sheet     = sheet,
    cols      = cols,
    collapsed = collapsed,
    levels    = levels
  )
}

#' @export
#' @rdname grouping-wb
wb_ungroup_cols <- function(wb, sheet = current_sheet(), cols) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$ungroup_cols(sheet = sheet, cols = cols)
}


#' @export
#' @rdname grouping-wb
#' @examples
#' ### create grouping levels
#' grp_rows <- list(
#'   "1" = seq(2, 3),
#'   "2" = seq(4, 8),
#'   "3" = seq(9, 13)
#' )
#'
#' grp_cols <- list(
#'   "1" = seq(2, 4),
#'   "2" = seq(5, 7),
#'   "3" = seq(8, 10),
#'   "4" = seq(11, 13)
#' )
#'
#' wb <- wb_workbook()
#' wb$add_worksheet("AirPass")
#' wb$add_data("AirPass", t2, row_names = TRUE)
#'
#' wb$group_cols("AirPass", cols = grp_cols)
#' wb$group_rows("AirPass", rows = grp_rows)
wb_group_rows <- function(wb, sheet = current_sheet(), rows, collapsed = FALSE, levels = NULL) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$group_rows(
    sheet     = sheet,
    rows      = rows,
    collapsed = collapsed,
    levels    = levels
  )
}

#' @export
#' @rdname grouping-wb
wb_ungroup_rows <- function(wb, sheet = current_sheet(), rows) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$ungroup_rows(sheet = sheet, rows = rows)
}


# creators ----------------------------------------------------------------

#' Modify workbook properties
#'
#' This function is useful for workbooks that are loaded. It can be used to set the
#' workbook `title`, `subject` and `category` field. Use [wb_workbook()]
#' to easily set these properties with a new workbook.
#'
#' To set properties, the following XML core properties are used.
#' - title = dc:title
#' - subject = dc:subject
#' - creator = dc:creator
#' - keywords = cp:keywords
#' - comments = dc:description
#' - modifier = cp:lastModifiedBy
#' - datetime_created = dcterms:created
#' - datetime_modified = dcterms:modified
#' - category = cp:category
#'
#' In addition, manager and company are used.
#' @name properties-wb
#' @param wb A Workbook object
#' @param modifier A character string indicating who was the last person to modify the workbook
#' @param custom A named vector of custom properties added to the workbook
#' @seealso [wb_workbook()]
#' @inheritParams wb_workbook
#' @return A wbWorkbook object, invisibly.
#' @export
#'
#' @examples
#' file <- system.file("extdata", "openxlsx2_example.xlsx", package = "openxlsx2")
#' wb <- wb_load(file)
#' wb$get_properties()
#'
#' # Add a title to properties
#' wb$set_properties(title = "my title")
#' wb$get_properties()
wb_get_properties <- function(wb) {
  assert_workbook(wb)
  wb$get_properties()
}

#' @rdname properties-wb
#' @export
wb_set_properties <- function(wb, creator = NULL, title = NULL, subject = NULL, category = NULL, datetime_created = NULL, datetime_modified = NULL, modifier = NULL, keywords = NULL, comments = NULL, manager = NULL, company = NULL, custom = NULL) {
  assert_workbook(wb)
  wb$clone()$set_properties(
    creator           = creator,
    title             = title,
    subject           = subject,
    category          = category,
    datetime_created  = datetime_created,
    datetime_modified = datetime_modified,
    modifier          = modifier,
    keywords          = keywords,
    comments          = comments,
    manager           = manager,
    company           = company,
    custom            = custom
  )
}

#' wb get and apply MIP section
#'
#' Read sensitivity labels from files and apply them to workbooks
#'
#' @details
#' The MIP section is a special user-defined XML section that is used to create
#' sensitivity labels in workbooks. It consists of a series of XML property
#' nodes that define the sensitivity label. This XML string cannot be created
#' and it is necessary to first load a workbook with a suitable sensitivity
#' label. Once the workbook is loaded, the string `fmips <- wb_get_mips(wb)`
#' can be extracted. This xml string can later be assigned to an
#' `options("openxlsx2.mips_xml_string" = fmips)` option.
#'
#' The sensitivity label can then be assigned with `wb_add_mips(wb)`. If no xml
#' string is passed, the MIP section is taken from the option. This should make
#' it easier for users to read the section from a specific workbook, save it to
#' a file or string and copy it to an option via the .Rprofile.
#'
#' @param wb a workbook
#' @param xml a mips string obtained from [wb_get_mips()] or a global option "openxlsx2.mips_xml_string"
#' @returns the workbook invisible ([wb_add_mips()]) or the xml string ([wb_get_mips()])
#' @export
wb_add_mips <- function(wb, xml = NULL) {
  assert_workbook(wb)
  wb$clone()$add_mips(xml = xml)
}

#' @param single_xml option to define if the string should be exported as single string. helpful if storing as option is desired.
#' @param quiet option to print a MIP section name. This is not always a human readable string.
#' @rdname wb_add_mips
#' @export
wb_get_mips <- function(wb, single_xml = TRUE, quiet = TRUE) {
  assert_workbook(wb)
  wb$get_mips(single_xml = single_xml, quiet = quiet)
}

#' Modify creators of a workbook
#'
#' Modify and get workbook creators
#'
#' @param wb A `wbWorkbook` object
#' @examples
#'
#' # workbook made with default creator (see [wbWorkbook])
#' wb <- wb_workbook()
#' wb_get_creators(wb)
#'
#' # add a new creator (assuming "test" isn't your default creator)
#' wb <- wb_add_creators(wb, "test")
#' wb_get_creators(wb)
#'
#' # remove the creator (should be the same as before)
#' wb <- wb_remove_creators(wb, "test")
#' wb_get_creators(wb)
#'
#' @return
#' * `wb_set_creators()`, `wb_add_creators()`, and `wb_remove_creators()` return
#'    the `wbWorkbook` object
#' * `wb_get_creators()` returns a `character` vector of creators
#'
#' @name creators-wb
#' @family workbook wrappers
NULL

#' @rdname creators-wb
#' @export
#' @param creators A character vector of names
wb_add_creators <- function(wb, creators) {
  assert_workbook(wb)
  wb$clone()$add_creators(creators)
}

#' @rdname creators-wb
#' @export
wb_set_creators <- function(wb, creators) {
  assert_workbook(wb)
  wb$clone()$set_creators(creators)
}

#' @rdname creators-wb
#' @export
wb_remove_creators <- function(wb, creators) {
  assert_workbook(wb)
  wb$clone()$remove_creators(creators)
}

#' @rdname creators-wb
#' @export
wb_get_creators <- function(wb) {
  assert_workbook(wb)
  strsplit(wb$get_properties()[["creator"]], ";")[[1]]
}


# names -------------------------------------------------------------------

#' Get / Set worksheet names for a workbook
#'
#' Gets / Sets the worksheet names for a [wbWorkbook] object.
#'
#' This only changes the sheet name as shown in spreadsheet software
#' and will not alter it elsewhere. Not in formulas, chart references,
#' named regions, pivot tables or anywhere else.
#'
#' @param wb A [wbWorkbook] object
#' @param old The name (or index) of the old sheet name. If `NULL` will assume
#'   all worksheets are to be renamed.
#' @param new The name of the new sheet
#' @name sheet_names-wb
#' @returns
#'   * `set_`: The `wbWorkbook` object.
#'   * `get_`: A named character vector of sheet names in order. The
#'   names represent the original value of the worksheet prior to any character
#'   substitutions.
#'
NULL
#' @rdname sheet_names-wb
#' @export
wb_set_sheet_names <- function(wb, old = NULL, new) {
  assert_workbook(wb)
  wb$clone()$set_sheet_names(old = old, new = new)
}
#' @rdname sheet_names-wb
#' @param escape Should the xml special characters be escaped?
#' @export
wb_get_sheet_names <- function(wb, escape = FALSE) {
  assert_workbook(wb)
  wb$get_sheet_names(escape = escape)
}

# others? -----------------------------------------------------------------

#' Modify author in the metadata of a workbook
#'
#' Just a wrapper of `wb$set_last_modified_by()`
#'
#' @param wb A workbook object
#' @param name A string object with the name of the LastModifiedBy-User
#' @param ... additional arguments
#' @family workbook wrappers
#' @export
#' @examples
#' wb <- wb_workbook()
#' wb_set_last_modified_by(wb, "test")
wb_set_last_modified_by <- function(wb, name, ...) {
  if (missing(name)) name <- substitute()
  assert_workbook(wb)
  wb$clone()$set_last_modified_by(name, ...)
}

#' Insert an image into a worksheet
#'
#' @description
#' The `wb_add_image()` function embeds external image files into a worksheet. It
#' supports standard raster formats and provides granular control over positioning
#' through a variety of anchoring methods. Images can be anchored to absolute
#' positions, individual cells, or defined ranges, and can optionally function
#' as clickable hyperlinks.
#'
#' @details
#' Image placement is determined by the `dims` argument and internal anchoring
#' logic. If a single cell is provided (e.g., "A1"), the image is placed using a
#' one-cell anchor where the top-left corner is fixed to the cell. If a range
#' is provided (e.g., "A1:D4"), a two-cell anchor is utilized, which can cause
#' the image to scale with the underlying rows and columns.
#'
#' Position offsets (`row_offset` and `col_offset`) allow for sub-cell precision
#' by shifting the image from its anchor point. Internally, all dimensions are
#' converted to English Metric Units (EMUs), where 1 inch equals 914,400 EMUs,
#' ensuring high-fidelity rendering across different display scales.
#'
#' Supported file types include `"jpeg"`, `"png"`, and `"bmp"`. If an `address`
#' is provided, the function creates a relationship to an external target or
#' email, transforming the image into a functional hyperlink.
#'
#' @param wb A [wbWorkbook] object.
#' @param sheet The name or index of the worksheet to receive the image.
#'   Defaults to the current sheet.
#' @param dims A character string defining the placement. A single cell (e.g., "A1")
#'   uses a one-cell anchor; a range (e.g., "A1:D4") uses a two-cell anchor.
#' @param file The path to the image file. Supported formats are JPEG, PNG, and BMP.
#' @param width,height The numeric width and height of the image.
#' @param row_offset,col_offset Offset vectors for fine-tuning the position within
#'   the anchor cell(s).
#' @param units The units for `width` and `height`. Must be one of `"in"` (inches),
#'   `"cm"` (centimeters), or `"px"` (pixels).
#' @param dpi The resolution (dots per inch) used for conversion when `units`
#'   is set to `"px"`. Defaults to 300.
#' @param address An optional character string specifying a URL, file path, or
#'   "mailto:" link to be opened when the image is clicked.
#' @param ... Additional arguments. Includes support for legacy `start_row` and
#'   `start_col` parameters.
#'
#' @examples
#' img <- system.file("extdata", "einstein.jpg", package = "openxlsx2")
#'
#' wb <- wb_workbook()$
#'   add_worksheet()$
#'   add_image("Sheet 1", dims = "C5", file = img, width = 6, height = 5)$
#'   add_worksheet()$
#'   add_image(dims = "B2", file = img)$
#'   add_worksheet()$
#'   add_image(dims = "G3", file = img, width = 15, height = 12, units = "cm")
#' @seealso [wb_add_chart_xml()] [wb_add_drawing()] [wb_add_mschart()] [wb_add_plot()]
#' @export
wb_add_image <- function(
  wb,
  sheet      = current_sheet(),
  dims       = "A1",
  file,
  width      = 6,
  height     = 3,
  row_offset = 0,
  col_offset = 0,
  units      = "in",
  dpi        = 300,
  address    = NULL,
  ...
) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$add_image(
    sheet      = sheet,
    dims       = dims,
    file       = file,
    width      = width,
    height     = height,
    row_offset = row_offset,
    col_offset = col_offset,
    units      = units,
    dpi        = dpi,
    address    = address,
    ...
  )
}


#' Add a chart XML to a worksheet
#'
#' @param wb a workbook
#' @param sheet the sheet on which the graph will appear
#' @param dims the dimensions where the sheet will appear
#' @param xml chart xml
#' @param style chart style
#' @param color chart color
#' @param col_offset,row_offset positioning
#' @param ... additional arguments
#' @seealso [wb_add_drawing()] [wb_add_image()] [wb_add_mschart()] [wb_add_plot()]
#' @export
wb_add_chart_xml <- function(
  wb,
  sheet      = current_sheet(),
  dims       = NULL,
  xml,
  style      = "",
  color      = "",
  col_offset = 0,
  row_offset = 0,
  ...
) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$add_chart_xml(
    sheet      = sheet,
    xml        = xml,
    style      = style,
    color      = color,
    dims       = dims,
    col_offset = col_offset,
    row_offset = row_offset,
    ...        = ...
  )
}


#' Clear content and formatting from a worksheet
#'
#' @description
#' The `wb_clean_sheet()` function removes data, formulas, and formatting from
#' a worksheet. It can be used to wipe an entire sheet clean or to target
#' specific cell regions (`dims`). This is particularly useful when you want
#' to reuse an existing sheet structure but replace the data or reset the styling.
#'
#' @details
#' Unlike deleting a worksheet, cleaning a sheet preserves the sheet's
#' existence, name, and properties (like tab color or sheet views) while
#' emptying the cell-level data.
#'
#' Selective Removal:
#' By toggling the logical arguments, you can choose exactly what to discard.
#' For example, you can remove data but keep the cell styles (borders, fills),
#' or vice-versa.
#'
#' * Numbers/Characters: Targeting these specifically allows you to clear
#'     constants while potentially leaving other elements intact.
#' * Styles: Resets cells to the workbook's default appearance.
#' * Merged Cells: Unmerges ranges; if `dims` is provided, only merges
#'     within that range are broken.
#'
#' @param wb A [wbWorkbook] object.
#' @param sheet The name or index of the worksheet to clean. Defaults to the
#'   current sheet.
#' @param dims Optional character string defining a cell range (e.g., "A1:G20").
#'   If `NULL`, the entire worksheet is cleaned.
#' @param numbers Logical; if `TRUE`, removes all numeric values, booleans,
#'   and error codes.
#' @param characters Logical; if `TRUE`, removes all text strings (shared,
#'   inline, or formula-based strings).
#' @param styles Logical; if `TRUE`, removes all cell styles and resets
#'   formatting to default.
#' @param merged_cells Logical; if `TRUE`, unmerges all cells (or those
#'   within `dims`).
#' @param hyperlinks Logical; if `TRUE`, removes hyperlinks from the specified
#'   region.
#'
#' @return The [wbWorkbook] object, invisibly.
#'
#' @section Notes:
#' * Currently, this function does not remove objects like images, charts,
#'     comments, or pivot tables.
#'
#' @export
wb_clean_sheet <- function(
    wb,
    sheet        = current_sheet(),
    dims         = NULL,
    numbers      = TRUE,
    characters   = TRUE,
    styles       = TRUE,
    merged_cells = TRUE,
    hyperlinks   = TRUE
) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$clean_sheet(
    sheet        = sheet,
    dims         = dims,
    numbers      = numbers,
    characters   = characters,
    styles       = styles,
    merged_cells = merged_cells,
    hyperlinks   = hyperlinks
  )
}

#' Preview a workbook in spreadsheet software
#'
#' @description
#' `wb_open()` provides a convenient interface to immediately view the contents
#' of a `wbWorkbook` object within a spreadsheet application. This function
#' serves as a high-level wrapper for [xl_open()], allowing users to inspect
#' the results of programmatic workbook construction without explicitly
#' managing file paths.
#'
#' @details
#' The function operates by creating a temporary copy of the workbook on the
#' local file system and subsequently invoking the system's default handler
#' or a specified spreadsheet application. For users utilizing the R6
#' interface, `wb$open()` is available as a shorter alias for this function.
#'
#' @param wb A [wbWorkbook] object to be previewed.
#' @param interactive Logical; determines if the file should be opened. When
#'   `NA` (the default), it inherits the value from [base::interactive()]. If
#'   `FALSE`, a warning is issued and the file is not opened.
#' @param flush Logical; if `TRUE`, the `flush` argument is passed to the internal
#'   save call. This controls the XML processing method used when writing the
#'   temporary file. For a detailed discussion on the performance and memory
#'   implications of this parameter, see [wb_save()].
#'
#' @return The [wbWorkbook] object, invisibly.
#'
#' @seealso [xl_open()], [wb_save()]
#' @export
wb_open <- function(wb, interactive = NA, flush = FALSE) {
  assert_workbook(wb)
  wb$open(interactive = interactive, flush = flush)
}

#' Register a style in a workbook
#'
#' @description
#' The `wb_add_style()` function serves as a general-purpose entry point for
#' registering XML-based style definitions into a `wbWorkbook`. While specific
#' wrappers like [wb_add_font()] target individual cell properties, `wb_add_style()`
#' is used to add pre-constructed styles—such as custom table styles or
#' differential formatting (DXF) styles—to the workbook's internal style manager.
#'
#' @details
#' Styles in the OpenXML specification are stored in a centralized catalog
#' (`styles.xml`). This function takes an XML character string, typically
#' generated by a `create_*()` function, and registers it under a specific
#' `style_name`.
#'
#' Once registered, these styles can be applied to cells, ranges, or tables
#' by referencing their name. This is particularly useful for maintaining
#' consistency across a large workbook or when creating complex "Table Styles"
#' that define headers, footers, and banding in a single object.
#'
#' @param wb A [wbWorkbook] object.
#' @param style A character string containing the XML definition of the style.
#'   This is usually the output of functions like [create_cell_style()] or
#'   [create_dxfs_style()].
#' @param style_name Optional; a unique name for the style. If `NULL`, the
#'   function attempts to derive a name from the object name or the internal
#'   XML attributes (e.g., for table styles).
#'
#' @return The [wbWorkbook] object, invisibly.
#'
#' @section Notes:
#' * If the `style` provided is a `tableStyle` node, the function automatically
#'     extracts the name from the XML attribute if `style_name` is not provided.
#' * Registering a style does not automatically apply it to a cell; it only
#'     makes the style available within the workbook's style catalog.
#'
#' @seealso
#' * [create_border()]
#' * [create_cell_style()]
#' * [create_dxfs_style()]
#' * [create_fill()]
#' * [create_font()]
#' * [create_numfmt()]
#' @family workbook styling functions
#' @examples
#' yellow_f <- wb_color(hex = "FF9C6500")
#' yellow_b <- wb_color(hex = "FFFFEB9C")
#'
#' yellow <- create_dxfs_style(font_color = yellow_f, bg_fill = yellow_b)
#' wb <- wb_workbook()
#' wb <- wb_add_style(wb, yellow)
#' @export
wb_add_style <- function(wb, style = NULL, style_name = NULL) {
  assert_workbook(wb)
  # deparse this name, otherwise it will remain "style"
  if (is.null(style_name)) style_name <- deparse(substitute(style))
  wb$clone(deep = TRUE)$add_style(style, style_name)
}

#' Get or set cell style indices
#'
#' @description
#' The `wb_get_cell_style()` and `wb_set_cell_style()` functions provide a
#' direct way to manage the internal style index (XF ID) of a cell. This is
#' particularly useful for "copy-pasting" the formatting from one cell to
#' another or for applying pre-defined styles at scale without the overhead
#' of creating new XML nodes for every cell.
#'
#' @details
#' In the OpenXML format, formatting is not stored inside every cell. Instead,
#' a workbook maintains a centralized style table, and each cell simply holds
#' an integer index (the Cell Style ID) pointing to a record in that table.
#'
#' `wb_get_cell_style()` retrieves these indices for a specified range. If
#' a cell has not been explicitly styled, the function returns the index for
#' the default style.
#'
#' `wb_set_cell_style()` applies a specific index or style definition to a
#' range. This is significantly faster and more memory-efficient than using high-level
#' wrappers like `wb_add_font()` when applying the exact same style to thousands
#' of individual cells.
#'
#' @param wb A [wbWorkbook] object.
#' @param sheet The name or index of the worksheet. Defaults to the current sheet.
#' @param dims A character string defining the cell range (e.g., "A1" or "A1:C10").
#' @param style An integer style ID (XF ID), a character string of a registered
#'   style name, or a cell dimension (e.g., "B1") from which to copy the style.
#'
#' @return
#' * For `wb_get_cell_style()`: A named vector where names are cell addresses
#'   (e.g., "A1") and values are the integer style indices.
#' * For `wb_set_cell_style()`: The [wbWorkbook] object, invisibly.
#'
#' @section Notes:
#' * These functions are the most efficient way to handle repetitive styling
#'     tasks in large worksheets.
#' * If `style` is a character string that is not a cell dimension, it is
#'     looked up in the workbook's Style Manager.
#'
#' @name wb_cell_style
#' @family styles
#' @examples
#' # set a style in B1
#' wb <- wb_workbook()$add_worksheet()$
#'   add_numfmt(dims = "B1", numfmt = "#,0")
#'
#' # get style from B1 to assign it to A1
#' numfmt <- wb$get_cell_style(dims = "B1")
#'
#' # assign style to a1
#' wb$set_cell_style(dims = "A1", style = numfmt)
#' @return A named vector with cell style index positions
#' @export
wb_get_cell_style <- function(wb, sheet = current_sheet(), dims) {
  assert_workbook(wb)
  wb$get_cell_style(sheet, dims)
}

#' @rdname wb_cell_style
#' @param style A style or a cell with a certain style
#' @export
wb_set_cell_style <- function(wb, sheet = current_sheet(), dims, style) {
  assert_workbook(wb)
  # needs deep clone for nested calls as in styles vignette copy cell styles
  wb$clone(deep = TRUE)$set_cell_style(sheet, dims, style)
}

#' @rdname wb_cell_style
#' @param cols The columns the style will be applied to, either "A:D" or 1:4
#' @param rows The rows the style will be applied to
#' @examples
#'
#' # set style across a workbook
#' wb <- wb_workbook()
#' wb <- wb_add_worksheet(wb)
#' wb <- wb_add_fill(wb, dims = "C3", color = wb_color("yellow"))
#' wb <- wb_set_cell_style_across(wb, style = "C3", cols = "C:D", rows = 3:4)
#' @export
wb_set_cell_style_across <- function(wb, sheet = current_sheet(), style, cols = NULL, rows = NULL) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$set_cell_style_across(sheet = sheet, style = style, cols = cols, rows = rows)
}

#' Modify borders in a cell region of a worksheet
#'
#' @description
#' The `wb_add_border()` function provides a high-level interface for applying
#' and managing cell borders within a `wbWorkbook`. It is designed to handle
#' both single cells and multi-cell regions, with built-in logic to differentiate
#' between exterior boundary borders and interior grid lines.
#'
#' @details
#' When applied to a range of cells (e.g., "A1:C3"), `wb_add_border()` treats
#' the selection as a single cohesive block. Parameters like `top_border` and
#' `left_border` apply only to the outermost edges of the entire range. To
#' draw lines between cells within the range, the `inner_hgrid` (horizontal)
#' and `inner_vgrid` (vertical) arguments are used.
#'
#' The function supports all standard spreadsheet border styles (e.g., "thin",
#' "thick", "double", "dotted"). If `update = TRUE`, the function attempts
#' to merge new border definitions with existing ones, preserving overlapping
#' styles where possible. Setting `update = NULL` acts as a reset, removing
#' all border styles from the specified `dims` and returning them to the
#' workbook default.
#'
#' For specialized needs, diagonal borders can be added using `diagonal_up`
#' and `diagonal_down`. Note that the OpenXML specification typically restricts
#' a cell to a single diagonal line style.
#'
#' @param wb A [wbWorkbook] object.
#' @param sheet The name or index of the worksheet to modify. Defaults to
#'   the current sheet.
#' @param dims A character string defining the cell range (e.g., "A1", "B2:G10").
#' @param top_border,bottom_border,left_border,right_border The border style
#'   for the exterior edges of the range.
#' @param top_color,bottom_color,left_color,right_color The colors for the
#'   exterior edges. Accepts [wb_color()] objects or hex codes.
#' @param inner_hgrid,inner_vgrid The border style for internal horizontal
#'   and vertical grid lines within a range.
#' @param inner_hcolor,inner_vcolor The colors for internal grid lines.
#' @param update Logical or `NULL`. If `TRUE`, updates existing borders.
#'   If `NULL`, removes borders. If `FALSE` (default), overwrites existing
#'   styles with the new definition.
#' @param diagonal_up,diagonal_down Character string for the diagonal line
#'   style (e.g., "thin").
#' @param diagonal_color A [wb_color()] object for the diagonal lines.
#' @param ... Additional arguments.
#'
#' @section Notes:
#' * The function internally partitions the `dims` range into nine zones
#'     (corners, edges, and core) to apply the correct combination of
#'     exterior and interior borders efficiently.
#' * Color and style arguments must be paired; if a style is `NULL`, any
#'     assigned color for that side will be ignored.
#' * All border styles are registered in the workbook's global style
#'     catalog to ensure XML consistency.
#'
#' @examples
#' wb <- wb_workbook()
#' wb <- wb_add_worksheet(wb, "S1")
#' wb <- wb_add_data(wb, "S1", mtcars)
#' wb <- wb_add_border(wb, 1, dims = "A1:K1",
#'  left_border = NULL, right_border = NULL,
#'  top_border = NULL, bottom_border = "double")
#' wb <- wb_add_border(wb, 1, dims = "A5",
#'  left_border = "dotted", right_border = "dotted",
#'  top_border = "hair", bottom_border = "thick")
#' wb <- wb_add_border(wb, 1, dims = "C2:C5")
#' wb <- wb_add_border(wb, 1, dims = "G2:H3")
#'
#' wb <- wb_add_border(wb, 1, dims = "G12:H13",
#'  left_color = wb_color(hex = "FF9400D3"), right_color = wb_color(hex = "FF4B0082"),
#'  top_color = wb_color(hex = "FF0000FF"), bottom_color = wb_color(hex = "FF00FF00"))
#' wb <- wb_add_border(wb, 1, dims = "A20:C23")
#' wb <- wb_add_border(wb, 1, dims = "B12:D14",
#'  left_color = wb_color(hex = "FFFFFF00"), right_color = wb_color(hex = "FFFF7F00"),
#'  bottom_color = wb_color(hex = "FFFF0000"))
#' wb <- wb_add_border(wb, 1, dims = "D28:E28")
#'
#' # With chaining
#'
#' wb <- wb_workbook()
#' wb$add_worksheet("S1")$add_data("S1", mtcars)
#' wb$add_border(1, dims = "A1:K1",
#'  left_border = NULL, right_border = NULL,
#'  top_border = NULL, bottom_border = "double")
#' wb$add_border(1, dims = "A5",
#'  left_border = "dotted", right_border = "dotted",
#'  top_border = "hair", bottom_border = "thick")
#' wb$add_border(1, dims = "C2:C5")
#' wb$add_border(1, dims = "G2:H3")
#' wb$add_border(1, dims = "G12:H13",
#'  left_color = wb_color(hex = "FF9400D3"), right_color = wb_color(hex = "FF4B0082"),
#'  top_color = wb_color(hex = "FF0000FF"), bottom_color = wb_color(hex = "FF00FF00"))
#' wb$add_border(1, dims = "A20:C23")
#' wb$add_border(1, dims = "B12:D14",
#'  left_color = wb_color(hex = "FFFFFF00"), right_color = wb_color(hex = "FFFF7F00"),
#'  bottom_color = wb_color(hex = "FFFF0000"))
#' wb$add_border(1, dims = "D28:E28")
#' # if (interactive()) wb$open()
#'
#' wb <- wb_workbook()
#' wb$add_worksheet("S1")$add_data("S1", mtcars)
#' wb$add_border(1, dims = "A2:K33", inner_vgrid = "thin",
#'  inner_vcolor = wb_color(hex = "FF808080"))
#'
#' wb$add_worksheet()$
#'   add_border(dims = "B2:D4", bottom_border = "thick", left_border = "thick",
#'     right_border = "thick", top_border = "thick")$
#'   add_border(dims = "C3:E5", update = TRUE)
#'
#' wb$add_worksheet()$
#'   add_border(
#'     dims = "B2:D4",
#'     diagonal_up = "thin",
#'     diagonal_down = "thin",
#'     diagonal_color = wb_color("red")
#'   )
#' @family styles
#' @seealso [create_border()]
#' @export
wb_add_border <- function(
    wb,
    sheet          = current_sheet(),
    dims           = "A1",
    bottom_color   = wb_color(hex = "FF000000"),
    left_color     = wb_color(hex = "FF000000"),
    right_color    = wb_color(hex = "FF000000"),
    top_color      = wb_color(hex = "FF000000"),
    bottom_border  = "thin",
    left_border    = "thin",
    right_border   = "thin",
    top_border     = "thin",
    inner_hgrid    = NULL,
    inner_hcolor   = NULL,
    inner_vgrid    = NULL,
    inner_vcolor   = NULL,
    update         = FALSE,
    diagonal_up    = NULL,
    diagonal_down  = NULL,
    diagonal_color = NULL,
    ...
) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$add_border(
    sheet          = sheet,
    dims           = dims,
    bottom_color   = bottom_color,
    left_color     = left_color,
    right_color    = right_color,
    top_color      = top_color,
    bottom_border  = bottom_border,
    left_border    = left_border,
    right_border   = right_border,
    top_border     = top_border,
    inner_hgrid    = inner_hgrid,
    inner_hcolor   = inner_hcolor,
    inner_vgrid    = inner_vgrid,
    inner_vcolor   = inner_vcolor,
    update         = update,
    diagonal_up    = diagonal_up,
    diagonal_down  = diagonal_down,
    diagonal_color = diagonal_color,
    ...            = ...
  )

}

#' Modify the background fill color in a cell region
#'
#' @description
#' The `wb_add_fill()` function applies background colors, patterns, or gradients
#' to a specified cell region. It allows for high-precision styling, ranging
#' from simple solid fills to complex geometric patterns and linear or path-based
#' gradients compliant with the OpenXML specification.
#'
#' @details
#' Background fills in spreadsheet software consist of a pattern type (the most
#' common being "solid") and a foreground color. If a non-solid pattern is chosen
#' (e.g., "darkVertical"), an optional `bg_color` can be specified to create a
#' two-tone effect.
#'
#' The function also includes built-in logic for "nth" selection, which is
#' particularly useful for manual "zebra-striping" or creating grid-like
#' visual patterns without needing to manually construct a complex vector of
#' cell addresses.
#'
#' Gradients:
#' For advanced visual effects, `gradient_fill` accepts raw XML strings defining
#' `<gradientFill>` nodes. These can specify `degree` (for linear gradients)
#' or `type="path"` (for radial-style gradients) along with multiple color
#' stops.
#'
#' Style Removal:
#' Setting `color = NULL` removes the fill style from the specified region,
#' reverting the cells to the workbook's default transparent background.
#'
#' @param wb A [wbWorkbook] object.
#' @param sheet The name or index of the worksheet. Defaults to the current sheet.
#' @param dims A character string defining the cell range (e.g., "A1:D10").
#' @param color A [wb_color()] object or hex string representing the primary fill
#'   (foreground) color. Defaults to yellow ("FFFFFF00").
#' @param pattern Character; the pattern type. Common values include "solid",
#'   "mediumGray", "lightGray", "darkGrid", and "lightTrellis". Defaults to "solid".
#' @param gradient_fill An optional XML string defining a gradient fill pattern.
#'   If provided, this overrides `color` and `pattern`.
#' @param every_nth_col,every_nth_row Numeric; applies the fill only to every
#'   $n$-th column or row within the specified `dims`. Useful for banding.
#' @param bg_color An optional [wb_color()] for the background of a patterned fill.
#' @param ... Additional arguments.
#'
#' @return The [wbWorkbook] object, invisibly.
#'
#' @examples
#' wb <- wb_workbook()
#' wb <- wb_add_worksheet(wb, "S1")
#' wb <- wb_add_data(wb, "S1", mtcars)
#' wb <- wb_add_fill(wb, "S1", dims = "D5:J23", color = wb_color(hex = "FFFFFF00"))
#' wb <- wb_add_fill(wb, "S1", dims = "B22:D27", color = wb_color(hex = "FF00FF00"))
#'
#' wb <- wb_add_worksheet(wb, "S2")
#' wb <- wb_add_data(wb, "S2", mtcars)
#'
#' gradient_fill1 <- '<gradientFill degree="90">
#' <stop position="0"><color rgb="FF92D050"/></stop>
#' <stop position="1"><color rgb="FF0070C0"/></stop>
#' </gradientFill>'
#' wb <- wb_add_fill(wb, "S2", dims = "A2:K5", gradient_fill = gradient_fill1)
#'
#' gradient_fill2 <- '<gradientFill type="path" left="0.2" right="0.8" top="0.2" bottom="0.8">
#' <stop position="0"><color theme="0"/></stop>
#' <stop position="1"><color theme="4"/></stop>
#' </gradientFill>'
#' wb <- wb_add_fill(wb, "S2", dims = "A7:K10", gradient_fill = gradient_fill2)
#' @return The `wbWorkbook` object, invisibly
#' @family styles
#' @export
wb_add_fill <- function(
    wb,
    sheet         = current_sheet(),
    dims          = "A1",
    color         = wb_color(hex = "FFFFFF00"),
    pattern       = "solid",
    gradient_fill = "",
    every_nth_col = 1,
    every_nth_row = 1,
    bg_color      = NULL,
    ...
) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$add_fill(
    sheet         = sheet,
    dims          = dims,
    color         = color,
    pattern       = pattern,
    gradient_fill = gradient_fill,
    every_nth_col = every_nth_col,
    every_nth_row = every_nth_row,
    bg_color      = bg_color,
    ...           = ...
  )
}

#' Modify font properties in a cell region
#'
#' @description
#' The `wb_add_font()` function provides granular control over the visual
#' appearance of text within a specified cell region. While other styling
#' functions include basic font options, `wb_add_font()` exposes the full range
#' of font attributes supported by the OpenXML specification, allowing for
#' precise adjustments to typeface, sizing, color, and emphasis.
#'
#' @details
#' This function operates on the font node of a cell's style. It is particularly
#' powerful when used with the `update` argument, which allows users to modify
#' specific attributes (like color) while preserving other existing font properties
#' (like bold or font name).
#'
#' For common tasks, adjusting `name`, `size`, and `color` is sufficient.
#' However, the function also supports advanced properties like `vert_align`
#' (for subscripts/superscripts), `family` (font categories), and `scheme`
#' (theme-based font sets).
#'
#' Note on Updates:
#' * If `update = FALSE` (default), the function applies the new font
#'   definition as a complete replacement for the existing font style.
#' * If `update` is a character vector (e.g., `c("color", "size")`), only those
#'   specific attributes are modified, and all other existing font properties
#'   are retained.
#' * Setting `update = NULL` removes the custom font style entirely, reverting
#'   the cells to the workbook's default font.
#'
#' @param wb A [wbWorkbook] object.
#' @param sheet The name or index of the worksheet. Defaults to the current sheet.
#' @param dims A character string defining the cell range (e.g., "A1:K1").
#' @param name Character; the font name. Defaults to "Aptos Narrow".
#' @param color A [wb_color()] object or hex string defining the font color.
#'   Defaults to black ("FF000000").
#' @param size Numeric; the font size. Defaults to 11.
#' @param bold Logical; applies bold formatting if `TRUE`.
#' @param italic Logical; applies italic formatting if `TRUE`.
#' @param outline Logical; applies an outline effect to the text.
#' @param strike Logical; applies a strikethrough effect.
#' @param underline Character; the underline style, such as "single" or "double".
#' @param family Character; the font family index (e.g., "1" for Roman, "2" for Swiss).
#' @param charset Character; the character set ID. See [fmt_txt()] for details.
#' @param condense Logical; whether the font should be condensed.
#' @param scheme Character; the font scheme. One of "minor", "major", or "none".
#' @param shadow Logical; applies a shadow effect to the text.
#' @param extend Logical; whether the font should be extended.
#' @param vert_align Character; vertical alignment. Options are "baseline",
#'   "superscript", or "subscript".
#' @param update Logical or character vector. Controls whether to overwrite
#'   the entire font style or only update specific properties.
#' @param ... Additional arguments.
#'
#' @return The [wbWorkbook] object, invisibly.
#'
#' @section Notes:
#' * This function modifies the cell-level style and does not alter rich text
#'     strings created with [fmt_txt()].
#' * Font styles are pooled in the workbook's style manager to ensure efficiency
#'     and XML compliance.
#'
#' @examples
#'  wb <- wb_workbook()
#'  wb <- wb_add_worksheet(wb, "S1")
#'  wb <- wb_add_data(wb, "S1", mtcars)
#'  wb <- wb_add_font(wb, "S1", "A1:K1", name = "Arial", color = wb_color(theme = "4"))
#' # With chaining
#'  wb <- wb_workbook()$add_worksheet("S1")$add_data("S1", mtcars)
#'  wb$add_font("S1", "A1:K1", name = "Arial", color = wb_color(theme = "4"))
#'
#' # Update the font color
#'  wb$add_font("S1", "A1:K1", color = wb_color("orange"), update = c("color"))
#' @return A `wbWorkbook`, invisibly
#' @family styles
#' @export
wb_add_font <- function(
      wb,
      sheet      = current_sheet(),
      dims       = "A1",
      name       = "Aptos Narrow",
      color      = wb_color(hex = "FF000000"),
      size       = "11",
      bold       = "",
      italic     = "",
      outline    = "",
      strike     = "",
      underline  = "",
      # fine tuning
      charset    = "",
      condense   = "",
      extend     = "",
      family     = "",
      scheme     = "",
      shadow     = "",
      vert_align = "",
      update     = FALSE,
      ...
) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$add_font(
    sheet      = sheet,
    dims       = dims,
    name       = name,
    color      = color,
    size       = size,
    bold       = bold,
    italic     = italic,
    outline    = outline,
    strike     = strike,
    underline  = underline,
    # fine tuning
    charset    = charset,
    condense   = condense,
    extend     = extend,
    family     = family,
    scheme     = scheme,
    shadow     = shadow,
    vert_align = vert_align,
    update     = update,
    ...        = ...
  )
}

#' Modify number formatting in a cell region
#'
#' Add number formatting to a cell region. You can use a number format created
#' by [create_numfmt()].
#' Setting `numfmt` to `NULL` removes the style and resets the cell to the workbook default.
#'
#' @param wb A Workbook
#' @param sheet the worksheet
#' @param dims the cell range
#' @param numfmt either an integer id for a builtin numeric font or a character string as described in the ***Details***
#'
#' @details
#' The list of number formats ID is located in the **Details** section of [create_cell_style()].
#'
#' ### General Number Formatting
#' - `"0"`: Displays numbers as integers without decimal places.
#' - `"0.00"`: Displays numbers with two decimal places (e.g., `123.45`).
#' - `"#,##0"`: Displays thousands separators without decimals (e.g., `1,000`).
#' - `"#,##0.00"`: Displays thousands separators with two decimal places (e.g., `1,000.00`).
#'
#' ### Currency Formatting
#' - `"$#,##0.00"`: Formats numbers as currency with two decimal places (e.g., `$1,000.00`).
#' - `"[$$-409]#,##0.00"`: Localized currency format in U.S. dollars.
#' - `"¥#,##0"`: Custom currency format (e.g., for Japanese yen) without decimals.
#' - `"£#,##0.00"`: GBP currency format with two decimal places.
#'
#' ### Percentage Formatting
#' - `"0%"`: Displays numbers as percentages with no decimal places (e.g., `50%`).
#' - `"0.00%"`: Displays numbers as percentages with two decimal places (e.g., `50.00%`).
#'
#' ### Scientific Formatting
#' - `"0.00E+00"`: Scientific notation with two decimal places (e.g., `1.23E+03` for `1230`).
#'
#' ### Date and Time Formatting
#' - `"yyyy-mm-dd"`: Year-month-day format (e.g., `2023-10-31`).
#' - `"dd/mm/yyyy"`: Day/month/year format (e.g., `31/10/2023`).
#' - `"mmm d, yyyy"`: Month abbreviation with day and year (e.g., `Oct 31, 2023`).
#' - `"h:mm AM/PM"`: Time with AM/PM format (e.g., `1:30 PM`).
#' - `"h:mm:ss"`: Time with seconds (e.g., `13:30:15` for `1:30:15 PM`).
#' - `"yyyy-mm-dd h:mm:ss"`: Full date and time format.
#'
#' ### Fraction Formatting
#' - `"# ?/?"`: Displays numbers as a fraction with a single digit denominator (e.g., `1/2`).
#' - `"# ??/??"`: Displays numbers as a fraction with a two-digit denominator (e.g., `1 12/25`).
#'
#' ### Custom Formatting
#' - `"_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)`:
#'   Custom currency format with parentheses for negative values and dashes for zero values.
#' - `"[Red]0.00;[Blue](0.00);0"`: Displays positive numbers in red, negatives in blue, and zeroes as plain.
#' - `"@"`: Text placeholder format (e.g., for cells with mixed text and numeric values).
#'
#' ### Formatting Symbols Reference
#' - `0`: Digit placeholder, displays a digit or zero.
#' - `#`: Digit placeholder, does not display extra zeroes.
#' - `.`: Decimal point.
#' - `,`: Thousands separator.
#' - `E+`, `E-`: Scientific notation.
#' - `_` (underscore): Adds a space equal to the width of the next character.
#' - `"text"`: Displays literal text within quotes.
#' - `*`: Repeat character to fill the cell width.
#'
#' @examples
#' wb <- wb_workbook()
#' wb <- wb_add_worksheet(wb, "S1")
#' wb <- wb_add_data(wb, "S1", mtcars)
#' wb <- wb_add_numfmt(wb, "S1", dims = "F1:F33", numfmt = "#.0")
#' # Chaining
#' wb <- wb_workbook()$add_worksheet("S1")$add_data("S1", mtcars)
#' wb$add_numfmt("S1", "A1:A33", numfmt = 1)
#' @return The `wbWorkbook` object, invisibly.
#' @family styles
#' @export

wb_add_numfmt <- function(
    wb,
    sheet = current_sheet(),
    dims  = "A1",
    numfmt
) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$add_numfmt(
    sheet  = sheet,
    dims   = dims,
    numfmt = numfmt
  )
}

#' Modify the style in a cell region
#'
#' @description
#' The `wb_add_cell_style()` function provides direct access to the cell-level
#' formatting record (the `xf` node) within a `wbWorkbook`. It is primarily
#' used to control text alignment (horizontal and vertical), text rotation,
#' indentation, and cell protection (locking and hiding).
#'
#' @details
#' While functions like [wb_add_font()] or [wb_add_fill()] target specific
#' sub-nodes of a style, `wb_add_cell_style()` manages the properties that
#' govern how content is positioned within the cell boundaries and how it
#' behaves when a worksheet is protected.
#'
#' This function also allows for the direct assignment of style element IDs
#' (e.g., `font_id`, `fill_id`). This is an advanced feature that allows
#' users to map pre-existing styles in the workbook's style catalog to specific
#' cells.
#'
#' Alignment and Text Control:
#' Options such as `wrap_text`, `shrink_to_fit`, and `text_rotation` are
#' essential for managing high-density data or creating stylized headers.
#' The `text_rotation` parameter accepts values in degrees (0–180), where
#' values above 90 represent downward-slanting text.
#'
#' Protection:
#' The `locked` and `hidden` parameters only take effect when worksheet
#' protection is enabled (see [wb_protect_worksheet()]). By default, all
#' cells in a spreadsheet are "locked," but this has no impact until the
#' sheet is protected.
#'
#' @param wb A [wbWorkbook] object.
#' @param sheet The name or index of the worksheet. Defaults to the current sheet.
#' @param dims A character string defining the cell range (e.g., "A1:K1").
#' @param horizontal Horizontal alignment. One of "general", "left", "center",
#'   "right", "fill", "justify", "centerContinuous", or "distributed".
#' @param vertical Vertical alignment. One of "top", "center", "bottom",
#'   "justify", or "distributed".
#' @param text_rotation Degrees of rotation (0 to 180).
#' @param wrap_text Logical; enables line wrapping within the cell.
#' @param shrink_to_fit Logical; automatically reduces font size to fit the
#'   column width.
#' @param indent Numeric; the indentation level for the cell content.
#' @param locked Logical; if `TRUE`, the cell cannot be edited when the
#'   sheet is protected.
#' @param hidden Logical; if `TRUE`, formulas are hidden when the sheet
#'   is protected.
#' @param ext_lst Character; an optional XML string containing an extension list (`<extLst>`)
#'   for the cell style.
#' @param justify_last_line Logical; if `TRUE`, justifies the last line of text
#'   within the cell (useful for distributed alignment).
#' @param pivot_button Logical; indicates if a pivot button should be displayed for the cell.
#' @param quote_prefix Logical; if `TRUE`, a single quote prefix is displayed in the
#'   formula bar but not the cell itself (often used for numbers stored as text).
#' @param reading_order Integer; the reading order for the cell content (e.g.,
#'   1 for Left-to-Right, 2 for Right-to-Left).
#' @param relative_indent Integer; the relative indentation level.
#' @param xf_id Integer; a direct reference to a master style (XF) ID in the style catalog.
#' @param font_id,fill_id,border_id,num_fmt_id Optional; direct integer IDs
#'   referencing existing style sub-nodes.
#' @param apply_alignment,apply_font,apply_fill,apply_border,apply_number_format,apply_protection
#'   Logical; explicitly flags whether the spreadsheet software should
#'   apply the corresponding style category.
#' @param ... Additional arguments.
#'
#' @return The [wbWorkbook] object, invisibly.
#'
#' @examples
#' wb <- wb_workbook()
#' wb <- wb_add_worksheet(wb, "S1")
#' wb <- wb_add_data(wb, "S1", x = mtcars)
#'
#' wb <- wb_add_cell_style(
#'     wb,
#'     dims = "A1:K1",
#'     text_rotation = "45",
#'     horizontal = "center",
#'     vertical = "center",
#'     wrap_text = "1"
#' )
#' # Chaining
#' wb <- wb_workbook()$add_worksheet("S1")$add_data(x = mtcars)
#' wb$add_cell_style(dims = "A1:K1",
#'                   text_rotation = "45",
#'                   horizontal = "center",
#'                   vertical = "center",
#'                   wrap_text = "1")
#' @return The `wbWorkbook` object, invisibly
#' @family styles
#' @export
wb_add_cell_style <- function(
    wb,
    sheet               = current_sheet(),
    dims                = "A1",
    apply_alignment     = NULL,
    apply_border        = NULL,
    apply_fill          = NULL,
    apply_font          = NULL,
    apply_number_format = NULL,
    apply_protection    = NULL,
    border_id           = NULL,
    ext_lst             = NULL,
    fill_id             = NULL,
    font_id             = NULL,
    hidden              = NULL,
    horizontal          = NULL,
    indent              = NULL,
    justify_last_line   = NULL,
    locked              = NULL,
    num_fmt_id          = NULL,
    pivot_button        = NULL,
    quote_prefix        = NULL,
    reading_order       = NULL,
    relative_indent     = NULL,
    shrink_to_fit       = NULL,
    text_rotation       = NULL,
    vertical            = NULL,
    wrap_text           = NULL,
    xf_id               = NULL,
    ...
) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$add_cell_style(
    sheet               = sheet,
    dims                = dims,
    apply_alignment     = apply_alignment,
    apply_border        = apply_border,
    apply_fill          = apply_fill,
    apply_font          = apply_font,
    apply_number_format = apply_number_format,
    apply_protection    = apply_protection,
    border_id           = border_id,
    ext_lst             = ext_lst,
    fill_id             = fill_id,
    font_id             = font_id,
    hidden              = hidden,
    horizontal          = horizontal,
    indent              = indent,
    justify_last_line   = justify_last_line,
    locked              = locked,
    num_fmt_id          = num_fmt_id,
    pivot_button        = pivot_button,
    quote_prefix        = quote_prefix,
    reading_order       = reading_order,
    relative_indent     = relative_indent,
    shrink_to_fit       = shrink_to_fit,
    text_rotation       = text_rotation,
    vertical            = vertical,
    wrap_text           = wrap_text,
    xf_id               = xf_id,
    ...                 = ...
  )
}

#' Apply styling to a cell region with a named style
#'
#' Set the styling to a named style for a cell region. Use [wb_add_cell_style()]
#' to style a cell region with custom parameters.
#' A named style is the one in spreadsheet software, like "Normal", "Warning".
#' @param wb A `wbWorkbook` object
#' @param sheet A worksheet
#' @param dims A cell range
#' @param name The named style name. Builtin styles are `Normal`, `Bad`, `Good`, `Neutral`, `Calculation`, `Check Cell`, `Explanatory Text`,  `Input`, `Linked Cell`, `Note`, `Output`, `Warning Text`, `Heading 1`, `Heading 2`, `Heading 3`, `Heading 4`, `Title`, `Total`, `$x% - Accent$y` (for x in 20, 40, 60 and y in 1:6), `Accent$y` (for y in 1:6), `Comma`, `Comma [0]`, `Currency`, `Currency [0]`, `Per cent`
#' @family styles
#' @param font_name,font_size optional else the default of the theme
#' @return The `wbWorkbook`, invisibly
#' @examples
#' wb <- wb_workbook()$add_worksheet()
#' name <- "Normal"
#' dims <- "A1"
#' wb$add_data(dims = dims, x = name)
#'
#' name <- "Bad"
#' dims <- "B1"
#' wb$add_named_style(dims = dims, name = name)
#' wb$add_data(dims = dims, x = name)
#'
#' name <- "Good"
#' dims <- "C1"
#' wb$add_named_style(dims = dims, name = name)
#' wb$add_data(dims = dims, x = name)
#' @export
wb_add_named_style <- function(
    wb,
    sheet = current_sheet(),
    dims = "A1",
    name = "Normal",
    font_name = NULL,
    font_size = NULL
) {
  assert_workbook(wb)
  assert_class(name, "character")
  wb$clone(deep = TRUE)$add_named_style(
    sheet = sheet,
    dims = dims,
    name = name,
    font_name = font_name,
    font_size = font_size
  )
}

#' Set a dxfs style for the workbook
#'
#' @description
#' The `wb_add_dxfs_style()` function defines a "Differential Formatting" (DXF)
#' style within a `wbWorkbook`. Unlike standard styles (XFs), which are assigned
#' directly to cells, DXF styles are used as templates for dynamic formatting
#' features such as conditional formatting rules and custom table styles.
#'
#' @details
#' DXF styles are differential because they usually only define a subset of cell
#' properties (e.g., just the font color or a background fill). When a conditional
#' formatting rule is triggered, the properties defined in the DXF style are
#' layered on top of the cell's existing base style.
#'
#' This function acts as a wrapper around [create_dxfs_style()], allowing you
#' to bundle font, border, fill, and number format attributes into a named
#' style that can be referenced later by its `name`.
#'
#' @param wb A [wbWorkbook] object.
#' @param name A unique character string to identify the DXF style.
#' @param font_name Character; the font name.
#' @param font_size Numeric; the font size.
#' @param font_color A [wb_color()] object for the font.
#' @param num_fmt Cell formatting. Previously this was a format code. To be backwards compatible, this still allows for a code
#' @param format_code A custom format code
#' @param border Logical; if `TRUE`, applies borders to the style.
#' @param border_color A [wb_color()] object for the borders.
#' @param border_style Character; the border style (e.g., "thin", "thick").
#'   Defaults to the "openxlsx2.borderStyle" option.
#' @param bg_fill A [wb_color()] object for the background fill.
#' @param gradient_fill An optional XML string for a gradient fill pattern.
#' @param text_bold Logical; if `TRUE`, applies bold formatting.
#' @param text_italic Logical; if `TRUE`, applies italic formatting.
#' @param text_underline Logical; if `TRUE`, applies underline formatting.
#' @param ... Additional arguments passed to [create_dxfs_style()].
#'
#' @return The [wbWorkbook] object, invisibly.
#'
#' @examples
#' wb <- wb_workbook()
#' wb <- wb_add_worksheet(wb)
#' wb <- wb_add_dxfs_style(
#'    wb,
#'    name = "nay",
#'    font_color = wb_color(hex = "FF9C0006"),
#'    bg_fill = wb_color(hex = "FFFFC7CE")
#'   )
#' @family workbook styling functions
#' @export
wb_add_dxfs_style <- function(
  wb,
  name,
  font_name      = NULL,
  font_size      = NULL,
  font_color     = NULL,
  num_fmt        = NULL,
  format_code    = NULL,
  border         = NULL,
  border_color   = wb_color(getOption("openxlsx2.borderColor", "black")),
  border_style   = getOption("openxlsx2.borderStyle", "thin"),
  bg_fill        = NULL,
  gradient_fill  = NULL,
  text_bold      = NULL,
  text_italic    = NULL,
  text_underline = NULL,
  ...
) {

  if (missing(name)) name <- substitute()

  assert_workbook(wb)
  wb$clone(deep = TRUE)$add_dxfs_style(
    name           = name,
    font_name      = font_name,
    font_size      = font_size,
    font_color     = font_color,
    num_fmt        = num_fmt,
    format_code    = format_code,
    border         = border,
    border_color   = border_color,
    border_style   = border_style,
    bg_fill        = bg_fill,
    gradient_fill  = gradient_fill,
    text_bold      = text_bold,
    text_italic    = text_italic,
    text_underline = text_underline,
    ...            = ...
  )

}

#' Add comment to worksheet
#'
#' @details
#' If applying a `comment` with a string, it will use [wb_comment()] default values. If additional background colors are applied, RGB colors should be provided, either as hex code or with builtin R colors. The alpha channel is ignored.
#'
#' @param wb A workbook object
#' @param sheet A worksheet of the workbook
#' @param dims Optional row and column as spreadsheet dimension, e.g. "A1"
#' @param comment A comment to apply to `dims` created by [wb_comment()], a string or a [fmt_txt()] object
#' @param ... additional arguments
#' @returns The Workbook object, invisibly.
#' @seealso [wb_comment()], [wb_add_thread()]
#' @keywords comments
#' @examples
#' wb <- wb_workbook()
#' wb$add_worksheet("Sheet 1")
#' # add a comment without author
#' c1 <- wb_comment(text = "this is a comment", author = "")
#' wb$add_comment(dims = "B10", comment = c1)
#' #' # Remove comment
#' wb$remove_comment(sheet = "Sheet 1", dims = "B10")
#' # Write another comment with author information
#' c2 <- wb_comment(text = "this is another comment", author = "Marco Polo", visible = TRUE)
#' wb$add_comment(sheet = 1, dims = "C10", comment = c2)
#' # Works with formatted text also.
#' formatted_text <- fmt_txt("bar", underline = TRUE)
#' wb$add_comment(dims = "B5", comment = formatted_text)
#' # With background color
#' wb$add_comment(dims = "B7", comment = formatted_text, color = wb_color("green"))
#' # With background image. File extension must be png or jpeg, not jpg?
#' tmp <- tempfile(fileext = ".png")
#' png(file = tmp, bg = "transparent")
#' plot(1:10)
#' rect(1, 5, 3, 7, col = "white")
#' dev.off()
#'
#' c1 <- wb_comment(text = "this is a comment", author = "", visible = TRUE)
#' wb$add_comment(dims = "B12", comment = c1, file = tmp)
#' @export
wb_add_comment <- function(
    wb,
    sheet   = current_sheet(),
    dims    = "A1",
    comment,
    ...
  ) {

  assert_workbook(wb)

  if (is.character(comment)) {
    comment <- wb_comment(text = comment, author = getOption("openxlsx2.creator"))
  }

  assert_comment(comment)

  wb$clone(deep = TRUE)$add_comment(
    sheet   = sheet,
    dims    = dims,
    comment = comment,
    ...     = ...
  )
}

#' @rdname wb_add_comment
#' @export
wb_get_comment <- function(
    wb,
    sheet = current_sheet(),
    dims  = NULL
) {

  assert_workbook(wb)

  wb$clone(deep = TRUE)$get_comment(
    sheet       = sheet,
    dims        = dims
  )
}

#' @rdname wb_add_comment
#' @export
wb_remove_comment <- function(
    wb,
    sheet      = current_sheet(),
    dims       = "A1",
    ...
  ) {

  assert_workbook(wb)

  wb$clone(deep = TRUE)$remove_comment(
    sheet       = sheet,
    dims        = dims,
    ...         = ...
  )
}

#' Helper for adding threaded comments
#'
#' Adds a person to a workbook, so that they can be the author of threaded
#' comments in a workbook with [wb_add_thread()]
#'
#' @name person-wb
#' @param wb a Workbook
#' @param name the name of the person to display.
#' @param id (optional) the display id
#' @param user_id (optional) the user id
#' @param provider_id (optional) the provider id
#' @seealso [wb_add_thread()]
#' @keywords comments
#' @export
wb_add_person <- function(
    wb,
    name        = NULL,
    id          = NULL,
    user_id     = NULL,
    provider_id = "None"
) {
  assert_workbook(wb)
  wb$clone()$add_person(
    name        = name,
    id          = id,
    user_id     = user_id,
    provider_id = provider_id
  )
}

#' @rdname person-wb
#' @export
wb_get_person <- function(wb, name = NULL) {
  assert_workbook(wb)
  wb$get_person(name)
}

#' Add threaded comments to a cell in a worksheet
#'
#' These functions allow adding thread comments to spreadsheets.
#' This is not yet supported by all spreadsheet software. A threaded comment must
#' be tied to a person created by [wb_add_person()].
#'
#' If a threaded comment is added, it needs a person attached to it.
#' The default is to create a person with provider id `"None"`.
#' Other providers are possible with specific values for `id` and `user_id`.
#' If you require the following, create a workbook via spreadsheet software load
#' it and get the values with [wb_get_person()]
#'
#' @param wb A workbook
#' @param sheet A worksheet
#' @param dims A cell
#' @param comment The text to add, a character vector.
#' @param person_id the person Id this should be added. The default is
#'   `getOption("openxlsx2.thread_id")` if set.
#' @param reply Is the comment a reply? (default `FALSE`)
#' @param resolve Should the comment be resolved? (default `FALSE`)
#' @seealso [wb_add_comment()] [`person-wb`]
#' @family worksheet content functions
#' @examples
#' wb <- wb_workbook()$add_worksheet()
#' # Add a person to the workbook.
#' wb$add_person(name = "someone who likes to edit workbooks")
#'
#' pid <- wb$get_person(name = "someone who likes to edit workbooks")$id
#'
#' # write a comment to a thread, reply to one and solve some
#' wb <- wb_add_thread(wb, dims = "A1", comment = "wow it works!", person_id = pid)
#' wb <- wb_add_thread(wb, dims = "A2", comment = "indeed", person_id = pid, resolve = TRUE)
#' wb <- wb_add_thread(wb, dims = "A1", comment = "so cool", person_id = pid, reply = TRUE)
#' @export
wb_add_thread <- function(
    wb,
    sheet     = current_sheet(),
    dims      = "A1",
    comment   = NULL,
    person_id,
    reply     = FALSE,
    resolve   = FALSE
) {
  # wb_add_thread now uses wb_comment internally. No change detected.
  # means that add_thread does not look at visibility. (I think it's fine.)
  if (missing(person_id)) {
    person_id <- substitute()
  }

  assert_workbook(wb)
  wb$clone(deep = TRUE)$add_thread(
    sheet     = sheet,
    dims      = dims,
    comment   = comment,
    person_id = person_id,
    reply     = reply,
    resolve   = resolve
  )
}

#' @rdname wb_add_thread
#' @export
wb_get_thread <- function(
    wb,
    sheet = current_sheet(),
    dims  = NULL
) {

  assert_workbook(wb)

  wb$clone(deep = TRUE)$get_thread(
    sheet       = sheet,
    dims        = dims
  )
}

#' Add a checkbox, radio button or drop menu to a cell in a worksheet
#'
#' You can add Form Control to a cell. The three supported types are a Checkbox,
#' a Radio button, or a Drop menu.
#'
#' @param wb A Workbook object
#' @param sheet A worksheet of the workbook
#' @param dims A single cell as spreadsheet dimension, e.g. "A1".
#' @param type A type "Checkbox" (the default), "Radio" a radio button or "Drop" a drop down menu
#' @param text A text to be shown next to the Checkbox or radio button (optional)
#' @param link A cell range to link to
#' @param range A cell range used as input
#' @param checked A logical indicating if the Checkbox or Radio button is checked
#' @returns The `wbWorkbook` object, invisibly.
#' @examples
#' wb <- wb_workbook()
#' wb <- wb_add_worksheet(wb)
#' wb <- wb_add_form_control(wb)
#' # Add
#' wb$add_form_control(dims = "C5", type = "Radio", checked = TRUE)
#' @export
wb_add_form_control <- function(
    wb,
    sheet   = current_sheet(),
    dims    = "A1",
    type    = c("Checkbox", "Radio", "Drop"),
    text    = NULL,
    link    = NULL,
    range   = NULL,
    checked = FALSE
) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$add_form_control(
      sheet   = sheet,
      dims    = dims,
      type    = type,
      text    = text,
      link    = link,
      range   = range,
      checked = checked
  )

}

#' Add conditional formatting to cells in a worksheet
#'
#' Add conditional formatting to cells.
#' You can find more details in `vignette("conditional-formatting")`.
#'
#' openxml uses the alpha channel first then RGB, whereas the usual default is RGBA.
#' @param wb A Workbook object
#' @param sheet A name or index of a worksheet
#' @param dims A cell or cell range like "A1" or "A1:B2"
#' @param rule The condition under which to apply the formatting. See **Examples**.
#' @param style A name of a style to apply to those cells that satisfy the rule. See [wb_add_dxfs_style()] how to create one.
#'   The default style has `font_color = "FF9C0006"` and `bg_fill = "FFFFC7CE"`
#' @param type The type of conditional formatting rule to apply. One of `"expression"`, `"colorScale"` or others mentioned in **Details**.
#' @param params A list of additional parameters passed.  See **Details** for more.
#' @param ... additional arguments
#' @family worksheet content functions
#' @details
#' Conditional formatting `type` accept different parameters. Unless noted,
#' unlisted parameters are ignored.
#' If an expression is pointing to a cell `"A1=1"`, this cell reference is fluid and not fixed like `"$A$1=1"`. It will behave similar to a formula, when `dims` is spanning multiple columns or rows (A1, A2, A3 ... in vertical direction, A1, B1, C1 ... in horizontal direction). If `dims` is a non consecutive range ("A1:B2,D1:F2"), the expression is applied to each range. For the second `dims` range it will be evaluated again as `"A1=1"`.
#' \describe{
#'   \item{`expression`}{
#'     `[style]`\cr A `Style` object\cr\cr
#'     `[rule]`\cr A formula expression (as a character). Valid operators are: `<`, `<=`, `>`, `>=`, `==`, `!=`
#'   }
#'   \item{`colorScale`}{
#'     `[style]`\cr A `character` vector of valid colors with length `2` or `3`\cr\cr
#'     `[rule]`\cr `NULL` or a `character` vector of valid colors of equal length to `styles`
#'   }
#'   \item{`dataBar`}{
#'     `[style]`\cr A `character` vector of valid colors with length `2` or `3`\cr\cr
#'     `[rule]`\cr A `numeric` vector specifying the range of the databar colors. Must be equal length to `style`\cr\cr
#'     `[params$showValue]`\cr If `FALSE` the cell value is hidden. Default `TRUE`\cr\cr
#'     `[params$gradient]`\cr If `FALSE` color gradient is removed. Default `TRUE`\cr\cr
#'     `[params$border]`\cr If `FALSE` the border around the database is hidden. Default `TRUE` \cr\cr
#'     `[params$direction]`\cr A `string` the direction in which the databar points. Must be equal to one of the following values: `"context"` (default), `"leftToRight"`, `"rightToLeft"`. \cr\cr
#'     `[params${axisColor,borderColor,negativeBarColorSameAsPositive,negativeBarBorderColorSameAsPositive}]` Colors and bools configuring the style of the border.
#'     `[params$axisPosition]`\cr A `string` specifying the data bar's axis position. Must be equal to one of the following values: `"automatic"` (default, variable position based on negative values), `"middle"` (cell midpoint), `"none"` (negative bars shown in same direction as positive bars). \cr\cr
#'   }
#'   \item{`duplicatedValues` / `uniqueValues` / `containsErrors`}{
#'     `[style]`\cr A `Style` object
#'   }
#'   \item{`contains`}{
#'     `[style]`\cr A `Style` object\cr\cr
#'     `[rule]`\cr The text to look for within cells
#'   }
#'   \item{`between`}{
#'     `[style]`\cr A `Style` object.\cr\cr
#'     `[rule]`\cr A `numeric` vector of length `2` specifying lower and upper bound (Inclusive)
#'   }
#'   \item{`topN`}{
#'     `[style]`\cr A `Style` object\cr\cr
#'     `[params$rank]`\cr A `numeric` vector of length `1` indicating number of highest values. Default `5L`\cr\cr
#'     `[params$percent]` If `TRUE`, uses percentage
#'   }
#'   \item{`bottomN`}{
#'     `[style]`\cr A `Style` object\cr\cr
#'     `[params$rank]`\cr A `numeric` vector of length `1` indicating number of lowest values. Default `5L`\cr\cr
#'     `[params$percent]`\cr If `TRUE`, uses percentage
#'   }
#'  \item{`iconSet`}{
#'     `[params$showValue]`\cr If `FALSE`, the cell value is hidden. Default `TRUE`\cr\cr
#'     `[params$reverse]`\cr If `TRUE`, the order is reversed. Default `FALSE`\cr\cr
#'     `[params$percent]`\cr If `TRUE`, uses percentage\cr\cr
#'     `[params$iconSet]`\cr Uses one of the implemented icon sets. Values must match the length of the icons
#'      in the set 3Arrows, 3ArrowsGray, 3Flags, 3Signs, 3Stars, 3Symbols, 3Symbols2, 3TrafficLights1, 3TrafficLights2,
#'      3Triangles, 4Arrows, 4ArrowsGray, 4Rating, 4RedToBlack, 4TrafficLights, 5Arrows, 5ArrowsGray, 5Boxes, 5Quarters, 5Rating.
#'      The default is 3TrafficLights1.
#'  }
#' }
#'
#' @examples
#' wb <- wb_workbook()
#' wb$add_worksheet("a")
#' wb$add_data(x = 1:4, col_names = FALSE)
#' wb$add_conditional_formatting(dims = wb_dims(cols = "A", rows = 1:4), rule = ">2")
#' @export
wb_add_conditional_formatting <- function(
    wb,
    sheet  = current_sheet(),
    dims   = NULL,
    rule   = NULL,
    style  = NULL,
    type   = c(
      "expression", "colorScale",
      "dataBar", "iconSet",
      "duplicatedValues", "uniqueValues",
      "containsErrors", "notContainsErrors",
      "containsBlanks", "notContainsBlanks",
      "containsText", "notContainsText",
      "beginsWith", "endsWith",
      "between", "topN", "bottomN"
    ),
    params = list(
      showValue = TRUE,
      gradient  = TRUE,
      border    = TRUE,
      percent   = FALSE,
      rank      = 5L,
      axisPosition = "automatic"
    ),
    ...
) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$add_conditional_formatting(
    sheet  = sheet,
    dims   = dims,
    rule   = rule,
    style  = style,
    type   = type,
    params = params,
    ...    = ...
  )
}

#' @rdname wb_add_conditional_formatting
#' @param first remove the first conditional formatting
#' @param last remove the last conditional formatting
#' @export
wb_remove_conditional_formatting <- function(
    wb,
    sheet  = current_sheet(),
    dims   = NULL,
    first  = FALSE,
    last   = FALSE
) {
  assert_workbook(wb)
  wb$clone()$remove_conditional_formatting(
    sheet  = sheet,
    dims   = dims,
    first  = first,
    last   = last
  )
}

#' Apply styling from a sheet to another within a workbook
#'
#' This function can be used to apply styling from a cell range, and apply it
#' to another cell range.
#' @param wb A workbook
#' @param from sheet we select the style from
#' @param to sheet to apply the style to
#' @export
wb_clone_sheet_style <- function(wb, from = current_sheet(), to) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$clone_sheet_style(from, to)
}

#' Add sparklines to a worksheet
#'
#' @description
#' `wb_add_sparklines()` takes the XML definitions created by [create_sparklines()]
#' and embeds them into the specified worksheet of a `wbWorkbook`.
#'
#' @param wb A [wbWorkbook] object.
#' @param sheet The name or index of the worksheet where the sparklines will
#'   be rendered. Defaults to the current sheet.
#' @param sparklines A character vector of sparkline XML strings generated
#'   by [create_sparklines()].
#'
#' @seealso [create_sparklines()]
#' @examples
#'  sl <- create_sparklines("Sheet 1", dims = "A3:K3", sqref = "L3")
#'  wb <- wb_workbook()
#'  wb <- wb_add_worksheet(wb)
#'  wb <- wb_add_data(wb, x = mtcars)
#'  wb <- wb_add_sparklines(wb, sparklines = sl)
#' @export
wb_add_sparklines <- function(wb, sheet = current_sheet(), sparklines) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$add_sparklines(sheet, sparklines)
}

#' Ignore error types on a worksheet
#'
#' @description
#' The `wb_add_ignore_error()` function allows you to suppress specific types of
#' background error checking warnings for a given cell range. This is useful for
#' preventing the display of green error indicators (triangles) in cases where
#' "errors" are intentional, such as numbers being stored as text for
#' formatting purposes.
#'
#' @details
#' Spreadsheet software performs background validation on formulas and data
#' entries. When a cell triggers a rule, a visual indicator appears. This
#' function modifies the `<ignoredErrors>` section of the worksheet XML to
#' whitelist specific ranges against specific rules.
#'
#' Most commonly, this is used with `number_stored_as_text = TRUE` when
#' IDs or codes (like "00123") must be preserved as character strings but
#' contain only numeric digits.
#'
#' @param wb A [wbWorkbook] object.
#' @param sheet The name or index of the worksheet. Defaults to the current sheet.
#' @param dims A character string defining the cell range (e.g., "A1:A100").
#' @param number_stored_as_text Logical; if `TRUE`, suppresses the error
#'   displayed when numeric values are stored as string/text types.
#' @param eval_error Logical; if `TRUE`, ignores errors resulting from
#'   formula evaluation (e.g., `#DIV/0!`, `#N/A`).
#' @param formula Logical; if `TRUE`, ignores formula consistency errors.
#' @param formula_range Logical; if `TRUE`, ignores errors where a formula
#'   omits cells in a region.
#' @param empty_cell_reference Logical; if `TRUE`, ignores errors when a
#'   formula refers to an empty cell.
#' @param list_data_validation Logical; if `TRUE`, ignores errors related to
#'   list data validation mapping.
#' @param two_digit_text_year Logical; if `TRUE`, ignores warnings about
#'   dates containing two-digit years.
#' @param unlocked_formula Logical; if `TRUE`, ignores errors for formulas
#'   in cells that are not locked.
#' @param calculated_column Logical; if `TRUE`, ignores errors in
#'   calculated columns of a table.
#' @param ... Additional arguments.
#'
#' @return The [wbWorkbook] object, invisibly.
#'
#' @section Notes:
#' * This function does not fix the underlying data; it only instructs the
#'     spreadsheet application not to flag the specific error type visually.
#' * If multiple error types need to be ignored for the same range, you can
#'     set multiple arguments to `TRUE` in a single call.
#'
#' @export
wb_add_ignore_error <- function(
    wb,
    sheet                 = current_sheet(),
    dims                  = "A1",
    calculated_column     = FALSE,
    empty_cell_reference  = FALSE,
    eval_error            = FALSE,
    formula               = FALSE,
    formula_range         = FALSE,
    list_data_validation  = FALSE,
    number_stored_as_text = FALSE,
    two_digit_text_year   = FALSE,
    unlocked_formula      = FALSE,
    ...
) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$add_ignore_error(
    sheet                 = sheet,
    dims                  = dims,
    calculated_column     = calculated_column,
    empty_cell_reference  = empty_cell_reference,
    eval_error            = eval_error,
    formula               = formula,
    formula_range         = formula_range,
    list_data_validation  = list_data_validation,
    number_stored_as_text = number_stored_as_text,
    two_digit_text_year   = two_digit_text_year,
    unlocked_formula      = unlocked_formula,
    ...                   = ...
    )
}

#' Modify the default view of a worksheet
#'
#' @description
#' The `wb_set_sheetview()` function controls the visual presentation of a
#' worksheet. It allows you to toggle UI elements like grid lines, row/column
#' headers, and formula visibility, as well as setting the zoom level and
#' view mode (e.g., Normal vs. Page Layout).
#'
#' @details
#' Sheet views are saved properties that tell the spreadsheet application
#' how to render the sheet upon opening. These settings are specific to the
#' worksheet and do not affect the actual data or styles of the cells.
#'
#' Common Use Cases:
#' * Zooming: Use `zoom_scale` to make large datasets more readable or
#'     to provide a high-level dashboard view.
#' * Clean Layouts: For reports or dashboards, setting `show_grid_lines = FALSE`
#'     and `show_row_col_headers = FALSE` creates a cleaner, application-like
#'     interface.
#' * Audit Mode: Setting `show_formulas = TRUE` is helpful for debugging
#'     complex spreadsheets by displaying the formulas directly in the cells.
#' * Right-to-Left: Essential for spreadsheets in languages like Arabic
#'     or Hebrew.
#'
#' @param wb A [wbWorkbook] object.
#' @param sheet The name or index of the worksheet. Defaults to the current sheet.
#' @param color_id,default_grid_color Integer; internal color index for grid
#'   lines. Default is 64 (automatic).
#' @param right_to_left Logical; if `TRUE`, column ordering is right-to-left.
#' @param show_formulas Logical; if `TRUE`, cells display their formulas
#'   instead of calculated values.
#' @param show_grid_lines Logical; if `TRUE` (default), the worksheet grid
#'   lines are visible.
#' @param show_outline_symbols Logical; if `TRUE`, shows symbols for grouped
#'   rows or columns.
#' @param show_row_col_headers Logical; if `TRUE`, shows the letters (columns)
#'   and numbers (rows) at the edges of the sheet.
#' @param show_ruler Logical; if `TRUE`, a ruler is shown in "Page Layout" view.
#' @param show_white_space Logical; if `TRUE`, margins and page gaps are shown
#'   in "Page Layout" view.
#' @param show_zeros Logical; if `FALSE`, cells containing a value of zero
#'   appear blank.
#' @param tab_selected Integer; a zero-based index indicating if this sheet
#'   tab is selected.
#' @param top_left_cell Character; the address of the cell that should be
#'   positioned in the top-left corner of the view (e.g., "B10").
#' @param view Character; the view mode. One of `"normal"`, `"pageBreakPreview"`,
#'   or `"pageLayout"`.
#' @param window_protection Logical; if `TRUE`, the panes within the sheet
#'   view are protected.
#' @param workbook_view_id Integer; links the sheet view to a specific
#'   global workbook view.
#' @param zoom_scale,zoom_scale_normal,zoom_scale_page_layout_view,zoom_scale_sheet_layout_view
#'   Integer; the zoom percentage (between 10 and 400).
#' @param ... Additional arguments.
#'
#' @return The [wbWorkbook] object, invisibly.
#'
#' @examples
#' wb <- wb_workbook()$add_worksheet()
#'
#' wb$set_sheetview(
#'   zoom_scale = 75,
#'   right_to_left = FALSE,
#'   show_formulas = TRUE,
#'   show_grid_lines = TRUE,
#'   show_outline_symbols = FALSE,
#'   show_row_col_headers = TRUE,
#'   show_ruler = TRUE,
#'   show_white_space = FALSE,
#'   tab_selected = 1,
#'   top_left_cell = "B1",
#'   view = "normal",
#'   window_protection = TRUE
#' )
#' @return The `wbWorkbook` object, invisibly
#' @export
wb_set_sheetview <- function(
    wb,
    sheet                        = current_sheet(),
    color_id                     = NULL,
    default_grid_color           = NULL,
    right_to_left                = NULL,
    show_formulas                = NULL,
    show_grid_lines              = NULL,
    show_outline_symbols         = NULL,
    show_row_col_headers         = NULL,
    show_ruler                   = NULL,
    show_white_space             = NULL,
    show_zeros                   = NULL,
    tab_selected                 = NULL,
    top_left_cell                = NULL,
    view                         = NULL,
    window_protection            = NULL,
    workbook_view_id             = NULL,
    zoom_scale                   = NULL,
    zoom_scale_normal            = NULL,
    zoom_scale_page_layout_view  = NULL,
    zoom_scale_sheet_layout_view = NULL,
    ...
) {
  assert_workbook(wb)
  wb$clone(deep = TRUE)$set_sheetview(
    sheet                        = sheet,
    color_id                     = color_id,
    default_grid_color           = default_grid_color,
    right_to_left                = right_to_left,
    show_formulas                = show_formulas,
    show_grid_lines              = show_grid_lines,
    show_outline_symbols         = show_outline_symbols,
    show_row_col_headers         = show_row_col_headers,
    show_ruler                   = show_ruler,
    show_white_space             = show_white_space,
    show_zeros                   = show_zeros,
    tab_selected                 = tab_selected,
    top_left_cell                = top_left_cell,
    view                         = view,
    window_protection            = window_protection,
    workbook_view_id             = workbook_view_id,
    zoom_scale                   = zoom_scale,
    zoom_scale_normal            = zoom_scale_normal,
    zoom_scale_page_layout_view  = zoom_scale_page_layout_view,
    zoom_scale_sheet_layout_view = zoom_scale_sheet_layout_view,
    ...                          = ...
  )
}

#' Modify the state of active and selected sheets in a workbook
#'
#' @description
#' Get and set table of sheets and their state as selected and active in a workbook
#'
#' Multiple sheets can be selected, but only a single one can be active (visible).
#' The visible sheet, must not necessarily be a selected sheet.
#'
#' @param wb a workbook
#' @returns a data frame with tabSelected and names
#' @export
#' @examples
#' wb <- wb_load(file = system.file("extdata", "openxlsx2_example.xlsx", package = "openxlsx2"))
#' # testing is the selected sheet
#' wb_get_selected(wb)
#' # change the selected sheet to Sheet2
#' wb <- wb_set_selected(wb, "Sheet2")
#' # get the active sheet
#' wb_get_active_sheet(wb)
#' # change the selected sheet to Sheet2
#' wb <- wb_set_active_sheet(wb, sheet = "Sheet2")
#' @name active_sheet-wb
wb_get_active_sheet <- function(wb) {
  assert_workbook(wb)
  wb$get_active_sheet()
}

#' @rdname active_sheet-wb
#' @param sheet a sheet name of the workbook
#' @export
wb_set_active_sheet <- function(wb, sheet) {
  # active tab requires a c index
  assert_workbook(wb)
  wb$clone()$set_active_sheet(sheet = sheet)
}

#' @rdname active_sheet-wb
#' @export
wb_get_selected <- function(wb) {
  assert_workbook(wb)
  wb$get_selected()
}

#' @rdname active_sheet-wb
#' @export
wb_set_selected <- function(wb, sheet) {
  assert_workbook(wb)
  wb$clone()$set_selected(sheet = sheet)
}