Modifying Excel Files using openxlsx

[This article was first published on R on Abhijit Dasgupta, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

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)

To leave a comment for the author, please follow the link and comment on their blog: R on Abhijit Dasgupta.

R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)