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

data.table, dataframe, r

Solution

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)

Problem

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 ```

Original source