1. Adapting sheets
Up to now, XLConnect isn't blowing your mind, is it? Just listing sheets and importing data from them into R is nothing new. But XLConnect has more to offer than just importing excel data into R. This package is an easy tool to modify the content of your workbook comfortably through R.
2. New data!
Say you managed to lay your hands om some fresh population data from 2010, that is stored in a data frame, pop_2010.
3. createSheet()
To store this info a new sheet, we start with loading XLConnect, and making a connection to the workbook. After that, we can use createSheet, and pass the workbook and the name of the new sheet, like this.
4. createSheet()
Now we can actually populate our new sheet with the data, for which we'll use
5. writeWorksheet()
writeWorksheet. The first argument, as always, is the workbook, followed by the data we want to add, so pop_2010 and finally the sheet we want to add it to. Let's use the sheet name that was specified in createSheet, but the number of the sheet, 3, would work fine as well.
If you open the excel file, though, you won't see the new sheet. You'll have to explicitly save the entire workbook to a file for the changes to take effect.
6. saveWorkbook()
You do this with saveWorkbook, like this.
7. saveWorkbook()
I suggest you specify a new filename, cities2.xlsx for example, so you don't overwrite the file you started with.
If you now check out the new Excel file, you see that the additional data is in there. Awesome!
Suppose that after creating this additional worksheet, you don't feel comfortable with the name you chose. In fact, you want to rename all sheets. Piece of cake:
8. renameSheet()
just use the renameSheet function. As uaual, the first argument is the workbook, and then you pass the old name and the new name. We'll use this command three times, the change year with y for the three different sheets.
9. renameSheet()
Finally, we save the result again to a new file with saveWorksheet: cities3.xlsx.
A quick peek at the new Excel file reveals that we successfully renamed the sheets. Perfect.
10. removeSheet()
Another Excel job would be to remove a sheet altogether. To remove the third sheet here, for example, simply use removeSheet with the workbook and the sheet name or sheet number as arguments.
11. removeSheet()
If you save the workbook to a file again and open up the file, our third sheet is gone.
12. Wrap-up
Of course these are pretty basic operations, that you can easily do in Excel as well, but the cool thing is that you can program all of these tasks in R in a reproducible way. If you update the commands that we've used here, and run them all again, one after the other, it should all still work fine.
Apart from the functions I discussed here, there are also methods to style cells, getting, setting and recalculate formulas, merging and splitting up cells, the whole shebang. But let's not dive into those here and start small.
13. Let's practice!
Head over to the exercises and give it a try yourself!