Package 'openxlsx'

Description

Get and set active sheet of the workbook

Usage

activeSheet(wb)

activeSheet(wb) <- value

Arguments

Value

return the active sheet of the workbook

Author(s)

Philipp Schauberger

Examples

wb <- createWorkbook()
addWorksheet(wb, sheetName = "S1")
addWorksheet(wb, sheetName = "S2")
addWorksheet(wb, sheetName = "S3")

activeSheet(wb) # default value is the first sheet active
activeSheet(wb) <- 1 ## active sheet S1
activeSheet(wb)
activeSheet(wb) <- "S2" ## active sheet S2
activeSheet(wb)

Description

Just a wrapper of wb$addCreator()

Usage

addCreator(wb, Creator)

Arguments

Author(s)

Philipp Schauberger

Examples

wb <- createWorkbook()
addCreator(wb, "test")

Description

Add excel column filters to a worksheet

Usage

addFilter(wb, sheet, rows, cols)

Arguments

Details

adds filters to worksheet columns, same as filter parameters in writeData. writeDataTable automatically adds filters to first row of a table. NOTE Can only have a single filter per worksheet unless using tables.

See Also

writeData()

addFilter()

Examples

wb <- createWorkbook()
addWorksheet(wb, "Sheet 1")
addWorksheet(wb, "Sheet 2")
addWorksheet(wb, "Sheet 3")

writeData(wb, 1, iris)
addFilter(wb, 1, row = 1, cols = 1:ncol(iris))

## Equivalently
writeData(wb, 2, x = iris, withFilter = TRUE)

## Similarly
writeDataTable(wb, 3, iris)
## Not run: 
saveWorkbook(wb, file = "addFilterExample.xlsx", overwrite = TRUE)

## End(Not run)

Description

Function adds a style to a specified set of cells.

Usage

addStyle(wb, sheet, style, rows, cols, gridExpand = FALSE, stack = FALSE)

Arguments

Author(s)

Alexander Walker

See Also

createStyle()

expand.grid

Examples

## See package vignette for more examples.

## Create a new workbook
wb <- createWorkbook("My name here")

## Add a worksheets
addWorksheet(wb, "Expenditure", gridLines = FALSE)

## write data to worksheet 1
writeData(wb, sheet = 1, USPersonalExpenditure, rowNames = TRUE)

## create and add a style to the column headers
headerStyle <- createStyle(
  fontSize = 14, fontColour = "#FFFFFF", halign = "center",
  fgFill = "#4F81BD", border = "TopBottom", borderColour = "#4F81BD"
)

## style for body
bodyStyle <- createStyle(border = "TopBottom", borderColour = "#4F81BD")
addStyle(wb, sheet = 1, bodyStyle, rows = 2:6, cols = 1:6, gridExpand = TRUE)
setColWidths(wb, 1, cols = 1, widths = 21) ## set column width for row names column
## Not run: 
saveWorkbook(wb, "addStyleExample.xlsx", overwrite = TRUE)

## End(Not run)

Description

Add a worksheet to a Workbook object

Usage

addWorksheet(
  wb,
  sheetName,
  gridLines = openxlsx_getOp("gridLines", TRUE),
  tabColour = NULL,
  zoom = 100,
  header = openxlsx_getOp("header"),
  footer = openxlsx_getOp("footer"),
  evenHeader = openxlsx_getOp("evenHeader"),
  evenFooter = openxlsx_getOp("evenFooter"),
  firstHeader = openxlsx_getOp("firstHeader"),
  firstFooter = openxlsx_getOp("firstFooter"),
  visible = TRUE,
  paperSize = openxlsx_getOp("paperSize", 9),
  orientation = openxlsx_getOp("orientation", "portrait"),
  vdpi = openxlsx_getOp("vdpi", 300),
  hdpi = openxlsx_getOp("hdpi", 300)
)

Arguments

Details

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

Value

XML tree

Author(s)

Alexander Walker

Examples

## Create a new workbook
wb <- createWorkbook("Fred")

## Add 3 worksheets
addWorksheet(wb, "Sheet 1")
addWorksheet(wb, "Sheet 2", gridLines = FALSE)
addWorksheet(wb, "Sheet 3", tabColour = "red")
addWorksheet(wb, "Sheet 4", gridLines = FALSE, tabColour = "#4F81BD")

## Headers and Footers
addWorksheet(wb, "Sheet 5",
  header = c("ODD HEAD LEFT", "ODD HEAD CENTER", "ODD HEAD RIGHT"),
  footer = c("ODD FOOT RIGHT", "ODD FOOT CENTER", "ODD FOOT RIGHT"),
  evenHeader = c("EVEN HEAD LEFT", "EVEN HEAD CENTER", "EVEN HEAD RIGHT"),
  evenFooter = c("EVEN FOOT RIGHT", "EVEN FOOT CENTER", "EVEN FOOT RIGHT"),
  firstHeader = c("TOP", "OF FIRST", "PAGE"),
  firstFooter = c("BOTTOM", "OF FIRST", "PAGE")
)

addWorksheet(wb, "Sheet 6",
  header = c("&[Date]", "ALL HEAD CENTER 2", "&[Page] / &[Pages]"),
  footer = c("&[Path]&[File]", NA, "&[Tab]"),
  firstHeader = c(NA, "Center Header of First Page", NA),
  firstFooter = c(NA, "Center Footer of First Page", NA)
)

addWorksheet(wb, "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")
)

addWorksheet(wb, "Sheet 8",
  firstHeader = c("FIRST ONLY L", NA, "FIRST ONLY R"),
  firstFooter = c("FIRST ONLY L", NA, "FIRST ONLY R")
)

## Need data on worksheet to see all headers and footers
writeData(wb, sheet = 5, 1:400)
writeData(wb, sheet = 6, 1:400)
writeData(wb, sheet = 7, 1:400)
writeData(wb, sheet = 8, 1:400)

## Save workbook
## Not run: 
saveWorkbook(wb, "addWorksheetExample.xlsx", overwrite = TRUE)

## End(Not run)

Description

Check equality of workbooks

Usage

## S3 method for class 'Workbook'
all.equal(target, current, ...)

Arguments

Description

This function exists to prevent conflicts with as.character.formula methods from other packages

Usage

## S3 method for class 'formula'
as.character(x, ...)

Arguments

Value

base::as.character.default(x)

Description

Compute optimal row heights for cell with fixed with and enabled automatic row heights parameter

Usage

auto_heights(
  wb,
  sheet,
  selected,
  fontsize = NULL,
  factor = 1,
  base_height = 15,
  extra_height = 12
)

Arguments

Value

list of indices of columns with fixed widths and optimal row heights

Author(s)

David Breuer

Examples

## Create new workbook
wb <- createWorkbook()
addWorksheet(wb, "Sheet")
sheet <- 1

## Write dummy data
long_string <- "ABC ABC ABC ABC ABC ABC ABC ABC ABC ABC ABC"
writeData(wb, sheet, c("A", long_string, "CCC"), startCol = 2, startRow = 3)
writeData(wb, sheet, c(4, 5), startCol = 4, startRow = 3)

