Is it possible to control the Excel to draw figures?

[hope-data-science]

openxlsx2 is awesome, I enjoy the new style to control Excel via R.

Even in openxlsx, we find no functions that control the data manipulation and visualization in Excel, we could only use R to draw figures and attach it to Excel files. Is there any way to use R to draw figures by utilizing the Excel facilities?

Thanks.

[JanMarvin]

Hi @hope-data-science , thanks for the kind words.

Adding charts is a non trivial task. There is a first draft in #219 (with screenshots 📷 ). Though there is no ETA yet when or even if this will ever be added to the package. This takes a lot of time to research and add. Even the basic example took quite a while to add. Modifying charts will be a follow up task.

[hope-data-science]

Thank you for the feedback, I can understand.

[JanMarvin]

I have moved this to discussion, since it not really an issue.

[JanMarvin]

Hi @hope-data-science , with the upcoming release 0.4 (I have just submitted the package to CRAN), we now provide rvg and mschart >= 0.4 support. For a few examples have a look at the opening post in #412. A vignette on handling charts will follow "soon". The new release allows writing rvg drawings and mschart charts to xlsx output. The latter allows creating native open xml charts. However, this does not allow to modify diagrams, for that a converter from openxlsx2 to mschart would be needed and if such a thing is ever achieved, it is something for the distant future.

[hope-data-science]

Thank you for the feedback. This is excellent! Modification might be useful, but the current drawing figures in excel controlled by R is wonderful enough. Thanks. If this comes out, I would very much like to write a post to introduce it in Chinese.

[JanMarvin]

Thanks! A couple more examples for rvg and mschart support in openxlsx2. CRAN is in the process of creating binaries for openxlsx2 release 0.4, everything should be available within the week.

Looking forward to your blog post!

library(openxlsx2) # openxlsx2 >= 0.4 for mschart and rvg support
library(mschart) # mschart >= 0.4 for openxlsx2 support 
library(ggplot2)
library(rvg)


## create a workbook
wb <- wb_workbook()$
  add_worksheet()


## add rvg example to workbook
tmp <- tempfile(fileext = ".xml")
dml_xlsx( file =  tmp, fonts = list(sans = "Bradley Hand"))
ggplot(data = iris,
       mapping = aes(x = Sepal.Length, y = Petal.Width)) +
  geom_point() + labs(title = "With font Bradley Hand") +
  theme_minimal(base_family = "sans", base_size = 18)
dev.off()

wb <- wb$
  add_drawing(xml = tmp)$
  add_drawing(xml = tmp, dims = NULL)


## create chart from mschart object (this creates new input data)
mylc <- ms_linechart(
  data = browser_ts,
  x = "date",
  y = "freq",
  group = "browser"
)
mylc <- chart_labels(
  mylc,
  title = "my title",
  xlab = "my x label",
  ylab = "my y label"
)

wb$add_worksheet()$add_mschart(dims = "A10:G25", graph = mylc)


## create chart referencing worksheet cells as input
# write data starting at B2
wb <- wb$
  add_worksheet()$
  add_data(x = mtcars, dims = "B2")$
  add_data(x = data.frame(name = rownames(mtcars)), dims = "A2")

# create wb_data object this will tell this mschart 
# from this PR to create a file corresponding to openxlsx2
dat <- wb_data(wb, dims = "A2:G10")

# create a few mscharts
scatter_plot <- ms_scatterchart(
  data = dat,
  x = "mpg",
  y = c("disp", "hp")
)

bar_plot <- ms_barchart(
  data = dat,
  x = "name",
  y = c("disp", "hp")
)

area_plot <- ms_areachart(
  data = dat,
  x = "name",
  y = c("disp", "hp")
)

line_plot <- ms_linechart(
  data = dat,
  x = "name",
  y = c("disp", "hp"),
  labels = c("disp", "hp")
)

# add the charts to the data
wb <- wb %>% 
  wb_add_mschart(dims = "F4:L20", graph = scatter_plot) %>%
  wb_add_mschart(dims = "F21:L37", graph = bar_plot) %>%
  wb_add_mschart(dims = "M4:S20", graph = area_plot) %>%
  wb_add_mschart(dims = "M21:S37", graph = line_plot)


## set colors for mschart object
require(officer)
mytheme <- mschart_theme(
  axis_title = fp_text(color = "red", font.size = 24, bold = TRUE),
  grid_major_line_y = fp_border(width = 1, color = "orange"),
  axis_ticks_y = fp_border(width = .4, color = "gray")
)


my_bc <- ms_barchart(
  data = browser_data, x = "browser",
  y = "value", group = "serie"
)
my_bc <- chart_settings(my_bc,
                        dir = "horizontal", grouping = "stacked",
                        gap_width = 150, overlap = 100
)
my_bc <- set_theme(my_bc, mytheme)

wb$add_worksheet()$add_mschart(dims = "A10:G25", graph = my_bc)

my_scatter <- ms_scatterchart(
  data = iris,
  x = "Sepal.Length",
  y = "Sepal.Width",
  group = "Species"
)
my_scatter <- chart_data_fill(
  my_scatter,
  values = c(
    virginica = "#6FA2FF",
    versicolor = "#FF6161",
    setosa = "#81FF5B"
  )
)

wb$add_worksheet()$add_mschart(dims = "A10:G25", graph = my_scatter)

wb$open()