How to quickly inspect R data frames in Excel

One of the best things about RStudio is that it offers a splendid interface. You can always keep one eye on all the variables in the environment, and inspect data frames using the View() function or by clicking the spreadsheet icons. But sometimes you need to do some quick exploratory transformations to test things out. In this blog post I explain how you can quickly port your data to Excel.

There are several ways to create some kind of link between R and Excel. A really sophisticated way is using BERT (Basic Excel R Toolkit). For example, you can create functions in R and use them in Excel. However, you’re forced to work outside your trusted R(Studio) environment, as it comes with its own unfamiliar RStudio-like interface. That’s why I decided to look at other solutions.

A quick and dirty function

If all you want to do is view your data frames in Excel, there’s an easy solution that works with base functions and openxlsx.

library(openxlsx)

tempxl <- function(x) {
  filename <- paste0('tempfile_',gsub("[^0-9A-Za-z]| ","" , Sys.time() ,ignore.case = TRUE),'.xlsx')
  write.xlsx(x,filename)
  shell.exec(filename)
  delFile <- menu(c('yes','no'), title = 'Delete temporary file? ')
  if (delFile == 1) {
    fileDeleted <- suppressWarnings(file.remove(filename))
    if (fileDeleted == F) {
      message('File not removed because it is still opened in Excel.')
    } else {
      message('File removed successfully.')
    }
  }
}

tempxl(df)

The function tempxl takes a data frame, and writes it away to a file with filename temp_<datetime>.xlsx. It immediately opens the file and waits by showing a menu that asks you to delete it — which only works if you closed the file in Excel.

If you think you’ll use this function a lot in the future, why don’t you add it to your .Rprofile file?

A dynamic link using excel.link

If you want to create a truly dynamic link between Excel an R, there is a great package called excel.link. The documentation is somewhat chaotic, so here’s a straightforward example to get you started!

library(excel.link)

xlc[a1] <- df
dt_xl <- xl.connect.table('a1')
dt_xl[]

The xlc function of excel.link opens Excel and writes a data frame to the coordinates of your choice, with the column names included. There are some variants of this function. xlr will write away rows and xlrc will write away both. In the example the coordinates are the cell A1.

Next, I create a dynamic link between the Excel file, starting in cell A1, by using the xl.connect.table function.

In the final line of the code, I print the contents of the active Excel file, by using square brackets. You can also put sequences of rows and columns in there if you want. You can also use the $ operator to access the columns by name.

By the way, if you’re having trouble understanding some of the code and concepts, I can highly recommend “An Introduction to Statistical Learning: with Applications in R”, which is the must-have data science bible. If you simply need an introduction into R, and less into the Data Science part, I can absolutely recommend this book by Richard Cotton. Hope it helps!

Great success!