[R] Question of the XLConnect package

From: christiaan pauw <cjpauw_at_gmail.com>
Date: Mon, 30 May 2011 17:24:30 +0200


Hi Everybody

I have started to learn how to use the XLConnect package and I think it is going to be very helpful to help me to operate between Excel users.

Is there a function that can export a dataframe to a Excel sheet and automatically create a named region for every column (excluding the column heading) that correspond to the column name? If this is possible one can then also create formulae from R that will just work when opened in Excel without having to figure out the indices in Excel

I have figured out how to do that individually for each column but would lie to automate the process

library(XLConnect)
wb <- loadWorkbook("Testdata.xlsx", create = TRUE) # create a workbook

testdata=expand.grid(letters[1:10], 1:10) # create some data

createSheet(wb,"testdata") # create sheet

writeWorksheet(wb,testdata,sheet="testdata") # write data to sheet

# Create named regions named the column names

col1idx=idx2cref(c(2,grep("Var1",names(testdata)), 1+length(testdata$Var1), grep("Var1",names(testdata)))) #

addres1=paste(col1idx[[1]],":",col1idx[[2]],sep="")

createName(wb,name="Var1",formula=paste("testdata!",addres1,sep=""))

# repeat for column 2
col2idx=idx2cref(c(2,grep("Var2",names(testdata)), 1+length(testdata$Var2), grep("Var2",names(testdata))))

addres2=paste(col2idx[[1]],":",col2idx[[2]],sep="")

createName(wb,name="Var2",formula=paste("testdata!",addres2,sep=""))

createSheet(wb,"results") # a place to test if the named regions can be used within excel

ave="=AVERAGE(Var2)"

som="=Sum(Var2)"

writeWorksheet(wb, data.frame(ave), sheet = "results", startRow = 2, startCol = 2)

writeWorksheet(wb, data.frame(som), sheet = "results", startRow = 4, startCol = 2)

saveWorkbook(wb)

What I need is:
1. a function to repeat this process of naming the regions over 169 columns, and
2. that the excel formulas in sheet "results" really work. (I found that if you find and replace "=" with "=" in Excel it make all the formulas work, maybe there is a more elegant way from within R.)

Does anybody have any advice?

Thanks in advance
Christiaan



sessionInfo()
R version 2.11.1 (2010-05-31)
x86_64-apple-darwin9.8.0

locale:
[1] en_US/en_US/en_US/C/en_US/en_US

attached base packages:
[1] splines grid stats graphics grDevices utils datasets
 methods base

other attached packages:
[1] Hmisc_3.8-0 survival_2.35-8 XLConnect_0.1-4
XLConnectJars_0.1-1 rJava_0.8-4 vcd_1.2-9
[7] colorspace_1.1-0 MASS_7.3-6

loaded via a namespace (and not attached):
[1] cluster_1.12.3 lattice_0.18-8 tools_2.11.1

        [[alternative HTML version deleted]]



R-help_at_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. Received on Mon 30 May 2011 - 15:26:51 GMT

This quarter's messages: by month, or sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

All messages

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Mon 30 May 2011 - 15:30:11 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.

list of date sections of archive