Applying formula to each column R openxlsx

0
votes

I've an excel workbook with multiple columns which have dates as the column name. Is there a way to apply formulas to each column in openxlsx. I need a way to dynamically populate each column with the appropriate excel formula as the no of columns is huge.

This is sheet 1. Please Note: The letter at the top of the columns names signify the excel column names and the numbers at the start of each row are the excel row numbers.

      A          B           C          D
   Product   1/1/2020     1/1/2019   1/1/2018      
1     1          1          10          1          
2     2          2          20          1           
3     3          3          30          10            
4     4          2          10          5            
5     5          4           5          10

This is sheet 2

      A         B          C            D           E
   Product    Skill     1/1/2020     1/1/2019   1/1/2018      
1     1         W2         1            1          10          
2     2         W45        20           1           0           
3     3         W0         40           5           0            
4     4         W1         50           5           1            
5     5         W2         2            2           1

This is the resultant sheet. The excel formula for cell C1 "='Sheet1'!B1*'Sheet2'!C1"

The excel formula for cell D1 "='Sheet1'!C1*'Sheet2'!D1"

The excel formula for cell E1 "='Sheet1'!D1*'Sheet2'!E1"

       A         B          C            D          E
    Product    Skill     1/1/2020     1/1/2019   1/1/2018      
1      1         W2         1           10          10          
2      2         W45        40          20           0           
3      3         W0         120         150          0            
4      4         W1         100         50           5            
5      5         W2         8           10          10

As I'm new to openxlsx is there a way to do this using the write_formula function. I want to automate the process as the number of columns is very large. Thanks.

2
rexcelexcel-formulaopenxlsx

2 Answers

0
votes

I try to give a solution. You can check whether it is acceptable.

library(openxlsx)
st1 <- read.xlsx("example.xlsx",colNames = FALSE,sheet = 1)
st2 <- read.xlsx("example.xlsx",colNames = FALSE,sheet = 2)
st3 = st2
st3[3:5] = st1[2:4] * st2[3:5]
# This means the multiply operation of two sheets
# st4$X3 = st1$X2 * st2$X3
# st4$X4 = st1$X3 * st2$X4
# st4$X5 = st1$X4 * st2$X5
0
votes

I think you want to wrap writeFormula() in a for loop.

library(openxlsx)
library(glue) #I find this easier than paste(), ymmv
wb <- your_workbook
sheetname <- sheet_you_are_working_on

col_start <- some_number
col_end   <- another_number

row_start <- third_number
row_end   <- fourth_number

for(c in col_start:col_end}
  writeFormula(wb, sheet_name, 
               glue("='Sheet1'!{col}{row}*'Sheet2'!{col}{row}",
                    col = letters[c], #might need to make your own letters if more than 24 columns
                    row = row_start : row_end
                     )
                )
}
#example with numbers
for(c in 4:5}
  writeFormula(wb, sheet_name, 
               glue("='Sheet1'!{col}{row}*'Sheet2'!{col}{row}",
                    col = letters[c], #"C" and "D" are within letters()
                    row = 1:5
                     )
                )
}

If you want, you can get even fancier across sheets by having a second loop that defines your sheet names.