将受密码保护的xlsx工作簿导入R

How can I import a worksheet from a password-protected xlsx workbook into R?

如何将受密码保护的xlsx工作簿中的工作表导入R？

I would like to be able to convert an Excel worksheet into a csv file without having to go through Excel itself.

我希望能够将Excel工作表转换为csv文件，而无需通过Excel本身。

It is possible for xls workbooks using the perl-based function xls2csv from package gdata. I gather that the problem is Spreadsheet::XLSX doesn't support it.

xls工作簿可以使用包gdata中基于perl的函数xls2csv。我认为问题是Spreadsheet :: XLSX不支持它。

There are a variety of functions and packages for importing non-encrypted xlsx workbooks, but none seems to address this issue.

有许多用于导入非加密xlsx工作簿的函数和包，但似乎都没有解决这个问题。

At present it seems the only alternatives are to go through Excel or figure out how to write perl code that can do it.

目前似乎唯一的选择是通过Excel或弄清楚如何编写可以执行它的perl代码。

2 个解决方案

#1

It looks to be what you need except it isn't with the xlsx package:

它看起来是你需要的，除了它不是xlsx包：

https://stat.ethz.ch/pipermail/r-help/2011-March/273678.html

library(RDCOMClient)
eApp <-  COMCreate("Excel.Application")
wk <-  eApp$Workbooks()$Open(Filename="your_file",Password="your_password")
tf <-  tempfile()
wk$Sheets(1)$SaveAs(tf, 3)

#2

To build on ed82's answer, there are a few caveats:

在ed82的答案的基础上，有一些警告：

You may need to pass another password parameter, WriteResPassword. See docs here

您可能需要传递另一个密码参数WriteResPassword。请参阅此处的文档
I didn't find learning COM interface appealing after I got used to xlsx R package. So I would rather save a copy of the protected Excel file without a password immediately, close it, and read it in with another package:

我习惯了xlsx R包后，没有发现学习COM接口的吸引力。所以我宁愿保存一份受保护的Excel文件，不要立即使用密码，关闭它，然后用另一个包读取它：

eApp <- COMCreate("Excel.Application")

# Find out whether you need to pass **Password** or **WriteResPassword**
wk <- eApp$Workbooks()$Open(Filename= filename, Password="somepass",                             WriteResPassword = "somepass")

# Save a copy, clear the password (otherwise copy is still pass-protected)
wk$SaveAs(Filename = '...somepath...', WriteResPassword = '', Password = '')

# The copied file is still open by COM, so close it
wk$Close(SaveChanges = F)

# Now read into data.frame using a familiar package {xlsx}
my.data <- raed.xlsx('...somepath...', sheetIndex = ...)

#1