2

I would like to pass the results of a linear model calculation from R to a worksheet in Excel. For doing this I am using XLConnect with the following code:

x <- 1000:2000
y <- 3*x+rnorm(length(x))
fit <- lm(y~x-1)
result <- summary(fit)$coeff

print(result)

require(XLConnect)
wb <- loadWorkbook("/Users/andreas/test1.xls", create = TRUE)
createSheet(wb, name = "test")
writeWorksheet(wb, result, sheet = "test", startRow = 1, startCol = 1)
saveWorkbook(wb)

However, the problem is that XLConnect does not pass the name of the coeffiecient(s) to Excel (here: x) although they are printed out correctly.

Does anybody has an idea about that issue?

I would greatly appreciate any help.

Andy

Andy
  • 9,483
  • 12
  • 38
  • 39

2 Answers2

5

The variable name in this case ends up as a rowname once result is converted to a data frame. There is an argument called rownames in writeWorksheet:

wb <- loadWorkbook("~/Desktop/test1.xls", create = TRUE)
createSheet(wb, name = "test")
writeWorksheet(wb, result, sheet = "test", startRow = 1, startCol = 1,rownames = "var")
saveWorkbook(wb)
joran
  • 169,992
  • 32
  • 429
  • 468
0

You could pass the names of the coefficient table separately to the values:

writeWorksheet(wb, colnames(result), sheet="test", startRow=1, startCol=2)
writeWorksheet(wb, rownames(result), sheet="test", startRow=2, startCol=1)
writeWorksheet(wb, result, sheet="test", startRow=2, startCol=2)
Hong Ooi
  • 56,353
  • 13
  • 134
  • 187
  • Hi Hong Ooi, thank you very much for your help. Unfortunately, your solution does not work. If I do names(result) it returns NULL. Do you have an idea about that – Andy Jun 26 '13 at 08:30
  • Ah, I didn't see that you were getting the table of coefficients from `summary`. I assumed you were just doing `coef(fit)`. – Hong Ooi Jun 26 '13 at 08:33
  • Thanks, that works great. Although, I do not understand why you create nr and nc, if you do not use them later on. – Andy Jun 26 '13 at 08:53
  • Because the coffee has worn off. :) Edited (yet again). – Hong Ooi Jun 26 '13 at 08:56