Import data from excel with HSSF in R

1
votes

I'm trying to import data from an excel file into R, with the library xlsx. I get the error:

Error in .jcall("RJavaTools", "Ljava/lang/Object;", "invokeMethod", cl, : org.apache.poi.EncryptedDocumentException: The supplied spreadsheet seems to be an Encrypted .xlsx file. It must be decrypted before use by XSSF, it cannot be used by HSSF

I changed the file from filename.xlsx to filename.xls, but I keep getting the same message

I also tried the advice of this links:

Import password-protected xlsx workbook into R

How to read xlsx file in protect mode to R

but it won't work.

The sheets of my file are protected but not the file itself.

2
rexcel
How didn't the alternatives you mentioned didn't work? Post the code you tried and the result. And if possible a link to the excel file itself. - Molx
By the way, changing the extension is not what you want to do. They are there for a reason, and even excel will warn you that something is funny if you try to open the file in it. - Molx

2 Answers

0
votes

It seems from the package xlsx website that facilities to work with password protected spreadsheets is a feature still being worked on - although a user Heather has made a fix.

See https://code.google.com/p/rexcel/issues/detail?id=49

But it is not clear if this extends to protected sheets as well.

Fercho - Can you try other workarounds?

  1. Save as csv and use read.csv to get data into R?
  2. Save a version of Excel file without protected sheets for your data input?
  3. Try other Excel to R programs like XLConnect? This package seems more up to date.

EDIT: Mango Solutions has a comparison of Excel and R tools. openxlsx can handle password protected sheets but is slower than XLConnect.

CODE for 1 Above

' Microsoft for Excel VBA for saving as csv
' First Select your sheet to turn to CSV file and then run code like this
' Save sheet as csv
ThisWorkbook.SaveAs Filename:=strSaveFilename, _
  FileFormat:= xlCSV

Workbook.SaveAs Method ' SYNTAX expression .SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)

0
votes

thanks, I finally did it in VBA it takes a little bit of time but it works, here is the code I used for VBA.

Sub LoopThroughFiles()

FolderName = "C:folder with files\"
If Right(FolderName, 1) <> Application.PathSeparator Then FolderName = FolderName & Application.PathSeparator
Fname = Dir(FolderName & "*.xls")

'loop through the files
Do While Len(Fname)

    With Workbooks.Open(FolderName & Fname)

       Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next
ws.Unprotect Password:="password 1"
ws.Unprotect Password:="password 2"

On Error GoTo 0 Next ws

For Each w In Application.Workbooks
w.Save
Next w

    End With

    ' go to the next file in the folder
    Fname = Dir

Loop

Application.Quit

End Sub

I used two password to unlock the sheets, I didn't know which password was so I try both on each file.

thanks again for the help.