## Set column widths and get optimal row heights
setColWidths(wb, sheet, c(1,2,3,4), c(10,20,10,20))
auto_heights(wb, sheet, 1:5)

Description

Build a workbook from a data.frame or named list

Usage

buildWorkbook(x, asTable = FALSE, ...)

Arguments

Details

This function can be used as shortcut to create a workbook object from a data.frame or named list. If names are available in the list they will be used as the worksheet names. The parameters in ... are collected and passed to writeData() or writeDataTable() to initially create the Workbook objects then appropriate parameters are passed to setColWidths().

columns of x with class Date or POSIXt are automatically styled as dates and datetimes respectively.

Value

A Workbook object

Optional Parameters

createWorkbook Parameters

creator

A string specifying the workbook author

addWorksheet Parameters

sheetName

Name of the worksheet

gridLines

A logical. If FALSE, the worksheet grid lines will be hidden.

tabColour

Colour of the worksheet tab. A valid colour (belonging to colours()) or a valid hex colour beginning with "#".

zoom

A numeric between 10 and 400. Worksheet zoom level as a percentage.

writeData/writeDataTable Parameters

startCol

A vector specifying the starting column(s) to write df

startRow

A vector specifying the starting row(s) to write df

xy

An alternative to specifying startCol and startRow individually. A vector of the form c(startCol, startRow)

colNames or col.names

If TRUE, column names of x are written.

rowNames or row.names

If TRUE, row names of x are written.

headerStyle

Custom style to apply to column names.

borders

Either "surrounding", "columns" or "rows" or NULL. If "surrounding", a border is drawn around the data. If "rows", a surrounding border is drawn a border around each row. If "columns", a surrounding border is drawn with a border between each column. If "all" all cell borders are drawn.

borderColour

Colour of cell border

borderStyle

Border line style.

keepNA

If TRUE, NA values are converted to #N/A (or na.string, if not NULL) in Excel, else NA cells will be empty. Defaults to FALSE.

na.string

If not NULL, and if keepNA is TRUE, NA values are converted to this string in Excel. Defaults to NULL.

freezePane Parameters

firstActiveRow

Top row of active region to freeze pane.

firstActiveCol

Furthest left column of active region to freeze pane.

firstRow

If TRUE, freezes the first row (equivalent to firstActiveRow = 2)

firstCol

If TRUE, freezes the first column (equivalent to firstActiveCol = 2)

colWidths Parameters

colWidths

May be a single value for all columns (or "auto"), or a list of vectors that will be recycled for each sheet (see examples)

Author(s)

Jordan Mark Barbone

See Also

write.xlsx()

Examples

x <- data.frame(a = 1, b = 2)
wb <- buildWorkbook(x)

y <- list(a = x, b = x, c = x)
buildWorkbook(y, asTable = TRUE)
buildWorkbook(y, asTable = TRUE, tableStyle = "TableStyleLight8")

Description

Clone a worksheet to a Workbook object

Usage

cloneWorksheet(wb, sheetName, clonedSheet)

Arguments

Value

XML tree

Author(s)

Reinhold Kainhofer

Examples

## Create a new workbook
wb <- createWorkbook("Fred")

## Add 3 worksheets
addWorksheet(wb, "Sheet 1")
cloneWorksheet(wb, "Sheet 2", clonedSheet = "Sheet 1")

## Save workbook
## Not run: 
saveWorkbook(wb, "cloneWorksheetExample.xlsx", overwrite = TRUE)

## End(Not run)

Description

Converts an Excel column label to an integer.

Usage

col2int(x)

Arguments

Examples

col2int(LETTERS)

Description

DEPRECATED! USE conditionalFormatting()

Usage

conditionalFormat(
  wb,
  sheet,
  cols,
  rows,
  rule = NULL,
  style = NULL,
  type = "expression"
)

Arguments

Details

DEPRECATED! USE conditionalFormatting()

Valid operators are "<", "<=", ">", ">=", "==", "!=". See Examples. Default style given by: createStyle(fontColour = "#9C0006", bgFill = "#FFC7CE")

Author(s)

Alexander Walker

See Also

createStyle()

Description

Add conditional formatting to cells

Usage

conditionalFormatting(
  wb,
  sheet,
  cols,
  rows,
  rule = NULL,
  style = NULL,
  type = "expression",
  ...
)

Arguments

Details

See Examples.

If type == "expression"

• style is a Style object. See createStyle()

• rule is an expression. Valid operators are "<", "<=", ">", ">=", "==", "!=".

If type == "colourScale"

• style is a vector of colours with length 2 or 3

• rule can be NULL or a vector of colours of equal length to styles

If type == "databar"

• style is a vector of colours with length 2 or 3

• rule is a numeric vector specifying the range of the databar colours. Must be equal length to style

• ...

  • showvalue If FALSE the cell value is hidden. Default TRUE.

  • gradient If FALSE colour gradient is removed. Default TRUE.

  • border If FALSE the border around the database is hidden. Default TRUE.

If type == "duplicates"

• style is a Style object. See createStyle()

• rule is ignored.

If type == "contains"

• style is a Style object. See createStyle()

• rule is the text to look for within cells

If type == "between"

• style is a Style object. See createStyle()

• rule is a numeric vector of length 2 specifying lower and upper bound (Inclusive)

If type == "blanks"

• style is a Style object. See createStyle()

• rule is ignored.

If type == "notBlanks"

• style is a Style object. See createStyle()

• rule is ignored.

If type == "topN"

• style is a Style object. See createStyle()

• rule is ignored

• ...

  • rank numeric vector of length 1 indicating number of highest values.

  • percent TRUE if you want top N percentage.

If type == "bottomN"

• style is a Style object. See createStyle()

• rule is ignored

• ...

  • rank numeric vector of length 1 indicating number of lowest values.

  • percent TRUE if you want bottom N percentage.

Author(s)

Alexander Walker, Philipp Schauberger

See Also

createStyle()

Examples

wb <- createWorkbook()
addWorksheet(wb, "cellIs")
addWorksheet(wb, "Moving Row")
addWorksheet(wb, "Moving Col")
addWorksheet(wb, "Dependent on")
addWorksheet(wb, "Duplicates")
addWorksheet(wb, "containsText")
addWorksheet(wb, "notcontainsText")
addWorksheet(wb, "beginsWith")
addWorksheet(wb, "endsWith")
addWorksheet(wb, "colourScale", zoom = 30)
addWorksheet(wb, "databar")
addWorksheet(wb, "between")
addWorksheet(wb, "topN")
addWorksheet(wb, "bottomN")
addWorksheet(wb, "containsBlanks")
addWorksheet(wb, "notContainsBlanks")
addWorksheet(wb, "logical operators")

negStyle <- createStyle(fontColour = "#9C0006", bgFill = "#FFC7CE")
posStyle <- createStyle(fontColour = "#006100", bgFill = "#C6EFCE")

