Skip to content

Saving Splitted Series to Excel via XLConnect

7 messages · arun, David Winsemius, Rui Barradas +2 more

Henrique Andrade
# 
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
arun
# 
Hi,

I think saving it as .csv file will be much easier.? I am getting all the series in the wide format.

write.csv(dados2,"dados2.csv")
  

 X2005.date X2005.value X2005.code X3939.date X3939.value X3939.code X433.date X433.value X433.code 
7 2012-01-01 152136 2005 2012-01-01 33.47 3939 2012-01-01 0.56 433 
8 2012-02-01 153081 2005 2012-02-01 34.48 3939 2012-02-01 0.45 433 
9 2012-03-01 155872 2005 2012-03-01 35.24 3939 2012-03-01 0.21 433 
10 2012-04-01 158356 2005 2012-04-01 38.42 3939 2012-04-01 0.64 433 
11 2012-05-01 162157 2005 2012-05-01 35.33 3939 2012-05-01 0.36 433 
12 2012-06-01 166226 2005 2012-06-01 34.43 3939 2012-06-01 0.08 433 

A.K.



----- Original Message -----
From: Henrique Andrade <henrique.coelho at gmail.com>
To: R Discussion List <r-help at r-project.org>
Cc: 
Sent: Wednesday, August 8, 2012 4:10 PM
Subject: [R] Saving Splitted Series to Excel via XLConnect

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 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.
Henrique Andrade
# 
Dear Arun,

Thanks a lot for your help, but I really need to save the splitted
data in a Excel file (xlsx or xls). This is because I will need to
make links between different Excel workbooks.

Best regards,
Henrique Andrade

Enviado via iPhone

Em 08/08/2012, ?s 17:25, arun <smartpink111 at yahoo.com> escreveu:
David Winsemius
#
On Aug 8, 2012, at 1:10 PM, Henrique Andrade wrote:

            

      
      
      
    

        
That doesn't make much sense. You have three columns. What are you  
calling a "series" (which is not a term that has any particular  
meaning in R.)

            

      
      
      
    

        
Perhaps:

str(dados2)  # it is a list of three dataframes

writeWorksheet(wb, do.call(rbind, dados2), sheet = "Teste1")
saveWorkbook(wb)

This basically undoes the split() operation that created dados2.

Please note that those dates are actually factor variables in R.

            

      
      
      
    

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      Rui Barradas
    

    

      
      #
    

  


  

      
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:

            

      
      
      
    

        

      
      
    

  
  


  


      

    

    

      
      

        


  

        

      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 at sapo.pt>:

            

      
      
      
    

        

      
      
    

            

      
      
      
    

        

  
    

      
      
    

  


  


      

    

    

      
      

        


  

        

      John
    

    

      
      #
    

  


  

      
On Wed, 8 Aug 2012 18:06:52 -0300

        
Henrique Andrade <henrique.coelho at gmail.com> wrote:

        

            

      
      
      
    

        
As it is your purpose is unclear.  If you can provide examples of
starting data and endstate it might help.  You can import a csv file
to an excel workbook.  Are you actually looking to export each column
of your R data as a separate xls file or as a separate sheet in a
workbook? There's no point in worrying about xls vs xlsx since you make
the conversion in excel anyway.  As it is your purpose is unclear.  If
you can provide examples of starting data and endstate it might help.  

JDougherty