export tables to excel files on multiple sheets with titles for each table

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

export tables to excel files on multiple sheets with titles for each table

eugen
Hello R-users,
Checking the archives, I recently came across this topic:
"export tables to Excel files"
(http://r.789695.n4.nabble.com/export-tables-to-Excel-files-td1565679.html#a1565679),
 and the following interesting references have been proposed:
http://rwiki.sciviews.org/doku.php?id=tips:data-io:ms_windows
http://www.r-bloggers.com/export-data-frames-to-multi-worksheet-excel-file-2/

but my problem is somehow a small extension to what has been discussed, and
although i have a solution, i seek something more elegant. I want to export
multiple dataframes (on multiple sheets), but i also want each of them to have
its own title that is to be written also in Excel. The packages/functions that i
have checked, cannot accommodate a title that is to be written on the sheet,
along with the actual dataframe of interest.

I can do something similar to what i need, but without writing the dataframes on
multiple sheets.

#head(USArrests) and head(iris) written with accompanying title one under each
other

write.excel<-function (tab, ...){
 zz <- file("example.dat", "a+b")
 cat("\"TITLE extra line",file = zz, sep = "\n")
 write.table(tab, file=zz, row.names=F,sep="\t")
 close(zz)}
 write.excel(head(USArrests))
 write.excel(head(iris))

Any suggestion on how to export the same information on two separate sheets, and
keeping also a title for each of them, is highly appreciated, as i have been
searching for some time for a good solution.

Thank you very much and have a great day ahead!
 





 Eugen Pircalabelu
(0032)471 842 140
(0040)727 839 293

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: export tables to excel files on multiple sheets with titles for each table

Felipe Carrillo
Check the WriteXLS package, I think it does that and also saves
each R object on a different excel sheet.
 
Felipe D. Carrillo
Supervisory Fishery Biologist
Department of the Interior
US Fish & Wildlife Service
California, USA



----- Original Message ----

> From: eugen pircalabelu <[hidden email]>
> To: R-help <[hidden email]>
> Sent: Tue, July 13, 2010 1:21:33 PM
> Subject: [R] export tables to excel files on multiple sheets with titles for
>each table
>
> Hello R-users,
> Checking the archives, I recently came across this topic:
> "export tables to Excel files"
>(http://r.789695.n4.nabble.com/export-tables-to-Excel-files-td1565679.html#a1565679),
>,
> and the following interesting references have been proposed:
> http://rwiki.sciviews.org/doku.php?id=tips:data-io:ms_windows
> http://www.r-bloggers.com/export-data-frames-to-multi-worksheet-excel-file-2/
>
> but my problem is somehow a small extension to what has been discussed, and
> although i have a solution, i seek something more elegant. I want to export
> multiple dataframes (on multiple sheets), but i also want each of them to have

> its own title that is to be written also in Excel. The packages/functions that
>i
>
> have checked, cannot accommodate a title that is to be written on the sheet,
> along with the actual dataframe of interest.
>
> I can do something similar to what i need, but without writing the dataframes
>on
>
> multiple sheets.
>
> #head(USArrests) and head(iris) written with accompanying title one under each

> other
>
> write.excel<-function (tab, ...){
> zz <- file("example.dat", "a+b")
> cat("\"TITLE extra line",file = zz, sep = "\n")
> write.table(tab, file=zz, row.names=F,sep="\t")
> close(zz)}
> write.excel(head(USArrests))
> write.excel(head(iris))
>
> Any suggestion on how to export the same information on two separate sheets,
>and
>
> keeping also a title for each of them, is highly appreciated, as i have been
> searching for some time for a good solution.
>
> Thank you very much and have a great day ahead!
>
>
>
>
>
>
> Eugen Pircalabelu
> (0032)471 842 140
> (0040)727 839 293
>
> ______________________________________________
> [hidden email] 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.
>




______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: export tables to excel files on multiple sheets with titles for each table

Marc Schwartz-3
If I am correctly understanding what Eugen is trying to do, WriteXLS() won't get him there. WriteXLS() will enable you to label/name the worksheets (tabs) but not allow you to precede the actual data frame rows and columns on the sheet with a title or label.

I suspect that you may have to look at the RCom package tools for this. This provides greater flexibility in writing to the worksheets and cells.

See http://rcom.univie.ac.at/ for more information.

HTH,

Marc Schwartz


On Jul 13, 2010, at 4:09 PM, Felipe Carrillo wrote:

> Check the WriteXLS package, I think it does that and also saves
> each R object on a different excel sheet.
>  
> Felipe D. Carrillo
> Supervisory Fishery Biologist
> Department of the Interior
> US Fish & Wildlife Service
> California, USA
>
>
>
> ----- Original Message ----
>> From: eugen pircalabelu <[hidden email]>
>> To: R-help <[hidden email]>
>> Sent: Tue, July 13, 2010 1:21:33 PM
>> Subject: [R] export tables to excel files on multiple sheets with titles for
>> each table
>>
>> Hello R-users,
>> Checking the archives, I recently came across this topic:
>> "export tables to Excel files"
>> (http://r.789695.n4.nabble.com/export-tables-to-Excel-files-td1565679.html#a1565679),
>> ,
>> and the following interesting references have been proposed:
>> http://rwiki.sciviews.org/doku.php?id=tips:data-io:ms_windows
>> http://www.r-bloggers.com/export-data-frames-to-multi-worksheet-excel-file-2/
>>
>> but my problem is somehow a small extension to what has been discussed, and
>> although i have a solution, i seek something more elegant. I want to export
>> multiple dataframes (on multiple sheets), but i also want each of them to have
>
>> its own title that is to be written also in Excel. The packages/functions that
>> i
>>
>> have checked, cannot accommodate a title that is to be written on the sheet,
>> along with the actual dataframe of interest.
>>
>> I can do something similar to what i need, but without writing the dataframes
>> on
>>
>> multiple sheets.
>>
>> #head(USArrests) and head(iris) written with accompanying title one under each
>
>> other
>>
>> write.excel<-function (tab, ...){
>> zz <- file("example.dat", "a+b")
>> cat("\"TITLE extra line",file = zz, sep = "\n")
>> write.table(tab, file=zz, row.names=F,sep="\t")
>> close(zz)}
>> write.excel(head(USArrests))
>> write.excel(head(iris))
>>
>> Any suggestion on how to export the same information on two separate sheets,
>> and
>>
>> keeping also a title for each of them, is highly appreciated, as i have been
>> searching for some time for a good solution.
>>
>> Thank you very much and have a great day ahead!

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: export tables to excel files on multiple sheets with titles for each table

Gabor Grothendieck
In reply to this post by eugen
On Tue, Jul 13, 2010 at 4:21 PM, eugen pircalabelu
<[hidden email]> wrote:

> Hello R-users,
> Checking the archives, I recently came across this topic:
> "export tables to Excel files"
> (http://r.789695.n4.nabble.com/export-tables-to-Excel-files-td1565679.html#a1565679),
>  and the following interesting references have been proposed:
> http://rwiki.sciviews.org/doku.php?id=tips:data-io:ms_windows
> http://www.r-bloggers.com/export-data-frames-to-multi-worksheet-excel-file-2/
>
> but my problem is somehow a small extension to what has been discussed, and
> although i have a solution, i seek something more elegant. I want to export
> multiple dataframes (on multiple sheets), but i also want each of them to have
> its own title that is to be written also in Excel. The packages/functions that i
> have checked, cannot accommodate a title that is to be written on the sheet,
> along with the actual dataframe of interest.
>

You might check through the packages listed here:
http://rwiki.sciviews.org/doku.php?id=tips:data-io:ms_windows&rev=1266947178

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: export tables to excel files on multiple sheets with titles for each table

Erich Neuwirth
In reply to this post by Marc Schwartz-3
You also could use RExcel and write some VBA macros doing this task for you.
You can essentially have the rcom R-centric solution or the
VBA-centric RExcel solution.


On Jul 14, 2010, at 12:19 AM, Marc Schwartz wrote:

> If I am correctly understanding what Eugen is trying to do, WriteXLS() won't get him there. WriteXLS() will enable you to label/name the worksheets (tabs) but not allow you to precede the actual data frame rows and columns on the sheet with a title or label.
>
> I suspect that you may have to look at the RCom package tools for this. This provides greater flexibility in writing to the worksheets and cells.
>
> See http://rcom.univie.ac.at/ for more information.
>
> HTH,
>
> Marc Schwartz
>
>
> On Jul 13, 2010, at 4:09 PM, Felipe Carrillo wrote:
>
>> Check the WriteXLS package, I think it does that and also saves
>> each R object on a different excel sheet.
>>
>> Felipe D. Carrillo
>> Supervisory Fishery Biologist
>> Department of the Interior
>> US Fish & Wildlife Service
>> California, USA
>>
>>
>>
>> ----- Original Message ----
>>> From: eugen pircalabelu <[hidden email]>
>>> To: R-help <[hidden email]>
>>> Sent: Tue, July 13, 2010 1:21:33 PM
>>> Subject: [R] export tables to excel files on multiple sheets with titles for
>>> each table
>>>
>>> Hello R-users,
>>> Checking the archives, I recently came across this topic:
>>> "export tables to Excel files"
>>> (http://r.789695.n4.nabble.com/export-tables-to-Excel-files-td1565679.html#a1565679),
>>> ,
>>> and the following interesting references have been proposed:
>>> http://rwiki.sciviews.org/doku.php?id=tips:data-io:ms_windows
>>> http://www.r-bloggers.com/export-data-frames-to-multi-worksheet-excel-file-2/
>>>
>>> but my problem is somehow a small extension to what has been discussed, and
>>> although i have a solution, i seek something more elegant. I want to export
>>> multiple dataframes (on multiple sheets), but i also want each of them to have
>>
>>> its own title that is to be written also in Excel. The packages/functions that
>>> i
>>>
>>> have checked, cannot accommodate a title that is to be written on the sheet,
>>> along with the actual dataframe of interest.
>>>
>>> I can do something similar to what i need, but without writing the dataframes
>>> on
>>>
>>> multiple sheets.
>>>
>>> #head(USArrests) and head(iris) written with accompanying title one under each
>>
>>> other
>>>
>>> write.excel<-function (tab, ...){
>>> zz <- file("example.dat", "a+b")
>>> cat("\"TITLE extra line",file = zz, sep = "\n")
>>> write.table(tab, file=zz, row.names=F,sep="\t")
>>> close(zz)}
>>> write.excel(head(USArrests))
>>> write.excel(head(iris))
>>>
>>> Any suggestion on how to export the same information on two separate sheets,
>>> and
>>>
>>> keeping also a title for each of them, is highly appreciated, as i have been
>>> searching for some time for a good solution.
>>>
>>> Thank you very much and have a great day ahead!
>
> ______________________________________________
> [hidden email] 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.
>

--
Erich Neuwirth
Didactic Center for Computer Science and Institute for Scientific Computing
University of Vienna





        [[alternative HTML version deleted]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: export tables to excel files on multiple sheets with titles for each table

Whit Armstrong-3
In reply to this post by eugen
It isn't beautiful, but I use this package to write excel files from linux.

http://github.com/armstrtw/Rexcelpoi

the basic idea is that each element of a list is written as a separate
sheet, but if a list element is itself a list, then all the elements
of that list are written to the same sheet (with a title corresponding
to the name of the list element).

-Whit


On Tue, Jul 13, 2010 at 4:21 PM, eugen pircalabelu
<[hidden email]> wrote:

> Hello R-users,
> Checking the archives, I recently came across this topic:
> "export tables to Excel files"
> (http://r.789695.n4.nabble.com/export-tables-to-Excel-files-td1565679.html#a1565679),
>  and the following interesting references have been proposed:
> http://rwiki.sciviews.org/doku.php?id=tips:data-io:ms_windows
> http://www.r-bloggers.com/export-data-frames-to-multi-worksheet-excel-file-2/
>
> but my problem is somehow a small extension to what has been discussed, and
> although i have a solution, i seek something more elegant. I want to export
> multiple dataframes (on multiple sheets), but i also want each of them to have
> its own title that is to be written also in Excel. The packages/functions that i
> have checked, cannot accommodate a title that is to be written on the sheet,
> along with the actual dataframe of interest.
>
> I can do something similar to what i need, but without writing the dataframes on
> multiple sheets.
>
> #head(USArrests) and head(iris) written with accompanying title one under each
> other
>
> write.excel<-function (tab, ...){
>  zz <- file("example.dat", "a+b")
>  cat("\"TITLE extra line",file = zz, sep = "\n")
>  write.table(tab, file=zz, row.names=F,sep="\t")
>  close(zz)}
>  write.excel(head(USArrests))
>  write.excel(head(iris))
>
> Any suggestion on how to export the same information on two separate sheets, and
> keeping also a title for each of them, is highly appreciated, as i have been
> searching for some time for a good solution.
>
> Thank you very much and have a great day ahead!
>
>
>
>
>
>
>  Eugen Pircalabelu
> (0032)471 842 140
> (0040)727 839 293
>
> ______________________________________________
> [hidden email] 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.
>

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: export tables to excel files on multiple sheets with titles for each table

Erich Neuwirth
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]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: SOLVED - export tables to excel files on multiple sheets with titles for each table

eugen
Hello R-users,
I was able using RExcel and the VBA script offered by Eric Neuwirth (modifying
it a little bit) to import dataframes on separate Excel sheets, each with its
own particular title. As such the problem is solved.

Thank you all for your suggestions.
Have a nice day ahead!  


 Eugen Pircalabelu






________________________________
From: Erich Neuwirth <[hidden email]>
To: Whit Armstrong <[hidden email]>

<[hidden email]>
Sent: Fri, July 16, 2010 6:00:55 AM
Subject: Re: [R] export tables to excel files on multiple sheets with titles for
each table


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

______________________________________________
[hidden email] 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.