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]] __ 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.
Re: [R] export tables to excel files on multiple sheets with titles for each table
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 eugen_pircalab...@yahoo.com
To: R-help r-h...@stat.math.ethz.ch
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!
__
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.
--
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.
Re: [R] export tables to excel files on multiple sheets with titles for each table
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
eugen_pircalab...@yahoo.com 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
__
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.
__
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.
[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
__
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.
Re: [R] export tables to excel files on multiple sheets with titles for each table
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 eugen_pircalab...@yahoo.com
To: R-help r-h...@stat.math.ethz.ch
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
__
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.
__
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.
Re: [R] export tables to excel files on multiple sheets with titles for each table
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 eugen_pircalab...@yahoo.com
To: R-help r-h...@stat.math.ethz.ch
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!
__
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.
Re: [R] export tables to excel files on multiple sheets with titles for each table
On Tue, Jul 13, 2010 at 4:21 PM, eugen pircalabelu eugen_pircalab...@yahoo.com 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_windowsrev=1266947178 __ 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.
Re: [R] export tables to Excel files
OK, I think dirung the disuccion we mixed up the directions of transfer. The example I sent was Excel - R For R - Excel you need something like RInterface.GetDataframe dfname, Worksheets(wsname).Range(A1) dfname, wsname and the cell refence (A1) have to be given for each dataframe you want to transfer. You need to install RExcel to do this. CRAN has a package RExcelInstaller which does everything for you. On 2/24/2010 4:35 PM, Tal Galili wrote: Hi Erich. I admit I haven't gotten all that is necessary to implement it. But thanks anyway :) Tal Contact Details:--- Contact me: tal.gal...@gmail.com mailto:tal.gal...@gmail.com | 972-52-7275845 Read me: www.talgalili.com http://www.talgalili.com (Hebrew) | www.biostatistics.co.il http://www.biostatistics.co.il (Hebrew) | www.r-statistics.com http://www.r-statistics.com (English) -- On Wed, Feb 24, 2010 at 12:55 PM, Erich Neuwirth erich.neuwi...@univie.ac.at mailto:erich.neuwi...@univie.ac.at wrote: This was a VBA program which from R put the dataframes into R. Then in R you can do whatever you want. On 2/24/2010 7:52 AM, Tal Galili wrote: (off mailing list) Thanks Erich for the example, only please note that I asked about the other way around (reading the excel into R) Either way - Thanks! Tal Contact Details:--- Contact me: tal.gal...@gmail.com mailto:tal.gal...@gmail.com mailto:tal.gal...@gmail.com mailto:tal.gal...@gmail.com | 972-52-7275845 Read me: www.talgalili.com http://www.talgalili.com http://www.talgalili.com (Hebrew) | www.biostatistics.co.il http://www.biostatistics.co.il http://www.biostatistics.co.il (Hebrew) | www.r-statistics.com http://www.r-statistics.com http://www.r-statistics.com (English) -- On Wed, Feb 24, 2010 at 1:01 AM, Erich Neuwirth erich.neuwi...@univie.ac.at mailto:erich.neuwi...@univie.ac.at mailto:erich.neuwi...@univie.ac.at mailto:erich.neuwi...@univie.ac.at wrote: In RExcel, you can write VBA macros to perform R-related function. A cooked up example (not checked) Sub TransferFrames() MySheetNames = Array(Sheet1, Sheet2, Sheet3) MyDFNames = Array(mydf1, mydf2, mydf3) RInterface.StartRServer For i = Lbound(MySheetNames) to UBound(MySheetNames) RInterface.PutDataframe MyDFNames(i), _ Worksheets(MySheetNames(i)).Range(A1).CurrentRegion Next i End Sub This will take the data in the rectangular nonempty range anchored at cell A1 for the 3 names sheets and transfer them to R as dataframes with the names given in MyDFNames On 2/23/2010 10:46 PM, Tal Galili wrote: Hi Richard, Thanks for pointing this out. BTW - How would you use Rexcel to write several data frames into several sheets in excel ? Thanks! Tal Contact Details:--- Contact me: tal.gal...@gmail.com mailto:tal.gal...@gmail.com mailto:tal.gal...@gmail.com mailto:tal.gal...@gmail.com | 972-52-7275845 Read me: www.talgalili.com http://www.talgalili.com http://www.talgalili.com (Hebrew) | www.biostatistics.co.il http://www.biostatistics.co.il http://www.biostatistics.co.il (Hebrew) | www.r-statistics.com http://www.r-statistics.com http://www.r-statistics.com (English) -- On Tue, Feb 23, 2010 at 5:04 PM, RICHARD M. HEIBERGER r...@temple.edu mailto:r...@temple.edu mailto:r...@temple.edu mailto:r...@temple.eduwrote: Please consider RExcel, which allows complete integration of R and Excel. See http://rcom.univie.ac.at for details and examples including a video. RExcel works in both directions (R to Excel, Excel to R) with Excel 2010, 2007, 2003, and 2002. Rich [[alternative HTML version deleted]] __ R-help@r-project.org
[R] export tables to Excel files
Dear R users, I've just posted a similar question about Illustrator. This time I would like to export the results of my statistic tables and my dataframes into Excel files. Up to now I've used write.csv(), but I have to resave every file in .xls in Excel. I would like to know if there is a function or package to export directly into *.xls. I have found xlsReadWrite which would be perfect for me, if only I could append data to an existing file (I need to do it) and it doesn't work with R2.10 (of course the version I use...). Of course, since I would like to export .xls files, I would have to be able to read them too, which means that the package WriteXLS wouldn't be enough. And in any case, appending data is not possible. The package xlsx is not what I need since I still use Excel 2003. Are there other packages that would correspond to my needs? There might also be a better, completely different, approach. I'm open to all suggestions of course! Thanks in advance for your help Ivan __ 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.
Re: [R] export tables to Excel files
See R wiki for discussion of various packages that interface R and Excel: http://rwiki.sciviews.org/doku.php?id=tips:data-io:ms_windows On Tue, Feb 23, 2010 at 4:35 AM, Ivan Calandra ivan.calan...@uni-hamburg.de wrote: Dear R users, I've just posted a similar question about Illustrator. This time I would like to export the results of my statistic tables and my dataframes into Excel files. Up to now I've used write.csv(), but I have to resave every file in .xls in Excel. I would like to know if there is a function or package to export directly into *.xls. I have found xlsReadWrite which would be perfect for me, if only I could append data to an existing file (I need to do it) and it doesn't work with R2.10 (of course the version I use...). Of course, since I would like to export .xls files, I would have to be able to read them too, which means that the package WriteXLS wouldn't be enough. And in any case, appending data is not possible. The package xlsx is not what I need since I still use Excel 2003. Are there other packages that would correspond to my needs? There might also be a better, completely different, approach. I'm open to all suggestions of course! Thanks in advance for your help Ivan __ 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. __ 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.
Re: [R] export tables to Excel files
Please consider RExcel, which allows complete integration of R and Excel. See http://rcom.univie.ac.at for details and examples including a video. RExcel works in both directions (R to Excel, Excel to R) with Excel 2010, 2007, 2003, and 2002. Rich [[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.
Re: [R] export tables to Excel files
Richard, is it possible to use RExcel under Linux, not to interface with Excel of course but to read and write Excel files? - Try http://prettygraph.com Pretty Graph , the easiest way to make R-powered graphs on the web. -- View this message in context: http://n4.nabble.com/export-tables-to-Excel-files-tp1565679p1566021.html Sent from the R help mailing list archive at Nabble.com. __ 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.
Re: [R] export tables to Excel files
Hi Richard, Thanks for pointing this out. BTW - How would you use Rexcel to write several data frames into several sheets in excel ? Thanks! Tal Contact Details:--- Contact me: tal.gal...@gmail.com | 972-52-7275845 Read me: www.talgalili.com (Hebrew) | www.biostatistics.co.il (Hebrew) | www.r-statistics.com (English) -- On Tue, Feb 23, 2010 at 5:04 PM, RICHARD M. HEIBERGER r...@temple.eduwrote: Please consider RExcel, which allows complete integration of R and Excel. See http://rcom.univie.ac.at for details and examples including a video. RExcel works in both directions (R to Excel, Excel to R) with Excel 2010, 2007, 2003, and 2002. Rich [[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. [[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.
