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
• 27,940 points
72 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
• 51,600 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, 2022 in Others by Avinash
• 1,260 points
69 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
• 51,600 points
229 views
0 votes
1 answer
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, 2022 in Others by narikkadan
• 51,600 points
140 views
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
• 51,600 points
207 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
• 51,600 points
111 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
• 51,600 points
249 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
• 51,600 points
257 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
• 51,600 points
67 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 in Others by narikkadan
• 51,600 points
40 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