export to xlsx with openxlsx and loop

General
#1

Hello,
I need to export multiple tables on an excel sheet called "Sheet_data".
I can create the tables, the workbook, and the sheet using openxlsx.
However, when I combine the loop, using for, and the openxlsx package, I failed.
I know that maybe I should use assign inside the loop, but I can't figure where.
When I open the file I noticed that I export the paste order, I mean, tab_1, tab_2, and so on.
Down I provide an example of my need.
Thanks for your time and interest.


data=mpg


tab_1=data %>% filter(class=="2seater") %>% group_by(manufacturer,year) %>% count()
tab_2=data %>% filter(class=="compact") %>% group_by(manufacturer,year) %>% count()
tab_3=data %>% filter(class=="midsize") %>% group_by(manufacturer,year) %>% count()
tab_4=data %>% filter(class=="minivan") %>% group_by(manufacturer,year) %>% count()



wb_e=createWorkbook()
addWorksheet(wb_e,"Sheet_data")

for (i in 1:4) {
  writeData(wb_e,"Sheet_data",paste("tab",i,sep = "_") ,startRow = 4+10*(i-1),startCol = 5)
  
}

saveWorkbook(wb_e,file="~/Desktop/tablex.xlsx",overwrite = TRUE)
#2

Have a look at this using purrr. It uses writexl instead of openxlsx but you should be able to adapt it.

#4

Thanks for the get(paste("tab_",j)) tip!
I rewrite the code. I can run the loop using dplyr over a categorical variable and exporting to excel.
I couldn't understand the purrr option. I can't manage that package.
Also, I obtained a list, and exporting the list on specific rows over excel was complex for me.
I know that loop is not recommended using R, but I'm not confident using purrr.
In fact, I can't use it.
Thanks again.

rm(list=ls())
library(openxlsx)
library(tidyverse)
library(janitor)

data=mpg
qist=data %>% select(manufacturer) %>% unique() %>% pull()
qist


for (i in qist) {

x=data %>% filter(manufacturer==i) %>% group_by(manufacturer,model) %>% count()  
assign(paste("tab_",i),x)
  }


wb=createWorkbook()
addWorksheet(wb,"Sheet_data")


i=1
for (j in qist) {
  
  writeData(wb,"Sheet_data", get(paste("tab_",j)),startRow = 4+10*(i-1),startCol = 5)
i=i+1
  }

saveWorkbook(wb,file ="/home/juan/Desktop/aa.xlsx",overwrite = TRUE)
#5

This is probably a cleaner implementation

library(openxlsx)
library(tidyverse)

data=mpg
qist= unique(data$manufacturer)

wb=createWorkbook()
addWorksheet(wb,"Sheet_data")

for (i in 1:length(qist)) {
  x = data %>% filter(manufacturer==qist[i]) %>% group_by(manufacturer,model) %>% count()  
  writeData(wb,"Sheet_data", x, startRow = 4+10*(i-1), startCol = 5)
}

saveWorkbook(wb,file ="/home/juan/Desktop/aa.xlsx",overwrite = TRUE)
1 Like
#6

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.

#3

Place your call to paste("tab",i,sep = "_")) inside get(), this will write the data and not the string to the workbook

for (i in 1:4) {
  writeData(wb_e,"Sheet_data", get(paste("tab",i,sep = "_")),startRow = 4+10*(i-1),startCol = 5)
}
1 Like