request for put_value example for whole dataframe

[mikecrobe]

Does anyone have an example of writing a data frame back to Excel from BERT - ie put_Value

I can read the data from Excel using range.to.data.frame - what do I need to write that back?

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.

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

Mike

#
# 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)

[duncanwerner]

As I said on SO, this works for me provided the range is the right size. The excel scripting example file shows how to write a data frame to a spreadsheet. If you are not seeing the results you expect, then something else is going wrong.

range <- new.sheet$get_Range( "B2:F152" );
range$put_Value( iris );

[mikecrobe]

Thank you for your persistence Duncan. I found the iris example.

What I have done is reduce the size of my example. From 4000 rows x 70 cols down to 17 x 9

The first doesn't work but the second does.

is there a size restriction on put_Value?

I also found how to get calling sequence for the methods - copy the code to the console (not code) window - and hover for tooltip.

Mike

[duncanwerner]

is there a size restriction on put_Value?

Not by design, but there might be an issue causing problems. Can you give me an example that definitely doesn't work?

[mikecrobe]

I have cut down the file down to something smaller than it used to be. Whilst still failing. Only cell B2 gets written to. Then there is a second file which is stripped down some more but works OK. Same code.

Code attached too BERTbug.zip

[bgaspers]

An alternative to put_Value is writing the dataframe to the clipboard and then pasting it in Excel. I have a VBA routine that does a bunch of regressions in R, from which I return the regression coefficients (i.e., so I'm running this from Excel in VBA, but you could do it from R with the appropriate Excel object references). Seems to work pretty well.

An example:

write.table(<yourdataframehere>,"clipboard",sep="\t",row.names=FALSE, col.names=TRUE) EXCEL$Application$get_ActiveWorkbook()$get_Sheets()$get_Item(<sheetindex>)$get_Range("A1")$PasteSpecial()

As I noted in another issue, though, copying data from Excel to the clipboard and reading it into a dataframe in R has failed for me so far, so I've used get_Value to read out the data from Excel.