Search code examples
rr-xlsx

R script for exporting custom list of numbers and letters into Excel

To help with some regular label-making and printing I need to do, I am looking to write a script that allows me to enter a range of sequential numbers (some with string identifiers) that I can export with a specific format to Excel. For example, if I entered the range '1:16', I am looking for an output in Excel exactly as:

enter image description here

For each unique sequential number (i.e., 1 to 16) the first five rows must be labeled with a 'U", the next three rows with an 'F' and the last two rows must be the number alone. The final exported matrix will be n columns x 21 rows, where n will vary depending on the number range I enter.

My main problem is in writing to Excel. I can't find out how to customize this output and write to specific rows and columns as in the example above. I am limited to 'openxlsx' since I work on a corporate secure workstation. Here is what I have so far:

Unfiltered <- rep (1 : 16, each = 5)
UnfilteredLabel <- paste (Unfiltered, "U")
UnfilteredLabel

Filtered <- rep (1 : 16, each = 3)
FilteredLabel <- paste (Filtered, "F")
FilteredLabel

Number <- rep (1 : 16, each = 2)
NoLabel <- paste (Number, "")
NoLabel

install.packages("openxlsx")
library(openxlsx)

write.xlsx(UnfilteredLabel, 'test.xlsx', append = TRUE)
write.xlsx(FilteredLabel, 'test.xlsx', append = TRUE)
write.xlsx(NoLabel, 'test.xlsx', append = TRUE)

Any help you may have would be very appreciated, thanks in advance!

Solution

  • Not the cleanest code. Give this a try:

    aa<-data.frame(matrix(c(1:16), byrow=F, ncol=4))
mm<-lapply(aa,function(x){
  ac<-mapply(rep,list(x), list(5,3,2))
  xy<-mapply(paste,ac,list("U","F",""), sep="")  
  xy<-matrix(unlist(xy), byrow=T,ncol=4)
})
fst<-do.call("rbind", list(mm[[1]],"",mm[[2]]))
snd<-do.call("rbind", list(mm[[3]],"",mm[[4]]))
mm<-do.call("cbind", list(fst,"",snd))
openxlsx::write.xlsx(mm, 'test.xlsx', append = TRUE)