CODEWITHSUNDEEP
rxlsx
grrgrrbla
grrgrrbla

Reputation: 2589

how to automatically rename a sheetname in a for loop (xlsx)

I am trying to construct a loop which automatically subsets a dataset, makes alot of computations, and prints the results for each group as seperate sheets to a xlsx-workbook-file. It should work along the lines of the following code:

library(xlsx)
clist <- c("group1", "group2", "group3")
for (i in clist) {
datasubset <- filter(data, group == i)

mean <- mean(datasubset)

results <- createWorkbook()
sheet.i <- createSheet(results, sheetName = paste("results", i, sep=" "))
addDataFrame(mean, sheet.i, startRow=1, startColumn=1, row.names = FALSE)
}

the last 2 lines obviously dont work. The question is how do I get the sheet.i to be named according to value of i. so for instance: sheet.group1, sheet.group2, etc. So with what code should "sheet.i" be substituted with? or is there another way to achieve my goal within a different package or a different approach?

Upvotes: 1

Views: 2112

Answers (1)

LyzandeR
LyzandeR

Reputation: 37879

Lot's of things need fixing in the above code for this to work but I hope this will help solve them:

library(xlsx)
x <- runif(900)
group <- rep(c('group1','group2','group3'),300)
df <- data.frame(x,group)

wb <- createWorkbook() #make workbook outside the loop. You only need 1 workbook
for ( i in c('group1','group2','group3')) {
  datasubset <- subset(df, group == i)

  mean <- data.frame(mean(datasubset[['x']])) #needs to be a dataframe to use addDataFrame

  sheet <- createSheet(wb , sheetName = sprintf('sheet.%s',i)) #make each sheet

  addDataFrame(mean, sheet, startRow=1, startColumn=1, row.names = FALSE) #add dataframe 'mean' to each sheet
}
saveWorkbook(wb, 'test.xlsx') #don't forget to save

and you got your excel with all your sheets!

This is my output:

enter image description here

Upvotes: 1

Related Questions