In R, there are many ways to save a dataframe as Excel file *.xlsx. In this tutorial, we will use the R package openxlsx and learn how to save a dataframe as xlsx file. First we will learn how to save a xlsx file using write.xlsx() function in openxlsx package. And then we will see an example of how to overwrite an existing xlsx file and finally we will learn how to assign a name to excel sheet.
Load Package and Create a Dataframe
Let us first load openxlsx package.
library(openxlsx)
We will build a simple dataframe from scratch by using two vectors and data.frame() function in R.
language <- c("R","Python", "SQL","Java", "JavaScript", "Rust", "Go")
salary <- c(143000, 150000,144000, 155000, 146000, 180000,179000)
# create a dataframe from two vectors
df <- data.frame(Language= language, Salary=salary)
Our dataframe looks like this with two columns and 7 rows.
df ## Language Salary ## 1 R 143000 ## 2 Python 150000 ## 3 SQL 144000 ## 4 Java 155000 ## 5 JavaScript 146000 ## 6 Rust 180000 ## 7 Go 179000
Save Dataframe as Xlsx file in R
To save a dataframe as xlsx file, we use write.xlsx() with the dataframe name and the file name we would like to write to. In this example, we save the dataframe into xlsx file named “language_salary.xlsx”.
write.xlsx(df, "language_salary.xlsx")
The saved excel file looks like this, with automatically assigned sheet name “sheet1” mentioned on top of the image here.
Over writing an existing Excel file
The above example considers the simplest case of writing a dataframe into xlsx file. However, if we already the have a file with the same name and try to write with the same statement as above,
write.xlsx(df, "language_salary.xlsx")
We will get an “Error in saveWorkbook” errors
Error in saveWorkbook(wb, file = file, overwrite = overwrite) : File already exists!
By default, write.xlsx() has the argument “overwrite=FALSE”, By specifying overwrite = TRUE, we can over write on an existing file.
write.xlsx(df, "language_salary.xlsx", overwrite = TRUE)
Assign name to Excel sheet in R
Earlier we noticed that write.xlsx() function automatically assigns sheet name as “Sheet1”. Sometimes, we would like to assign a name to the data we write to xlsx file. We can use “sheetName” argument with the name for the dataframe we are saving as Excel file.
write.xlsx(df, "language_salary.xlsx",
sheetName = "salary",
overwrite = TRUE)