## rule applies to all each cell in range
writeData(wb, "cellIs", -5:5)
writeData(wb, "cellIs", LETTERS[1:11], startCol = 2)
conditionalFormatting(wb, "cellIs",
  cols = 1,
  rows = 1:11, rule = "!=0", style = negStyle
)
conditionalFormatting(wb, "cellIs",
  cols = 1,
  rows = 1:11, rule = "==0", style = posStyle
)

## highlight row dependent on first cell in row
writeData(wb, "Moving Row", -5:5)
writeData(wb, "Moving Row", LETTERS[1:11], startCol = 2)
conditionalFormatting(wb, "Moving Row",
  cols = 1:2,
  rows = 1:11, rule = "$A1<0", style = negStyle
)
conditionalFormatting(wb, "Moving Row",
  cols = 1:2,
  rows = 1:11, rule = "$A1>0", style = posStyle
)

## highlight column dependent on first cell in column
writeData(wb, "Moving Col", -5:5)
writeData(wb, "Moving Col", LETTERS[1:11], startCol = 2)
conditionalFormatting(wb, "Moving Col",
  cols = 1:2,
  rows = 1:11, rule = "A$1<0", style = negStyle
)
conditionalFormatting(wb, "Moving Col",
  cols = 1:2,
  rows = 1:11, rule = "A$1>0", style = posStyle
)

## highlight entire range cols X rows dependent only on cell A1
writeData(wb, "Dependent on", -5:5)
writeData(wb, "Dependent on", LETTERS[1:11], startCol = 2)
conditionalFormatting(wb, "Dependent on",
  cols = 1:2,
  rows = 1:11, rule = "$A$1<0", style = negStyle
)
conditionalFormatting(wb, "Dependent on",
  cols = 1:2,
  rows = 1:11, rule = "$A$1>0", style = posStyle
)

## highlight cells in column 1 based on value in column 2
writeData(wb, "Dependent on", data.frame(x = 1:10, y = runif(10)), startRow = 15)
conditionalFormatting(wb, "Dependent on",
  cols = 1,
  rows = 16:25, rule = "B16<0.5", style = negStyle
)
conditionalFormatting(wb, "Dependent on",
  cols = 1,
  rows = 16:25, rule = "B16>=0.5", style = posStyle
)


## highlight duplicates using default style
writeData(wb, "Duplicates", sample(LETTERS[1:15], size = 10, replace = TRUE))
conditionalFormatting(wb, "Duplicates", cols = 1, rows = 1:10, type = "duplicates")

## cells containing text
fn <- function(x) paste(sample(LETTERS, 10), collapse = "-")
writeData(wb, "containsText", sapply(1:10, fn))
conditionalFormatting(wb, "containsText", cols = 1, rows = 1:10, type = "contains", rule = "A")

## cells not containing text
fn <- function(x) paste(sample(LETTERS, 10), collapse = "-")
writeData(wb, "containsText", sapply(1:10, fn))
conditionalFormatting(wb, "notcontainsText", cols = 1, 
                     rows = 1:10, type = "notcontains", rule = "A")


## cells begins with text
fn <- function(x) paste(sample(LETTERS, 10), collapse = "-")
writeData(wb, "beginsWith", sapply(1:100, fn))
conditionalFormatting(wb, "beginsWith", cols = 1, rows = 1:100, type = "beginsWith", rule = "A")


## cells ends with text
fn <- function(x) paste(sample(LETTERS, 10), collapse = "-")
writeData(wb, "endsWith", sapply(1:100, fn))
conditionalFormatting(wb, "endsWith", cols = 1, rows = 1:100, type = "endsWith", rule = "A")

## colourscale colours cells based on cell value
df <- read.xlsx(system.file("extdata", "readTest.xlsx", package = "openxlsx"), sheet = 4)
writeData(wb, "colourScale", df, colNames = FALSE) ## write data.frame

## rule is a vector or colours of length 2 or 3 (any hex colour or any of colours())
## If rule is NULL, min and max of cells is used. Rule must be the same length as style or NULL.
conditionalFormatting(wb, "colourScale",
  cols = 1:ncol(df), rows = 1:nrow(df),
  style = c("black", "white"),
  rule = c(0, 255),
  type = "colourScale"
)

setColWidths(wb, "colourScale", cols = 1:ncol(df), widths = 1.07)
setRowHeights(wb, "colourScale", rows = 1:nrow(df), heights = 7.5)

## Databars
writeData(wb, "databar", -5:5)
conditionalFormatting(wb, "databar", cols = 1, rows = 1:11, type = "databar") ## Default colours

## Between
# Highlight cells in interval [-2, 2]
writeData(wb, "between", -5:5)
conditionalFormatting(wb, "between", cols = 1, rows = 1:11, type = "between", rule = c(-2, 2))

## Top N 
writeData(wb, "topN", data.frame(x = 1:10, y = rnorm(10)))
# Highlight top 5 values in column x
conditionalFormatting(wb, "topN", cols = 1, rows = 2:11, 
 style = posStyle, type = "topN", rank = 5)#'
# Highlight top 20 percentage in column y
conditionalFormatting(wb, "topN", cols = 2, rows = 2:11, 
 style = posStyle, type = "topN", rank = 20, percent = TRUE)

## Bottom N 
writeData(wb, "bottomN", data.frame(x = 1:10, y = rnorm(10)))
# Highlight bottom 5 values in column x
conditionalFormatting(wb, "bottomN", cols = 1, rows = 2:11, 
 style = negStyle, type = "topN", rank = 5)
# Highlight bottom 20 percentage in column y
conditionalFormatting(wb, "bottomN", cols = 2, rows = 2:11, 
 style = negStyle, type = "topN", rank = 20, percent = TRUE)
 
## cells containing blanks
sample_data <- sample(c("X", NA_character_), 10, replace = TRUE)
writeData(wb, "containsBlanks", sample_data)
conditionalFormatting(wb, "containsBlanks", cols = 1, rows = 1:10, 
type = "blanks", style = negStyle)

## cells not containing blanks
sample_data <- sample(c("X", NA_character_), 10, replace = TRUE)
writeData(wb, "notContainsBlanks", sample_data)
conditionalFormatting(wb, "notContainsBlanks", cols = 1, rows = 1:10, 
type = "notBlanks", style = posStyle)

## Logical Operators
# You can use Excels logical Operators
writeData(wb, "logical operators", 1:10)
conditionalFormatting(wb, "logical operators",
  cols = 1, rows = 1:10,
  rule = "OR($A1=1,$A1=3,$A1=5,$A1=7)"
)
## Not run: 
saveWorkbook(wb, "conditionalFormattingExample.xlsx", TRUE)

## End(Not run)


#########################################################################
## Databar Example

wb <- createWorkbook()
addWorksheet(wb, "databar")

## Databars
writeData(wb, "databar", -5:5, startCol = 1)
conditionalFormatting(wb, "databar", cols = 1, rows = 1:11, type = "databar") ## Defaults

