exporting a list from R to excel in a good format

Question

I'm trying to use the library(xlsx) to write some data from R into excel in a readable format.

My dataset is formatted as:

tbl <- list("some_name"=head(mtcars),"some_name2"=head(iris))

I would like to write this table to excel, with each item in the list being identified and the data being next to the item. E.g. the excel file should look like

"some_name" in cell A1

paste the dataframe head(mtcars) in cell A3

"some_name2" in cell A11

paste the dataframe head(iris) in cell A13

or something similar, e.g. pasting each item into a new worksheet.

Using

write.xlsx(tbl,"output.xlsx")

will output it correctly however it is not formatted in a readable way.

Any help would be great

[Here's a post](https://stackoverflow.com/q/27713310/5325862) with 9 answers on writing a list of data frames to different sheets in the same workbook. I'm not finding an answer that also places a label text, but you could expand on those answers — camille, May 10 '19 at 14:56
Also, not sure if you're tied to using `xlsx`, but I've had an easier time building more complex workbooks with [`openxlsx`](https://cran.r-project.org/web/packages/openxlsx/index.html) instead. May just be personal preference though. — camille, May 10 '19 at 15:06

pzhao · Answer 1 · 2019-05-10T15:50:36.637

The following codes create a xlsx file with multiple sheets, each of which holds a list name as the sheet name and a title, and a data frame below the title. You can modify it as you like.

require(xlsx)

ls2xlsx <- function(x, wb){
  for(i in 1:length(x)){
    sh <- createSheet(wb, names(x[i]))
    cl_title <- createCell(createRow(sh, 1), 1)
    addDataFrame(x[i], sh, startRow = 2, startColumn = 1)
    setCellValue(cl_title[[1, 1]], names(x[i]))
  }
}

tbl <- list("some_name" = head(mtcars),"some_name2"=head(iris))
wb <- createWorkbook()
ls2xlsx(tbl, wb)
saveWorkbook(wb, 'test.xlsx')

score 0 · Answer 2 · answered May 10 '19 at 15:51

The following function writes a list of dataframes to an .xlsx file.
It has two modes, given by argument beside.

beside = TRUE is the default. It writes just one sheet, with the dataframe name on the first row, then an empty cell, then the dataframe. And repeats this for all dataframes, written side by side.
beside = FALSE writes one dataframe per sheet. The sheets' names are the dataframes names. If the list members do not have a name, the name is given by argument sheetNamePrefix.

The .xlsx file is written in the directory given by argument file.

writeList_xlsx <- function(x, file, beside = TRUE, sheetNamePrefix = "Sheet"){
  xnames <- names(x)
  shNames <- paste0(sheetNamePrefix, seq_along(x))
  if(is.null(xnames)) xnames <- shNames
  if(any(xnames == "")){
    xnames[xnames == ""] <- shNames[xnames == ""]
  }
  wb <- createWorkbook(type = "xlsx")
  if(beside){
    sheet <- createSheet(wb, sheetName = shNames[1])
    row <- createRow(sheet, rowIndex = 1)
    col <- 0
    for(i in seq_along(x)){
      col <- col + 1
      cell <- createCell(row, colIndex = col)
      setCellValue(cell[[1, 1]], xnames[i])
      col <- col + 2
      addDataFrame(x[[i]], sheet, 
                   startRow = 1, startColumn = col,
                   row.names = FALSE)
      col <- col + ncol(x[[i]])
    }
  }else{
    for(i in seq_along(x)){
      sheet <- createSheet(wb, sheetName = xnames[i])
      addDataFrame(x[[i]], sheet, row.names = FALSE)
    }
  }
  if(!grepl("\\.xls", file)) file <- paste0(file, ".xlsx")
  saveWorkbook(wb, file = file)
}

writeList_xlsx(tbl, file = "test.xlsx")
writeList_xlsx(tbl, file = "test2.xlsx", beside = FALSE)

exporting a list from R to excel in a good format

2 Answers2