How to Export a data frame to Excel in R

Filed Under: R Programming
Export A Dataframe To Excel In R

What if I tell, that you can export data frames to excel in R within a couple of minutes? This is possible with the help of functions like writexl and openxlsx. Seems interesting? In this tutorial, we are going to see how we can export data frames to excel in R.

Before we dive into the tutorials, let’s understand what is writexl and openxlsx functions.

Start with installing required packages

writexl: writexl is a library in R, which is capable of writing R data frames into excel files. To install this package, run the below code.

install.packages('writexl')

Openxlsx: The R library which is very helpful in manipulating the data and export to Excel. To install this package, run the below code.

install.packages('openxlsx')

Create a data frame and Export to Excel

Well, I hope you have successfully installed those packages. Now let’s create a data frame in R and export it to Excel using ‘writexl’ function.

#creates a dataframe in R
mydataframe<- data.frame(name=c('John','Angelina','Lisa','Joseph','Antonio'), 
age=c(32,30,26,23,28),
gender=c('male','female','female','male','male'))

After creating this data frame, let’s export it to Excel using the writexl function.

#import the required library
library(writexl)
#exports the data to excel and dumps it to the working directory/folder
#to see your working directory run 'getwd()' in R studio

mydataframe<- data.frame(name=c('John','Angelina','Lisa','Joseph','Antonio'), 
age=c(32,30,26,23,28),
gender=c('male','female','female','male','male'))

write_xlsx(mydataframe, 'export_a_dataframe_to_excel.xlsx')

The output excel file is shown below.

Exporting A Datafame In R To Excel

Import a dataset in R and export it to Excel

You have exported a simple data frame in R to excel in the above section. Here we are going to import a data set or a CSV file and export it to Excel file.

#imports the iris data set
x<-datasets::iris
View(x) 
#exports the data frames to excel 
write_xlsx(x, 'Exporitng_a_csv_file_to_excel.xlsx')
Data Frame To Excel

Export a data frame to Excel by manipulating data using openxlsx

Till now, we have exported the data frames to Excel without any data manipulations. But what if you need to export the data with some meaningful changes? There comes the use of functions ‘dplyr’ and ‘openxlsx’.

Dplyr library will assist you in data manipulation i.e. you can make changes to the dataset by adding a column, a row, or a percentage and more.

Openxlsx function allows you to make all possible changes such as adding a style, font, color and you can also create a worksheet using this function.

The below image shows the basic data set available in R.

Openxlsx Function In R

Let’s make some changes in the header of the data frame using dplyr. Here we are changing the color of headlines and alignment to center. We have also changed the values in the Illiteracy column as percentages.

Execute the below code to create excel file with new header style.

#imports the required libraries
library(dplyr)
library(rio)
library(openxlsx)

#creates a data frame with percentage and manipulated it using dplyr
mydata<- as.data.frame(state.x77)%>%
 mutate(State=row.names(state.x77),Illiteracy=Illiteracy/100)%>%
 select(State, Population:Area)

#creates a new headline style
my_headline<-createStyle(halign = "center",textDecoration = "Bold",fontSize = 14,fontColour = "blue")

#changes the headline style
write.xlsx(mydata, "new_header_style.xlsx", headerStyle=my_headline)
#chnages the values in illeteracy column as percentages 
class(mydata$Illiteracy)<-"percentage"

#exports the excel file with new header style and percentage of values
write.xlsx(mydata, 'with_percentage_stye.xlsx', headerStyle=my_headline)

The data frame with a new headline style and alignment style is shown below.

New Header Style

Create a new table style in R and export to Excel

You can also change the table theme or style by adding this piece of code to the above code.

#creates a new table style or theme
write.xlsx(mydata, "new_table_style.xlsx", asTable = T, tableStyle='TableStyleLight1')
New Table Style In R 1

Alright, we have created data frames, imported data frames, and exported to excel using the writexl and openxlsx functions. We did data manipulation and exported the data frame with a new look to excel.

But, there are some other possibilities that you can do in R. Let’s look into that.


Can we create a workbook in R and Export to Excel?

The answer will be a big ‘YES’. You can create an entire workbook and add worksheets into that with some data along with some changes which will be meaningful and make sense to readers.

Let’s create!!!

#creates a new workbook with new worksheet
myworkbook<-createWorkbook()
addWorksheet(myworkbook,"MySheet")
writeData(myworkbook,"MySheet",mydata)
 
#adds the different nemurical styles and the number formates to data frame 
my_dollar_style<-createStyle(numFmt = "$0,0")
my_percentage_style<-createStyle(numFmt = "0.0%")
my_comma_style<-createStyle(numFmt = "0,0")
 
#incorporates the styles to data
addStyle(myworkbook, "MySheet", style = my_dollar_style, rows = 2:nrow(mydata),cols = 3)
addStyle(myworkbook,"MySheet",style = my_percentage_style,rows = 2:nrow(mydata),cols = 4)
addStyle(myworkbook,"MySheet",style = my_comma_style,rows = 2:nrow(mydata), cols = 9)

#save the workbook with new number formats as well
saveWorkbook(myworkbook,"with_numerical_styling.xlsx", overwrite = T)

In the below workbook, you can see the income column has been added with a dollar sign and, values are separated by commas. These numerical format changes will make sense and convey the information in a more detailed way.

New Workbook In R

Conclusion

R is a perfect language for data analysis. You can do wonders using data in R. In this tutorial we have gone through the various ways of exporting a data frame to excel. You got to know how you can manipulate the data and bring some life into that before exporting to excel.

That’s all for now, stay connected for more R tutorials and I am sure that we can continue playing with some more data the next time. Happy exporting!!!

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages