Excel Files and Operations in R Programming Language: Methods and Examples

Microsoft Excel is the most widely used spreadsheet program which stores data in the .xls or .xlsx format. R can read directly from these files using some excel specific packages. Few such packages are - XLConnect, xlsx, gdata etc. We will be using xlsx package. R can also write into excel file using this package.

Install xlsx Package

You can use the following command in the R console to install the "xlsx" package. It may ask to install some additional packages on which this package is dependent. Follow the same command with required package name to install the additional packages.

install.packages("xlsx")

Verify and Load the "xlsx" Package

Use the following command to verify and load the "xlsx" package.

# Verify the package is installed.
any(grepl("xlsx",installed.packages()))

# Load the library into R workspace.
library("xlsx")

When the script is run we get the following output-

[1] TRUE

Input as xlsx File

Open Microsoft excel. Copy and paste the following data in the work sheet named as sheet1.

id	Name	salary	Job_date	dept
1	Rumman	12623.3	01-01-2012	IT
2	Jaman	32515.2	23-09-2013	Operations
3	Inza	342611	15-11-2014	IT
4	Azam	232729	11-05-2014	HR
5	Sabir	45843.25	27-03-2015	Finance
6	Jakir	322578	21-05-2013	IT
7	Sourav	221632.8	30-07-2013	Operations
8	Ramu	22722.5	17-06-2014	Finance

Also copy and paste the following data to another worksheet and rename this worksheet to "city".

Name	city
Rumman	kolkata
Jaman	Mumbai
Inza	Mumbai
Azam	kolkata
Sabir	Mumbai
Jakir	kolkata
Sourav	Mumbai
Ramu	kolkata

Save the Excel file as "inputData.xlsx". You should save it in the current working directory of the R workspace.

Reading the Excel File

The input.xlsx is read by using the read.xlsx() function as shown below. The result is stored as a data frame in the R environment.

# Read the first worksheet in the file input.xlsx.
data

When we execute the above code, it produces the following result −

   id   Name    salary   Job_date       dept
1  1 Rumman  12623.30 2012-01-01         IT
2  2  Jaman  32515.20 2013-09-23 Operations
3  3   Inza 342611.00 2014-11-15         IT
4  4   Azam 232729.00 2014-05-11         HR
5  5  Sabir  45843.25 2015-03-27    Finance
6  6  Jakir 322578.00 2013-05-21         IT
7  7 Sourav 221632.80 2013-07-30 Operations
8  8   Ramu  22722.50 2014-06-17    Finance

All Code in one file

 install.packages("xlsx")

# Verify the package is installed.
any(grepl("xlsx",installed.packages()))

# Load the library into R workspace.
library("xlsx")

# Read the first worksheet in the file input.xlsx.
data <- read.xlsx("inputData.xlsx", sheetIndex = 1)
print(data)