This article will show how to read password-protected Excel files in the R programming language.
Also Read: C Program to Draw Sine Wave Using C Graphics
Let us use the excelFile.xlsx excel file here which is shown below:
excelFile.xlsx:
Reading Password Protected Excel file in R
This can be done using various methods. They are:
Method #1: Using excel.link Package
The excel.link package is divided into two distinct parts:
- One for sending data/graphics to a running instance of Excel.
- Another for working with data tables in Excel in a manner similar to that of a data.frame.
In this case, we will use excel.link package to read a password-protected file.
Installation of excel.link Package:
Type the below command in the R console for installing the excel.link package:
install.packages("excel.link")
Output:
Installing package into ‘C:/Users/cirus/AppData/Local/R/win-library/4.2’ (as ‘lib’ is unspecified) trying URL 'https://cloud.r-project.org/bin/windows/contrib/4.2/excel.link_0.9.10.zip' Content type 'application/zip' length 782729 bytes (764 KB) downloaded 764 KB package ‘excel.link’ successfully unpacked and MD5 sums checked The downloaded binary packages are in C:\Users\cirus\AppData\Local\Temp\RtmpOwpVEv\downloaded_packages
xl.read.file() Function:
In R language, the xl.read.file() function is used to read Excel files.
Syntax:
xl.read.file( "fileName", password = "pass")
Example1
Approach:
- Import excel.link package using the library() function
- Pass the excel file path, and password as arguments to the xl.read.file() function to read the excel file with the password and store it in a variable.
- Display the above dataframe using the head() function.
- The Exit of the Program.
Below is the implementation:
# Import excel.link package using the library() function
library("excel.link")
# Pass the excel file path, password as arguments to the xl.read.file() function
# to read the excel file with the password and store it in a variable
datafrme <- xl.read.file("C:/Users/cirus/Downloads/excelFile.xlsx", password = "Sheetstips")
# Display the above dataframe values
datafrme
Output:
Website name Name Age City State Student Employed 2 Sheets Tips Vikram 21 Hyderabad Telangana Yes No 3 Python-Programs Ashritha 22 Pune Mumbai Yes No 4 BTechGeeks Manish 19 Indore Madhya Pradesh Yes No 5 PythonArray Anish 20 Rajkot Gujarat Yes No 6 Sheets Tips Vishal 18 Ranchi Jharkhand Yes No 7 Python-Programs Pavan 23 Lucknow Uttarpradesh No Yes 8 Sheets Tips John 32 Los Angeles America No Yes 9 Python-Programs Virat 34 Agra Delhi No Yes 10 BTechGeeks Cary 36 Tokyo Japan No Yes 11 PythonArray Tara 39 Helsinki Finland No Yes 12 Sheets Tips Smith 42 Nairobi Kenya No Yes 13 Python-Programs Elizabeth 66 Paris Italy No Yes Married Scholorship Id 2 No Not Applicable 1234 3 No Already Taken 2344 4 No Already Taken 1345 5 No Already Taken 1356
Example2
The same module can be used to unlock the file and then copy its contents to another, allowing it to be accessible without a password again. In this case, we save the file with a password of NULL and save it to another file.
Syntax:
xl.save.file( file.object, "newFile, password = NULL , write.res.password = NULL)
Approach:
- Import excel.link package using the library() function
- Pass the excel file path, and password as arguments to the xl.read.file() function
to read the excel file with the password and store it in a variable - Pass the above dataframe, no password excel file name, password as NULL to the xl.save.file() function to save the above dataframe into another new file.
- Pass the above output excel file name as an argument to the xl.read.file() to read the file without any password and store it in another variable
- Display the above output dataframe.
- The Exit of the Program.
Below is the implementation:
# Import excel.link package using the library() function
library("excel.link")
# Pass the excel file path, password as arguments to the xl.read.file() function
# to read the excel file with the password and store it in a variable
datafrme <- xl.read.file("C:/Users/cirus/Downloads/excelFile.xlsx", password = "sheetstips")
# Pass the above dataframe, no Password excel file name, password as NULL to the
# xl.save.file() function to save the above dataframe into a another new file.
xl.save.file(datafrme, "C:/Users/cirus/Downloads/noPasswordExcelFile.xlsx", password = NULL,
write.res.password = NULL)
# Pass the above output excel file name as as argument to the xl.read.file()
# to read the file without any password and store it in another variable
output_datafrme <- xl.read.file("C:/Users/cirus/Downloads/noPasswordExcelFile.xlsx")
# Display the above output dataframe
output_datafrme
Output:
Website name Name Age City State Student Employed Married Scholorship Id 2 Sheets Tips Vikram 21 Hyderabad Telangana Yes No No Not Applicable 1234 3 Python-Programs Ashritha 22 Pune Mumbai Yes No No Already Taken 2344 4 BTechGeeks Manish 19 Indore Madhya Pradesh Yes No No Already Taken 1345 5 PythonArray Anish 20 Rajkot Gujarat Yes No No Already Taken 1356 6 Sheets Tips Vishal 18 Ranchi Jharkhand Yes No No Already Taken 1938 7 Python-Programs Pavan 23 Lucknow Uttarpradesh No Yes No Not Applicable 2345 8 Sheets Tips John 32 Los Angeles America No Yes Yes Not Applicable 5622 9 Python-Programs Virat 34 Agra Delhi No Yes No Not Applicable 1734 10 BTechGeeks Cary 36 Tokyo Japan No Yes Yes Already Taken 4562 11 PythonArray Tara 39 Helsinki Finland No Yes Yes Not Applicable 2365 12 Sheets Tips Smith 42 Nairobi Kenya No Yes Yes Not Applicable 2463 13 Python-Programs Elizabeth 66 Paris Italy No Yes Yes Not Applicable 2677
Method #2: Using XLConnect Package
To read the password-protected file, we will use the XLConnect package. The XLConnect package has comprehensive functionality for reading, writing, and formatting Excel data.
> install.packages("XLConnect")
Installing package into ‘C:/Users/cirus/AppData/Local/R/win-library/4.2’
(as ‘lib’ is unspecified)
trying URL 'https://cloud.r-project.org/bin/windows/contrib/4.2/XLConnect_1.0.5.zip'
Content type 'application/zip' length 29541925 bytes (28.2 MB)
downloaded 28.2 MB
package ‘XLConnect’ successfully unpacked and MD5 sums checked
The downloaded binary packages are in
C:\Users\cirus\AppData\Local\Temp\RtmpOwpVEv\downloaded_packages
The package uses the Apache POI API1 as the underlying interface. XLConnect allows you to generate structured Excel reports with graphics directly from R.
loadWorkbook() Function:
The loadWorkbook() function is used to load the Microsoft Excel workbooks.
Syntax:
loadWorkbook(filename , password )
readWorksheet() Function:
The readWorksheet() function is used to read data from the excel worksheets.
Syntax:
readWorksheet( object, sheet).
Parameters
- object: It is the workbook object to be used
- sheet: It is the name or index of the worksheet from which to read
Approach:
- Import XLConnect module using the library() function
- Pass the excel file path, password as arguments to the loadWorkbook() function of the XLConnect module to read the excel file with the password and store it in a variable.
- Pass the above workbook object, sheetname as arguments to the readWorksheet() function to read the above workbook object and store it in another variable.
- Display the above dataframe using the head() function.
- The Exit of the Program.
Below is the implementation:
# Import XLConnect module using the library() function
library(XLConnect)
# Pass the excel file path, password as arguments to the loadWorkbook() function
# of the XLConnect module to read the excel file with the password and
# store it in a variable
workbookObj <- loadWorkbook("sampleExcelFile.xlsx", password = "Sheetstips")
# Pass the above workbook object, sheetname as arguments to the readWorksheet() function
# to read the above workbook object and store it in another variable
datafrme <- readWorksheet(workbookObj, "website")
# Display the above dataframe using the head() function
head(datafrme)
Output:
Website name Name Age City State Student Employed Married Scholorship Id 2 Sheets Tips Vikram 21 Hyderabad Telangana Yes No No Not Applicable 1234 3 Python-Programs Ashritha 22 Pune Mumbai Yes No No Already Taken 2344 4 BTechGeeks Manish 19 Indore Madhya Pradesh Yes No No Already Taken 1345 5 PythonArray Anish 20 Rajkot Gujarat Yes No No Already Taken 1356 6 Sheets Tips Vishal 18 Ranchi Jharkhand Yes No No Already Taken 1938 7 Python-Programs Pavan 23 Lucknow Uttarpradesh No Yes No Not Applicable 2345 8 Sheets Tips John 32 Los Angeles America No Yes Yes Not Applicable 5622 9 Python-Programs Virat 34 Agra Delhi No Yes No Not Applicable 1734 10 BTechGeeks Cary 36 Tokyo Japan No Yes Yes Already Taken 4562 11 PythonArray Tara 39 Helsinki Finland No Yes Yes Not Applicable 2365 12 Sheets Tips Smith 42 Nairobi Kenya No Yes Yes Not Applicable 2463 13 Python-Programs Elizabeth 66 Paris Italy No Yes Yes Not Applicable 2677