How to read Excel file in R?

[vishrut-singhal]

R Excel file

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:

`

install.packages(“package name”)

`

Example

`

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

#Installing xlsx package  
install.packages("xlsx")  
  
# Verifying the package is installed.  
any(grepl("xlsx",installed.packages()))  
  
# Loading the library into R workspace.  
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:

`

read.xlsx(file_name,sheet_index)

`

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

Example

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

Output