Use R to create chart in Excel sheet

0 votes
In order to create excel files from my R output, I use the openXLSX program. I haven't been able to find a way to include Excel charts in an Excel workbook. I discovered that Python provides an Excel file creation module with a class for including Excel charts.

Is R capable of accomplishing this?
Nov 10 in Others by Kithuzzz
• 20,660 points

1 answer to this question.

0 votes

Here is a solution utilizing the XLConnect package. Just a quick reminder that it creates new files rather than adding sheets or charts to already existing files and relies on chart templates that you must build in advance.

It is comprised of two stages:

  1. Preparing Excel templates for the type of charts you would like to use.
  2. Updating the template files with the data from R as required each time.
First step: Create Excel templates for the different types of charts you'll need. All of the templates can be found in either one file (in various sheets) or numerous ones. Include the types of charts you'll need in the template, but use "named ranges" rather than specific cells when referencing them.

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)
answered Nov 10 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
0 answers

How to use VIF in r?

1 I'm new to R and using caret ...READ MORE

Jun 13 in Others by Avinash
• 1,260 points
0 votes
1 answer

How to create a drop-down in excel with custom values

You can accomplish that using code rather ...READ MORE

answered Sep 25 in Others by narikkadan
• 37,660 points
0 votes
1 answer

How to use the INDIRECT function in Excel

Try this: =INDEX(INDIRECT($A$1&"!$1:$1048576"),MATCH($B$1,INDI ...READ MORE

answered Sep 29 in Others by narikkadan
• 37,660 points
0 votes
1 answer

Download multiple excel files linked through urls in R

Try something along the lines of: for (i ...READ MORE

answered Sep 23 in Others by narikkadan
• 37,660 points
0 votes
1 answer

Excel, How to split cells by comma delimiter into new cells

The Excel manual method: choose Text to Column ...READ MORE

answered Oct 3 in Others by narikkadan
• 37,660 points
0 votes
1 answer

Comparing two Excel files in R and returning different titles

Solution: library(tidyverse) dat <- read_xlsx("Book1.xlsx") dat2 <- read_xlsx("Book2.xlsx") book1_output <- anti_join(dat,dat2, ...READ MORE

answered Oct 14 in Others by narikkadan
• 37,660 points
0 votes
1 answer

Conversion of PDF file to Excel in R

I looked at the pdf, and it ...READ MORE

answered Oct 16 in Others by narikkadan
• 37,660 points
0 votes
1 answer

In a excel formula I need to create a list of names on one sheet based upon criteria/data of another sheet

The final formula is: =IF(ROWS($H$3:H3)<=$I$1,INDEX(Personnel! ...READ MORE

answered Nov 25 in Others by narikkadan
• 37,660 points
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24 in Others by narikkadan
• 37,660 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP