wbWorkbook: R6 class for a Workbook

Description

R6 class for a Workbook

Arguments

Value

The integer position of the sheet

The wbWorkbook object

A named

character vector of sheet names in their order. The names represent the original value of the worksheet prior to any character substitutions.

The wbWorkbook object

The sheet tables. character() if empty

The wbWorkbook object

Returns sheet visibility

The wbWorkbook object

a character vector of cell styles

Public fields

sheet_names: sheet_names
calcChain: calcChain
charts: charts
is_chartsheet: is_chartsheet
customXml: customXml
connections: connections
ctrlProps: ctrlProps
Content_Types: Content_Types
app: app
core: core
custom: custom
drawings: drawings
drawings_rels: drawings_rels
embeddings: embeddings
externalLinks: externalLinks
externalLinksRels: externalLinksRels
headFoot: headFoot
media: media
metadata: metadata
persons: persons
pivotTables: pivotTables
pivotTables.xml.rels: pivotTables.xml.rels
pivotDefinitions: pivotDefinitions
pivotRecords: pivotRecords
pivotDefinitionsRels: pivotDefinitionsRels
queryTables: queryTables
slicers: slicers
slicerCaches: slicerCaches
sharedStrings: sharedStrings
styles_mgr: styles_mgr
tables: tables
tables.xml.rels: tables.xml.rels
theme: theme
vbaProject: vbaProject
vml: vml
vml_rels: vml_rels
comments: comments
threadComments: threadComments
workbook: workbook
workbook.xml.rels: workbook.xml.rels
worksheets: worksheets
worksheets_rels: worksheets_rels
sheetOrder: The sheet order. Controls ordering for worksheets and worksheet names.
path: path
creator: A character vector of creators
title: title
subject: subject
category: category
datetimeCreated: The datetime (as POSIXt) the workbook is created. Defaults to the current Sys.time() when the workbook object is created, not when the Excel files are saved.

Methods

Public methods

Method `new()`

Creates a new wbWorkbook object

Usage

wbWorkbook$new(
  creator = NULL,
  title = NULL,
  subject = NULL,
  category = NULL,
  datetimeCreated = Sys.time()
)

Arguments

creator: character vector of creators. Duplicated are ignored.

title

title

subject

subject

category

Returns

a wbWorkbook object

Method `append()`

Append a field. This method is intended for internal use

Usage

wbWorkbook$append(field, value)

Arguments

field: A valid field name

value

A value for the field

Method `append_sheets()`

Append to self$workbook$sheets This method is intended for internal use

Usage

wbWorkbook$append_sheets(value)

Arguments

value: A value for self$workbook$sheets

Method `validate_sheet()`

validate sheet

Usage

wbWorkbook$validate_sheet(sheet)

Arguments

sheet: A character sheet name or integer location

Method `add_chartsheet()`

Add a chart sheet to the workbook

Usage

wbWorkbook$add_chartsheet(
  sheet = next_sheet(),
  tabColor = NULL,
  zoom = 100,
  visible = c("true", "false", "hidden", "visible", "veryhidden"),
  ...
)

Arguments

sheet: sheet

tabColor

tabColor

zoom

zoom

visible

visible

...

...

Returns

The wbWorkbook object, invisibly

Method `add_worksheet()`

Add worksheet to the wbWorkbook object

Usage

wbWorkbook$add_worksheet(
  sheet = next_sheet(),
  gridLines = TRUE,
  rowColHeaders = TRUE,
  tabColor = NULL,
  zoom = 100,
  header = NULL,
  footer = NULL,
  oddHeader = header,
  oddFooter = footer,
  evenHeader = header,
  evenFooter = footer,
  firstHeader = header,
  firstFooter = footer,
  visible = c("true", "false", "hidden", "visible", "veryhidden"),
  hasDrawing = FALSE,
  paperSize = 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)),
  ...
)

Arguments

sheet: sheet

gridLines

gridLines

rowColHeaders

rowColHeaders

tabColor

tabColor

zoom

zoom

header

header

footer

footer

oddHeader

oddHeader

oddFooter

oddFooter

evenHeader

evenHeader

evenFooter

evenFooter

firstHeader

firstHeader

firstFooter

firstFooter

visible

visible

hasDrawing

hasDrawing

paperSize

paperSize

