能否使用R编写Excel公式或数据验证？

Question

我正在尝试将R数据框写入Excel，并希望添加额外的单元格/列，这些单元格/列具有Excel公式和/或数据验证值（例如，使用Excel中的"数据" / "验证"菜单为单元格提供可接受值的下拉列表）。

我查看了R包：xlsx、XLConnect和openxlsx。这些都很适合编写单元格，但不适用于公式或数据验证设置。

我目前认为必须使用AppleScript（在Mac上）或VBA解决方案对xlsx文件进行后处理。如果可能的话，我希望所有操作都在R中完成，以便不必跨程序分割逻辑。

在两种情况下，编写单元格的数据验证设置（而非编写Excel公式）更为重要。使用情况是将R数据框写入Excel并包括未填充的列以进行更正。对于未填充的列，我希望该列的下拉列表中包含可接受的值（例如，“是” / “否”）。

谢谢您的帮助。

- Mac471

---

我认为期望一个R包提供VBA的所有功能是期望过高了。 - IRTFM

我并不是要求 R 取代 VBA - 我只想知道是否存在这种能力，或者人们是否有兴趣开发类似的东西。特别是数据验证用例，将 R 数据框导出到非 R 用户更友好的环境中将非常有帮助。 - Mac471

我们大多数热衷于使用 R 的用户认为 Excel 不够“用户友好”。 - IRTFM

@BondedDust - 感谢您对R和Excel的看法！非常感谢。还有其他人有任何与所提出问题相关的信息吗？ - Mac471

我认为你提到的包只能读取Excel数据。请参考http://www.thertrader.com/2014/02/11/a-million-ways-to-connect-r-and-excel/了解另一种方法（通过VBA）。 - Roman Luštrik

Answer 1

下面的示例显示如何将下拉列表添加到Excel单元格中。

# Libraries
library(openxlsx)

# Create workbook
wb = createWorkbook()

# Add worksheet "Customers" to the workbook
addWorksheet(wb, "Customers")

# Create Customers dataframe
customers_df = data.frame("Name" = c("Alex", "Kate", "Mary"), "Gender" = 
c("male", "female", "female"))

# Add Customers dataframe to the sheet "Customers"
writeData(wb, sheet = "Customers", x = customers_df, startCol = 1)

# Add worksheet "Drop-down values" to the workbook
addWorksheet(wb, "Drop-down values")

# Create drop-down values dataframe
gender_values_df = data.frame("Gender" = c("male", "female"))

# Add drop-down values dataframe to the sheet "Drop-down values"
writeData(wb, sheet = "Drop-down values", x = gender_values_df, startCol = 
1)

# Add drop-downs to the column Gender on the worksheet "Customers"
dataValidation(wb, "Customers", col = 2, rows = 2:4, type = "list", value = 
"'Drop-down values'!$A$2:$A$3")

# Save workbook
saveWorkbook(wb, "D:/Customers.xlsx", overwrite = TRUE)

更多信息可以在这里找到：dataValidation

- Nadzeya Yakimchyk

---

感谢您提供可重现的示例。确认它有效，并将此解决方案标记为回答原始问题。 - Mac471

在Excel表格中，是否可以使用未预定义的列表值？例如：dataValidation(wb, "Customers", col = 2, rows = 2:4, type = "list", value = c("Yes", "No")) - Max M

但是我该如何将dataValidation包添加到Angular中呢？ - Faruk AYDIN

Answer 2

作为一个经常在R和Excel之间切换的人，我建议你在这种情况下让Excel来驱动工作流程。可以编写Excel宏来从R创建的文件中导入数据，或者使用RExcel（一个Excel插件），它允许从Excel对R会话进行交互式控制。

- Art

---

好建议。我正在AWS Linux服务器上运行R脚本，并希望避免在Windows PC上使用Excel进行后处理。感谢提醒可以使用RExcel在Excel中运行R会话-我会研究一下的。也许我可以执行一个从保存的图像（RData文件）加载所需数据框的脚本，然后从Excel中创建需要的内容。 - Mac471