R Excel File Handling

As we know, Microsoft Excel has widespread used in many different application fields. R provides some packages for performing actions on excel, such as reading, writing, manipulating data, etc.

So, what we need first is to install the package. Excel stores data in .xls or .xlsx format. In this tutorial, we are using .xlsx format. The xlsx package depends on 'rJava' and 'xlsxjars' packages as it is a Java based solution, so make sure these packages are installed on the system.

Install Excel(.xlsx) Package

To install a package, use the install.package() command with the package name, like as follows -

install.packages("xlsx")

Load Excel(.xlsx) Package

We hope you have successfully installed the excel package. Now to use this package in your application, you need to load the package library by using library() function.

library("xlsx")

Reading Excel File

R provides read.xlsx() function to read data from an excel file. The syntax of read.xlsx() is -

read.xlsx(file, sheetIndex = "sheetname", header=TRUE)

Here, file is the filename with path, sheetIndex is the index of the sheet and if we set the header parameter to TRUE, it takes the first row as header of the document.

Example

Suppose, there is an excel file name 'employee.xlsx' located in the current working directory.

id name age department
1 Dhyan 26 HR
2 Jorz 27 Finance
3 Mary 29 IT
4 Sinoy 32 IT
5 Lyo 30 Finance

The following command reads the above excel file -

data <- read.xlsx('employee.xlsx', sheetName = 1)
print(data)

Writing to Excel Package

R provides write.xlsx() function to create a new xlsx file or append data to the existing excel file. This is syntax of write.xlsx() -

write.xlsx(data, file, sheetName="Sheet1", 
  col.names=TRUE, row.names=TRUE, append=FALSE, showNA=TRUE, password=NULL)

Here, data is the data in data.frame format to write in the workbook, the file is the filename of the spreadsheet, the sheetName is the name of excel sheet, col.names and row.names are logical values and this indicates whether the column names and row names of x are written along with x in the file. The append is also a logical value, set it to true if we want to append the data to an existing file. The showNA parameter is used to set the NA value and the last password parameter is used to make the excel sheet secure with a password.

The following command writes to an excel file -

data <- write.xlsx('employee.xlsx', sheetName = 1)
print(data)