data.table :: fread读取Excel工作簿中的所有工作表 [英] data.table::fread Read all worksheets in an Excel workbook
问题描述
我的Excel文档 my.xlsx
有两个名为 Sheet1 和 Sheet2 的工作表.我想使用 data.table
R
包中的 fread
函数读取Excel工作簿中的所有工作表.下面的代码只是阅读了活动的工作表.想知道如何在不知道其名称的情况下阅读所有工作表.谢谢
My Excel document my.xlsx
has two Sheets named Sheet1 and Sheet2. I want to read all worksheets in an Excel workbook using fread
function from data.table
R
package. The following code just read the active worksheet. Wonder how to read all worksheets without knowing their names. Thanks
df3 <- data.table::fread("in2csv my.xlsx")
> names(df3)
[1] "A" "B"
> df3
A B
1: 1 2
2: 2 4
3: 3 6
4: 4 8
5: 5 10
推荐答案
我上次需要从XLSX读取许多图纸时,使用了 openxlsx :: read.xlsx
.
I used openxlsx::read.xlsx
the last time I needed to read many sheets from an XLSX.
#install.packages("openxlsx")
library(openxlsx)
#?openxlsx::read.xlsx
#using file chooser:
filename <- file.choose()
#or hard coded file name:
#filename <- "filename.xlsx"
#get all the sheet names from the workbook
SheetNames<-getSheetNames(filename)
# loop through each sheet in the workbook
for (i in SheetNames){
#Read the i'th sheet
tmp_sheet<-openxlsx::read.xlsx(filename, i)
#if the input file exists, append the new data;; else use the first sheet to initialize the input file
ifelse(exists("input"),
input<-rbind(input, tmp_sheet),
input<-tmp_sheet)
}
注意:假定每个工作表具有相同的列结构和数据类型.您可能需要对数据进行规范化/标准化(例如 tmp_sheet<-as.data.frame(sapply(tmp_sheet,as.character),stringsAsFactors = FALSE)
),或将每个工作表加载到其中拥有自己的数据框,并在合并之前进行进一步的预处理.
Note: This assumes each worksheet has identical column structure and data types. You may need to standardize\normalize the data (ex. tmp_sheet <- as.data.frame(sapply(tmp_sheet,as.character), stringsAsFactors=FALSE)
), or load each sheet into it's own dataframe and pre-process further before merging.
这篇关于data.table :: fread读取Excel工作簿中的所有工作表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!