range2dataframe.R

#----------------------------------------------------------------
# convert an excel range (obtained from a spreadsheet function
# or the COM/scripting API) into a data frame, optionally with
# headers in the first row.
#----------------------------------------------------------------
range2dataframe <- function(x, headers=F){

  # remove headers from data if necessary
  data <- if(headers) x[-1,] else x;

  # format data
  df <- as.data.frame( lapply( split( data, col(data)), unlist ));

  # add headers if available
  if( headers ){ colnames(df) <- x[1,]; }

  # done
  df;  
  
}

BERT returns Excel ranges as lists of lists (with one exception*). The reason for this is that Excel ranges can mix types (strings, numbers, booleans) while R lists cannot. If you coerce the value to a single list (for example via unlist), then the result will be a single type -- if there are any strings in the data, everything will be converted to a string.

If you are trying to create a data frame, most likely you have some columns of strings and some columns of numbers. The above function converts a list of lists into a data frame on a column-by-column basis, so if one column is all numbers, R will treat it as numbers. The function can also (optionally) pull column headers out of the first row.

* The exception is if everything in the data set is numeric, BERT will return a matrix of numbers.