orientation

orientation

hdpi

hdpi

vdpi

vdpi

...

...

Returns

The wbWorkbook object, invisibly

Method `clone_worksheet()`

Clone a workbooksheet

Usage

wbWorkbook$clone_worksheet(old = current_sheet(), new = next_sheet())

Arguments

old: name of worksheet to clone

new

name of new worksheet to add

Method `add_data()`

add data

Usage

wbWorkbook$add_data(
  sheet = current_sheet(),
  x,
  startCol = 1,
  startRow = 1,
  dims = rowcol_to_dims(startRow, startCol),
  array = FALSE,
  colNames = TRUE,
  rowNames = FALSE,
  withFilter = FALSE,
  name = NULL,
  sep = ", ",
  applyCellStyle = TRUE,
  removeCellStyle = FALSE,
  na.strings = na_strings(),
  inline_strings = TRUE
)

Arguments

sheet: sheet

x

startCol

startCol

startRow

startRow

dims

dims

array

array

colNames

colNames

rowNames

rowNames

withFilter

withFilter

name

name

sep

sep

applyCellStyle

applyCellStyle

removeCellStyle

if writing into existing cells, should the cell style be removed?

na.strings

Value used for replacing NA values from x. Default na_strings() uses the special #N/A value within the workbook.

inline_strings

write characters as inline strings

return

The wbWorkbook object

Method `add_data_table()`

add a data table

Usage

wbWorkbook$add_data_table(
  sheet = current_sheet(),
  x,
  startCol = 1,
  startRow = 1,
  dims = rowcol_to_dims(startRow, startCol),
  colNames = TRUE,
  rowNames = FALSE,
  tableStyle = "TableStyleLight9",
  tableName = NULL,
  withFilter = TRUE,
  sep = ", ",
  firstColumn = FALSE,
  lastColumn = FALSE,
  bandedRows = TRUE,
  bandedCols = FALSE,
  applyCellStyle = TRUE,
  removeCellStyle = FALSE,
  na.strings = na_strings(),
  inline_strings = TRUE
)

Arguments

sheet: sheet

x

startCol

startCol

startRow

startRow

dims

dims

colNames

colNames

rowNames

rowNames

tableStyle

tableStyle

tableName

tableName

withFilter

withFilter

sep

sep

firstColumn

firstColumn

lastColumn

lastColumn

bandedRows

bandedRows

bandedCols

bandedCols

applyCellStyle

applyCellStyle

removeCellStyle

if writing into existing cells, should the cell style be removed?

na.strings

Value used for replacing NA values from x. Default na_strings() uses the special #N/A value within the workbook.

inline_strings

write characters as inline strings

Method `add_pivot_table()`

add pivot table

Usage

wbWorkbook$add_pivot_table(
  x,
  sheet = next_sheet(),
  dims = "A3",
  filter,
  rows,
  cols,
  data,
  fun,
  params
)

Arguments

x: a wb_data object

sheet

a worksheet

dims

the worksheet cell where the pivot table is placed

filter

a character object with names used to filter

rows

a character object with names used as rows

cols

a character object with names used as cols

data

a character object with names used as data

fun

a character object of functions to be used with the data

params

a list of parameters to modify pivot table creation

Method `add_formula()`

add formula

Usage

wbWorkbook$add_formula(
  sheet = current_sheet(),
  x,
  startCol = 1,
  startRow = 1,
  dims = rowcol_to_dims(startRow, startCol),
  array = FALSE,
  cm = FALSE,
  applyCellStyle = TRUE,
  removeCellStyle = FALSE
)

Arguments

sheet: sheet

x

startCol

startCol

startRow

startRow

dims

dims

array

array

cm

applyCellStyle

applyCellStyle

removeCellStyle

if writing into existing cells, should the cell style be removed?

Method `add_style()`

add style

Usage

wbWorkbook$add_style(style = NULL, style_name = NULL)

Arguments

style: style

style_name

style_name

Method `to_df()`

to_df

Usage

wbWorkbook$to_df(
  sheet,
  startRow = 1,
  startCol = NULL,
  rowNames = FALSE,
  colNames = TRUE,
  skipEmptyRows = FALSE,
  skipEmptyCols = FALSE,
  skipHiddenRows = FALSE,
  skipHiddenCols = FALSE,
  rows = NULL,
  cols = NULL,
  detectDates = TRUE,
  na.strings = "#N/A",
  na.numbers = NA,
  fillMergedCells = FALSE,
  dims,
  showFormula = FALSE,
  convert = TRUE,
  types,
  named_region
)

