How do I append data from a data frame in R to an Excel sheet that already exists

Solution 1:

Appending to an existing Excel worksheet is a bit of a pain. Instead, read all of your Excel data files into R, combine them within R, and then write the single combined data frame to a new Excel file (or write to a csv file if you don't need the data to be in an Excel workbook). See code below for both the easy way and the hard way.

Easy Way: Do all the work in R and save a single combined data frame at the end

For example, if all of your Excel data files are in the current working directory and the first worksheet in each Excel file contains the data, you could do the following:

library(xlsx)

# Get file names
file.names = list.files(pattern="xlsx$")

# Read them into a list
df.list = lapply(file.names, read.xlsx, sheetIndex=1, header=TRUE)

Then combine them into a single data frame and write to disk:

df = do.call(rbind, df.list)

write.xlsx(df, "combinedData.xlsx", sheetName="data", row.names=FALSE)

Hard Way: Append successive data frames to a pre-existing Excel worksheet

Create a list of data frames that we want to write to Excel (as discussed above, in your actual use case, you'll read your data files into a list in R). We'll use the built-in iris data frame for illustration here:

df.list = split(iris, iris$Species)

To write each data frame to a single Excel worksheet, first, create an Excel workbook and the worksheet where we want to write the data:

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

# Add the first data frame
addDataFrame(df.list[[1]], sheet=sheet, row.names=FALSE, startRow=1)

Now append all of the remaining data frames using a loop. Increment startRow each time so that the next data frame is written in the correct location.

startRow = nrow(df.list[[1]]) + 2    

for (i in 2:length(df.list)) {

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

  startRow = startRow + nrow(df.list[[i]])

  }

Save the workbook:

saveWorkbook(wb, "combinedData.xlsx")

addDataFrame is useful if you want to layout various summary tables in various parts of an Excel worksheet and make it all look nice for presentation. However, if you're just combining raw data into a single data file, I think it's a lot easier to do all the work in R and then just write the combined data frame to an Excel worksheet (or csv file) at the end.