Site icon R-bloggers

Using the xlsx package to create an Excel file

[This article was first published on R-exercises, 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.

Microsoft Excel is perhaps the most popular data anlysis tool out there. While arguably convenient, spreadsheet software is error prone and Excel code can be very hard to review and test.

After successfully completing this exercise set, you will be able to prepare a basic Excel document using just R (no need to touch Excel yourself), leaving behind a reproducible R-script.

Solutions are available here.

Exercise 1
Install and load the xlsx package, using the dependencies = TRUE option.

Exercise 2
Create an xlsx workbook object in your R workspace and call it wb.

Exercise 3
Create a sheet object in wb named iris assign it the name sheet1 in your workspace.

Exercise 4
Write the built-in Iris data.frame to the iris sheet without row names. Hint: use the addDataFrame() function.

Now you can write your workbook anytime to your working directory using saveWorkbook(wb, "filename.xlsx").

< aside class='stb-icon'>
Learn more about working with excel and R in the online course Learn By Example: Statistics and Data Science in R. In this course you will learn how to:
  • Learn some of the differences between working in Excel with regression modelling and R
  • Learn about different statistical concepts
  • And much more

Exercise 5
Apply ‘freeze pane’ on the top row.

Exercise 6
Set width of columns 1 through 5 to 12, that is 84 pixels.

Exercise 7
Use Font, CellBlock and CB.setFont to make the header in bold.

Exercise 8
Using tapply generate a table with the mean of ‘petal width’ by species and write to a new sheet called pw, from row 2 down.

Exercise 9
Add a title in cell A1 above the table, merge the cells of the first three columns.

Exercise 10
Save your workbook to your working directory and open using Excel. Go back to R and continue formatting and adding information to your workbook at will.

Related exercise sets:

  1. Building Shiny App exercises part 1
  2. Data wrangling : I/O (Part-1)
  3. Building Shiny App exercises part 4
  4. Explore all our (>1000) R exercises
  5. Find an R course using our R Course Finder directory

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

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.