How to export to Excel with R

Hi. I’m Sharon Machlis at IDG, here with episode 35 of Do More With R: Export your data to Excel – with formatting or multiple tabs!

I’m going to use two packages for this: rio and openxlsx. I’ll also load dplyr, because I almost always end up using dplyr.

For data, I’m going to use the state.x77 data set that comes with R. It’s a matrix; I’ll turn it into data frame with as.data.frame() so I can add a column with state names, which are character strings. The next lines of code add the state names. That last line moves the State name column to position number 1. I also divided the Illiteracy column by 100 so that a number like 2.1 – which stands for 2.1 percent – will be .021. That’s just so I can show some Excel percent formatting.

If you only want to send your data to a single Excel sheet without formatting, rio makes it incredibly easy. It’s just export(data frame name, “file name ending in dot xlsx”). The file name can be anything, as long as it ends in .xlsx. (rio::export() can create a lot of other file types also, if you change the file name extension to something like dot csv or dot json).

You can see the new Excel file here. It’s basic with no formatting, but it’s got all the data.

When it creates an Excel file, rio::export() is a wrapper for the openxlsx package’s write.xlsx() function. To add arguments when I create my file, I’ll switch to write.xlsx().

Next, I’ll add simple header formatting. An easy way to do that is to create a headline style object with the createStyle() function. Then I can use that style in the write.xlsx command. You see it here in the headerStyle argument.

There are a couple of ways to format the cells. If you’re happy with Excel’s out-of-the-box formats like “Number”, “Currency”, and “Percentage”, you can set those with a class() command. In the third line, I set the class of the Illiteracy column to “percentage.” Now if I create an Excel file, that column will have Excel’s Perentage format. You can see the Illiteracy column now is in percent style.

One more cool option for write.xlsx: You can create an Excel table, complete with dropdown filters and sorting and table styles.

It’s as easy as adding asTable = TRUE and then choosing any of Excel’s available table styles (here I use Light 2). Now I’ve got a table!

Unfortunately, I’m almost never happy with Excel’s default formats. I don’t want 2 decimal places after the percent column, I want one. And for the Income column, I want to add a dollar sign but no decimal places.

For more specific styling in cells below the header, I need a slightly more robust openxlsx process. Each step is simple, but there’s just a bit more to it than a single write command.

Let me show you some cod examples. First you create a workbook object. Next you add a worksheet to that workbook. Then write data to the sheet. There are a couple of different ways to add styling. One is what I showed before, adding a class to a data frame column. Another way is to create a specific style – here I made a percent format with one decimal place. Then I need to use addStyle() to apply the style to worksheet number one in my workbook (I can specify sheets by number or name). For body styling, I also need to specify the exact rows and columns I want for addStyle(). In the last line here, I want my style in column 3, for rows 2 through the last row of my data (that’s the nrow(mydf)). And, last step: Save the workbook with saveWorkbook(). Let me show you an example with the states data frame.

This code creates a workbook, adds a worksheet, writes data to the sheet”. Next, I create some styles. The dollar style adds a dollar sign and comma, and rounds to no decimal places because there’s no .00 in the format. The percent style adds a percent sign and one decimal place (but no comma). And the basic comma format has a comma and no decimal places.Then I apply each style with addStyle()

Finally, I save the workbook to a file called with_granular_styling.xlsx. I added overwrite = TRUE – that makes sure I get a new version of my file if the file already exists. See I’ve got commas in Population and Area, dollar sign but no cents in Income, and one decimal place for Illiteracy.

Last thing I want to show you is how to write data to multiple tabs. Here I’ve created 4 new data frames from the original states data, by region: Northeast, West, South, and NorthCentral. Next, I made a list of those data frames, and named each item in the list. Now if I want to quickly export those to a single Excel file, one data frame per tab, without worrying about styling, I can use the write.xlsx() function. My data frame list is the first argument and an Excel file name is the 2nd argument. And, there’s my data, one data frame per tab!

That’s it for this episode, thanks for watching! For more R tips, head to the Do More With R page at go dot infoworld dot com slash more with R, all lowercase except for the R.

You can also find the Do More With R playlist on the YouTube IDG Tech Talk channel -- where you can subscribe so you never miss an episode. Hope to see you next time!