如何使用R生成XLS格式工作簿？

Great question! While the openxlsx package is excellent for creating XLSX files, it doesn’t support generating XLS (Excel 97-2003) workbooks directly. But there are a few reliable ways to achieve this in R—here are your best options:

Option 1: Use the xlsx Package #

This is one of the most straightforward methods, though it does require Java to be installed on your system (JRE or JDK works fine). Here’s how to use it:

• First, install and load the package:

  install.packages("xlsx")
  library(xlsx)
  

• Create your data and write it directly to an XLS workbook:

  # Sample data frame
  sample_data <- data.frame(
    Employee = c("Priya", "Raj"),
    Department = c("Engineering", "Marketing")
  )
  
  # Write to XLS file
  write.xlsx(sample_data, file = "employee_records.xls", sheetName = "Staff")
  

The package automatically handles the XLS format when the file extension is .xls.

Option 2: Use the XLConnect Package #

Another Java-dependent package that gives you more control over workbook creation (like adding multiple sheets, formatting cells, etc.):

• Install and load the package:

  install.packages("XLConnect")
  library(XLConnect)
  

• Create a workbook, add sheets, and save as XLS:

  # Create a new workbook (creates the file if it doesn't exist)
  wb <- loadWorkbook("project_data.xls", create = TRUE)
  
  # Add a sheet and write data
  createSheet(wb, name = "Q1_Sales")
  writeWorksheet(wb, sample_data, sheet = "Q1_Sales")
  
  # Add a second sheet with different data
  createSheet(wb, name = "Q2_Sales")
  writeWorksheet(wb, data.frame(Product = c("A", "B"), Sales = c(1500, 2200)), sheet = "Q2_Sales")
  
  # Save the workbook
  saveWorkbook(wb)
  

Option 3: Convert XLSX to XLS (No Java Required) #

If you prefer to avoid Java dependencies, you can first generate an XLSX file with openxlsx, then convert it to XLS using a command-line tool like LibreOffice (you’ll need LibreOffice installed on your system):

# Generate XLSX with openxlsx
library(openxlsx)
write.xlsx(sample_data, "temp.xlsx")

# Convert to XLS using LibreOffice's headless mode
system("libreoffice --headless --convert-to xls temp.xlsx")

This will create a temp.xls file in the same directory. Note that the exact command might vary slightly depending on your operating system.

内容的提问来源于stack exchange，提问作者Achintya Mengi