Creating Excel Files from R using {openxlsx}

Yesterday I had to share data with some internal stakeholders in excel format. Why excel? Well, I guess if you are not a “data professional”, then that’s the easiest format to work with.

So, I started R, read in the dataset, did some data wrangling and then wanted to write the data to a xlsx file. I heard great things about the openxlsx package so I used it for the first time. My initial attempt was using the write.xlsx() function. That certainly did the job but the resulting file didn’t look how I like my excel files. I am a fan of a freezed top row with bold and centered column headers.

I digged a bit into the openxlsx documentation and it turned out that the package can do all of this. Great!

Here is the code I used (obviously not exporting the mtcars dataset):

library(openxlsx)
data(mtcars)

header_style <- createStyle(halign = "center", textDecoration = "bold")

wb <- createWorkbook()

addWorksheet(wb, "Data")
writeData(wb, "Data", mtcars, headerStyle = header_style)
freezePane(wb, "Data", firstRow = TRUE)
setColWidths(wb, "Data", cols = 1:ncol(mtcars), widths = "auto")
saveWorkbook(wb, file = "mtcars.xlsx", overwrite = TRUE)

The only thing that didn’t quite work was adjusting the column width to fit the content. While there is a setColWidths() function where you can set the widths parameter to "auto" that didn’t work a 100%. It did look much better than having all columns in the standard width, though. In the end that was good enough for me.