Export a named list in R as individual worksheets within an Excel workbook (.xlsx)

Export_XLSX.R

#
# Author:   Cristian E. Nuno
# Purpose:  Add list of data frames as worksheets to
#           an existing Excel workbook
# Date:     August 10, 2018
#


# create ExportXLSX function
ExportXLSX <- function( named.list, desired.file.path = getwd(), name.of.file, add.to.existing.file = FALSE ){
  
  # Inputs:
  #   1. named.list:            an R list object that is named. The name of each element will be used
  #                             to name each worksheet within the Excel workbook.
  #                             i.e. length of named.list is equal to the number of Worksheets created
  #
  #   2. desired.file.path:     a character vector specifying the file path you wish to save
  #                             the Excel workbook. By default, the location is your current
  #                             working directory.
  #
  #   3. name.of.file:          a character vector specifying the name you assign to the Excel workbook.
  #                             By default, the name of the file ends with ".xlsx".
  #
  #   4. add.to.existing.file:  a logical vector specifiying whether or not to add data as worksheets to an
  #                             existing workbook
  #
  # Output:
  #   1. An Excel workbook (.xlsx) file that contains 1 worksheet for each element
  #      within `named.list`` that is saved at the location specified in `desired.file.path` 
  #      with the file named specified in `name.of.file`
  
  # Build in checks to ensure that users
  # contain the 'openxlsx' package from the CRAN
  if( !require( openxlsx ) ){
    install.packages( pkgs = "openxlsx" )
  }
  
  # load necessary package
  require( openxlsx )
  
  # build in checks to stop the function without the proper input
  if( class( named.list ) != "list" ){
    stop( "ExportXLSX() requires the `named.list` argument to be a list object. See ?list for more details." )
  }
  
  # build in checks to stop the function without the list being named
  if( class( named.list ) == "list" && is.null( names( named.list ) ) ){
    stop( "ExportXLSX() requires the `named.list` argument to be a named list. To be more descriptive of the data contained in each Excel sheet, see ?base::names or ?stats::setNames on how to name your list object." )
  }
  
  # check the existence of an existing workbook
  if( add.to.existing.file ){
    
    # load existing workbook
    # note: checking for file type extension
    if( grepl( pattern = ".xlsx", x = name.of.file ) ){
      
      wb <- loadWorkbook( file = name.of.file )
      
    } else{
      
      wb <- loadWorkbook( file = paste0( name.of.file, ".xlsx" ) )
      
    } # end of if-else that checks for file type extension
    
  } else{
    
    # create new workbook since doesn't currently exist
    wb <- createWorkbook()
    
  } # end of if-else that checks the existence of an existing workbook
  
  # add data to each worksheet
  invisible( mapply( FUN = function( i, j )
    # if a name from named.list does appear in the workbook
    # overwrite the worksheet
    if( i %in% names( wb ) ){
      writeData( wb = wb
                 , sheet = i
                 , x = j )
    } else{
      # if a name from named.list does not appear in the workbook
      # add it as a worksheet
      addWorksheet( wb = wb, sheetName = i )
      
      # and then write data to the newly created worksheet
      writeData( wb = wb
                 , sheet = i
                 , x = j )
    } # end of if-else testing if names exist within the workbook
    , names( named.list )
    , named.list ) )
  
  # set column widths to automatically adjust to fit the column name
  invisible( mapply( FUN = function( i, j )
    if( !is.null( ncol( j ) ) ){
      setColWidths( wb = wb
                    , sheet = i
                    , cols = 1:ncol( j )
                    , widths = "auto" )
    } 
    , names( named.list )
    , named.list ) )
  
  # now export the XLSX file to the desired file path
  if( grepl( pattern = ".xlsx", x = name.of.file ) ){
    saveWorkbook( wb = wb
                  , file = file.path( desired.file.path
                                      , name.of.file ) 
                  , overwrite = TRUE )
  } else{
    saveWorkbook( wb = wb
                  , file = file.path( desired.file.path
                                      , paste0( name.of.file, ".xlsx" ) )
                  , overwrite = TRUE )
  }
  
} # end of ExportXLSX() function

# end of script #