CODEWITHSUNDEEP
ropenxlsx
Olivier7121
Olivier7121

Reputation: 333

openxlsx - Merge 2 cells on several rows in one call?

Is it currently possible to merge, say 2 cells (same row, consecutive columns), on several rows in one (or at least less than the total number of rows considered) call to mergeCells?

[EDIT]: Reproducible example:

library(openxlsx)

OutputFolder <- file.path(".", "Output")
if(!dir.exists(OutputFolder))   dir.create(OutputFolder)

OutputFile <- file.path(OutputFolder, "Reprex_Openxlsx_mergeCells.xlsx")

Workbook4Export <- createWorkbook()

addWorksheet(wb = Workbook4Export, sheetName = "Tab_1", zoom = 80, gridLines = FALSE)

for(i in 1:100000)
{
  mergeCells(wb = Workbook4Export, sheet = "Tab_1", cols = 1 + 0:1, rows = i)
}

Is there an alternative to this very long loop in terms of number of calls to mergeCells? For instance with the natural GUI of Excel, one can copy the whole format of a row of 2 columns (here 2 merged columns on the first row) and then paste on the other 99 000 subsequent rows with just a click or two to have each and every of these rows with the 2 columns merged.

This is what I am ultimately looking for within openxlsx.

Upvotes: 0

Views: 467

Answers (2)

Lazy
Lazy

Reputation: 148

The issue here is not actually the looping. mergeCells essentially just sets wb$worksheets[[wb$validateSheet(sheet)]]$mergeCells and add xml statements of the form <mergeCell ref="from:to" />. The issue with performance arises from a check whether the current merge overlaps with any previous ones. This gives the whole thing a complexity of θ(n) for adding a merge to n previous merges or θ(n²) for adding n merges in total.

The trick here is to avoid invoking mergeCells but rather set the merges manually:

corner1 <- openxlsx::getCellRefs(data.frame(1:100000, 1))
corner2 <- openxlsx::getCellRefs(data.frame(1:100000, 2))
Workbook4Export$worksheets[[Workbook4Export$validateSheet("Tab_1")]]$mergeCells <-
    sprintf("<mergeCell ref=\"%s:%s\"/>", corner1, corner2)

Upvotes: 1

Peter
Peter

Reputation: 12699

I'm not sure there is anything "wrong" with the loop approach you put forward in the question.

It seems to be in the nature of things with openxlsx that on a one-to-one comparison there is a bit more typing to generate code compared with a one-off multiple click operation within a workbook.

The benefit with openxlsx is that once the code is generated it is scalable: you can use it accurately n number of times without any more clicking. Even nicer if you are using the same approach multiple times to create you own function(s).

Anyway enough of that: here's an alternative using purrr (which just disguises the loop)

Might be interesting to benchmark the alternative approaches.

library(openxlsx)

OutputFolder <- file.path(".", "Output")
if(!dir.exists(OutputFolder))   dir.create(OutputFolder)

OutputFile <- file.path(OutputFolder, "Reprex_Openxlsx_mergeCells.xlsx")

Workbook4Export <- createWorkbook()

addWorksheet(wb = Workbook4Export, sheetName = "Tab_1", zoom = 80, gridLines = FALSE)

rows_2merge <- 1:100

purrr::walk(rows_2merge, ~mergeCells(wb = Workbook4Export, sheet = "Tab_1", cols = 1 + 0:1, rows = .x))

saveWorkbook(Workbook4Export, "Output/merge_cells_eg.xlsx")

Upvotes: 1

Related Questions