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.