If you are exporting your dataframes to Excel on Windows and
if you have Excel installed and
if you are willing to make your hands dirty by programming VBA
(the programming language built into Excel) and
if you are willing to install RExcel
(by way of the CRAN package RExcelInstaller or by visiting rcom.univie.ac.at)
then here is some code which transfers all dataframes in the
global environment in R into separate worksheets in the active workbook in
Excel.
This version adds the name of the dataframe as a header on the sheet and also
uses it as the name of the worksheet.
It should be easy to adapt this to add headers and worksheet names of your
choice.
If you want to use this code, you have to set a reference to RExcelVBALib in the
workbook with the VBA code below.
I you have never used RExcel before: There are many examples
demonstrating different techniques to connect R and Excel for
data transfer and computation.
-=-=-=-=-=-==
Option Explicit
Sub TransferToExcel(dfName As String, header As String)
Dim myWs As Worksheet
Set myWs = ActiveWorkbook.Worksheets.Add
myWs.Name = dfName
myWs.Cells(1, 1).Value = header
rinterface.GetDataframe dfName, myWs.Cells(2, 1)
End Sub
Sub DoIt()
Dim i As Integer
Dim dfNames As Variant
Dim myDfName As String
rinterface.StartRServer
dfNames = RDataFrameNames()
For i = LBound(dfNames) To UBound(dfNames)
myDfName = CStr(dfNames(i, LBound(dfNames, 2)))
TransferToExcel myDfName, myDfName
Next i
rinterface.StopRServer
End Sub
Function RDataFrameNames() As Variant
Dim cmdString As String
Dim myDfNames As Variant
rinterface.StartRServer
cmdString = "dfpos<-sapply(ls(),function(x)is.data.frame(eval(as.name(x))))"
rinterface.RRun cmdString
cmdString = "dfnames<-ls()[dfpos]"
rinterface.RRun cmdString
myDfNames = rinterface.GetRExpressionValueToVBA("dfnames")
RInterface.RRun "rm(dfnames,dfpos)"
RDataFrameNames = myDfNames
End Function
On Jul 15, 2010, at 3:29 AM, Whit Armstrong wrote:
> It isn't beautiful, but I use this package to write excel files from linux.
>
> http://github.com/armstrtw/Rexcelpoi
--
Erich Neuwirth
Didactic Center for Computer Science and Institute for Scientific Computing
University of Vienna
[[alternative HTML version deleted]]
______________________________________________
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
