从R /外部环境填充Excel支持宏的单元格 [英] Populating Excel Macro-enabled cells from R/outside environment

问题描述

我有一个支持宏的模板，我希望以编程方式从R填充。

我有一个R进程可以为每个项目检索不同的数据集我处理，处理它们，然后我想要它复制和填充每个数据集的模板，绕过需要手动填充每个数据集的模板。

我一直在使用XLConnect R软件包，但不支持.xlsm文件。我考虑使用RExcel，但是我想从R环境运行程序，而不是在Excel中运行程序（也有很多困难试图安装RExcel）。

这是一个课程数据库验证过程，必须每年进行一次，所以我的部门为每一门课程填写模板是不可行的，必须以自动化方式完成。我想使用R的一部分原因是因为我可以用Shiny构建一个漂亮的用户界面，其中excel工作簿只是觉得死亡。

是否有另一种方式以编程方式填充excel宏单元格，实际上让宏接受并运行数据，或者我需要学习VBA并编写程序呃处理这个问题？

解决方案

如下所示：

  library（xlsx）
 
 xl<  -  loadWorkbook（Workbook1.xlsm）
 sheet<  -  getSheets（xl）
 cells <  -  getCells（getRows（sheets [[1]]））
 setCellValue（cells [[1]]，400）
 saveWorkbook（xl，NewWorkbook.xlsm）
  

将保存来自 Workbook1.xlsm 的所有宏，同时将实际的单元格填充数据。

I have a macro-enabled template in excel that I wish to populate programmatically from R.

I have an R process that retrieves different set of data for every item I with to process, processes them, and then I want it to duplicate and populate the template for each data set, bypassing the need to fill the template out by hand for every set of data.

I have been using the XLConnect R package, however it does not support .xlsm files. I thought about using RExcel, but I want to run the program from an R environment, not from within Excel (also I had many difficulties trying to install RExcel).

This is for a course curriculum database validation process that must be done each year, so filling the template out by hand for every single course in my department is simply not feasible and must be done in an automated manner. Part of the reason I want to use R is because I can build a nice user interface with Shiny, where excel workbooks just feel like death.

Is there another way to populate excel macro cells programmatically and actually get the macros to accept and run on the data, or do I need to learn VBA and write a program in excel to handle this?

解决方案

Something like the following:

library(xlsx)

xl <- loadWorkbook("Workbook1.xlsm")
sheets <- getSheets(xl)
cells <- getCells(getRows(sheets[[1]]))
setCellValue(cells[[1]], 400)
saveWorkbook(xl, "NewWorkbook.xlsm")

will preserve all the macros from Workbook1.xlsm whilst populating actual cells with data.

这篇关于从R /外部环境填充Excel支持宏的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！