writeData(wb, "databar", -5:5, startCol = 3)
conditionalFormatting(wb, "databar", cols = 3, rows = 1:11, type = "databar", border = FALSE)

writeData(wb, "databar", -5:5, startCol = 5)
conditionalFormatting(wb, "databar",
  cols = 5, rows = 1:11,
  type = "databar", style = c("#a6a6a6"), showValue = FALSE
)

writeData(wb, "databar", -5:5, startCol = 7)
conditionalFormatting(wb, "databar",
  cols = 7, rows = 1:11,
  type = "databar", style = c("#a6a6a6"), showValue = FALSE, gradient = FALSE
)

writeData(wb, "databar", -5:5, startCol = 9)
conditionalFormatting(wb, "databar",
  cols = 9, rows = 1:11,
  type = "databar", style = c("#a6a6a6", "#a6a6a6"), showValue = FALSE, gradient = FALSE
)
## Not run: 
saveWorkbook(wb, file = "databarExample.xlsx", overwrite = TRUE)

## End(Not run)

Description

Convert excel column name to integer index e.g. "J" to 10

Usage

convertFromExcelRef(col)

Arguments

Examples

convertFromExcelRef("DOG")
convertFromExcelRef("COW")

## numbers will be removed
convertFromExcelRef("R22")

Description

Convert from excel date number to R Date type

Usage

convertToDate(x, origin = "1900-01-01", ...)

Arguments

Details

Excel stores dates as number of days from some origin day

See Also

writeData()

Examples

## 2014 April 21st to 25th
convertToDate(c(41750, 41751, 41752, 41753, 41754, NA))
convertToDate(c(41750.2, 41751.99, NA, 41753))

Description

Convert from excel time number to R POSIXct type.

Usage

convertToDateTime(x, origin = "1900-01-01", ...)

Arguments

Details

Excel stores dates as number of days from some origin date

Examples

## 2014-07-01, 2014-06-30, 2014-06-29
x <- c(41821.8127314815, 41820.8127314815, NA, 41819, NaN)
convertToDateTime(x)
convertToDateTime(x, tz = "Australia/Perth")
convertToDateTime(x, tz = "UTC")

Description

Just a wrapper of wb$copy()

Usage

copyWorkbook(wb)

Arguments

Value

Workbook

Examples

wb <- createWorkbook()
wb2 <- wb ## does not create a copy
wb3 <- copyWorkbook(wb) ## wrapper for wb$copy()

addWorksheet(wb, "Sheet1") ## adds worksheet to both wb and wb2 but not wb3

names(wb)
names(wb2)
names(wb3)

Description

Create a cell Comment object to pass to writeComment()

Usage

createComment(
  comment,
  author = Sys.getenv("USERNAME"),
  style = NULL,
  visible = TRUE,
  width = 2,
  height = 4
)

Arguments

See Also

writeComment()

Examples

wb <- createWorkbook()
addWorksheet(wb, "Sheet 1")

c1 <- createComment(comment = "this is comment")
writeComment(wb, 1, col = "B", row = 10, comment = c1)

s1 <- createStyle(fontSize = 12, fontColour = "red", textDecoration = c("BOLD"))
s2 <- createStyle(fontSize = 9, fontColour = "black")

c2 <- createComment(comment = c("This Part Bold red\n\n", "This part black"), style = c(s1, s2))
c2

writeComment(wb, 1, col = 6, row = 3, comment = c2)
## Not run: 
saveWorkbook(wb, file = "createCommentExample.xlsx", overwrite = TRUE)

## End(Not run)

Description

Create / delete a named region

Usage

createNamedRegion(wb, sheet, cols, rows, name, overwrite = FALSE)

deleteNamedRegion(wb, name)

Arguments

Details

Region is given by: min(cols):max(cols) X min(rows):max(rows)

Author(s)

Alexander Walker

See Also

getNamedRegions()

Examples

## create named regions
wb <- createWorkbook()
addWorksheet(wb, "Sheet 1")

## specify region
writeData(wb, sheet = 1, x = iris, startCol = 1, startRow = 1)
createNamedRegion(
  wb = wb,
  sheet = 1,
  name = "iris",
  rows = 1:(nrow(iris) + 1),
  cols = 1:ncol(iris)
)


## using writeData 'name' argument
writeData(wb, sheet = 1, x = iris, name = "iris2", startCol = 10)

out_file <- tempfile(fileext = ".xlsx")
## Not run: 
saveWorkbook(wb, out_file, overwrite = TRUE)

## see named regions
getNamedRegions(wb) ## From Workbook object
getNamedRegions(out_file) ## From xlsx file

## delete one
deleteNamedRegion(wb = wb, name = "iris2")
getNamedRegions(wb)

## read named regions
df <- read.xlsx(wb, namedRegion = "iris")
head(df)

df <- read.xlsx(out_file, namedRegion = "iris2")
head(df)

## End(Not run)

Description

Create a new style to apply to worksheet cells

Usage

createStyle(
  fontName = NULL,
  fontSize = NULL,
  fontColour = NULL,
  numFmt = openxlsx_getOp("numFmt", "GENERAL"),
  border = NULL,
  borderColour = openxlsx_getOp("borderColour", "black"),
  borderStyle = openxlsx_getOp("borderStyle", "thin"),
  bgFill = NULL,
  fgFill = NULL,
  halign = NULL,
  valign = NULL,
  textDecoration = NULL,
  wrapText = FALSE,
  textRotation = NULL,
  indent = NULL,
  locked = NULL,
  hidden = NULL
)

Arguments

Value

A style object

Author(s)

Alexander Walker

See Also

addStyle()

Examples

## See package vignettes for further examples

## Modify default values of border colour and border line style
options("openxlsx.borderColour" = "#4F80BD")
options("openxlsx.borderStyle" = "thin")

## Size 18 Arial, Bold, left horz. aligned, fill colour #1A33CC, all borders,
style <- createStyle(
  fontSize = 18, fontName = "Arial",
  textDecoration = "bold", halign = "left", fgFill = "#1A33CC", border = "TopBottomLeftRight"
)

## Red, size 24, Bold, italic, underline, center aligned Font, bottom border
style <- createStyle(
  fontSize = 24, fontColour = rgb(1, 0, 0),
  textDecoration = c("bold", "italic", "underline"),
  halign = "center", valign = "center", border = "Bottom"
)

# borderColour is recycled for each border or all colours can be supplied

# colour is recycled 3 times for "Top", "Bottom" & "Right" sides.
createStyle(border = "TopBottomRight", borderColour = "red")

# supply all colours
createStyle(border = "TopBottomLeft", borderColour = c("red", "yellow", "green"))

Description

Create a new Workbook object

Usage

createWorkbook(
  creator = ifelse(.Platform$OS.type == "windows", Sys.getenv("USERNAME"),
    Sys.getenv("USER")),
  title = NULL,
  subject = NULL,
  category = NULL
)

Arguments

Value

Workbook object

