1

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.

Community
  • 1
  • 1
Fercho
  • 11
  • 2
  • 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 Jun 01 '15 at 22:26
  • 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 Jun 01 '15 at 22:29

2 Answers2

0

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)

micstr
  • 4,191
  • 6
  • 38
  • 63
  • thank you micstr. I try the xlconnect package but still getting the same error Error: EncryptedDocumentException (Java): 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'm going to try the first option you gave me, but I have a lot of excel files to do this, Im using file.copy(file, filename.csv) to do a loop on it, but how can I especified the sheet in the file, since csv is a format that only saves one sheet of the excel file. – Fercho Jun 02 '15 at 17:46
  • Fercho - please see link in my edit and try `openxlsx` first as it seems to be able to handle protected sheets. Your file.copy method does not look like you can choose a sheet. if this new package is no good, then you might need VBA to save down your sheets to csv. – micstr Jun 02 '15 at 18:48
0

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.

Fercho
  • 11
  • 2