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