CODEWITHSUNDEEP
ropenxlsxxlconnect
Alysson Silva
Alysson Silva

Reputation: 35

Exporting .XLS file from openxlsx

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:

Error in the extension of xls

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)

Upvotes: 1

Views: 758

Answers (3)

Simon Poltier
Simon Poltier

Reputation: 173

As an alternative, here's how you would implement the code in the question in XLConnect, which supports writing .xls files and works on Mac, Windows and Linux:

library(XLConnect)

export_workbook_from_df <- function(data, path) {

    # create a workbook and worksheet
    wb <- loadWorkbook(paste0(path, ".xls"), create = TRUE)
    createSheet(wb, name = "mySheet")

    # define and configure a cell style for formatting numbers
    num_format_style <- createCellStyle(wb, name = "numFormat")
    setDataFormat(num_format_style, "0,00")

    # set the style action to use the created style when next writing data
    setStyleAction(wb, XLC$"STYLE_ACTION.NAME_PREFIX")
    setStyleNamePrefix(wb, "numFormat")

    # write the data to the worksheet, save the file
    writeWorksheet(wb, data, sheet = "mySheet", startRow = 1, startCol = 1)
    saveWorkbook(wb)
}

Note that the format string (here 0,00) may be interpreted differently depending on your locale and / or Excel settings. You may want to use the Excel "format cell ..." dialog to preview and adjust your custom format.

Upvotes: 0

Emmanuel Hamel
Emmanuel Hamel

Reputation: 2233

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

Upvotes: 1

br00t
br00t

Reputation: 1614

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.

Upvotes: 2

Related Questions