How to write contents of data frame back to range?

Question

I need to perform the following sequence:

1. Open Excel Workbook
2. Read specific worksheet into R dataframe
3. Read from a database updating dataframe
4. Write dataframe back to worksheet

I have steps 1-3 working OK using the BERT tool. (the R scripting interface) For step 2 I use range.to.data.frame from BERT

Any pointer on how to perform step 4? There is no data.frame.to.range I tried range$put_Value(df) but no error return and no update to Excel I can update a single cell from R using put_Value - which I cannot see documented

#
# manipulate status data using R BERT tool
#

wb <- EXCEL$Application$get_ActiveWorkbook()
wbname = wb$get_FullName()

ws <- EXCEL$Application$get_ActiveSheet()
topleft = ws$get_Range( "a1" )
rng = topleft$get_CurrentRegion()

#rngbody = rng$get_Offset(1,0)

ssot = rng$get_Value()

ssotdf = range.to.data.frame( ssot, headers=T )
# emulate data update on 2 columns
ssotdf$ServerStatus = "Disposed"
ssotdf$ServerID = -1

# try to write df back
retcode = rng$put_Value(ssotdf)

Answer 1

This answer doesn't use R Excel BERT. Try the openxlsx library. You probably can do all the steps using that library. For the step 4, after installing openxlsx library, the following code will write a file:

openxlsx::write.xlsx(ssotdf, 'Dataframe.xlsx',asTable = T)

Answer 2

I think your problem is that you are not changing the size of the range, so you are not going to see your new columns. Try creating a new range that has two extra columns before you insert the data.

Answer 3

I just had the same question and was able to resolve it by transforming the data.frame to a matrix in the call to put_value. I figured this out after playing with the old version in excel-functions.r. Try something like:

retcode = rng$put_Value(as.matrix(ssotdf))

Answer 4

You may have already solved your problem but, if not, the following stripped down R function does what I think you need:

testDF <- function(rng1,rng2){
  app <- EXCEL$Application
  ref1 <- app$get_Range( rng1 ) # get source range reference
  data <- ref1$get_Value()      # get source range data
  #
  ref2 <- app$get_Range( rng2 ) # get destination range reference
  ref2$put_Value( data )        # put data in destination range
}

I simulated a dataframe by setting values in range "D4:F6" of the speadsheet to:

col1    col2    col3
 1       2      txt1
 7       3      txt2

then ran

testDF("D4:F6","H10:J12")

in the Bert console. The dataframe then appears in range "H10:J12".