Arguments

sheet: Either sheet name or index. When missing the first sheet in the workbook is selected.

startRow

first row to begin looking for data.

startCol

first column to begin looking for data.

rowNames

If TRUE, the first col of data will be used as row names.

colNames

If TRUE, the first row of data will be used as column names.

skipEmptyRows

If TRUE, empty rows are skipped.

skipEmptyCols

If TRUE, empty columns are skipped.

skipHiddenRows

If TRUE, hidden rows are skipped.

skipHiddenCols

If TRUE, hidden columns are skipped.

rows

A numeric vector specifying which rows in the Excel file to read. If NULL, all rows are read.

cols

A numeric vector specifying which columns in the Excel file to read. If NULL, all columns are read.

detectDates

If TRUE, attempt to recognize dates and perform conversion.

na.strings

A character vector of strings which are to be interpreted as NA. Blank cells will be returned as NA.

na.numbers

A numeric vector of digits which are to be interpreted as NA. Blank cells will be returned as NA.

fillMergedCells

If TRUE, the value in a merged cell is given to all cells within the merge.

dims

Character string of type "A1:B2" as optional dimensions to be imported.

showFormula

If TRUE, the underlying Excel formulas are shown.

convert

If TRUE, a conversion to dates and numerics is attempted.

types

A named numeric indicating, the type of the data. 0: character, 1: numeric, 2: date, 3: posixt, 4:logical. Names must match the returned data

named_region

Character string with a named_region (defined name or table). If no sheet is selected, the first appearance will be selected.

Returns

a data frame

Method `load()`

load workbook

Usage

wbWorkbook$load(
  file,
  xlsxFile = NULL,
  sheet,
  data_only = FALSE,
  calc_chain = FALSE
)

Arguments

file: file

xlsxFile

xlsxFile

sheet

sheet

data_only

data_only

calc_chain

calc_chain

Returns

The wbWorkbook object invisibly

Method `save()`

Save the workbook

Usage

wbWorkbook$save(path = self$path, overwrite = TRUE)

Arguments

path: The path to save the workbook to

overwrite

If FALSE, will not overwrite when path exists

Returns

The wbWorkbook object invisibly

Method `open()`

open wbWorkbook in Excel.

Usage

wbWorkbook$open(interactive = NA)

Arguments

interactive: If FALSE will throw a warning and not open the path. This can be manually set to TRUE, otherwise when NA (default) uses the value returned from base::interactive()

Returns

The wbWorkbook, invisibly

Method `buildTable()`

Build table

Usage

wbWorkbook$buildTable(
  sheet = current_sheet(),
  colNames,
  ref,
  showColNames,
  tableStyle,
  tableName,
  withFilter,
  totalsRowCount = 0,
  showFirstColumn = 0,
  showLastColumn = 0,
  showRowStripes = 1,
  showColumnStripes = 0
)

Arguments

sheet: sheet

colNames

colNames

ref

ref

showColNames

showColNames

tableStyle

tableStyle

tableName

tableName

withFilter

withFilter

totalsRowCount

totalsRowCount

showFirstColumn

showFirstColumn

showLastColumn

showLastColumn

showRowStripes

showRowStripes

showColumnStripes

showColumnStripes

Returns

The wbWorksheet object, invisibly

Method `update_table()`

update a data_table

Usage

wbWorkbook$update_table(sheet = current_sheet(), dims = "A1", tabname)

Arguments

sheet: a worksheet

dims

cell used as start

tabname

a tablename

Returns

The wbWorksheet object, invisibly

Method `copy_cells()`

copy cells around in a workbook

Usage

wbWorkbook$copy_cells(
  sheet = current_sheet(),
  dims = "A1",
  data,
  as_value = FALSE,
  as_ref = FALSE,
  transpose = FALSE
)

Arguments

sheet: a worksheet

dims

cell used as start

data

a wb_data object

as_value

should a copy of the value be written

as_ref

should references to the cell be written

transpose

should the data be written transposed

Returns

The wbWorksheet object, invisibly

