使用R在Excel工作表中创建图表 [英] Use R to create chart in Excel sheet

问题描述

我正在使用openXLSX包从我的R输出生成Excel文件.
我找不到将excel图表添加到excel工作簿中的方法.
我看到python具有用于创建Excel文件的模块，该模块具有用于添加excel图表的类.
有没有办法用R做到这一点?

I am using openXLSX package to generate excel files from my R output.
There is no way I have found to add excel charts to the excel workbook.
I saw that python has a module for creating Excel files that has a class for adding excel charts.
Is there a way to do this with R?

推荐答案

以下是使用软件包XLConnect的解决方案. 不过请注意，它依赖于图表模板(您需要预先创建)，并且它会生成新文件，而不是将图纸或图表添加到现有文件中.

Here's a solution using package XLConnect. A small note though, it relies on templates of charts, which you need to create in advance, and it generates new files instead of appending sheets or charts to existing files.

它包括两个阶段:

1. 为要使用的图表类型准备Excel模板.
2. 每次根据需要使用R中的数据更新模板文件.

第一步:根据您需要的图表类型，在excel中准备模板.您可以将所有模板放在同一文件中(在不同的工作表中)或放在几个不同的文件中.在准备模板时，请在工作表中包括所需的图表类型，但无需引用特定的单元格，而需要使用命名范围". 请参阅例如.您还可以使用我创建的示例文件.请注意在文件和图表的数据引用中使用命名范围(如Sheet1!bar_names和Sheet1!values而不是Sheet1!$A$2:$A$4和Sheet1!$B$2:$B$4).

First step: prepare templates in excel, according to the types of charts you will need. You can have all the templates on the same file (in different sheets) or in several different files. When you prepare the templates, include the type of charts you need within the sheet, but instead of referring to specific cells, you need to use "named ranges". See for example. You can also use the sample file I created. Note the use of named ranges in the file and in the chart's data references (as Sheet1!bar_names and Sheet1!values instead of Sheet1!$A$2:$A$4 and Sheet1!$B$2:$B$4).

关于Excel中命名范围的注释.命名范围意味着您将要在图表中使用的数据命名，然后告诉图表"使用命名范围，而不是绝对位置.您可以在Excel中的公式"菜单中访问名称管理器".我们使用命名范围的原因是XLConnect能够控制命名范围，因此，当我们修改命名范围时，图表将动态更新.

A side note on named ranges in Excel. Named ranges mean you give names to the data you're going to use in the chart, and then you "tell the chart" to use the named range, instead of an absolute location. You can access the "Name Manager" in excel in the "Formulas" menu. The reason we are using named ranges is that XLConnect is able to control named ranges, hence the chart will dynamically update when we modify the named range.

第二步::对以下代码进行改编，使其适合您的需求.通常使用您自己的数据框并在createName函数中更新引用.

Second step: use an adaptation of the following code such that it suits your needs. Mostly use your own data frame and update the reference in the createName function.

library(XLConnect) # load library
wb1 <- loadWorkbook(filename = "edit_chart_via_R_to_excel.xlsx") 
new.df <- data.frame(Type = c("Ford", "Hyundai", "BMW", "Other"),
          Number = c(45, 35, 25, 15)) # sample data
writeWorksheet(wb1, data = new.df, sheet = "Sheet1", 
               startRow = 1, startCol = 1, header = TRUE)
# update named ranges for the chart's use.
# Note that 
# "Sheet1!$A$2:$A$5" and "Sheet1!$B$2:$B$5" 
# should change according to the data you are updating
createName(wb1, "bar_names", "Sheet1!$A$2:$A$5", overwrite = TRUE) 
createName(wb1, "values", "Sheet1!$B$2:$B$5", overwrite = TRUE)
saveWorkbook(wb1)

这应该可以解决问题.

This should do the trick.

请注意，如果要将模板作为新文件提供(并保留原始模板而不覆盖它)，则可以在开始修改之前复制并保存模板.

Note that you can copy and save the template before you start the modifications, if you want to provide it as a new file (and to preserve the original template without overwriting it).

这篇关于使用R在Excel工作表中创建图表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！