Error reading .xlsx file from Internet

[ameshkoff]

Expected Behavior

I am trying to read file from Internet, either directly with read.xlsx or saving to the (temporary) file on disc.

read.xlsx("https://cbr.ru/Queries/UniDbQuery/DownloadExcel/98956?Posted=True&mode=1&VAL_NM_RQ=R01235&From=01.06.2020&To=16.06.2020&FromDate=06%2F01%2F2020&ToDate=06%2F16%2F2020")

OR

tmp.file <- tempfile(fileext = ".xlsx")

download.file(url = "https://cbr.ru/Queries/UniDbQuery/DownloadExcel/98956?Posted=True&mode=1&VAL_NM_RQ=R01235&From=01.06.2020&To=16.06.2020&FromDate=06%2F01%2F2020&ToDate=06%2F16%2F2020", destfile = tmp.fle, mode = "wb") read.xlsx(tmp.file, sheet = 1) The result should be a data.frame with the currencies exchange ratings.

Actual Behavior

I get the same error in both cases:

workbook has no worksheets

Investigation

If the downloaded file is open and saved via Excel read.xlsx works fine. As I could see the issues could be the result of "protected" mode of downloaded files or something like this.

The issue is already addressed on StackOverflow but no workaround is described (manually open and save the file in Excel is out of question):

• https://stackoverflow.com/questions/56805281/import-xlsx-file-in-r-error-workbook-has-no-worksheets
• https://stackoverflow.com/questions/36180661/r-libraryopenxlsx-error-workbook-has-no-worksheets

sessionInfo()

• Version of openxlsx: 4.1.15
• Version of R: 4.0.1
• OS: Windows 10

[ameshkoff]

OK, I have investigated a bit more. The problem is:

• workbook.xml in the downloaded file contains tags with x prefix, i.e.: x:workbook, x:sheets, x:sheet
• workbook.xml after opening&saving in Excel contains tags without prefix: workbook, sheets, sheet

Proccessing in read.xlsx.default uses regular expressions assuming no prefices with these tags. Is it possible to correct at least some of these regexps to allow processing tags with x (or maybe other) prefices?

[ycphs]

Hi,

That is the old repository for this R Package.

Please use the new one:

https://github.com/ycphs/openxlsx