Exporting .XLS file from openxlsx

Question

I have a function that receives DataFrame does a bunch of transformations with openxlsx and exports the data from R to .xlsx:

export_workbook_from_df <- function(data, path) {
  wb <- openxlsx::createWorkbook()
  
  openxlsx::addWorksheet(wb, sheetName = "Sheet1")
  
  openxlsx::openxlsx_setOp("numFmt", "0,00")
  
  number_format <- openxlsx::createStyle(numFmt = "Number") # create thousands format
  
  wb |>
    openxlsx::addStyle(sheet = 1,
                       number_format,
                       rows = 1:nrow(dados) + 1, cols = c(6),
                       gridExpand = T
                       )

  openxlsx::writeData(wb, sheet = 1, data)
  
  openxlsx::saveWorkbook(wb, paste0(path, ".xlsx"))
}

if I try to save as .xls using openxlsx::saveWorkbook(wb, paste0(path, ".xls")) I get the following error:

Which roughly translates to:

The format of the file and the extension don't correspond. The file may be corrupted or not be safe. Don't open it, unless you trust the source. Do you want to open anyway?

The file works fine if I save it as .xlsx and manually save as .xls within Excel;

I also tried using XLConnect to load the file after is saved and export in a different format, like:

  openxlsx::saveWorkbook(wb, paste0(path, ".xlsx"))
  XLConnect::loadWorkbook(paste0(path, ".xlsx")) %>%
  XLConnect::saveWorkbook(paste0(path, ".xls"))

While it does export the file as .xls I get the same error.

It may be worth mentioning that when I open the file I get exactly the same data as in the .xlsx file when using either methods (using openxlsx and XLConnect)

Answer 1

The xls and xlsx file formats are not the same: XLSX is a zipped, XML-based file format. Microsoft Excel 2007 and later uses XLSX as the default file format when creating a new spreadsheet. Support for loading and saving legacy XLS files is also included. XLS is the default format used with Office 97-2003. When you try to load the XLSX which you saved as an XLS Excel barfs as above because it is expecting the old binary format but it is encountering a zipped XML-based one instead.

Answer 2

With the RDCOMClient R package, you can export directly as ".xls" files with xlWbk1$SaveAs(path_Excel_File_Output, -4143). You can also convert a ".xlsx" file to a ".xls" file. This approach only works on Windows.

library(RDCOMClient)
path_Excel_File1 <- "D:\\file1.xlsx"
path_Excel_File_Output <- "D:\\file1.xls"

xlApp <- COMCreate("Excel.Application")
xlWbk1 <- xlApp$Workbooks()$Open(path_Excel_File1)

xlWbk1$SaveAs(path_Excel_File_Output, -4143) # saving as .xls