什么是将包含多个工作表的大型Excel电子表格转换为.CSV文件的最简单方法?请注意,我已经测试了XLConnect和XLSX,并发现我的Excel表会导致它崩溃。因此,我特别寻找不使用XLConnect或XLSX软件包的解决方案。
require(gdata)
## install support for xlsx files
installXLSXsupport()
excelFile <- ("/full/path/to/excelFile.xlsx")
## note that the perl scripts that gdata uses do not cope well will tilde expansion
## on *nix machines. So use the full path.
numSheets <- sheetCount(excelFile, verbose=TRUE)
for ( i in 1:numSheets) {
mySheet <- read.xls(excelFile, sheet=i)
write.csv(mySheet, file=paste(i, "csv", sep="."), row.names=FALSE)
}
http://rwiki.sciviews.org/doku.php?id=tips:data-io:ms_windows
编辑:针对read.xlsx选项:
如果您有Perl运行,则需要当前版本的gdata。
require(gdata)
installXLSXsupport() #now the example from help(read.xls)
# load the third worksheet, skipping the first two non-data lines...
if( 'XLSX' %in% xlsFormats() ) # if XLSX is supported..
data <- read.xls(exampleFile2007, sheet="Sheet with initial text", skip=2)
data
#-----------------------
X X.1 D E. F G Factor
1 NA FirstRow 1 NA NA NA Red
2 NA SecondRow 2 1 NA NA Green
3 NA ThirdRow 3 2 1 NA Red
4 NA FourthRow 4 3 2 1 Black
#------------------------
write.csv(data)
这是在Mac上完成的,在这个问题之前,我总是在installXLSXsupport()阶段遇到错误。这一次,我从终端命令行启动了Perl,并在首先设置个人配置、定义大陆上的CPAN镜像后成功运行。
library("readxl")
#function to read all sheets of a workbook
read_excel_allsheets <- function(filename) {
sheets <- readxl::excel_sheets(filename)
x <- lapply(sheets, function(X) readxl::read_excel(filename, sheet = X))
names(x) <- sheets
x
}
sheetnames <- read_excel_allsheets("excelFile.xlsx")
names(sheetnames)
read_excel(file) %>% data.table::fwrite(fileout)
- Rob