openxlsx2 (version 0.7)

write_formula: Write a character vector as an Excel Formula

Description

Write a a character vector containing Excel formula to a worksheet.

Usage

write_formula(
  wb,
  sheet,
  x,
  startCol = 1,
  startRow = 1,
  dims = rowcol_to_dims(startRow, startCol),
  array = FALSE,
  cm = FALSE,
  applyCellStyle = TRUE,
  removeCellStyle = FALSE
)

Arguments

wb

A Workbook object containing a worksheet.

sheet

The worksheet to write to. Can be the worksheet index or name.

x

A character vector.

startCol

A vector specifying the starting column to write to.

startRow

A vector specifying the starting row to write to.

dims

Spreadsheet dimensions that will determine startCol and startRow: "A1", "A1:B2", "A:B"

array

A bool if the function written is of type array

cm

A bool if the function is of type cm (array with hidden curly braces)

applyCellStyle

apply styles when writing on the sheet

removeCellStyle

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

Details

Currently only the English version of functions are supported. Please don't use the local translation. The examples below show a small list of possible formulas:

• SUM(B2:B4)

• AVERAGE(B2:B4)

• MIN(B2:B4)

• MAX(B2:B4)

• ...

See Also

write_data()

Examples

## There are 3 ways to write a formula

wb <- wb_workbook()
wb$add_worksheet("Sheet 1")
wb$add_data("Sheet 1", x = iris)

## SEE int2col() to convert int to Excel column label

## 1. -  As a character vector using write_formula

v <- c("SUM(A2:A151)", "AVERAGE(B2:B151)") ## skip header row
write_formula(wb, sheet = 1, x = v, startCol = 10, startRow = 2)
write_formula(wb, 1, x = "A2 + B2", startCol = 10, startRow = 10)


## 2. - As a data.frame column with class "formula" using write_data

df <- data.frame(
  x = 1:3,
  y = 1:3,
  z = paste(paste0("A", 1:3 + 1L), paste0("B", 1:3 + 1L), sep = " + "),
  z2 = sprintf("ADDRESS(1,%s)", 1:3),
  stringsAsFactors = FALSE
)

class(df$z) <- c(class(df$z), "formula")
class(df$z2) <- c(class(df$z2), "formula")

wb$add_worksheet("Sheet 2")
wb$add_data(sheet = 2, x = df)



## 3. - As a vector with class "formula" using write_data

v2 <- c("SUM(A2:A4)", "AVERAGE(B2:B4)", "MEDIAN(C2:C4)")
class(v2) <- c(class(v2), "formula")

wb$add_data(sheet = 2, x = v2, startCol = 10, startRow = 2)

## 4. - Writing internal hyperlinks

wb <- wb_workbook()
wb$add_worksheet("Sheet1")
wb$add_worksheet("Sheet2")
write_formula(wb, "Sheet1", x = '=HYPERLINK("#Sheet2!B3", "Text to Display - Link to Sheet2")')

## 5. - Writing array formulas

set.seed(123)
df <- data.frame(C = rnorm(10), D = rnorm(10))

wb <- wb_workbook()
wb <- wb_add_worksheet(wb, "df")

wb$add_data("df", df, startCol = "C")

write_formula(wb, "df", startCol = "E", startRow = "2",
             x = "SUM(C2:C11*D2:D11)",
             array = TRUE)