How to convert xls to xlsx using R-CodePudding

I want to convert a bunch of xls files to xlsx in R so I can use the openxlsx package to overwrite in specific cells.

Up until now, I've been changing the format manually by opening each xls file and then saving them as .xlsx. As you can imagine this is really tiresome and it is kind of manageable now that I have less than 10 files, but I am expecting to have a lot more of these files.

I was wondering if someone has found a way of doing this automatically. I am using R 4.2.1 so the RDCOMClient package is not an option (I have read that this could be done easily with that package but it is not available anymore)

Edit I am working with tables within an Excel sheet and I need to maintian the format hence the overwriting.

I am familiar with the openxlsx package and my main issue is getting the Workbook objects out of the xls files. That is why I want to convert them into xlsx files.

CodePudding user response：

We can use

library(readxl)
library(writexl)

myxls <- list.files(path=".", pattern=".xls")
myxls <- myxls[!grepl("xlsx", myxls)]

for (i in 1:length(myxls)) {
  thisdata <- read_excel(myxls[i])
  newname <- gsub(".xls", ".xlsx", myxls[i])
  write_xlsx(thisdata, newname)
}

You will then be able to open each newly created xlsx file with openxlsx.

Edit: for format issues you might take a look at the rio package:

library("rio")
created <- mapply(convert, myxls, gsub("xlsx", "xls", myxls))

I haven't tested it myself, but it might be worth a try.

CodePudding user response：

In case someone else is also interested, I really didn't figure out how to do this in R but I did found this https://www.extendoffice.com/documents/excel/1349-excel-batch-convert-xls-to-xlsx.html#a2 and I can keep the exact same format from the original file.

CodePudding user response：

Try ssconvert which comes with the free gnumeric spreadsheet. It may issue warnings but will likely work despite that.

files <- c("a.xls", "b.xls", "c.xls")
ssconvert <- r"{"C:\Program Files (x86)\Gnumeric\1.12.17\bin\ssconvert.exe"}"
for(f in files) shell(paste(ssconvert, print(f), sub("xls$", "xlsx", f)))