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, 2022 in Others by Kithuzzz
• 38,000 points
1,069 views

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)
saveWorkbook(wb1)
answered Nov 10, 2022 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

Create a hyperlink to a sheet with same name as the value in the selected cell in Excel through VBA

Credit to Spectral Instance who found the ...READ MORE

answered Feb 6, 2023 in Others by narikkadan
• 63,600 points
930 views
0 votes
0 answers

How to use VIF in r?

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

Jun 13, 2022 in Others by Avinash
• 1,260 points
444 views
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, 2022 in Others by narikkadan
• 63,600 points
1,394 views
0 votes
1 answer
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, 2022 in Others by narikkadan
• 63,600 points
1,436 views
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, 2022 in Others by narikkadan
• 63,600 points
916 views
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, 2022 in Others by narikkadan
• 63,600 points
2,014 views
0 votes
1 answer

Conversion of PDF file to Excel in R

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

answered Oct 16, 2022 in Others by narikkadan
• 63,600 points
1,764 views
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, 2022 in Others by narikkadan
• 63,600 points
1,128 views
0 votes
1 answer

Output python dataframe to excel and create a new data_validation column in the exported excel sheet

Use pandas.ExcelWriter with worksheet.data_validation from xlswriter : df["code"] = None items = list(range(1,10)) max_row, max_col = ...READ MORE

answered Jan 24, 2023 in Others by narikkadan
• 63,600 points
1,371 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP