Handling empty data frame with write.xlsx

Question

How do I handle empty data frames when i'm using write.xlsx within a loop?

Below is what the loop looks like, where source("./Scripts/Analysis_details.R") is referencing the r file where the data frames are created.

library(xlsx) 
    for (A in unique(df_base$A)) {
          df<- df_base[df_base$A==A,]
          source("./Scripts/Analysis_details.R")
          output_file = paste("./Output/report_", A, '_', Sys.Date(), ".xlsx", sep='')
          write.xlsx(df1, file=output_file, sheetName="df1", append=TRUE, row.names=FALSE, showNA = FALSE)
          write.xlsx(df2, file=output_file, sheetName="df2", append=TRUE, row.names=FALSE, showNA = FALSE)
          write.xlsx(df3, file=output_file, sheetName="df3", append=TRUE, row.names=FALSE, showNA = FALSE)
          write.xlsx(df4, file=output_file, sheetName="df4", append=TRUE, row.names=FALSE, showNA = FALSE)}

The error that I'm getting is...

Error in mapply(setCellValue, cells[seq_len(nrow(cells)), colIndex[ic]],  : zero-length inputs cannot be mixed with those of non-zero length

score 8 · Accepted Answer · answered Dec 18 '17 at 19:03

I was able to work around this by putting a modified write.xlsx() function into my script. If the data frame has zero rows, .write_block() is skipped and and the file is saved with just the column names. Note that you'll also have to copy the original .write_block() function into your script as well.

write.xlsx.custom <- function(x, file, sheetName="Sheet1",
                       col.names=TRUE, row.names=TRUE, append=FALSE, showNA=TRUE)
{
    if (!is.data.frame(x))
        x <- data.frame(x)    # just because the error message is too ugly

    iOffset <- jOffset <- 0
    if (col.names)
        iOffset <- 1
    if (row.names)
        jOffset <- 1

    if (append && file.exists(file)){
        wb <- loadWorkbook(file)
    } else {
        ext <- gsub(".*\\.(.*)$", "\\1", basename(file))
        wb  <- createWorkbook(type=ext)
    }  
    sheet <- createSheet(wb, sheetName)

    noRows <- nrow(x) + iOffset
    noCols <- ncol(x) + jOffset
    if (col.names){
        rows  <- createRow(sheet, 1)                  # create top row
        cells <- createCell(rows, colIndex=1:noCols)  # create cells
        mapply(setCellValue, cells[1,(1+jOffset):noCols], colnames(x))
    }
    if (row.names)             # add rownames to data x                   
        x <- cbind(rownames=rownames(x), x)

    if(nrow(x) > 0) {
        colIndex <- seq_len(ncol(x))
        rowIndex <- seq_len(nrow(x)) + iOffset

        .write_block(wb, sheet, x, rowIndex, colIndex, showNA)
    }
    saveWorkbook(wb, file)

    invisible()
}

I found the original functions at https://github.com/cran/xlsx/blob/master/R/write.xlsx.R

Handling empty data frame with write.xlsx

1 Answers1