[R] Saving Splitted Series to Excel via XLConnect

[Henrique Andrade]

Dear Rui and David,

Thanks a lot for your help and advices. Now finally I have
what I want ;-) The final code looks like this:

<R code begin>

dados <- data.frame(matrix(c("2012-01-01","2012-02-01",
  "2012-03-01","2012-04-01","2012-05-01","2012-06-01",
  "2012-01-01","2012-02-01","2012-03-01","2012-04-01",
  "2012-05-01","2012-06-01","2012-01-01","2012-02-01",
  "2012-03-01","2012-04-01","2012-05-01","2012-06-01",
  0.56,0.45,0.21,0.64,0.36,0.08,152136,153081,155872,
  158356,162157,166226,33.47,34.48,35.24,38.42,35.33,
  34.43,433,433,433,433,433,433,2005,2005,2005,2005,
  2005,2005,3939,3939,3939,3939,3939,3939),nrow=18,
  ncol=3,byrow=FALSE,dimnames=list(c(1,2,3,4,5,6,7,8,9,
  10,11,12,13,14,15,16,17,18),c("date","value","code"))))

dados2 <- split(dados, dados$code)

library(XLConnect)

wb <- loadWorkbook("Henrique.xlsx", create = TRUE)
series <- seq_along(dados2)
createSheet(wb, name = "Planilha")

lapply(series, function(i){
	column <- (3*i) - 2
	writeWorksheet(wb, dados2[[i]], sheet = "Planilha",
        startCol = column)})
saveWorkbook(wb)

<R code end>

I did not use the option to build a new sheet for each "i"
because I have more than 200 objects in my real problem.

Again, many thanks to you guys!

Best regards (or "Um abraço"),
Henrique Andrade


2012/8/8 Rui Barradas <ruipbarradas em sapo.pt>:
> Hello,
>
> First of all, apologies to Henrique, he'll receive th same answer twice, but
> I forgot to Cc the list.
>
>
> In order to write 3 worksheets you need to create 3 worksheets. What
> happened is that you were overwriting the previous sheets and ended up just
> with the last one. So adopt a different method: lapply().
>
>
> wb <- loadWorkbook("Teste.xlsx", create = TRUE)
>
> series <- seq_along(dados2)
> sheet <- paste0("Teste", series)
> lapply(series, function(i){
>     createSheet(wb, name = sheet[i])
>     writeWorksheet(wb, dados2[[i]], sheet = sheet[i])})
>
> saveWorkbook(wb)
>
> This worked with me.
>
> Hope this helps,
>
> Rui Barradas
> Em 08-08-2012 21:10, Henrique Andrade escreveu:
>>
>> Dear R Discussion List,
>>
>>
>> I would like to save my data as a xlsx file. But at first
>> I need to split it and then save each series into a Excel
>> column. Please take a look at the following code:
>>
>> dados <- data.frame(matrix(c("2012-01-01","2012-02-01",
>>    "2012-03-01","2012-04-01","2012-05-01","2012-06-01",
>>    "2012-01-01","2012-02-01","2012-03-01","2012-04-01",
>>    "2012-05-01","2012-06-01","2012-01-01","2012-02-01",
>>    "2012-03-01","2012-04-01","2012-05-01","2012-06-01",
>>    0.56,0.45,0.21,0.64,0.36,0.08,152136,153081,155872,
>>    158356,162157,166226,33.47,34.48,35.24,38.42,35.33,
>>    34.43,433,433,433,433,433,433,2005,2005,2005,2005,
>>    2005,2005,3939,3939,3939,3939,3939,3939),nrow=18,
>>    ncol=3,byrow=FALSE,dimnames=list(c(1,2,3,4,5,6,7,8,9,
>>    10,11,12,13,14,15,16,17,18),c("date","value","code"))))
>>
>> dados2 <- split(dados, dados$code)
>> dados2
>>
>> library(XLConnect)
>>
>> wb <- loadWorkbook("Teste.xlsx", create = TRUE)
>> createSheet(wb, name = "Teste1")
>> writeWorksheet(wb, dados2, sheet = "Teste1")
>> saveWorkbook(wb)
>>
>> With this code I only get the "433" series. How could I
>> fix my code? How could I include the other series?
>>
>> Many thanks in advance,
>> Henrique Andrade
>>
>> ______________________________________________
>> R-help em 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.
>
>



-- 
Henrique Andrade