Data Science Desktop Survival Guide by Graham Williams
Desktop Survival Project Home Preface Data Science Introducing R R Constructs R Tasks R Strings R Read, Write, and Create Data Template Data Exploration Data Wrangling Data Visualisation ML Template ML Scenarios ML Activities ML Applications ML Algorithms Cluster Analysis Decision Trees Computer Vision Graph Data Privacy Literate Data Science Coding with Style Resources Bibliography Index

CLICK HERE TO VISIT THE UPDATED SURVIVAL GUIDE

Write Excel Data

20200601 An Excel spreadsheet can be written using xlsx. The basic functionality of xlsx::write.xlsx() is to write a data frame to the spreadsheet. Using rattle::weatherAUS a more sophisticated example will create a new workbook using xlsx::createWorkbook(). For each location a new sheet is created using xlsx::createSheet() which is then populated using xlsx::addDataFrame(). Once all locations are added as separate sheets to the workbook we can write the actual spreadsheet using xlsx::saveWorkbook().

options(java.parameters="-Xmx8000m")
library(xlsx)         # Write Excel spreadsheets: write.xlsx() saveWorkbook().

locations <- unique(ds$Location)
wb <- createWorkbook(type="xlsx")

for (l in locations)
{
  wb %>%
    createSheet(sheetName=l) ->
  ws

  ds %>%
    filter(Location==l) %>%
    select(-Location) %>%
    data.frame() %>%
    addDataFrame(sheet=ws, row.names=FALSE)
}

saveWorkbook(wb, 'weatherAUS.xlsx')

Notice the use of the java.parameters= option to specify a larger allocation of memory for Java. This is required for even moderate sized datasets like the rattle::weatherAUS dataset. The option needs to be specified before the package is loaded.

See Section 6.4 for an example of using this created spreadsheet.