How To Use XLSX Package To Read Data In R

Introduction

 
R can read data from various types of files having data in different formats. One such data format is the format of files created using Microsoft Excel. Files saved in Microsoft Excel are generally in .xls or .xlsx format.
 
Data available in files supporting .xls or .xlsx format can be read by R with the help of various packages provided in the directory of R.
 
In this article, we will be using the xlsx package to read excel data from files available in .xls or .xlsx format. Data can also be written back to an Excel file using the same xlsx package.
 

Installing the Package

 
To install a package in R environment, we can execute the below command on the console of R:
  1. > install.packages("xlsx")  
It will generate the following output:
  1. > install.packages("xlsx")  
  2. WARNING: Rtools is required to build R packages but is not currently installed. Please download and install the appropriate version of Rtools before proceeding:  
  3.   
  4. https://cran.rstudio.com/bin/windows/Rtools/  
  5. Installing package into ‘R/win-library/4.0’  
  6. (as ‘lib’ is unspecified)  
  7. trying URL 'https://cran.rstudio.com/bin/windows/contrib/4.0/xlsx_0.6.5.zip'  
  8. Content type 'application/zip' length 374680 bytes (365 KB)  
  9. downloaded 365 KB  
  10.   
  11. package ‘xlsx’ successfully unpacked and MD5 sums checked  
  12.   
  13. The downloaded binary packages are in  
  14.     RtmpKoVlUZ\downloaded_packages  
  15. >  
We can also execute the command below to check if xlsx package has been installed or not:
  1. > any(grepl("xlsx",installed.packages()))  
The above code will generate the following output:
  1. [1] TRUE  
  2. >  
So, as we can see, the syntax is returning the value as TRUE, which means that the xlsx package is installed properly.
 
To load the xlsx package, we need to install and load the rJava package as well:
  1. install.packages("rJava")  
We can also execute the command below to check if the rJava package has been installed or not:
  1. > any(grepl("rJava",installed.packages()))  
  2. [1] TRUE  
After installing the package we can load the package as a library into the R environment and later use it to load excel data in R.
 
To load a rJava package library in R, we can run the below syntax as follows:
  1. > library(rJava)  
After installing the package we can load the package as a library into the R environment and later use it to load excel data in R.
 
To load a xlsx package library in R, we can run the below syntax as follows:
  1. > library(xlsx)  
Therefore, the above syntax loads the package into the environment of R.
 

Loading the Data

 
Now we will be reading and loading data from a file named workers.xlsx. The read.xlsx() function can be used to load excel data from a file supporting .xls and .xlsx format. Once the data gets load, it is stored in a data frame.
 
Instead of loading the entire data of workers.xlsx file, we can load a single worksheet as well using the below syntax:
  1. > data_excel = read.xlsx("CHIT_Profile.xlsx", sheetIndex = 1)  
  2. print(data_excel)  
The above code snippet will generate the following:
  1. > data_excel = read.xlsx("CHIT_Profile.xlsx", sheetIndex = 1)  
  2. print(data_excel)  
  3.    Month Installment After.Lifting Yield.by.Month Total.amount.to.pay Interest.Premium  
  4. 1      1        5000          7000         200000              280000            80000  
  5. 2      2        5000          7000         201000              278000            77000  
  6. 3      3        5000          7000         202000              276000            74000  
  7. 4      4        5000          7000         203000              274000            71000  
  8. 5      5        5000          7000         204000              272000            68000  
  9. 6      6        5000          7000         205000              270000            65000  
  10. 7      7        5000          7000         206000              268000            62000  
  11. 8      8        5000          7000         207000              266000            59000  
  12. 9      9        5000          7000         208000              264000            56000  
  13. 10    10        5000          7000         209000              262000            53000  
  14. 11    11        5000          7000         210000              260000            50000  
  15. 12    12        5000          7000         211000              258000            47000  
  16. 13    13        5000          7000         212000              256000            44000  
  17. 14    14        5000          7000         213000              254000            41000  
  18. 15    15        5000          7000         214000              252000            38000  
  19. 16    16        5000          7000         215000              250000            35000  
  20. 17    17        5000          7000         216000              248000            32000  
  21. 18    18        5000          7000         217000              246000            29000  
  22. 19    19        5000          7000         218000              244000            26000  
  23. 20    20        5000          7000         219000              242000            23000  
  24. 21    21        5000          7000         220000              240000            20000  
  25. 22    22        5000          7000         221000              238000            17000  
  26. 23    23        5000          7000         222000              236000            14000  
  27. 24    24        5000          7000         223000              234000            11000  
  28. 25    25        5000          7000         224000              232000             8000  
  29. 26    26        5000          7000         225000              230000             5000  
  30. 27    27        5000          7000         226000              228000             2000  
  31. 28    28        5000          7000         227000              226000            -1000  
  32. 29    29        5000          7000         228000              224000            -4000  
  33. 30    30        5000          7000         229000              222000            -7000  
  34. 31    31        5000          7000         230000              220000           -10000  
  35. 32    32        5000          7000         231000              218000           -13000  
  36. 33    33        5000          7000         232000              216000           -16000  
  37. 34    34        5000          7000         233000              214000           -19000  
  38. 35    35        5000          7000         234000              212000           -22000  
  39. 36    36        5000          7000         235000              210000           -25000  
  40. 37    37        5000          7000         236000              208000           -28000  
  41. 38    38        5000          7000         237000              206000           -31000  
  42. 39    39        5000          7000         238000              204000           -34000  
  43. 40    40        5000          7000         239000              202000           -37000  
  44. >  
