How to use Excel file in R?

 The xlsx is a file extension of a spreadsheet file format which was created by Microsoft to work with Microsoft Excel. In the present era, Microsoft Excel is a widely used spreadsheet program that sores data in the .xls or .xlsx format. R allows us to read data directly from these files by providing some excel specific packages. There are lots of packages such as XLConnect, xlsx, gdata, etc. We will use xlsx package, which not only allows us to read data from an excel file but also allow us to write data in it.

Install xlsx Package

Our primary task is to install "xlsx" package with the help of install.package command. When we install the xlsx package, it will ask us to install some additional packages on which this package is dependent. For installing the additional packages, the same command is used with the required package name. There is the following syntax of install command:

1. install.packages("package name")   

Example

1. install.packages("xlsx")  

Output

Verifying and Loading of "xlsx" Package

In R, grepl() and any() functions are used to verify the package. If the packages are installed, these functions will return True else return False. For verifying the package, both the functions are used together.

For loading purposes, we use the library() function with the appropriate package name. This function loads all the additional packages also.

Example

1. #Installing xlsx package  
2. install.packages("xlsx")  
3.   
4. # Verifying the package is installed.  
5. any(grepl("xlsx",installed.packages()))  
6.   
7. # Loading the library into R workspace.  
8. library("xlsx")  

Output

Creating an xlsx File

Once the xlsx package is loaded into our system, we will create an excel file with the following data and named it employee.

Apart from this, we will create another table with the following data and give it a name as employee_info.

Note: Both the files will be saved in the current working directory of the R workspace.

Reading the Excel File

Like the CSV file, we can read data from an excel file. R provides read.xlsx() function, which takes two arguments as input, i.e., file name and index of the sheet. This function returns the excel data in the form of a data frame in the R environment. There is the following syntax of read.xlsx() function:

1. read.xlsx(file_name,sheet_index)  

Let's see an example in which we read data from our employee.xlsx file.

Example

1. #Loading xlsx package  
2. library("xlsx")  
3.   
4. # Reading the first worksheet in the file employee.xlsx.  
5. excel_data<- read.xlsx("employee.xlsx", sheetIndex = 1)  
6. print(excel_data)  

Output

Writing data into Excel File

In R, we can also write the data into our .xlsx file. R provides a write.xlsx() function to write data into the excel file. There is the following syntax of write.xlsx() function:

1. write.xlsx(data_frame,file_name,col.names,row.names,sheetnames,append)  

Here,

• The data_frame is our data, which we want to insert into our excel file.
• The file_names is the name of that file in which we want to insert our data.
• The col.names and row.names are the logical values that are specifying whether the column names/row names of the data frame are to be written to the file.
• The append is a logical value, which indicates our data should be appended or not into an existing file.

Let's see an example to understand how write.xlsx() function works with its parameters.

Example

1. #Loading xlsx package  
2. library("xlsx")  
3.   
4. #Creating data frame  
5. emp.data<- data.frame(    
6. name = c("Raman","Rafia","Himanshu","jasmine","Yash"),    
7. salary = c(623.3,915.2,611.0,729.0,843.25),     
8. start_date = as.Date(c("2012-01-01", "2013-09-23", "2014-11-15", "2014-05-11","2015-03-27")),  
9. dept = c("Operations","IT","HR","IT","Finance"),    
10. stringsAsFactors = FALSE    
11. )    
12.   
13. # Writing the first data set in employee.xlsxRscript  
14. write.xlsx(emp.data, file = "employee.xlsx", col.names=TRUE, row.names=TRUE,sheetName="Sheet2",append = TRUE)  
15.       
16. # Reading the first worksheet in the file employee.xlsx.  
17. excel_data<- read.xlsx("employee.xlsx", sheetIndex = 1)  
18. print(excel_data)  
19.   
20. # Reading the first worksheet in the file employee.xlsx.  
21. excel_data<- read.xlsx("employee.xlsx", sheetIndex = 2)  
22. print(excel_data)  

Output