write.xlsx()

In this document, I will introduce the write.xlsx() function and show what it’s for.

What is it for?

The write.xlsx allows you to create an Excel file from a data frame or list of data frames, with similar functionality to write.csv.

The basic syntax is shown below:

write.xlsx(x, file, asTable = FALSE, overwrite = TRUE, ...)

There are also many optional parameters you can use to customize the Excel file, such as:

  • creator Name of the workbook author
  • sheetName Name of the worksheet
  • tabColour Colour of the worksheet tab
  • keepNA If FALSE, NA will be empty cells. If True, NAs will be converted to #N/A.

You can also freeze panes or adjust the grid, borders, or column widths.

Example 1

Let’s create an Excel file from the penguins data set. You can do this just by specifying:

  • data frame
  • where to save it
  • file name
write.xlsx(penguins,
           file = here("Data", "penguins_new.xlsx"))
knitr::include_graphics(here("excel1.jpg"))

Example 2

Let’s try customizing by changing the header style and removing the grid lines using the cars data set.

glimpse(cars)
## Rows: 50
## Columns: 2
## $ speed <dbl> 4, 4, 7, 7, 8, 9, 10, 10, 10, 11, 11, 12, 12, 12, 12, 13, 13, 13…
## $ dist  <dbl> 2, 10, 4, 22, 16, 10, 18, 26, 34, 17, 28, 14, 20, 24, 28, 26, 34…
#create a style variable
style <- createStyle(
textDecoration = "BOLD", fontSize = 14,
fontName = "Arial")

#create an Excel file
write.xlsx(as.data.frame(cars),
           file = here("Data", "car_data.xlsx"),
           sheetName = "cars",
           headerStyle = style,
           gridLines = FALSE)
knitr::include_graphics(here("excel2.jpg"))

Is it helpful?

Yes, write.xlsx could be useful if you are cleaning or manipulating a data set in R, but want to save a copy of your data in Excel. This function has a lot of customization options, but it may be easier to customize your file in Excel, rather than with this function.