Hi All,
I have to read data from the Source table (excel file) and do some calculations on the data and send the data to multiple sheets in an excel file.
I was able to do it using FOR loop, but, I was asked to include an Array where the operation will work only on the sheets specified in the array.
Can anyone please help me out with how to use array in this case?
Below is the code and sample source data, I was able to read the data and send the data related to each ID to multiple sheets using FOR loop,
Source Data in excel file
| Id | Ename | Sal |
|---|---|---|
| 101 | John | 2000 |
| 102 | Max | 3000 |
| 103 | Ted | 1000 |
Code
library(tidyverse)
library(readxl)
library(openxlsx)
readxl_example()
df <- read_excel("Employee.xlsx")
df <- df %>%
mutate(new_column = Sal*10)
output <- split(df, df$Id)
wb<- createWorkbook()
for (i in 1:length(output)){
addWorksheet(wb, sheetName = names(output[i]))
writeData (wb, sheet= names(output[i]), x = output[[i]])
}
saveWorkbook(wb,"test.xlsx", overwrite = TRUE)