Modifying Excel Files using openxlsx

October 15, 2018

(This article was first published on R on Abhijit Dasgupta, and kindly contributed to R-bloggers)

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:

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. offers daily e-mail updates about R news and tutorials on topics such as: Data science, Big Data, R jobs, visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...

If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Comments are closed.

Search R-bloggers


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)