Export Data from R to Excel

Exporting data to excel from R

Well, it depends on what type are your columns in the data frame. "dplyr::write_csv" should do the job, but note you have to convert the columns as type character to keep the structure in excel

enter image description here

enter image description here

how to export dataframes from R to excel and separate them by an empty row

Using openxlsx, which I hope works better for you than the other packages you mentioned, you can do:

library(openxlsx)

# create your workbook
mywb <- createWorkbook() 

# create the sheets you need based on the first list of tables
for (sheetName in names(listofdfs)){
    addWorksheet(mywb , sheetName )
}

# get all your lists of tables in a single list
l_listOfDF <- mget(ls(pattern="listofdf"))

# initiate the index of the row where you will want to start writing (one per sheet)
startR <- rep(1, length(listofdfs)) ; names(startR) <- names(listofdfs)

# loop over the lists of tables using index and then over the elements / sheets using their names
for(N_myListOfDF in seq(l_listOfDF)){

  for(pageName in names(l_listOfDF[[N_myListOfDF]])){

    # write the name/number of your table in the correct sheet, at the correct row
    writeData(mywb, sheet=pageName, startRow=startR[pageName], paste0("Table ", N_myListOfDF, "."))

    # write your data in the correct sheet at the correct row (the one after the name)
    writeData(mywb, sheet=pageName, startRow=startR[pageName]+1, l_listOfDF[[N_myListOfDF]][[pageName]])
    
    # update the row number (the + 3 is to leave space for name of table, headers and blank row
    startR[pageName] <- startR[pageName]+nrow(l_listOfDF[[N_myListOfDF]][[pageName]]) + 3

  }
}

# save your workbook in a file
saveWorkbook(mywb , "myfile.xlsx")

Output file:

How to create and export a large number of tables from R to excel?

You can do this as below (set n to 104 for complete solution)

n=3
openxlsx::write.xlsx(
  setNames(
    lapply(1:n,\(x) {
      d <- tibble::rownames_to_column(as.data.frame(freq(d[[x]])), "Modalités") %>% select(-4)
    }), paste0("Sheet",1:n)),
  file="mydata.xlsx"
)

---

Related Topics