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

