Search code examples
rexcelxlsxxlconnect

R. Exporting a list to a single sheet in Excel

I'd like to export a list of different-sized objects to a single Excel sheet. In other words, I'd like one matrix to show up, then below it, the next matrix. Here's a simple example using XLConnect:

mat1<-matrix(c(0,1,2,3),nrow=2,ncol=2)

mat2<-matrix(c(0,1,2,3,4,5),nrow=2,ncol=3)

list<-list(mat1,mat2)

wb<-loadWorkbook("XLConnectExample1.xlsx",creat=TRUE)
createSheet(wb,name="sheet")
writeWorksheet(wb,list,sheet="sheet")
saveWorkbook(wb)

Right now, mat1 is simply written over the top of mat2.

Solution

  • I'm not that familiar with XLConnect but here's a method using the xlsx package. The key is to keep track of the cumulative number of rows taken up by the matrices that have already been written to the worksheet, so that you don't overwrite them.

    library(xlsx)

# Fake data
mat1<-matrix(c(0,1,2,3),nrow=2,ncol=2)
mat2<-matrix(c(0,1,2,3,4,5),nrow=2,ncol=3)
mat3 = matrix(1:8, nrow=4)

mat.list<-list(mat1,mat2,mat3)

    Create a workbook and a worksheet within that workbook:

    wb = createWorkbook()
sheet = createSheet(wb, "data")

    Write each matrix, one below the other, skipping one row in between. r is a row counter that we use to determine the starting row for placing each successive matrix.

    r = 1
for (i in 1:length(mat.list)) {

  addDataFrame(mat.list[[i]], sheet, col.names=FALSE, row.names=FALSE, startRow=r)

  r = r + nrow(mat.list[[i]]) + 1

}

saveWorkbook(wb, "test.xlsx")

    Here's a screenshot of the resulting Excel worksheet:

    enter image description here