Method `get_base_font()`

Get the base font

Usage

wbWorkbook$get_base_font()

Returns

A list of of the font

Method `set_base_font()`

Get the base font

Usage

wbWorkbook$set_base_font(
  fontSize = 11,
  fontColor = wb_color(theme = "1"),
  fontName = "Calibri",
  ...
)

Arguments

fontSize: fontSize

fontColor

fontColor

fontName

fontName

...

...

Returns

The wbWorkbook object

Method `set_bookview()`

Set the book views

Usage

wbWorkbook$set_bookview(
  activeTab = NULL,
  autoFilterDateGrouping = NULL,
  firstSheet = NULL,
  minimized = NULL,
  showHorizontalScroll = NULL,
  showSheetTabs = NULL,
  showVerticalScroll = NULL,
  tabRatio = NULL,
  visibility = NULL,
  windowHeight = NULL,
  windowWidth = NULL,
  xWindow = NULL,
  yWindow = NULL
)

Arguments

activeTab: activeTab

autoFilterDateGrouping

autoFilterDateGrouping

firstSheet

firstSheet

minimized

minimized

showHorizontalScroll

showHorizontalScroll

showSheetTabs

showSheetTabs

showVerticalScroll

showVerticalScroll

tabRatio

tabRatio

visibility

visibility

windowHeight

windowHeight

windowWidth

windowWidth

xWindow

xWindow

yWindow

yWindow

Returns

The wbWorkbook object

Method `get_sheet_names()`

Get sheet names

Usage

wbWorkbook$get_sheet_names()

Method `set_sheet_names()`

Sets a sheet name

Usage

wbWorkbook$set_sheet_names(old = NULL, new)

Arguments

old: Old sheet name

new

New sheet name

Returns

The wbWorkbook object, invisibly

Method `set_row_heights()`

Sets a row height for a sheet

Usage

wbWorkbook$set_row_heights(
  sheet = current_sheet(),
  rows,
  heights = NULL,
  hidden = FALSE
)

Arguments

sheet: sheet

rows

rows

heights

heights

hidden

hidden

Returns

The wbWorkbook object, invisibly

Method `remove_row_heights()`

Sets a row height for a sheet

Usage

wbWorkbook$remove_row_heights(sheet = current_sheet(), rows)

Arguments

sheet: sheet

rows

rows

Returns

The wbWorkbook object, invisibly description creates column object for worksheet

Method `createCols()`

Usage

wbWorkbook$createCols(sheet = current_sheet(), n, beg, end)

Arguments

sheet: sheet

n

beg

beg

end

end

Method `group_cols()`

Group cols

Usage

wbWorkbook$group_cols(
  sheet = current_sheet(),
  cols,
  collapsed = FALSE,
  levels = NULL
)

Arguments

sheet: sheet

cols

cols

collapsed

collapsed

levels

levels

Returns

The wbWorkbook object, invisibly

Method `ungroup_cols()`

ungroup cols

Usage

wbWorkbook$ungroup_cols(sheet = current_sheet(), cols)

Arguments

sheet: sheet

cols

= cols

Method `remove_col_widths()`

Remove row heights from a worksheet

Usage

wbWorkbook$remove_col_widths(sheet = current_sheet(), cols)

Arguments

sheet: A name or index of a worksheet

cols

Indices of columns to remove custom width (if any) from.

Returns

The wbWorkbook object, invisibly

Method `set_col_widths()`

Group cols

Usage

wbWorkbook$set_col_widths(
  sheet = current_sheet(),
  cols,
  widths = 8.43,
  hidden = FALSE
)

Arguments

sheet: sheet

cols

cols

widths

Width of columns

hidden

A logical vector to determine which cols are hidden; values are repeated across length of cols

Returns

The wbWorkbook object, invisibly

Method `group_rows()`

Group rows

Usage

wbWorkbook$group_rows(
  sheet = current_sheet(),
  rows,
  collapsed = FALSE,
  levels = NULL
)

Arguments

sheet: sheet

rows

rows

collapsed

collapsed

levels

levels

Returns

The wbWorkbook object, invisibly

Method `ungroup_rows()`

ungroup rows

Usage

wbWorkbook$ungroup_rows(sheet = current_sheet(), rows)

Arguments

sheet: sheet

rows

rows

Returns