Author(s)

Alexander Walker

See Also

loadWorkbook()

saveWorkbook()

Examples

## Create a new workbook
wb <- createWorkbook()

## Save workbook to working directory
## Not run: 
saveWorkbook(wb, file = "createWorkbookExample.xlsx", overwrite = TRUE)

## End(Not run)

## Set Workbook properties
wb <- createWorkbook(
  creator = "Me",
  title = "title here",
  subject = "this & that",
  category = "something"
)

Description

Add Excel data validation to cells

Usage

dataValidation(
  wb,
  sheet,
  cols,
  rows,
  type,
  operator,
  value,
  allowBlank = TRUE,
  showInputMsg = TRUE,
  showErrorMsg = TRUE
)

Arguments

Examples

wb <- createWorkbook()
addWorksheet(wb, "Sheet 1")
addWorksheet(wb, "Sheet 2")

writeDataTable(wb, 1, x = iris[1:30, ])

dataValidation(wb, 1,
  col = 1:3, rows = 2:31, type = "whole",
  operator = "between", value = c(1, 9)
)

dataValidation(wb, 1,
  col = 5, rows = 2:31, 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
)

writeData(wb, 2, x = df)
dataValidation(wb, 2,
  col = 1, rows = 2:12, type = "date",
  operator = "greaterThanOrEqual", value = as.Date("2016-01-01")
)

dataValidation(wb, 2,
  col = 2, rows = 2:12, type = "time",
  operator = "between", value = df$t[c(4, 8)]
)
## Not run: 
saveWorkbook(wb, "dataValidationExample.xlsx", overwrite = TRUE)

## End(Not run)


######################################################################
## If type == 'list'
# operator argument is ignored.

wb <- createWorkbook()
addWorksheet(wb, "Sheet 1")
addWorksheet(wb, "Sheet 2")

writeDataTable(wb, sheet = 1, x = iris[1:30, ])
writeData(wb, sheet = 2, x = sample(iris$Sepal.Length, 10))

dataValidation(wb, 1, col = 1, rows = 2:31, type = "list", value = "'Sheet 2'!$A$1:$A$10")

# openXL(wb)

Description

Delete contents and styling from a cell.

Usage

deleteData(wb, sheet, cols, rows, gridExpand = FALSE)

Arguments

Author(s)

Alexander Walker

Examples

## write some data
wb <- createWorkbook()
addWorksheet(wb, "Worksheet 1")
x <- data.frame(matrix(runif(200), ncol = 10))
writeData(wb, sheet = 1, x = x, startCol = 2, startRow = 3, colNames = FALSE)

## delete some data
deleteData(wb, sheet = 1, cols = 3:5, rows = 5:7, gridExpand = TRUE)
deleteData(wb, sheet = 1, cols = 7:9, rows = 5:7, gridExpand = TRUE)
deleteData(wb, sheet = 1, cols = LETTERS, rows = 18, gridExpand = TRUE)
## Not run: 
saveWorkbook(wb, "deleteDataExample.xlsx", overwrite = TRUE)

## End(Not run)

Description

Deletes the whole column from a workbook, shifting the remaining columns to the left

Usage

deleteDataColumn(wb, sheet, col)

Arguments

Author(s)

David Zimmermann

Examples

## write some data
wb <- createWorkbook()
addWorksheet(wb, "tester")

for (i in seq(5)) {
  mat <- data.frame(x = rep(paste0(int2col(i), i), 10))
  writeData(wb, sheet = 1, startRow = 1, startCol = i, mat)
  writeFormula(wb, sheet = 1, startRow = 12, startCol = i,
               x = sprintf("=COUNTA(%s2:%s11)", int2col(i), int2col(i)))
}
deleteDataColumn(wb, 1, col = 3)
## Not run: 
saveWorkbook(wb, "deleteDataColumnExample.xlsx", overwrite = TRUE)

## End(Not run)

Description

Freeze a worksheet pane

Usage

freezePane(
  wb,
  sheet,
  firstActiveRow = NULL,
  firstActiveCol = NULL,
  firstRow = FALSE,
  firstCol = FALSE
)

Arguments

Author(s)

Alexander Walker

Examples

## Create a new workbook
wb <- createWorkbook("Kenshin")

## Add some worksheets
addWorksheet(wb, "Sheet 1")
addWorksheet(wb, "Sheet 2")
addWorksheet(wb, "Sheet 3")
addWorksheet(wb, "Sheet 4")

## Freeze Panes
freezePane(wb, "Sheet 1", firstActiveRow = 5, firstActiveCol = 3)
freezePane(wb, "Sheet 2", firstCol = TRUE) ## shortcut to firstActiveCol = 2
freezePane(wb, 3, firstRow = TRUE) ## shortcut to firstActiveRow = 2
freezePane(wb, 4, firstActiveRow = 1, firstActiveCol = "D")

## Save workbook
## Not run: 
saveWorkbook(wb, "freezePaneExample.xlsx", overwrite = TRUE)

## End(Not run)

Description

Get all entries from workbook worksheet without xml tags

Usage

get_worksheet_entries(wb, sheet)

Arguments

Value

vector of strings

Author(s)

David Breuer

Examples

## Create new workbook
wb <- createWorkbook()
addWorksheet(wb, "Sheet")
sheet <- 1

## Write dummy data
writeData(wb, sheet, c("A", "BB", "CCC"), startCol = 2, startRow = 3)
writeData(wb, sheet, c(4, 5), startCol = 4, startRow = 3)

## Get text entries
get_worksheet_entries(wb, sheet)

Description

Return the workbook default font

Returns the base font used in the workbook.

Usage

getBaseFont(wb)

Arguments

Author(s)

Alexander Walker

Examples

## create a workbook
wb <- createWorkbook()
getBaseFont(wb)

## modify base font to size 10 Arial Narrow in red
modifyBaseFont(wb, fontSize = 10, fontColour = "#FF0000", fontName = "Arial Narrow")

getBaseFont(wb)

Description

Return excel cell coordinates from (x,y) coordinates

Usage

getCellRefs(cellCoords)

Arguments

Value

Excel alphanumeric cell reference

Author(s)

Philipp Schauberger, Alexander Walker

Examples

getCellRefs(data.frame(1, 2))
# "B1"
getCellRefs(data.frame(1:3, 2:4))
# "B1" "C2" "D3"

Description

Just a wrapper of wb$getCreators() Get the names of the

Usage

getCreators(wb)

Arguments

Value

vector of creators

Author(s)

Philipp Schauberger

Examples

wb <- createWorkbook()
getCreators(wb)

Description

Return the date origin used internally by an xlsx or xlsm file

Usage

getDateOrigin(xlsxFile)

Arguments

Details

Excel stores dates as the number of days from either 1904-01-01 or 1900-01-01. This function checks the date origin being used in an Excel file and returns is so it can be used in convertToDate()

Value

One of "1900-01-01" or "1904-01-01".

Author(s)

Alexander Walker

See Also

convertToDate()

Examples

