r - 如何在 R 中打开 .xlsb 文件？

Question

我正在尝试在 R 中打开一个 .xlsb 文件并不断收到类似的错误。

关于如何解决此问题而无需下载数据并将其保存为不同格式的任何建议？

read.xlsx(paste(OutputLocation,FileName, sep=""), sheetName = "Master Data")

错误消息:
.jcall("RJavaTools", "Ljava/lang/Object;", "invokeMethod", cl, 中的错误:
org.apache.poi.xssf.XLSBUnsupportedException:不支持 .XLSB 二进制工作簿

rigDataWB<-loadWorkbook(paste(OutputLocation,FileName, sep=""))

错误消息:
错误:XLSBUnsupportedException (Java):不支持 .XLSB 二进制工作簿

请注意:

我无法安装 Perl 库。

我正在运行 64 位 R。

引用:
http://www.milanor.net/blog/?p=779

我的数据来自:
http://phx.corporate-ir.net/phoenix.zhtml?c=79687&p=irol-reportsother

Answer 1

readxlsb 包可以将 Excel 二进制 (.xlsb) 文件读入 R。以下是从包小插图中获取的一些信息:

read_xlsb(path, sheet, range, col_names, col_types, na, trim_ws, skip, ...)

sheet:

Either a name, or the index of the sheet to read. Index of the first sheet is 1. If the sheet name is embedded in the range argument, or implied if range is a named range, then this argument is ignored

range:

range can be specified as

• A named range. Named ranges are not case sensitive
• In Sheet!A1 notation
• In Sheet!R1C1 notation
• As a cellranger::cell_limits object

col_names

• TRUE: The first row is used for column names. Empty cells result in a column name of the form ‘column.i’
• FALSE: Column names will be ‘column.i’
• Character vector: vector containing column names.

col_types

Can be implied from the spreadsheet or specified in advanced. When specifying types, options are

• “logical” (or “boolean”), “numeric” (or “double”), “integer”, “date” and “string” (or “character”)
• Use “skip” (or “ignore”) to skip a column

na

A character string that is interpret as NA. This does not effect the implied data type for a column.

trim_ws

Should leading and trailing whitespaces be trimmed from character strings?

skip

The number of rows to skip before reading data.

library(readxlsb)

res = read_xlsb(path = system.file("extdata", "TestBook.xlsb", package = "readxlsb"), 
                range = "PORTFOLIO", 
                debug = TRUE)

ls(res$env)
#> [1] "content"      "named_ranges" "sheets"       "stream"

res$env$named_ranges
#>             name                     range sheet_idx first_column first_row
#> 1   INFO_RELEASE          FirstSheet!$A$11         0            1        11
#> 2        OUTLOOK 'My SecondTab'!$A$1:$C$13         1            1         1
#> 3      PORTFOLIO      FirstSheet!$A$3:$C$9         0            1         3
#> 4 SAVED_DATETIME          FirstSheet!$C$13         0            3        13
#> 5          TITLE           FirstSheet!$A$1         0            1         1
#>   last_column last_row
#> 1           1       11
#> 2           3       13
#> 3           3        9
#> 4           3       13
#> 5           1        1

由 reprex package 创建于 2020-07-07| (v0.3.0)