Quickly view an R data.frame, vector, or data.table in Excel

Question

How do you quickly open small R table / vector objects in Excel?

For example suppose you have the following three objects that you want to view in Excel:

## A data frame with commas and quotes
df = data.frame(
  area = unname(state.x77[,'Area']),
  frost = unname(state.x77[,'Frost']),
  comments = "Ok for a visit, but don't want to live there",
  challengeComments = c('"', '""'))
row.names(df) = state.name
df = df[1:10, ]
df['California', 'comments'] = "Would like to live here"

## A Matrix
mat = matrix(rnorm(100), 10)

## A Vector
v = 1:10

Answer 1

I wrote this function to accomplish that task. I call it "write temp file", or "wtf". It only works on windows if you have csv files associated with Excel.

You might look at the code in PBSmodelling::openFile to see how to adopt it to different operating systems.

wtf = function (x) {
  tempFilePath = paste(tempfile(), ".csv")
  tempPath = dirname(tempFilePath)
  preferredFile = paste(deparse(substitute(x)), ".csv", sep = "")
  preferredFilePath = file.path(tempPath, preferredFile)

  if(length(dim(x))>2){
    stop('Too many dimensions')
  }
  if(is.null(dim(x))){
    x = as.data.frame(x)
  }
  if (is.null(rownames(x))) {
    tmp = 1:nrow(x)
  }else {
    tmp = rownames(x)
  }
  rownames(x) = NULL
  x = data.frame(RowLabels = tmp, x)
  WriteAttempt = try(
    write.table(x, file=preferredFilePath, quote=TRUE, sep=",", na="",
                row.names=FALSE, qmethod="double"),
    silent = TRUE)
  if ("try-error" %in% class(WriteAttempt)) {
    write.table(x, file=tempFilePath, , quote=TRUE, sep=",", na="",
                row.names=FALSE, qmethod="double")
    shell.exec(tempFilePath)
  } else {
    shell.exec(preferredFilePath)
  }
}


wtf(df)
wtf(mat)
wtf(v)

if you open the same object multiple times, it will still work thanks to the error handling, but it will have a messy temp name.

wtf(df)
df$MoreData = pi
wtf(df)

Answer 2

I wrote a function to open files in Libre Office Calc or Excel. See here for details.

view <- function(data, autofilter=TRUE) {
    # data: data frame
    # autofilter: whether to apply a filter to make sorting and filtering easier
    open_command <- switch(Sys.info()[['sysname']],
                           Windows= 'open',
                           Linux  = 'xdg-open',
                           Darwin = 'open')
    require(XLConnect)
    temp_file <- paste0(tempfile(), '.xlsx')
    wb <- loadWorkbook(temp_file, create = TRUE)
    createSheet(wb, name = "temp")
    writeWorksheet(wb, data, sheet = "temp", startRow = 1, startCol = 1)
    if (autofilter) setAutoFilter(wb, 'temp', aref('A1', dim(data)))
    saveWorkbook(wb, )
    system(paste(open_command, temp_file))
}

Answer 3

I wanted something that mimicked View() but in Excel, not in the internal view of RStudio. Learned little bits from numerous posts above. Eventually came to a pretty simple little bit of code that....so far... seems to do the trick for me.
Just want to quickly view it, but in Excel. If I decide I want to save the file, I can handle that within Excel. Otherwise it just automatically create a temp file in the default temp directory for that session of R. It's setup to act on the .Last.value of the R session as a default, or you pipe in an object that can be coerced to a data frame

I just have it defined in my R profile so its always available. Might make more sens to name it ViewExt for "View External" so as to not link it directly to Excel but whatever program of choice the user has set in Windows to be the default editor for a .csv file. But thats what I use so that's what I named it

    ViewExcel <- function(df = .Last.value, file = tempfile(fileext = ".csv")) {
       df <- try(as.data.frame(df))
       stopifnot(is.data.frame(df))
       utils::write.csv(df, file = file)
       base::shell.exec(file)
    }

    #Examples
    mtcars
    ViewExcel()
    mtcars %>% ViewExcel()
    ViewExcel(mtcars)

Answer 4

Sorry for shameless advertisement... You can try my package http://cran.r-project.org/web/packages/excel.link/index.html It looks like:

library(excel.link)
xlrc[a1]=df

It depends on Omegahat RDCOMClient package so it is necessary install it from source:

install.packages("RDCOMClient", repos = "http://www.omegahat.org/R")
install.packages("excel.link", repos = "http://cran.at.r-project.org",type="source")