## create a file with some dates
## Not run: 
write.xlsx(as.Date("2015-01-10") - (0:4), file = "getDateOriginExample.xlsx")
m <- read.xlsx("getDateOriginExample.xlsx")

## convert to dates
do <- getDateOrigin(system.file("extdata", "readTest.xlsx", package = "openxlsx"))
convertToDate(m[[1]], do)

## End(Not run)

Description

Return a vector of named regions in a xlsx file or Workbook object

Usage

getNamedRegions(x)

Arguments

See Also

createNamedRegion()

Examples

## create named regions
wb <- createWorkbook()
addWorksheet(wb, "Sheet 1")

## specify region
writeData(wb, sheet = 1, x = iris, startCol = 1, startRow = 1)
createNamedRegion(
  wb = wb,
  sheet = 1,
  name = "iris",
  rows = 1:(nrow(iris) + 1),
  cols = 1:ncol(iris)
)


## using writeData 'name' argument to create a named region
writeData(wb, sheet = 1, x = iris, name = "iris2", startCol = 10)
## Not run: 
out_file <- tempfile(fileext = ".xlsx")
saveWorkbook(wb, out_file, overwrite = TRUE)

## see named regions
getNamedRegions(wb) ## From Workbook object
getNamedRegions(out_file) ## From xlsx file

## read named regions
df <- read.xlsx(wb, namedRegion = "iris")
head(df)

df <- read.xlsx(out_file, namedRegion = "iris2")
head(df)

## End(Not run)

Description

Returns the worksheet names within an xlsx file

Usage

getSheetNames(file)

Arguments

Value

Character vector of worksheet names.

Author(s)

Alexander Walker

Examples

getSheetNames(system.file("extdata", "readTest.xlsx", package = "openxlsx"))

Description

Returns list of style objects in the workbook

Usage

getStyles(wb)

Arguments

See Also

replaceStyle()

Examples

## load a workbook
wb <- loadWorkbook(file = system.file("extdata", "loadExample.xlsx", package = "openxlsx"))
getStyles(wb)[1:3]

Description

List Excel tables in a workbook

Usage

getTables(wb, sheet)

Arguments

Value

character vector of table names on the specified sheet

Examples

wb <- createWorkbook()
addWorksheet(wb, sheetName = "Sheet 1")
writeDataTable(wb, sheet = "Sheet 1", x = iris)
writeDataTable(wb, sheet = 1, x = mtcars, tableName = "mtcars", startCol = 10)

getTables(wb, sheet = "Sheet 1")

Description

Group a selection of columns

Usage

groupColumns(wb, sheet, cols, hidden = FALSE, level = -1)

Arguments

Details

Group columns together, with the option to hide them.

NOTE: setColWidths() has a conflicting hidden parameter; changing one will update the other.

Author(s)

Joshua Sturm, Reinhold Kainhofer

See Also

ungroupColumns() to ungroup columns. groupRows() for grouping rows.

Description

Group a selection of rows

Usage

groupRows(wb, sheet, rows, hidden = FALSE, level = -1)

Arguments

Author(s)

Joshua Sturm

See Also

ungroupRows() to ungroup rows. groupColumns() for grouping columns.

Examples

wb <- createWorkbook()
addWorksheet(wb, 'Sheet1')
addWorksheet(wb, 'Sheet2')

writeData(wb, "Sheet1", iris)
writeData(wb, "Sheet2", iris)

## create list of groups
# lines used for grouping (here: species)
grp <- list(
  seq(2, 51),
  seq(52, 101),
  seq(102, 151)
)
# assign group levels
names(grp) <- c("1","0","1")
groupRows(wb, "Sheet1", rows = grp)

# different grouping
names(grp) <- c("1","2","3")
groupRows(wb, "Sheet2", rows = grp)

# alternatively, one can call groupRows multiple times
addWorksheet(wb, 'Sheet3')
writeData(wb, "Sheet3", iris)
groupRows(wb, "Sheet3", 2:51, level = 1)
groupRows(wb, "Sheet3", 102:151, level = 1)

addWorksheet(wb, 'Sheet4')
writeData(wb, "Sheet4", iris)
groupRows(wb, "Sheet4", 2:51, level = 1)
groupRows(wb, "Sheet4", 52:101, level = 2)
groupRows(wb, "Sheet4", 102:151, level = 3)

# Nested grouping can also be achieved without explicitly given the levels
addWorksheet(wb, 'Sheet5')
writeData(wb, "Sheet5", iris)
groupRows(wb, "Sheet5", 2:151)
groupRows(wb, "Sheet5", 52:151)
groupRows(wb, "Sheet5", 102:151)

Description

Replace NULL

Usage

x %||% y

Arguments

Examples

## Not run: 
x <- NULL
x <- x %||% "none"
x <- x %||% NA

## End(Not run)

Description

Insert an image into a worksheet

Usage

insertImage(
  wb,
  sheet,
  file,
  width = 6,
  height = 3,
  startRow = 1,
  startCol = 1,
  units = "in",
  dpi = 300,
  address
)

Arguments

Author(s)

Alexander Walker

See Also

insertPlot()

Examples

## Create a new workbook
wb <- createWorkbook("Ayanami")

## Add some worksheets
addWorksheet(wb, "Sheet 1")
addWorksheet(wb, "Sheet 2")
addWorksheet(wb, "Sheet 3")
addWorksheet(wb, "Sheet 4")

## Insert images
img <- system.file("extdata", "einstein.jpg", package = "openxlsx")
insertImage(wb, "Sheet 1", img, startRow = 5, startCol = 3, width = 6, height = 5)
insertImage(wb, 2, img, startRow = 2, startCol = 2)
insertImage(wb, 3, img, width = 15, height = 12, startRow = 3, startCol = "G", units = "cm")
insertImage(wb, 4, img, address = "https://github.com/ycphs/openxlsx")

## Save workbook
## Not run: 
saveWorkbook(wb, "insertImageExample.xlsx", overwrite = TRUE)

## End(Not run)

Description

The current plot is saved to a temporary image file using dev.copy. This file is then written to the workbook using insertImage.

Usage

insertPlot(
  wb,
  sheet,
  width = 6,
  height = 4,
  xy = NULL,
  startRow = 1,
  startCol = 1,
  fileType = "png",
  units = "in",
  dpi = 300
)

Arguments

Author(s)

Alexander Walker

See Also

insertImage()

Examples

## Not run: 
## Create a new workbook
wb <- createWorkbook()

## Add a worksheet
addWorksheet(wb, "Sheet 1", gridLines = FALSE)

## create plot objects
require(ggplot2)
p1 <- qplot(mpg,
  data = mtcars, geom = "density",
  fill = as.factor(gear), alpha = I(.5), main = "Distribution of Gas Mileage"
)
p2 <- qplot(age, circumference,
  data = Orange, geom = c("point", "line"), colour = Tree
)

## Insert currently displayed plot to sheet 1, row 1, column 1
print(p1) # plot needs to be showing
insertPlot(wb, 1, width = 5, height = 3.5, fileType = "png", units = "in")

