wb<-wb_workbook()$add_worksheet()$add_data(x=head(cars))$add_formula(x="SUM(A2, B2)", dims="D2")$add_formula(x="A2 + B2", dims="D3")# wb$open()

wb<-wb_workbook()$add_worksheet()$add_data(x=head(cars))$add_formula(x="A2:A7 * B2:B7", dims="C2:C7", array=TRUE)# wb$open()

m1<-matrix(1:6, ncol=2)m2<-matrix(7:12, nrow=2)wb<-wb_workbook()$add_worksheet()$add_data(x=m1)$add_data(x=m2, dims=wb_dims(from_col=4))$add_formula(x="MMULT(A2:B4, D2:F3)", dims="H2:J4", array=TRUE)# wb$open()

# we expect to find this in D1:E1# coef(lm(head(cars)))wb<-wb_workbook()$add_worksheet()$add_data(x=head(cars))$add_formula(x="LINEST(A2:A7, B2:B7, TRUE)", dims="D2:E2", array=TRUE)# wb$open()

wb<-wb_workbook()$add_worksheet()$add_data(x=head(cars))$add_formula(x="SUM(ABS(A2:A7))", dims="D2", cm=TRUE)#> Warning in write_data2(wb = wb, sheet = sheet, data = x, name = name, colNames#> = colNames, : modifications with cm formulas are experimental. use at own risk# wb$open()

dataTable formula differences

Given a basic table like the above, a similarly basic formula fortotal_sales would be “= A2 * C2” with the row valuechanging at each row.

An implementation for this formula usingwb_add_formula() would look this (taken from currentdocumentation) lets say we’ve read in the data and assigned it to thetablecompany_sales

## creating example datacompany_sales<-data.frame(    sales_price=c(20,30,40),    COGS=c(5,11,13),    sales_quantity=c(1,2,3))## write in the formulacompany_sales$total_sales<-paste(paste0("A",1:3+1L),paste0("C",1:3+1L), sep=" * ")## add the formula classclass(company_sales$total_sales)<-c(class(company_sales$total_sales),"formula")## write a workbookwb<-wb_workbook()$add_worksheet("Total Sales")$add_data_table(x=company_sales)

Then we create the workbook, worksheet, and usewb_add_data_table().

One of the advantages of the open xmldataTable syntaxis that we don’t have to specify row numbers or columns as letters. Thetable also grows dynamically, adding new rows as new data is appendedand extending formulas to the new rows. ThesedataTablehave named columns that we can use instead of letters. When writing theformulas within thedataTable we would use the followingsyntax[@[column_name]] to reference the current row. Sothe “total_sales” formula written in open xml indataTablewould look like this;=[@[sales_price]] * [@[sales_quantity]]

If we are writing the formula outside of thedataTablewe have to reference the table name. In this case lets say the tablename is ‘daily_sales’=daily_sales[@[sales_price]] * daily_sales[@[sales_quantity]]

However, if we were to pass this as the text for the formula to bewritten it would cause an error because the syntax that open xmlrequires for selecting the current row is different.

In open xml thedataTable formula looks like this:

<calculatedColumnFormula>  daily_sales[[#This Row],[sales_price]]*daily_sales[[#ThisRow],[sales_quantity]]</calculatedColumnFormula>

Now we can see that open xml replaces[@[sales_price]]withdaily_sales[[#This Row],[sales_price]] We must thenuse this syntax when writing formulas fordataTable

## Because we want the `dataTable` formula to propagate down the entire column of the data## we can assign the formula by itself to any column and allow that single string to be repeated for each row.## creating example dataexample_data<-data.frame(    sales_price=c(20,30,40),    COGS=c(5,11,13),    sales_quantity=c(1,2,3))## base R methodexample_data$gross_profit<-"daily_sales[[#This Row],[sales_price]] - daily_sales[[#This Row],[COGS]]"example_data$total_COGS<-"daily_sales[[#This Row],[COGS]] * daily_sales[[#This Row],[sales_quantity]]"example_data$total_sales<-"daily_sales[[#This Row],[sales_price]] * daily_sales[[#This Row],[sales_quantity]]"example_data$total_gross_profit<-"daily_sales[[#This Row],[total_sales]] - daily_sales[[#This Row],[total_COGS]]"class(example_data$gross_profit)<-c(class(example_data$gross_profit),"formula")class(example_data$total_COGS)<-c(class(example_data$total_COGS),"formula")class(example_data$total_sales)<-c(class(example_data$total_sales),"formula")class(example_data$total_gross_profit)<-c(class(example_data$total_gross_profit),"formula")

wb$add_worksheet("Daily Sales")$add_data_table(    x=example_data,    table_style="TableStyleMedium2",    table_name="daily_sales")

And if we open the workbook to view the table we created we can seethat the formula has worked.

We can also see that it has replaced[#This Row] with@.

For completion, the formula as we wrote it appears as;

#### sum dataTable exampleswb$add_worksheet("sum_examples")### Note: dataTable formula do not need to be used inside of dataTables. dataTable formula are for referencing the data within the dataTable.### Note: dataTable formula do not need to be used inside of dataTables. dataTable formula are for referencing the data within the dataTable.sum_examples<-data.frame(  description=c("sum_sales_price","sum_product_Price_Quantity"),  formula=c("",""))wb$add_data(x=sum_examples)# add formulaswb$add_formula(x="sum(daily_sales[[#Data],[sales_price]])", dims="B2")wb$add_formula(x="sum(daily_sales[[#Data],[sales_price]] * daily_sales[[#Data],[sales_quantity]])", dims="B3", array=TRUE)#### dataTable referencingwb$add_worksheet("dt_references")### Adding the headers by themselves.wb$add_formula(  x="daily_sales[[#Headers],[sales_price]:[total_gross_profit]]",  dims="A1:G1",  array=TRUE)### Adding the raw data by reference and selecting them directly.wb$add_formula(  x="daily_sales[[#Data],[sales_price]:[total_gross_profit]]",  start_row=2,  dims="A2:G4",  array=TRUE)# wb$open()