R language Excel file

Microsoft Excel is the most widely used spreadsheet program that stores .xls data .xlsx or other formats. T he R language can use some excel-specific packages directly from these files. R arely are such packages - XLConnect, xlsx, gdata, etc. W e will use the xlsx package. T he R language can also use this package to write to excel files.

Install the xlsx package

You can use the following commands in the R console to install the "xlsx" package. I t may require the installation of some additional packages that this package relies on. I nstall additional packages with the same command that has the desired package name.

install.packages("xlsx")

Validate and load the "xlsx" package

Use the following commands to validate 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 runs, we get the following output.

[1] TRUE
Loading required package: rJava
Loading required package: methods
Loading required package: xlsxjars

Enter as an xlsx file

Open Microsoft Excel. C opy and paste the following data into a worksheet named sheet1.

id	name      salary    start_date	dept
1	Rick	  623.3	    1/1/2012	IT
2	Dan       515.2     9/23/2013   Operations
3	Michelle  611	    11/15/2014	IT
4	Ryan	  729	    5/11/2014	HR
5	Gary	  843.25    3/27/2015	Finance
6	Nina	  578       5/21/2013	IT
7	Simon	  632.8	    7/30/2013	Operations
8	Guru	  722.5	    6/17/2014	Finance

Also copy and paste the following data into another worksheet and rename it "city."

name	 city
Rick	 Seattle
Dan      Tampa
Michelle Chicago
Ryan	 Seattle
Gary	 Houston
Nina	 Boston
Simon	 Mumbai
Guru	 Dallas

Save the Excel file as input .xlsx. I t should be saved in the current working directory of the R work area.

Read the Excel file

Read the input .xlsx by using the read.xlsx () function, as shown below. T he results are stored as data frames in the R-language environment.

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

When we execute the code above, it produces the following results -

      id,   name,    salary,   start_date,     dept
1      1    Rick     623.30    2012-01-01      IT
2      2    Dan      515.20    2013-09-23      Operations
3      3    Michelle 611.00    2014-11-15      IT
4      4    Ryan     729.00    2014-05-11      HR
5     NA    Gary     843.25    2015-03-27      Finance
6      6    Nina     578.00    2013-05-21      IT
7      7    Simon    632.80    2013-07-30      Operations
8      8    Guru     722.50    2014-06-17      Finance