Re: [R] Creating an Excel file with multiple spreadsheets
2009/3/9 Phil Spector : > The xlsReadWrite package provides write.xls for Windows, but it cannot write _multiple_ spreadsheets -- Regards, Hans-Peter __ 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] Creating an Excel file with multiple spreadsheets
On Mon, 09-Mar-2009 at 02:34PM -0400, Jorge Ivan Velez wrote: |> DeaR all, |> |> I'd like to know how to create an Excel file with multiple |> spreadsheets from R. I searched the help files and found [1] but it |> is not what I want to do. If you're happy to limit yourself to distributing your excel file to people who have excel 2007 (or have the converters for the older versions of office), or with a manual step of opening it yourself in 2007 and saving-as in an older version, I have a script that utilises the xml format that Excel now accepts. It's a bit ugly as it's got lots of stuff specific to my organisation, but if you want it can be obtained from http://bioinformatics.cancerresearchuk.org/cms/index.php?page=gpk-resources I really should contact the authors of the r/excel packages to see if any of them would want a cleaned up version of this. Regards - Gavin [[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] Creating an Excel file with multiple spreadsheets
The xlsReadWrite package provides write.xls for Windows, while the dataframes2xls package provides write.xls for non-Windows systems. - Phil Spector Statistical Computing Facility Department of Statistics UC Berkeley spec...@stat.berkeley.edu On Mon, 9 Mar 2009, Carl Witthoft wrote: Just wondering -- the referenced thread at [1] http://tinyurl.com/aurprr included a comment from Gabor (I think) that some day the gdata package might include a 'write.xls' based on M Schwartz' perl script. Any progress there? Carl __ 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] Creating an Excel file with multiple spreadsheets
I do not believe that anything formal has been done. Calling the perl script within an R function call would not be overly difficult, the key would be to package the perl modules required by the script, otherwise the user would have to install these manually from CPAN or via an OS specific package management system. I would also note that I recently posted an updated version of the script here: https://stat.ethz.ch/pipermail/r-help/2009-March/190327.html which generalizes the script to use the updated version of Spreadsheet::WriteExcel, overcoming the prior file size limit and also better handles UTF-8 encoding, which the prior version did not do properly. HTH, Marc Schwartz On Mar 9, 2009, at 5:03 PM, Gabor Grothendieck wrote: It was from Greg, not me. On Mon, Mar 9, 2009 at 5:53 PM, Carl Witthoft wrote: Just wondering -- the referenced thread at [1] http://tinyurl.com/aurprr included a comment from Gabor (I think) that some day the gdata package might include a 'write.xls' based on M Schwartz' perl script. Any progress there? Carl __ 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] Creating an Excel file with multiple spreadsheets
It was from Greg, not me. On Mon, Mar 9, 2009 at 5:53 PM, Carl Witthoft wrote: > Just wondering -- the referenced thread at > > [1] http://tinyurl.com/aurprr > > included a comment from Gabor (I think) that some day the gdata package > might include a 'write.xls' based on M Schwartz' perl script. > > Any progress there? > > > Carl > > __ > 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] Creating an Excel file with multiple spreadsheets
Just wondering -- the referenced thread at [1] http://tinyurl.com/aurprr included a comment from Gabor (I think) that some day the gdata package might include a 'write.xls' based on M Schwartz' perl script. Any progress there? Carl __ 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] Creating an Excel file with multiple spreadsheets
if you don't find the solution you need, I have a package that uses
Apache POI to do this, but you will need to compile it yourself.
contact me if you want to go this route.
-Whit
On Mon, Mar 9, 2009 at 3:34 PM, Patrick Connolly
wrote:
> On Mon, 09-Mar-2009 at 02:34PM -0400, Jorge Ivan Velez wrote:
>
> |> DeaR all,
> |>
>
> |> I'd like to know how to create an Excel file with multiple
> |> spreadsheets from R. I searched the help files and found [1] but it
> |> is not what I want to do.
>
> I use the Perl script from Marc Schwartz in that list. It's very
> useful. I think if you can work out the R code to make the dataframes
> you want, it's a matter of using write.csv() to make the CSV files that
> the Perl code can use.
>
>
>
> |>
> |> [1] http://tinyurl.com/aurprr
> |>
>
>
> --
> ~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.
> ___ Patrick Connolly
> {~._.~} Great minds discuss ideas
> _( Y )_ Average minds discuss events
> (:_~*~_:) Small minds discuss people
> (_)-(_) . Eleanor Roosevelt
>
> ~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.
>
> __
> 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] Creating an Excel file with multiple spreadsheets
On Mon, 09-Mar-2009 at 02:34PM -0400, Jorge Ivan Velez wrote:
|> DeaR all,
|>
|> I'd like to know how to create an Excel file with multiple
|> spreadsheets from R. I searched the help files and found [1] but it
|> is not what I want to do.
I use the Perl script from Marc Schwartz in that list. It's very
useful. I think if you can work out the R code to make the dataframes
you want, it's a matter of using write.csv() to make the CSV files that
the Perl code can use.
|>
|> [1] http://tinyurl.com/aurprr
|>
--
~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.
___Patrick Connolly
{~._.~} Great minds discuss ideas
_( Y )_ Average minds discuss events
(:_~*~_:) Small minds discuss people
(_)-(_) . Eleanor Roosevelt
~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.
__
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] Creating an Excel file with multiple spreadsheets
DeaR all,
I'd like to know how to create an Excel file with multiple spreadsheets from
R. I searched the help files and found [1] but it is not what I want to do.
Let's say I have a data frame called DF as in the following code
# Some data
set.seed(123)
ID<-sample(5,100,replace=TRUE)
X<-rnorm(100)
Y<-rpois(100,10)
Z<-X*Y
DF<-data.frame(ID,X,Y,Z)
str(DF)
I'd like to split DF by ID and then save the result in an Excel file (say
"information.xls") with multiple spreadsheets (one per ID). Also, my output
should include the original data as a new spreadsheet called, for example,
"original". At the end I will have (in this example) six different
spreadsheets in "information.xls".
To split() DF I can easily do
# Splitting data
sDF<-with(DF,split(DF,ID))
sDF
and to save the information per ID in different Excel files, rather than in
"information.xls" with a spreadsheet by ID, I can do
# Working directory -- please change this when run the code in your R
session
setwd("C:\\")
# Saving the information by ID in a different Excel file
# called "information_ID_numberhere.xls"
lapply(1:5,function(i)
write.table(sDF[[i]][,-1],paste("information_ID_",i,".xls",sep=""),col.names=TRUE,row.names=FALSE,sep="\t")
)
How can I export DF (the original data), DF such as ID==1, DF such as
ID==2,..., DF such as ID==5 to "information.xls" (just one file) naming the
spreadsheets as "original", "ID_1", "ID_2",...,"ID_5", respectively?
I'm running R 2.8.1 Patched on XP. Here is my sessionInfo()
R version 2.8.1 Patched (2009-01-01 r47434)
i386-pc-mingw32
locale:
LC_COLLATE=English_United States.1252;LC_CTYPE=English_United
States.1252;LC_MONETARY=English_United
States.1252;LC_NUMERIC=C;LC_TIME=English_United States.1252
attached base packages:
[1] grid splines stats graphics grDevices utils datasets
methods base
other attached packages:
[1] ggplot2_0.8.2 reshape_0.8.2 plyr_0.1.5 proto_0.3-8
epicalc_2.8.1.1
[6] survival_2.34-1foreign_0.8-33 lattice_0.17-20
xlsReadWrite_1.3.2 RODBC_1.2-5
Any help would be greatly appreciated.
Thanks in advance,
Jorge Ivan Velez
[1] http://tinyurl.com/aurprr
[[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.
