Writing an excel doc with a subset of my data in R. Currently the code is excessively long

Question

I'm fairly new to R. I have used the 'r2excel' package to create an excel workbook from a subset of my R data. This should have 3 sheets for the three different subsets. What I have written works fine, but its a lot of code and is very clunky, just re written three times.

If there is a better way of framing my question/best practice tips, I'd love to learn.

# Create an Excel workbook. 
filename <- "validitycheck.xlsx" #The name of the file that will be saved
wb <- createWorkbook(type="xlsx") #Creating an excel workbook
# Create a sheet in that workbook to contain the data table
sheet1 <- createSheet(wb, sheetName = "prime1")
sheet2 <- createSheet(wb, sheetName = "prime2")
sheet3 <- createSheet(wb, sheetName = "prime3")
 
# Add header for sheet 1
xlsx.addHeader(wb, sheet1, value="Prime 1",level=1, 
 color="black", underline=1)
xlsx.addLineBreak(sheet1, 1)
# Add header for sheet 2
xlsx.addHeader(wb, sheet2, value="Prime 2",level=1, 
 color="black", underline=1)
xlsx.addLineBreak(sheet2, 1)
# Add header for sheet 3
xlsx.addHeader(wb, sheet3, value="Prime 3",level=1, 
 color="black", underline=1)
xlsx.addLineBreak(sheet3, 1)
# Add a paragraph : Author
author=paste("Author : Gabriella. \n",
 "20% randomly chosen validity check.",
 "\n Source: filename", sep="")
#Add author for sheet 1
xlsx.addParagraph(wb, sheet1,value=author, isItalic=TRUE, colSpan=5, 
 rowSpan=4, fontColor="darkgray", fontSize=14)
#Add author para for sheet 2
xlsx.addParagraph(wb, sheet2,value=author, isItalic=TRUE, colSpan=5, 
 rowSpan=4, fontColor="darkgray", fontSize=14)
#Add author para for sheet 3
xlsx.addParagraph(wb, sheet3,value=author, isItalic=TRUE, colSpan=5, 
 rowSpan=4, fontColor="darkgray", fontSize=14)
xlsx.addLineBreak(sheet1, 3)
xlsx.addLineBreak(sheet2, 3)
xlsx.addLineBreak(sheet3, 3)
# Add table : add a data frame for sheet 1
xlsx.addHeader(wb, sheet1, value="Validty for prime 1")
xlsx.addLineBreak(sheet1, 1)
xlsx.addTable(wb, sheet1, sample_prime1,
 fontColor="darkblue", fontSize=14,
 rowFill=c("white", "lightblue"),
 startCol=2, 
 row.names=FALSE, 
 col.names = TRUE)
# Add table : add a data frame for sheet 2
xlsx.addHeader(wb, sheet2, value="Validty for prime 2")
xlsx.addLineBreak(sheet2, 1)
xlsx.addTable(wb, sheet2, sample_prime2,
 fontColor="darkblue", fontSize=14,
 rowFill=c("white", "lightblue"),
 startCol=2, 
 row.names=FALSE, 
 col.names = TRUE)
# Add table : add a data frame for sheet 3
xlsx.addHeader(wb, sheet3, value="Validty for prime 3")
xlsx.addLineBreak(sheet3, 1)
xlsx.addTable(wb, sheet3, sample_prime3,
 fontColor="darkblue", fontSize=14,
 rowFill=c("white", "lightblue"),
 startCol=2, 
 row.names=FALSE, 
 col.names = TRUE)
# save the workbook to an Excel file
saveWorkbook(wb, filename, password = "secret")
xlsx.openFile(filename)# View the file
```

• pacmaninbw: Welcome to Code Review. You have done a pretty good job of asking your question, if you want to improve it more or for future use see the asking section of our help center.

Answer 1

@Duck from Stack overflow provided the answer. Pasting it here. Its is essentially writing a function:

library(r2excel)
#Data
sample_prime1 <- 1:4
sample_prime2 <- 10:14
sample_prime3 <- 22:26
#Store in a list
List <- list(sample_prime1,sample_prime2,sample_prime3)
#Function
myfun <- function(wb,name,df) {
 # Create object
 sheet <- createSheet(wb, sheetName = name)
 # Add headers
 xlsx.addHeader(wb, sheet, value=paste0('Validty for prime ',name))
 xlsx.addLineBreak(sheet, 1)
 xlsx.addTable(wb, sheet, data.frame(df),
 fontColor="darkblue", fontSize=14,
 rowFill=c("white", "lightblue"),
 startCol=2, 
 row.names=FALSE, 
 col.names = FALSE)
}
#Create workbook
filename <- "validitycheck.xlsx" 
wb <- createWorkbook(type="xlsx")
#Loop
#Vector for names
vnames <- paste0('prime',1:length(List))
#Code
for(i in 1:length(List))
{
 myfun(wb,vnames[i],List[i])
}