The wbWorkbook object

Method `remove_worksheet()`

Remove a worksheet

Usage

wbWorkbook$remove_worksheet(sheet = current_sheet())

Arguments

sheet: The worksheet to delete

Returns

The wbWorkbook object, invisibly

Method `add_data_validation()`

Adds data validation

Usage

wbWorkbook$add_data_validation(
  sheet = current_sheet(),
  cols,
  rows,
  type,
  operator,
  value,
  allowBlank = TRUE,
  showInputMsg = TRUE,
  showErrorMsg = TRUE,
  errorStyle = NULL,
  errorTitle = NULL,
  error = NULL,
  promptTitle = NULL,
  prompt = NULL
)

Arguments

sheet: sheet

cols

cols

rows

rows

type

type

operator

operator

value

value

allowBlank

allowBlank

showInputMsg

showInputMsg

showErrorMsg

showErrorMsg

errorStyle

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)

errorTitle

The error title

error

The error text

promptTitle

The prompt title

prompt

The prompt text

Method `merge_cells()`

Set cell merging for a sheet

Usage

wbWorkbook$merge_cells(sheet = current_sheet(), rows = NULL, cols = NULL)

Arguments

sheet: sheet

rows, cols

Row and column specifications.

Returns

The wbWorkbook object, invisibly

Method `unmerge_cells()`

Removes cell merging for a sheet

Usage

wbWorkbook$unmerge_cells(sheet = current_sheet(), rows = NULL, cols = NULL)

Arguments

sheet: sheet

rows, cols

Row and column specifications.

Returns

The wbWorkbook object, invisibly

Method `freeze_pane()`

Set freeze panes for a sheet

Usage

wbWorkbook$freeze_pane(
  sheet = current_sheet(),
  firstActiveRow = NULL,
  firstActiveCol = NULL,
  firstRow = FALSE,
  firstCol = FALSE
)

Arguments

sheet: sheet

firstActiveRow

firstActiveRow

firstActiveCol

firstActiveCol

firstRow

firstRow

firstCol

firstCol

Returns

The wbWorkbook object, invisibly

Method `add_comment()`

Add comment

Usage

wbWorkbook$add_comment(
  sheet = current_sheet(),
  col = NULL,
  row = NULL,
  dims = rowcol_to_dim(row, col),
  comment
)

Arguments

sheet: sheet

col

column to apply the comment

row

row to apply the comment

dims

row and column as spreadsheet dimension, e.g. "A1"

comment

a comment to apply to the worksheet

Method `remove_comment()`

Remove comment

Usage

wbWorkbook$remove_comment(
  sheet = current_sheet(),
  col = NULL,
  row = NULL,
  dims = rowcol_to_dims(row, col),
  gridExpand = TRUE
)

Arguments

sheet: sheet

col

column to apply the comment

row

row to apply the comment

dims

row and column as spreadsheet dimension, e.g. "A1"

gridExpand

Remove all comments inside the grid. Similar to dims "A1:B2"

Method `add_conditional_formatting()`

Add conditional formatting

Usage

wbWorkbook$add_conditional_formatting(
  sheet = current_sheet(),
  cols,
  rows,
  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)
)

Arguments

sheet: sheet

cols

cols

rows

rows

rule

rule

style

style

type

type

params

Additional parameters

Method `add_image()`

Insert an image into a sheet

Usage

wbWorkbook$add_image(
  sheet = current_sheet(),
  file,
  width = 6,
  height = 3,
  startRow = 1,
  startCol = 1,
  rowOffset = 0,
  colOffset = 0,
  units = "in",
  dpi = 300,
  dims = rowcol_to_dim(startRow, startCol)
)

Arguments

sheet: sheet

file

file

width

width

height

height

startRow

startRow

startCol

startCol

rowOffset

rowOffset

colOffset

colOffset

units

units

dpi

dpi

dims

dims

Returns

The wbWorkbook object, invisibly

Method `add_plot()`

Add plot. A wrapper for add_image()

Usage

wbWorkbook$add_plot(
  sheet = current_sheet(),
  width = 6,
  height = 4,
  xy = NULL,
  startRow = 1,
  startCol = 1,
  rowOffset = 0,
  colOffset = 0,
  fileType = "png",
  units = "in",
  dpi = 300,
  dims = rowcol_to_dim(startRow, startCol)
)

