openxlsx2 (version 0.8)

write_formula: Write a character vector as an Excel Formula

Description

Write a a character vector containing Excel formula to a worksheet. Use wb_add_formula() or add_formula() in new code

Usage

write_formula(
  wb,
  sheet,
  x,
  dims = wb_dims(start_row, start_col),
  start_col = 1,
  start_row = 1,
  array = FALSE,
  cm = FALSE,
  apply_cell_style = TRUE,
  remove_cell_style = FALSE,
  ...
)

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.

dims

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

start_col

A vector specifying the starting column to write to.

start_row

A vector specifying the starting row to write to.

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)

apply_cell_style

apply styles when writing on the sheet

remove_cell_style

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

...

additional arguments

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)

• ...

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)