CODEWITHSUNDEEP
rdplyropenxlsx2
Bregma
Bregma

Reputation: 1

How can I use group_walk() to write xlsx files formatted to openxlsx2 wb objects?

I am trying to use group_walk() with openxlsx2 workbook objects to produce multiple xlsx files with greater control of the worksheet properties.

I can use the code below to create simple xlsx files...

mtcars %>% 
dplyr::group_by(cyl) %>% tidyr::nest() %>%
dplyr::group_walk(~ readr::write_xlsx(.x$data, paste0(.y$cyl, ".csv")))

But I would like to be able to do something like this...

library(openxlsx2)
library(tidyverse)

wb <- wb_workbook(
creator = "Me") %>%
wb_add_worksheet("Cyl") %>%
wb_set_col_widths(1, widths = "auto")

mtcars %>%
group_by(cyl) %>%
group_walk(~ wb_save(wb, .x$data, paste0(.y$cyl, ".xlsx")))

The end result would be multiple xlsx files for each cyl with properties based on the wb object.

I have tried various versions of the code above, but can't seem to connect the grouped data with the wb object.

Upvotes: 0

Views: 128

Answers (1)

LMc
LMc

Reputation: 18732

Try something like:

library(openxlsx2)
library(dplyr)

wb <- wb_workbook(
  creator = "Me") %>%
  wb_add_worksheet("Cyl") 

mtcars %>%
  group_by(cyl) %>%
  group_walk(~ wb_save(wb$add_data(x = .x), paste0(.y[[1]], ".xlsx")))

A few things to note about your attempt:

  • Your pipe chain to wb_set_col_widths does not work because the cols argument is required and you have not specified it.
  • wb_save does not add data. You are passing .x$data to the file name argument and you are passing paste0(.y$cyl, ".xlsx") to the overwrite argument. The latter is supposed to be logical TRUE/FALSE if you want to overwrite the workbook if it already exists.
  • When you are using a formula function .x represents the data you are iterating over and .y the group value. .x$data will attempt to extract the column data, which does not exist and is giving your error.

To get a clearer understanding I would encourage you to use the browser() function in group_walk. This will allow you to explore an iteration from the console (this will open a new file and your prompt may look a little different in this state):

mtcars %>%
  group_by(cyl) %>%
  group_walk(~ browser())

Browse[1]> .y
# A tibble: 1 × 1
    cyl
  <dbl>
1     4

Browse[1]> .x
# A tibble: 11 × 10
     mpg  disp    hp  drat    wt  qsec    vs    am  gear  carb
   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
 1  22.8 108      93  3.85  2.32  18.6     1     1     4     1
 2  24.4 147.     62  3.69  3.19  20       1     0     4     2
 3  22.8 141.     95  3.92  3.15  22.9     1     0     4     2
 4  32.4  78.7    66  4.08  2.2   19.5     1     1     4     1
 5  30.4  75.7    52  4.93  1.62  18.5     1     1     4     2
 6  33.9  71.1    65  4.22  1.84  19.9     1     1     4     1
 7  21.5 120.     97  3.7   2.46  20.0     1     0     3     1
 8  27.3  79      66  4.08  1.94  18.9     1     1     4     1
 9  26   120.     91  4.43  2.14  16.7     0     1     5     2
10  30.4  95.1   113  3.77  1.51  16.9     1     1     5     2
11  21.4 121     109  4.11  2.78  18.6     1     1     4     2

To exit this state hit the "Esc" button or select the red stop button 🟥 (assuming you are using RStudio).

Upvotes: 1

Related Questions