Arguments

sheet: sheet

width

width

height

height

xy

startRow

startRow

startCol

startCol

rowOffset

rowOffset

colOffset

colOffset

fileType

fileType

units

units

dpi

dpi

dims

dims

Method `add_drawing()`

Add xml drawing

Usage

wbWorkbook$add_drawing(
  sheet = current_sheet(),
  xml,
  dims = NULL,
  colOffset = 0,
  rowOffset = 0
)

Arguments

sheet: sheet

xml

xml

dims

dims

colOffset, rowOffset

offsets for column and row

Method `add_chart_xml()`

Add xml drawing

Add xml chart

Usage

wbWorkbook$add_chart_xml(
  sheet = current_sheet(),
  xml,
  dims = NULL,
  colOffset = 0,
  rowOffset = 0
)

Arguments

sheet: sheet

xml

xml

dims

dims

colOffset, rowOffset

startCol and startRow

Method `add_mschart()`

Add mschart chart to the workbook

Usage

wbWorkbook$add_mschart(
  sheet = current_sheet(),
  dims = NULL,
  graph,
  colOffset = 0,
  rowOffset = 0
)

Arguments

sheet: the sheet on which the graph will appear

dims

the dimensions where the sheet will appear

graph

mschart graph

colOffset, rowOffset

startCol and startRow

Method `add_form_control()`

add form control to workbook

Usage

wbWorkbook$add_form_control(
  sheet = current_sheet(),
  dims = "A1",
  type = NULL,
  text = NULL,
  link = NULL,
  range = NULL,
  checked = FALSE
)

Arguments

sheet: sheet

dims

dims

type

type

text

text

link

link

range

range

checked

checked

Returns

The wbWorkbook object, invisibly

Method `print()`

Prints the wbWorkbook object

Usage

wbWorkbook$print()

Returns

The wbWorkbook object, invisibly; called for its side-effects

Method `protect()`

Protect a workbook

Usage

wbWorkbook$protect(
  protect = TRUE,
  password = NULL,
  lockStructure = FALSE,
  lockWindows = FALSE,
  type = 1,
  fileSharing = FALSE,
  username = unname(Sys.info()["user"]),
  readOnlyRecommended = FALSE
)

Arguments

protect: protect

password

password

lockStructure

lockStructure

lockWindows

lockWindows

type

type

fileSharing

fileSharing

username

username

readOnlyRecommended

readOnlyRecommended

Returns

The wbWorkbook object, invisibly

Method `protect_worksheet()`

protect worksheet

Usage

wbWorkbook$protect_worksheet(
  sheet = current_sheet(),
  protect = TRUE,
  password = NULL,
  properties = NULL
)

Arguments

sheet: sheet

protect

protect

password

password

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"

Method `set_creators()`

Set creator(s)

Usage

wbWorkbook$set_creators(creators)

Arguments

creators: A character vector of creators to set. Duplicates are ignored.

Method `add_creators()`

Add creator(s)

Usage

wbWorkbook$add_creators(creators)

Arguments

creators: A character vector of creators to add. Duplicates are ignored.

Method `remove_creators()`

Remove creator(s)

Usage

wbWorkbook$remove_creators(creators)

Arguments

creators: A character vector of creators to remove. All duplicated are removed.

Method `set_last_modified_by()`

Change the last modified by

Usage

wbWorkbook$set_last_modified_by(LastModifiedBy = NULL)

Arguments

LastModifiedBy: A new value

Returns

The wbWorkbook object, invisibly

Method `page_setup()`

page_setup()

Usage

wbWorkbook$page_setup(
  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,
  fitToWidth = FALSE,
  fitToHeight = FALSE,
  paperSize = NULL,
  printTitleRows = NULL,
  printTitleCols = NULL,
  summaryRow = NULL,
  summaryCol = NULL
)

Arguments

sheet: sheet

orientation

orientation

scale

scale

left

left

right

right

top

top

bottom

bottom

header

header

footer

footer

fitToWidth

fitToWidth

fitToHeight

fitToHeight

paperSize

paperSize

printTitleRows

printTitleRows

printTitleCols

printTitleCols

summaryRow

summaryRow

summaryCol

summaryCol

Returns

The wbWorkbook object, invisibly