## Insert plot 2
print(p2)
insertPlot(wb, 1, xy = c("J", 2), width = 16, height = 10, fileType = "png", units = "cm")

## Save workbook
saveWorkbook(wb, "insertPlotExample.xlsx", overwrite = TRUE)

## End(Not run)

Description

Converts an integer to an Excel column label.

Usage

int2col(x)

Arguments

Examples

int2col(1:10)

Description

loadWorkbook returns a workbook object conserving styles and formatting of the original .xlsx file.

Usage

loadWorkbook(file, xlsxFile = NULL, isUnzipped = FALSE, na.convert = TRUE)

Arguments

Value

Workbook object.

Author(s)

Alexander Walker, Philipp Schauberger

See Also

removeWorksheet()

Examples

## load existing workbook from package folder
wb <- loadWorkbook(file = system.file("extdata", "loadExample.xlsx", package = "openxlsx"))
names(wb) # list worksheets
wb ## view object
## Add a worksheet
addWorksheet(wb, "A new worksheet")

## Save workbook
## Not run: 
saveWorkbook(wb, "loadExample.xlsx", overwrite = TRUE)

## End(Not run)

Description

Wrapper to create internal hyperlink string to pass to writeFormula(). Either link to external urls or local files or straight to cells of local Excel sheets.

Usage

makeHyperlinkString(sheet, row = 1, col = 1, text = NULL, file = NULL)

Arguments

See Also

writeFormula()

Examples

## Writing internal hyperlinks
wb <- createWorkbook()
addWorksheet(wb, "Sheet1")
addWorksheet(wb, "Sheet2")
addWorksheet(wb, "Sheet 3")
writeData(wb, sheet = 3, x = iris)

## External Hyperlink
x <- c("https://www.google.com", "https://www.google.com.au")
names(x) <- c("google", "google Aus")
class(x) <- "hyperlink"

writeData(wb, sheet = 1, x = x, startCol = 10)


## Internal Hyperlink - create hyperlink formula manually
writeFormula(
  wb, "Sheet1",
  x = '=HYPERLINK(\"#Sheet2!B3\", "Text to Display - Link to Sheet2")',
  startCol = 3
)

## Internal - No text to display using makeHyperlinkString() function
writeFormula(
  wb, "Sheet1",
  startRow = 1,
  x = makeHyperlinkString(sheet = "Sheet 3", row = 1, col = 2)
)

## Internal - Text to display
writeFormula(
  wb, "Sheet1",
  startRow = 2,
  x = makeHyperlinkString(
    sheet = "Sheet 3", row = 1, col = 2,
    text = "Link to Sheet 3"
  )
)

## Link to file - No text to display
writeFormula(
  wb, "Sheet1",
  startRow = 4,
  x = makeHyperlinkString(
    sheet = "testing", row = 3, col = 10,
    file = system.file("extdata", "loadExample.xlsx", package = "openxlsx")
  )
)

## Link to file - Text to display
writeFormula(
  wb, "Sheet1",
  startRow = 3,
  x = makeHyperlinkString(
    sheet = "testing", row = 3, col = 10,
    file = system.file("extdata", "loadExample.xlsx", package = "openxlsx"),
    text = "Link to File."
  )
)

## Link to external file - Text to display
writeFormula(
  wb, "Sheet1",
  startRow = 10, startCol = 1,
  x = '=HYPERLINK("[C:/Users]", "Link to an external file")'
)

## Link to internal file
x = makeHyperlinkString(text = "test.png", file = "D:/somepath/somepicture.png")
writeFormula(wb, "Sheet1", startRow = 11, startCol = 1, x = x)

## Not run: 
saveWorkbook(wb, "internalHyperlinks.xlsx", overwrite = TRUE)

## End(Not run)

Description

Merge cells within a worksheet

Usage

mergeCells(wb, sheet, cols, rows)

Arguments

Details

As merged region must be rectangular, only min and max of cols and rows are used.

Author(s)

Alexander Walker

See Also

removeCellMerge()

Examples

## Create a new workbook
wb <- createWorkbook()

## Add a worksheet
addWorksheet(wb, "Sheet 1")
addWorksheet(wb, "Sheet 2")

## Merge cells: Row 2 column C to F (3:6)
mergeCells(wb, "Sheet 1", cols = 2, rows = 3:6)

## Merge cells:Rows 10 to 20 columns A to J (1:10)
mergeCells(wb, 1, cols = 1:10, rows = 10:20)

## Intersecting merges
mergeCells(wb, 2, cols = 1:10, rows = 1)
mergeCells(wb, 2, cols = 5:10, rows = 2)
mergeCells(wb, 2, cols = c(1, 10), rows = 12) ## equivalent to 1:10 as only min/max are used
# mergeCells(wb, 2, cols = 1, rows = c(1,10)) # Throws error because intersects existing merge

## remove merged cells
removeCellMerge(wb, 2, cols = 1, rows = 1) # removes any intersecting merges
mergeCells(wb, 2, cols = 1, rows = 1:10) # Now this works

## Save workbook
## Not run: 
saveWorkbook(wb, "mergeCellsExample.xlsx", overwrite = TRUE)

## End(Not run)

Description

Modify the default font for this workbook

Usage

modifyBaseFont(wb, fontSize = 11, fontColour = "black", fontName = "Calibri")

Arguments

Details

The font name is not validated in anyway. Excel replaces unknown font names with Arial. Base font is black, size 11, Calibri.

Author(s)

Alexander Walker

Examples

## create a workbook
wb <- createWorkbook()
addWorksheet(wb, "S1")
## modify base font to size 10 Arial Narrow in red
modifyBaseFont(wb, fontSize = 10, fontColour = "#FF0000", fontName = "Arial Narrow")

writeData(wb, "S1", iris)
writeDataTable(wb, "S1", x = iris, startCol = 10) ## font colour does not affect tables
## Not run: 
saveWorkbook(wb, "modifyBaseFontExample.xlsx", overwrite = TRUE)

## End(Not run)

Description

get or set worksheet names

Usage

## S3 method for class 'Workbook'
names(x)

## S3 replacement method for class 'Workbook'
names(x) <- value

Arguments

Examples

wb <- createWorkbook()
addWorksheet(wb, "S1")
addWorksheet(wb, "S2")
addWorksheet(wb, "S3")

names(wb)
names(wb)[[2]] <- "S2a"
names(wb)
names(wb) <- paste("Sheet", 1:3)

Description

This function tries to open a Microsoft Excel (xls/xlsx) file or an openxlsx Workbook with the proper application, in a portable manner.

In Windows (c) and Mac (c), it uses system default handlers, given the file type.

In Linux it searches (via which) for available xls/xlsx reader applications (unless options('openxlsx.excelApp') is set to the app bin path), and if it finds anything, sets options('openxlsx.excelApp') to the program choosen by the user via a menu (if many are present, otherwise it will set the only available). Currently searched for apps are Libreoffice/Openoffice (soffice bin), Gnumeric (gnumeric) and Calligra Sheets (calligrasheets).

