0

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!

Kevin Arseneau
  • 6,186
  • 1
  • 21
  • 40
Ryan
  • 11
  • 5

1 Answers1

0

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)
JeanVuda
  • 1,738
  • 14
  • 29
  • Very close! Thanks a lot for your help. The output on this specific code works, other than row 1 has headings "V1" to "V9" in sequence - is there a way to exclude this row? Also, The code doesn't work when I go beyond the range 1:16. For example, when I use 1:216. I would like the outputs to be placed across columns from A to 'n' while the max rows should be 21, like in my example data output above. Thanks again! – Ryan Jan 23 '18 at 14:18
  • You need to edit the first line to `aa<-data.frame(matrix(c(1:216), byrow=F, ncol=54))` (or change it to match your needs!) and fiddle with how you stack the results in `fst<-do.call("rbind", list(mm[[1]],"",mm[[2]])); snd<-do.call("rbind", list(mm[[3]],"",mm[[4]]))`. – JeanVuda Jan 23 '18 at 15:37
  • Thanks! I'm still having problems stacking the results in the 'fst' and 'snd' do.call functions. I'm having trouble stacking the output as in my example output above. This is especially difficult for me because the number range will change every time I run this code (e.g., 1:16, 1:216, etc.). Any idea on how I can modify this so the range will change but the output structure will be the same with n columns (depending on range) x 21 rows (as in above example)? I also still cannot figure out how to remove the headings "V1" to "V9" (and so forth) in the output. Very sorry for all the questions! – Ryan Feb 09 '18 at 16:28