Method `set_header_footer()`

Sets headers and footers

Usage

wbWorkbook$set_header_footer(
  sheet = current_sheet(),
  header = NULL,
  footer = NULL,
  evenHeader = NULL,
  evenFooter = NULL,
  firstHeader = NULL,
  firstFooter = NULL
)

Arguments

sheet: sheet

header

header

footer

footer

evenHeader

evenHeader

evenFooter

evenFooter

firstHeader

firstHeader

firstFooter

firstFooter

Returns

The wbWorkbook object, invisibly

Method `get_tables()`

get tables

Usage

wbWorkbook$get_tables(sheet = current_sheet())

Arguments

sheet: sheet

Method `remove_tables()`

remove tables

Details

A Workbook

fun can be either of AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR, VARP

minor helper wrapping xl_open which does the entire same thing

Examples

Run this code


## ------------------------------------------------
## Method `wbWorkbook$add_border`
## ------------------------------------------------


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 = c(rgb="FF808080"))

## ------------------------------------------------
## Method `wbWorkbook$add_fill`
## ------------------------------------------------

 # example from the gradient fill manual page
 gradient_fill <- "
   
   
  "

## ------------------------------------------------
## Method `wbWorkbook$add_font`
## ------------------------------------------------

 wb <- wb_workbook()$add_worksheet("S1")$add_data("S1", mtcars)
 wb$add_font("S1", "A1:K1", name = "Arial", color = wb_color(theme = "4"))

## ------------------------------------------------
## Method `wbWorkbook$add_numfmt`
## ------------------------------------------------

 wb <- wb_workbook()$add_worksheet("S1")$add_data("S1", mtcars)
 wb$add_numfmt("S1", "A1:A33", numfmt = 1)

## ------------------------------------------------
## Method `wbWorkbook$add_cell_style`
## ------------------------------------------------

 wb <- wb_workbook()$add_worksheet("S1")$add_data("S1", mtcars)
 wb$add_cell_style("S1", "A1:K1",
                   textRotation = "45",
                   horizontal = "center",
                   vertical = "center",
                   wrapText = "1")

Run the code above in your browser using DataLab

Description

Arguments

Value

Public fields

Methods

Public methods

Method new()

Usage

Arguments

Returns

Method append()

Usage

Arguments

Method append_sheets()

Usage

Arguments

Method validate_sheet()

Usage

Arguments

Method add_chartsheet()

Usage

Arguments

Returns

Method add_worksheet()

Usage

Arguments

Returns

Method clone_worksheet()

Usage

Arguments

Method add_data()

Usage

Arguments

Method add_data_table()

Usage

Arguments

Method add_pivot_table()

Usage

Arguments

Method add_formula()

Usage

Arguments

Method add_style()

Usage

Arguments

Method to_df()

Usage

Arguments

Returns

Method load()

Usage

Arguments

Returns

Method save()

Usage

Arguments

Returns

Method open()

Usage

Arguments

Returns

Method buildTable()

Usage

Arguments

Returns

Method update_table()

Usage

Arguments

Returns

Method copy_cells()

Usage

Arguments

Returns

Method get_base_font()

Usage

Returns

Method set_base_font()

Usage

Arguments

Returns

Method `new()`

Method `append()`

Method `append_sheets()`

Method `validate_sheet()`

Method `add_chartsheet()`

Method `add_worksheet()`

Method `clone_worksheet()`

Method `add_data()`

Method `add_data_table()`

Method `add_pivot_table()`

Method `add_formula()`

Method `add_style()`

Method `to_df()`

Method `load()`

Method `save()`

Method `open()`

Method `buildTable()`

Method `update_table()`

Method `copy_cells()`

Method `get_base_font()`

Method `set_base_font()`

Method `set_bookview()`

Method `get_sheet_names()`

Method `set_sheet_names()`

Method `set_row_heights()`

Method `remove_row_heights()`

Method `createCols()`

Method `group_cols()`

Method `ungroup_cols()`

Method `remove_col_widths()`

Method `set_col_widths()`

Method `group_rows()`

Method `ungroup_rows()`

Method `remove_worksheet()`

Method `add_data_validation()`

Method `merge_cells()`

Method `unmerge_cells()`

Method `freeze_pane()`

Method `add_comment()`

Method `remove_comment()`