Usage

openXL(file=NULL)

Arguments

Author(s)

Luca Braglia

Examples

# file example
example(writeData)
# openXL("writeDataExample.xlsx")

# (not yet saved) Workbook example
wb <- createWorkbook()
x <- mtcars[1:6, ]
addWorksheet(wb, "Cars")
writeData(wb, "Cars", x, startCol = 2, startRow = 3, rowNames = TRUE)
# openXL(wb)

Description

openxlsx simplifies the the process of writing and styling Excel xlsx files from R and removes the dependency on Java.

Details

The openxlsx package uses global options, most to simplify formatting. These are stored in the op.openxlsx object.

openxlsx.bandedCols

FALSE

openxlsx.bandedRows

TRUE

openxlsx.borderColour

"black"

openxlsx.borders

"none"

openxlsx.borderStyle

"thin"

openxlsx.compressionLevel

"9"

openxlsx.creator

""

openxlsx.dateFormat

"mm/dd/yyyy"

openxlsx.datetimeFormat

"yyyy-mm-dd hh:mm:ss"

openxlsx.headerStyle

NULL

openxlsx.keepNA

FALSE

openxlsx.na.string

NULL

openxlsx.numFmt

NULL

openxlsx.orientation

"portrait"

openxlsx.paperSize

9

openxlsx.tabColour

"TableStyleLight9"

openxlsx.tableStyle

"TableStyleLight9"

openxlsx.withFilter

NA Whether to write data with or without a filter. If NA will make filters with writeDataTable and will not for writeData

See the Formatting vignette for examples.

Additional options

Author(s)

Maintainer: Jan Marvin Garbuszus [email protected] [contributor]

Authors:

• Philipp Schauberger [email protected]

• Alexander Walker [email protected]

Other contributors:

• Luca Braglia [contributor]

• Joshua Sturm [contributor]

• Jordan Mark Barbone [email protected] (ORCID) [contributor]

• David Zimmermann [email protected] [contributor]

• Reinhold Kainhofer [email protected] [contributor]

See Also

• vignette("Introduction", package = "openxlsx")

• vignette("formatting", package = "openxlsx")

• writeData()

• writeDataTable()

• write.xlsx()

• read.xlsx()

• op.openxlsx()

for examples

Description

See and get the openxlsx options

Usage

op.openxlsx

openxlsx_getOp(x, default = NULL)

openxlsx_setOp(x, value)

Arguments

Format

An object of class list of length 34.

Details

openxlsx_getOp() retrieves the "openxlsx" options found in op.openxlsx. If none are set (currently NULL) retrieves the default option from op.openxlsx. This will also check that the intended option is a standard option (listed in op.openxlsx) and will provide a warning otherwise.

openxlsx_setOp() is a safer way to set an option as it will first check that the option is a standard option (as above) before setting.

Examples

openxlsx_getOp("borders")
op.openxlsx[["openxlsx.borders"]]

Description

Lookup tables for font size

Usage

openxlsxFontSizeLookupTable

openxlsxFontSizeLookupTableBold

Format

A data.frame with column names corresponding to font names

Examples

data(openxlsxFontSizeLookupTable)
data(openxlsxFontSizeLookupTableBold)

Description

insert page breaks into a worksheet

Usage

pageBreak(wb, sheet, i, type = "row")

Arguments

See Also

addWorksheet()

Examples

wb <- createWorkbook()
addWorksheet(wb, "Sheet 1")
writeData(wb, sheet = 1, x = iris)

pageBreak(wb, sheet = 1, i = 10, type = "row")
pageBreak(wb, sheet = 1, i = 20, type = "row")
pageBreak(wb, sheet = 1, i = 2, type = "column")
## Not run: 
saveWorkbook(wb, "pageBreakExample.xlsx", TRUE)

## End(Not run)
## In Excel: View tab -> Page Break Preview

Description

Set page margins, orientation and print scaling

Usage

pageSetup(
  wb,
  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

Details

paperSize is an integer corresponding to:

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)

Author(s)

Alexander Walker, Joshua Sturm

Examples

wb <- createWorkbook()
addWorksheet(wb, "S1")
addWorksheet(wb, "S2")
writeDataTable(wb, 1, x = iris[1:30, ])
writeDataTable(wb, 2, x = iris[1:30, ], xy = c("C", 5))

## landscape page scaled to 50%
pageSetup(wb, sheet = 1, orientation = "landscape", scale = 50)

## portrait page scales to 300% with 0.5in left and right margins
pageSetup(wb, sheet = 2, orientation = "portrait", scale = 300, left = 0.5, right = 0.5)


## print titles
addWorksheet(wb, "print_title_rows")
addWorksheet(wb, "print_title_cols")

writeData(wb, "print_title_rows", rbind(iris, iris, iris, iris))
writeData(wb, "print_title_cols", x = rbind(mtcars, mtcars, mtcars), rowNames = TRUE)

pageSetup(wb, sheet = "print_title_rows", printTitleRows = 1) ## first row
pageSetup(wb, sheet = "print_title_cols", printTitleCols = 1, printTitleRows = 1)
## Not run: 
saveWorkbook(wb, "pageSetupExample.xlsx", overwrite = TRUE)

## End(Not run)

Description

Protect or unprotect a workbook from modifications by the user in the graphical user interface. Replaces an existing protection.

Usage

protectWorkbook(
  wb,
  protect = TRUE,
  password = NULL,
  lockStructure = FALSE,
  lockWindows = FALSE,
  type = 1L
)

Arguments

Author(s)

Reinhold Kainhofer

Examples

wb <- createWorkbook()
addWorksheet(wb, "S1")
protectWorkbook(wb, protect = TRUE, password = "Password", lockStructure = TRUE)
## Not run: 
saveWorkbook(wb, "WorkBook_Protection.xlsx", overwrite = TRUE)

## End(Not run)
# Remove the protection
protectWorkbook(wb, protect = FALSE)
## Not run: 
saveWorkbook(wb, "WorkBook_Protection_unprotected.xlsx", overwrite = TRUE)

## End(Not run)

Description

Protect or unprotect a worksheet from modifications by the user in the graphical user interface. Replaces an existing protection.

Usage

protectWorksheet(
  wb,
  sheet,
  protect = TRUE,
  password = NULL,
  lockSelectingLockedCells = NULL,
  lockSelectingUnlockedCells = NULL,
  lockFormattingCells = NULL,
  lockFormattingColumns = NULL,
  lockFormattingRows = NULL,
  lockInsertingColumns = NULL,
  lockInsertingRows = NULL,
  lockInsertingHyperlinks = NULL,
  lockDeletingColumns = NULL,
  lockDeletingRows = NULL,
  lockSorting = NULL,
  lockAutoFilter = NULL,
  lockPivotTables = NULL,
  lockObjects = NULL,
  lockScenarios = NULL
)

Arguments