Search code examples
rxlsx

In R, is it possible to write an empty data frame with no observations but with column names to an Excel Sheet?

I have 2 data frame: one with observations and one with no observations. I am using the "xlsx" package to write the data frame to an Excel Sheet. Because the second data frame has no observations, the write.xlsx function outputs the error:

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

I want to be able to write an Excel Sheet with the empty data frame and keep the column names, and output with no observations. 

library(xlsx)
nonemptydf <- data.frame("SN" = 1:2,
                         "Age" = c(21, 15),
                         "Name" = c("John", "Jane"))
emptydf <- data.frame("SN" = numeric(),
                      "Age" = numeric(),
                      "Name" = character())
write.xlsx(nonemptydf,
           "Test.xlsx",
           sheetName = "Not empty")

#The code below won't work because emptydf has no observations
write.xlsx(emptydf,
           "Test.xlsx",
           sheetName = "Empty",
           append = TRUE)

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

Solution

  • Tibbles have more flexibility and functionality than 'data.frame' objects. In particular, you can use the tribble() function to specify column names directly in the call.

    # load the packages    
library(tidyverse)    # provides the tribble() function
library(zip)          # helps zip the dataset that will be exported to R
library(rio)          # better export package than xlsx

# create your tibble
d <- tribble(
       ~SN, ~Age, ~Name
)

# convert to data.frame (if you'd prefer this class); it's not necessary though...
d <- as.data.frame(d)

# export your data frame as an Excel-compatible file
export(d, "data.xlsx")