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
apps: apps
charts: charts
isChartSheet: isChartSheet
connections: connections
Content_Types: Content_Types
app: app
core: core
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
rowHeights: rowHeights
slicers: slicers
slicerCaches: slicerCaches
sharedStrings: sharedStrings
styles_mgr: styles_mgr
styles_xml: styles_xml
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_worksheet()`

Add worksheet to the wbWorkbook object

Usage

wbWorkbook$add_worksheet(
  sheet = next_sheet(),
  gridLines = TRUE,
  rowColHeaders = TRUE,
  tabColour = 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

tabColour

tabColour

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

Add a chart sheet to the workbook

Usage

wbWorkbook$addChartSheet(sheet = current_sheet(), tabColour = NULL, zoom = 100)

Arguments

sheet: sheet

tabColour

tabColour

zoom

zoom

Returns

The wbWorkbook object, invisibly

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,
  xy = NULL,
  colNames = TRUE,
  rowNames = FALSE,
  withFilter = FALSE,
  name = NULL,
  sep = ", ",
  applyCellStyle = TRUE,
  removeCellStyle = FALSE,
  na.strings
)

Arguments

sheet: sheet

x

startCol

startCol

startRow

startRow

dims

dims

array

array

xy

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

na.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),
  xy = NULL,
  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
)

Arguments

sheet: sheet

x

startCol

startCol

startRow

startRow

dims

dims

xy

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

na.strings

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,
  xy = NULL,
  applyCellStyle = TRUE,
  removeCellStyle = FALSE
)

Arguments

sheet: sheet

x

startCol

startCol

startRow

startRow

dims

dims

array

array

xy

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 `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 `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 = 12,
  fontColour = wb_colour(theme = "1"),
  fontName = "Calibri"
)

Arguments

fontSize: fontSize

fontColour

fontColour

fontName

fontName

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

Deprecated. Use set_sheet_names() instead

Usage

wbWorkbook$setSheetName(sheet = current_sheet(), name)

Arguments

sheet: Old sheet name

name

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)

Arguments

sheet: sheet

rows

rows

heights

heights

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,
  row,
  dims = rowcol_to_dims(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,
  row,
  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", "duplicatedValues", "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
)

Arguments

sheet: sheet

file

file

width

width

height

height

startRow

startRow

startCol

startCol

rowOffset

rowOffset

colOffset

colOffset

units

units

dpi

dpi

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
)

Arguments

sheet: sheet

width

width

height

height

xy

startRow

startRow

startCol

startCol

rowOffset

rowOffset

colOffset

colOffset

fileType

fileType

units

units

dpi

dpi

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 = c("1", "2", "4", "8"),
  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

Usage

wbWorkbook$remove_tables(sheet = current_sheet(), table)

Arguments

sheet: sheet

table

table

Method `add_filter()`

add filters

Usage

wbWorkbook$add_filter(sheet = current_sheet(), rows, cols)

Arguments

sheet: sheet

rows

rows

cols

cols

Method `remove_filter()`

remove filters

Usage

wbWorkbook$remove_filter(sheet = current_sheet())

Arguments

sheet: sheet

Method `grid_lines()`

grid lines

Usage

wbWorkbook$grid_lines(sheet = current_sheet(), show = FALSE)

Arguments

sheet: sheet

show

show

Method `add_named_region()`

add a named region

Usage

wbWorkbook$add_named_region(
  sheet = current_sheet(),
  cols,
  rows,
  name,
  localSheetId = NULL,
  overwrite = FALSE
)

Arguments

sheet: sheet

cols

cols

rows

rows

name

name

localSheetId

localSheetId

overwrite

overwrite

Method `remove_named_region()`

remove a named region

Usage

wbWorkbook$remove_named_region(sheet = current_sheet(), name = NULL)

Arguments

sheet: sheet

name

name

Method `set_order()`

set worksheet order

Usage

wbWorkbook$set_order(sheets)

Arguments

sheets: sheets

Returns

The wbWorkbook object

Method `get_sheet_visibility()`

Get sheet visibility

Usage

wbWorkbook$get_sheet_visibility()

Method `set_sheet_visibility()`

Set sheet visibility

Usage

wbWorkbook$set_sheet_visibility(sheet = current_sheet(), value)

Arguments

sheet: sheet

value

value

Method `add_page_break()`

Add a page break

Usage

wbWorkbook$add_page_break(sheet = current_sheet(), row = NULL, col = NULL)

Arguments

sheet: sheet

row

row

col

col

Method `clean_sheet()`

clean sheet (remove all values)

Usage

wbWorkbook$clean_sheet(
  sheet = current_sheet(),
  numbers = TRUE,
  characters = TRUE,
  styles = TRUE,
  merged_cells = TRUE
)

Arguments

sheet: sheet

numbers

remove all numbers

characters

remove all characters

styles

remove all styles

merged_cells

remove all merged_cells

Returns

The wbWorksheetObject, invisibly

Method `add_border()`

create borders for cell region

Usage

wbWorkbook$add_border(
  sheet = current_sheet(),
  dims = "A1",
  bottom_color = wb_colour(hex = "FF000000"),
  left_color = wb_colour(hex = "FF000000"),
  right_color = wb_colour(hex = "FF000000"),
  top_color = wb_colour(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
)

Arguments

sheet: a worksheet

dims

dimensions on the worksheet e.g. "A1", "A1:A5", "A1:H5"

bottom_color, left_color, right_color, top_color, inner_hcolor, inner_vcolor

a color, either something openxml knows or some RGB color

left_border, right_border, top_border, bottom_border, inner_hgrid, inner_vgrid

the border style, if NULL no border is drawn. See create_border for possible border styles

Returns

The wbWorksheetObject, invisibly

Examples


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_colour(hex = "FF9400D3"), right_color = wb_colour(hex = "FF4B0082"),
 top_color = wb_colour(hex = "FF0000FF"), bottom_color = wb_colour(hex = "FF00FF00"))
wb$add_border(1, dims = "A20:C23")
wb$add_border(1, dims = "B12:D14",
 left_color = wb_colour(hex = "FFFFFF00"), right_color = wb_colour(hex = "FFFF7F00"),
 bottom_color = wb_colour(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 `add_fill()`

provide simple fill function

Usage

wbWorkbook$add_fill(
  sheet = current_sheet(),
  dims = "A1",
  color = wb_colour(hex = "FFFFFF00"),
  pattern = "solid",
  gradient_fill = "",
  every_nth_col = 1,
  every_nth_row = 1
)

Arguments

sheet: the worksheet

dims

the cell range

color

the colors to apply, e.g. yellow: wb_colour(hex = "FFFFFF00")

pattern

various default "none" but others are possible: "solid", "mediumGray", "darkGray", "lightGray", "darkHorizontal", "darkVertical", "darkDown", "darkUp", "darkGrid", "darkTrellis", "lightHorizontal", "lightVertical", "lightDown", "lightUp", "lightGrid", "lightTrellis", "gray125", "gray0625"

gradient_fill

a gradient fill xml pattern.

every_nth_col

which col should be filled

every_nth_row

which row should be filled

Returns

The wbWorksheetObject, invisibly

Examples

 # example from the gradient fill manual page
 gradient_fill <- "<gradientFill degree=\"90\">
   <stop position=\"0\"><color rgb=\"FF92D050\"/></stop>
   <stop position=\"1\"><color rgb=\"FF0070C0\"/></stop>
  </gradientFill>"

Method `add_font()`

provide simple font function

Usage

wbWorkbook$add_font(
  sheet = current_sheet(),
  dims = "A1",
  name = "Calibri",
  color = wb_colour(hex = "FF000000"),
  size = "11",
  bold = "",
  italic = "",
  outline = "",
  strike = "",
  underline = "",
  charset = "",
  condense = "",
  extend = "",
  family = "",
  scheme = "",
  shadow = "",
  vertAlign = ""
)

Arguments

sheet: the worksheet

dims

the cell range

name

font name: default "Calibri"

color

rgb color: default "FF000000"

size

font size: default "11",

bold

bold

italic

italic

outline

outline

strike

strike

underline

underline

charset

charset

condense

condense

extend

extend

family

font family

scheme

font scheme

shadow

shadow

vertAlign

vertical alignment

Returns

The wbWorksheetObject, invisibly

Examples

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

Method `add_numfmt()`

provide simple number format function

Usage

wbWorkbook$add_numfmt(sheet = current_sheet(), dims = "A1", numfmt)

Arguments

sheet: the worksheet

di

Details

A Workbook

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_colour(hex = "FF9400D3"), right_color = wb_colour(hex = "FF4B0082"),
 top_color = wb_colour(hex = "FF0000FF"), bottom_color = wb_colour(hex = "FF00FF00"))
wb$add_border(1, dims = "A20:C23")
wb$add_border(1, dims = "B12:D14",
 left_color = wb_colour(hex = "FFFFFF00"), right_color = wb_colour(hex = "FFFF7F00"),
 bottom_color = wb_colour(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_colour(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_worksheet()

Usage

Arguments

Returns

Method clone_worksheet()

Usage

Arguments

Method addChartSheet()

Usage

Arguments

Returns

Method add_data()

Usage

Arguments

Method add_data_table()

Usage

Arguments

Method add_formula()

Usage

Arguments

Method add_style()

Usage

Arguments

Method save()

Usage

Arguments

Returns

Method open()

Usage

Arguments

Returns

Method buildTable()

Usage

Arguments

Returns

Method get_base_font()

Usage

Returns

Method set_base_font()

Usage

Arguments

Returns

Method get_sheet_names()

Usage

Method set_sheet_names()

Usage

Arguments

Returns

Method setSheetName()

Usage

Arguments

Returns

Method set_row_heights()

Usage

Arguments

Returns

Method remove_row_heights()

Usage

Arguments

Returns

Method createCols()

Method `new()`

Method `append()`

Method `append_sheets()`

Method `validate_sheet()`

Method `add_worksheet()`

Method `clone_worksheet()`

Method `addChartSheet()`

Method `add_data()`

Method `add_data_table()`

Method `add_formula()`

Method `add_style()`

Method `save()`

Method `open()`

Method `buildTable()`

Method `get_base_font()`

Method `set_base_font()`

Method `get_sheet_names()`

Method `set_sheet_names()`

Method `setSheetName()`

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

Method `add_conditional_formatting()`

Method `add_image()`

Method `add_plot()`

Method `print()`

Method `protect()`

Method `protect_worksheet()`