Search code examples
rdata-analysis

How do I loop the merging of Excel cells every 6 cells using R?

I'm trying to use R to automatically merge existing cells within an imported excel workbook.

In the first row, I want to merge 6 cells at a time until the end of the table.

If I want to do the 6-cell merging N number of times, I have to repeat the mergeCells function N times as well.

Let's say for N=8

mergeCells(wb, 1, cols = 7:12, rows = 1:1)

mergeCells(wb, 1, cols = 13:18, rows = 1:1)

mergeCells(wb, 1, cols = 19:24, rows = 1:1)

mergeCells(wb, 1, cols = 25:30, rows = 1:1)

mergeCells(wb, 1, cols = 31:36, rows = 1:1)

mergeCells(wb, 1, cols = 37:42, rows = 1:1)

mergeCells(wb, 1, cols = 43:48, rows = 1:1)

mergeCells(wb, 1, cols = 49:54, rows = 1:1)

mergeCells is repeated 8 times.

The last input:

mergeCells(wb, 1, cols = 49:54, rows = 1:1)

in algebraic terms is bascially

mergeCells(wb, 1, cols = (6*N+1):(6*N+6), rows = 1:1)

Instead of manually inputting the fields as above, is there a way to make it automatically repeated for any arbitrary N (i.e. N can be any number at any given time)?

Solution

  • You can use a for loop.

    library(openxlsx)

wb <- createWorkbook()
addWorksheet(wb, "Sheet 1")
 
for(N in 1:8){
    mergeCells(wb, 1, cols = (6*N+1):(6*N+6), rows = 1:1)
}