I’ve been creating several output tables for a paper, which I usually store as sheets in an Excel file, since my collaborators are entirely in the Microsoft Office ecosystem.

One issue I often run into is having to modify a single sheet in that file with updated data, while keeping the rest of the file intact. This is necessary since I’ve perhaps done some custom formatting in Excel on some of the tables, and I don’t want to re-format them everytime I modify a single sheet. This problem can be alleviated by creating output functions in R that properly format the output tables in the first place, making the entire process reproducible. Working on this one, stay tuned!

But for now, how to modify a single Excel sheet in a file? The openxlsx package allows this to be done very easily. As an aside, if you interact with R and Excel and are not using openxlsx, why aren’t you? This doesn’t depend on Java and has several powerful features.

The following code reads an existing Excel file, checks if a particular sheet exists, creates it if it doesn’t, and writes data from a data.frame results to it and then saves it back on disk:

library(openxlsx)
boldHeader <- createStyle(textDecoration = 'bold') # Makes first row bold
wb <- loadWorkbook('Tables.xlsx')
if (!('Supplemental Table 1' %in% names(wb))) addWorksheet(wb, 'Supplemental Table 1')
writeData(wb, 'Supplemental Table 1', results, headerStyle = boldHeader)
setColWidths(wb, 'Supplemental Table 1', cols = 1:ncol(results), widths = 'auto')
saveWorkbook(wb, 'Tables.xlsx', overwrite = T)