Here is another example of an Excel file loading via the read.xlsx function:
  1. >  movies_data = read.xlsx("movies_data.xlsx",sheetIndex = 1)  
  2. > head(movies_data,5)  
  3.   serial  
  4. 1      1  
  5. 2      3  
  6. 3      4  
  7. 4      5  
  8. 5      6  
  9. Plot  
  10. Despite his tarnished reputation after the events of The Dark Knight, in which he took the rap for Dent's crimes, Batman feels compelled to intervene to assist the city and its police force which is struggling to cope with Bane's plans to destroy the city.  
  11. Based on the novel written by Stephen Chbosky, this is about 15-year-old Charlie (Logan Lerman), an endearing and naive outsider, coping with first love (Emma Watson), the suicide of his best friend, and his own mental illness while struggling to find a group of people with whom he belongs. The introvert freshman is taken under the wings of two seniors, Sam and Patrick, who welcome him to the real world.  
  12. Mike Lane is a thirty-year old living in Tampa,Florida. By day he works as a roofer whilst at night, as Magic Mike, he is the star attraction of the Kings of Tampa, a group of male strippers. Secretly he wants out in order to further a projected furniture-making business but his credit rating precludes a bank loan for this despite his considerable savings. One night Adam, a teen-aged work-mate of Mike, follows him to the club and, when one of the acts is unable to go on,he is prevailed upon to strip - becoming a huge hit. However success goes to his head and his foolish actions not only threaten to jeopardize his sister Brooke ambitions as well.  
  13. When Bond's latest assignment goes gravely wrong and agents around the world are exposed, MI6 is attacked forcing M to relocate the agency. These events cause her authority and position to be challenged by Gareth Mallory (Ralph Fiennes), the new Chairman of the Intelligence and Security Committee. With MI6 now compromised from both inside and out, M is left with one ally she can trust: Bond. 007 takes to the shadows - aided only by field agent, Eve (Naomie Harris) - following a trail to the mysterious Silva (Javier Bardem), whose lethal and hidden motives have yet to reveal themselves.  
  14. 5 Against medical advice and without the knowledge of her husband Pat Solatano Sr., caring Dolores Solatano discharges her adult son, Pat Solatano Jr., from a Maryland mental health institution after his minimum eight month court ordered stint. The condition of the release includes Pat Jr. s institutionalization was due to his beating up the lover of his wife Nikki, he was diagnosed with bipolar disorder. Nikki has since left him and has received a restraining order against him. Although he is on medication (which he doesn  cope well, however no less so than some others who have never been institutionalized, such as his Philadelphia Eagles obsessed OCD father who has resorted to being a bookie to earn a living, his best friend Ronnie who quietly seethes over the control wielded by his wife Veronica, and Veronica's widowed sister, Tiffany Maxwell, a recovering sex addict. In their fragile mental states, Pat Jr. and Tiffany embark on a love/hate friendship based primarily on what help the other can provide in achieving their individual goals. But they may reevaluate their goals as their relationship progresses.  
  15.                             Title imdbVotes  
  16. 1           The Dark Knight Rises      2679  
  17. 2 The Perks of Being a Wallflower      1270  
  18. 3                      Magic Mike      2580  
  19. 4                         Skyfall      1807  
  20. 5         Silver Linings Playbook      1828  
  21.                                                                                             Poster imdbRating  
  22. 1 http://ia.media-imdb.com/images/M/MV5BMTk4ODQzNDY3Ml5BMl5BanBnXkFtZTcwODA0NTM4Nw@@._V1_SX300.jpg         75  
  23. 2 http://ia.media-imdb.com/images/M/MV5BMzIxOTQyODU1OV5BMl5BanBnXkFtZTcwMDQ4Mjg4Nw@@._V1_SX300.jpg         71  
  24. 3 http://ia.media-imdb.com/images/M/MV5BMTQzMDMzOTA5M15BMl5BanBnXkFtZTcwMjc4MTg4Nw@@._V1_SX300.jpg         51  
  25. 4 http://ia.media-imdb.com/images/M/MV5BMjAyODkzNDgzMF5BMl5BanBnXkFtZTcwMDMxMDI4Nw@@._V1_SX300.jpg         68  
  26. 5 http://ia.media-imdb.com/images/M/MV5BMTM2MTI5NzA3MF5BMl5BanBnXkFtZTcwODExNTc0OA@@._V1_SX300.jpg         68  
  27.                    Genre    imdbID Year                    Language  
  28. 1       Action, Thriller tt1345836 2012 English                      
  29. 2         Drama, Romance tt1659337 2012   English                    
  30. 3          Comedy, Drama tt1915581 2012   English                    
  31. 4       Action, Thriller tt1074638 2012   English                    
  32. 5 Comedy, Drama, Romance tt1045658 2012   English