How to use R's Reticulate package alongside Python's OpenPyxl to hide rows in excel

Question

I'm trying to create an excel file with hidden/folded rows like on the openpyxl webpage but via the reticulate package in R. Currently, I'm able to do everything in the process, from generating dummy data to saving the file, save for manipulating the row_dimensions of my sheet. I'm going this route because none of R's excel writing packages, at least from what I've found, are able to collapse rows. The below code represents the current form that, while it doesn't alter row dimensions, is able to output to a working excel file.

rc1<-letters
rc2<-seq(1,1040,40)
rc3<-seq(0,18199,700)
rc<-data.frame(rc1,rc2,rc3)
library(reticulate)
pyxl<-import("openpyxl")
rct<-pyxl$Workbook()
pcta<-rct$active
pcta$title<-"Trial"
library(magrittr)
for (i in 1:length(rc1)) {
  for (j in 1:length(rc)) {
    a<-rc[i,j]
    a %>% pcta$cell(i,j,.)
  }
}
pcta$row_dimensions[1:4]<-10
rct$save("trial.xlsx")

I've also tried assigning to single values only to then cause the follow errors when the 1:4 in the above code is replaced by either 1 or "1".

Error in py_call_impl(callable, dots$args, dots$keywords) : 
  TypeError: 'float' object is not iterable 
 Error in py_call_impl(callable, dots$args, dots$keywords) : 
  TypeError: '<' not supported between instances of 'str' and 'int'

score 0 · Accepted Answer · answered Nov 21 '19 at 20:47

A bit embarrassing to be posting the answer to my own question only an hour or so later, but I figured I'd post it for the benefit of anyone else who might be trying to solve a similar issue. Firstly, the numbers need to be declared within as.integer() to avoid float types. Secondly, pcta$row_dimensions[] is not the way to accomplish this, but pcta$row_dimensions$group(). I've produced the solution below.

rc1<-letters
rc2<-seq(1,1040,40)
rc3<-seq(0,18199,700)
rc<-data.frame(rc1,rc2,rc3)
library(reticulate)
pyxl<-import("openpyxl")
rct<-pyxl$Workbook()
pcta<-rct$active
pcta$title<-"Trial"
library(magrittr)
for (i in 1:length(rc1)) {
  for (j in 1:length(rc)) {
    a<-rc[i,j]
    a %>% pcta$cell(i,j,.)
  }
}
pcta$row_dimensions$group(as.integer(1),as.integer(5),hidden = TRUE)
rct$save("trial.xlsx")

How to use R's Reticulate package alongside Python's OpenPyxl to hide rows in excel

1 Answers1