Search code examples
rlistexport-to-excel

How to export a complex R list with embedded matrixes into XLS?

I'm trying to export a complex list created in R with embedded matrixes to XLS but am getting frustrated. I need to export this list to XLS so I can run calculation validations. The code at the bottom generates the list neatly rendered by R Studio console as shown in the first image below, and in the same code I use writexl() to send the list to XLS but as you can see in the second image below the XLS is unworkable. Ideally, I'd like to render the list in XLS in the same form as pictured in the first image below or at least in some format where it's not too difficult to read the cells (one numeric value per cell, etc.) and their headers and run some calculation next to the cells.

Any recommendations for how to do this?

Here is the list neatly rendered in R Studio console:

enter image description here

Here is how it is wretchedly rendered in writexl():

enter image description here

Here is the code:

library(writexl)

outputList <- list(
  Series_One = list(
    Rsv = matrix(
      c(3, 2, 10, 3, 254, 6, 0, 0, 2, 0, 8, 2, 447, 2, 0, 0, 240, 0, 7, 7, 382, 7, 0, 232),
      nrow = 3,
      dimnames = list(NULL, c("In", "Dep", "Tgt", "Top", "Due_Cl_A", "Draw_Cl_A", "End", "Out"))),
    Cl_A_current = matrix(
      c(0, 254, 192, 232, 168,0, 254, 192, 232, 168,0, 254, 192, 232, 168),
      nrow = 3,
      dimnames = list(NULL, c("In", "Due", "FC_cover", "FC_short", "Out"))),
    Cl_A_prior_XYZ = matrix(
      c(0, 254, 0, 447, 0, 64, 0, 447, 0, 382, 0, 0,0, 254, 0, 447, 0, 64, 0, 447, 0),
      nrow = 3,
      dimnames = list(NULL, c("In", "Due", "FC_cover", "FC_short", "RA_cover", "RA_short", "Out")))
  ),
  Series_Two = list(
    Cl_A_current = matrix(
      c(2, 123, 2, 121, 2, 140, 2, 138, 360, 183, 183, 0, 176, 0,150),
      nrow = 3,
      dimnames = list(NULL, c("In", "Due", "FC_cover", "FC_short", "Out"))),
    Cl_A_prior_XYZ = matrix(
      c(0, 121, 0, 121, 0, 259, 0, 259, 176, 259, 176, 83, 0, 0, 176, 83, 0, 0),
      nrow = 3,
      dimnames = list(NULL, c("In", "Due", "FC_cover", "FC_short", "RA_cover", "RA_short"))),
    Rsv = matrix(
      c(0, 3, 10, 0, 121, 3, 0, 0, 0, 0, 12, 0, 259, 0, 0, 0, 0, 15, 0, 83, 0, 0, 0, 0),
      nrow = 3,
      dimnames = list(NULL, c("In", "Dep", "Tgt", "Top", "Due_Cl_A", "Draw_Cl_A", "End", "Out")))
  )
)
# Print the list
outputList

# Export to XLS
write_xlsx(as.data.frame(outputList), "C:\\Users\\...\\OneDrive\\Desktop\\outputList.xlsx")
Solution

  • With the help of purrr and openxlsx you could profit from openxlsx::write.xlsx ability to export lists of dataframes into Excel tabs/sheets.

    Try:

    library(purrr)
library(openxlsx)

    Two workbooks, each df in a different tab.

    write.xlsx(x =  map(.x = outputList$Series_One, .f = as.data.frame), file = "Series One.xlsx", asTable = TRUE)
write.xlsx(x =  map(.x = outputList$Series_Two, .f = as.data.frame), file = "Series Two.xlsx", asTable = TRUE)

    One workbook, two sheets. One for each series binding and labeling its tables in a tidy format

    map(outputList, .f = function(x) { map_df(x, .f = as.data.frame, .id = "table_name") } ) |>
  openxlsx::write.xlsx(file = "example_2_sheets_binded.xlsx", asTable = TRUE)