Using SWITCH statement in formula adds an @ in Excel

[jhorzek]

Hi,

Thank you very much for the great package! I am currently trying to implement some automations and ran into a an issue when using SWITCH in a formula within a data table. Here is a minimal example:

library(openxlsx2)

wb <- wb_workbook()
example_data <-
  data.frame(
    sales_price = c(20, 30, 40),
    COGS = c(5, 11, 13),
    sales_quantity = c(1, 2, 3)
  )

## base R method
example_data$switch1       <- "=SWITCH(daily_sales[[#This Row],[sales_price]], 20, 1, 0)"
example_data$switch2       <- "=IF(daily_sales[[#This Row],[sales_price]] > 0, SWITCH(daily_sales[[#This Row],[sales_price]], 20, 1, 0), NA())"
class(example_data$switch1)       <- c(class(example_data$switch1),       "formula")
class(example_data$switch2)       <- c(class(example_data$switch2),       "formula")
wb$
  add_worksheet("Daily Sales")$
  add_data_table(
    x           = example_data,
    table_style = "TableStyleMedium2",
    table_name  = "daily_sales",
  )
wb$save(file = "switch.xlsx",
        overwrite = TRUE)

When opening the file in Excel, I get #NAME? errors. This is due to an @ that is added at the beginning of the SWITCH statement. That is, the formula in Excel is =@SWITCH([@[sales_price]], 20, 1, NA()) instead of =SWITCH([@[sales_price]], 20, 1, NA()). When using the SWITCH in an IF statement, the @ gets pushed in front of the IF, which also results in an error.

I am using R 4.4.0 on Windows 11 with openxlsx2 version 1.11.0.9000.

Best,
Jannik

[JanMarvin]

Hi @jhorzek , thank you for your kind words!

The error is related to the following:

As can be seen the list of Excel functions, SWITCH() is a modern Excel formula (the label MS365 gives the hint)

To use this function you have to add the prefix _xlfn. (see here). There is another nasty thing:
The function is a cell metadata function - basically an array function with a special tag to hide that it is an array function. You can use the code below, which will make the function work as expected, but will show its array roots.

I haven't really researched cm-functions, it is possible to write them with wb_add_formula(cm = TRUE) but they print a warning since their initial implementation and unfortunately it is not yet possible to add them via the class()-approach you want to use in the code below.

library(openxlsx2)

wb <- wb_workbook()
example_data <-
  data.frame(
    sales_price = c(20, 30, 40),
    COGS = c(5, 11, 13),
    sales_quantity = c(1, 2, 3)
  )

## base R method
example_data$switch1       <- "=_xlfn.SWITCH(daily_sales[[#This Row],[sales_price]], 20, 1, 0)"
example_data$switch2       <- "=IF(daily_sales[[#This Row],[sales_price]] > 0, _xlfn.SWITCH(daily_sales[[#This Row],[sales_price]], 20, 1, 0), NA())"
class(example_data$switch1)       <- c(class(example_data$switch1),       "array_formula")
class(example_data$switch2)       <- c(class(example_data$switch2),       "array_formula")
wb$
  add_worksheet("Daily Sales")$
  add_data_table(
    x           = example_data,
    table_style = "TableStyleMedium2",
    table_name  = "daily_sales",
  )
if (interactive()) wb$open()

PS: I move this question to discussion, because it is working as expected. The only way to avoid this, would be to always try to keep up with the Excel formula list and parse the formulas ... something I do not really desire.

[JanMarvin]

We create the cell metadata information here:

openxlsx2/R/write.R

Lines 1240 to 1270 in 0b247cd

	if (cm) {
	# need to set cell metadata in wb$metadata
	if (is.null(wb$metadata)) {
	wb$append("Content_Types", "<Override PartName=\"/xl/metadata.xml\" ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.sheetMetadata+xml\"/>")
	wb$metadata <- # danger danger no clue what this means!
	xml_node_create(
	"metadata",
	xml_attributes = c(
	xmlns = "http://schemas.openxmlformats.org/spreadsheetml/2006/main",
	"xmlns:xda" = "http://schemas.microsoft.com/office/spreadsheetml/2017/dynamicarray"
	),
	xml_children = read_xml(
	"<metadataTypes count=\"1\">
	<metadataType name=\"XLDAPR\" minSupportedVersion=\"120000\" copy=\"1\" pasteAll=\"1\" pasteValues=\"1\" merge=\"1\" splitFirst=\"1\" rowColShift=\"1\" clearFormats=\"1\" clearComments=\"1\" assign=\"1\" coerce=\"1\" cellMeta=\"1\"/>
	</metadataTypes>
	<futureMetadata name=\"XLDAPR\" count=\"1\">
	<bk>
	<extLst>
	<ext uri=\"{bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}\">
	<xda:dynamicArrayProperties fDynamic=\"1\" fCollapsed=\"0\"/>
	</ext>
	</extLst>
	</bk>
	</futureMetadata>,
	<cellMetadata/>",
	pointer = FALSE
	)
	)
	}

But as said, it worked for a few examples, but haven't looked into it further (and ... it's not yet back on the agenda 😃 ).

[jhorzek]

Oh wow, thank you very much for this super swift response! The _xlfn prefix solved the issue perfectly :)