I need to create a bar chart in Excel on a separate sheet to the data. The only way I have managed to do this is by adding the data to the workbook object then referencing that as the data source for the chart. However, when I do it this way the group argument in ms_barchart function does not appear to work. See example below. If I create the chart directly from the data frame the chart and a data table are added to the same sheet and the group by argument works. I can't find a function to move the chart to a new sheet after its added through wb_add_mschart
library(openxlsx2)
library(mschart)
## Create Workbook object and add worksheets
wbTest <- wb_workbook()
wbTest <- wbTest %>%
wb_add_worksheet("add_mschart - wb_data") %>%
wb_add_worksheet("Chart1") %>%
wb_add_worksheet("Chart2") %>%
wb_add_data(x = browser_data, sheet = "add_mschart - wb_data", dims = "A1")
##Read data from workbook object
dat <- wb_data(wbTest, sheet = "add_mschart - wb_data", dims = "A1:C19")
##Create chart from dataframe
chart_01 <- ms_barchart(
data = browser_data, x = "browser",
y = "value", group = "serie"
)
##Create chart using same data but read from workbook object
chart_02 <- ms_barchart(
data = dat, x = "browser",
y = "value", group = "serie"
)
## add charts to workbook
wbTest <- wbTest %>%
wb_add_mschart(sheet = "chart1", dim = "H20", graph = chart_01) %>%
wb_add_mschart(sheet = "chart2", graph = chart_02)
## save workbook
wb_save(wbTest, file = "ChartTest2.xlsx", overwrite = TRUE)
The data you see in the top corner of the sheet Chart1 is what is used by mschart for the data creation. And mschart on it's own uses some opinionated data aggregation. If you want to recreate the plot, we can use something like this (tested it with Arch Linux and LibreOffice). I've added a Chart3 sheet and the data in wide format to the data sheet.
In theory it should be fairly easy to write the data to a different sheet, I just never saw the need, because I do not like the under the hood mschart data aggregation in the first place.
library(openxlsx2)
library(mschart)
## Create Workbook object and add worksheets
wbTest <- wb_workbook()
prep_data <- browser_data %>%
tidyr::pivot_wider(names_from = serie)
wbTest <- wbTest %>%
wb_add_worksheet("add_mschart - wb_data") %>%
wb_add_worksheet("Chart1") %>%
wb_add_worksheet("Chart2") %>%
wb_add_worksheet("Chart3") %>%
wb_add_data(x = browser_data, sheet = "add_mschart - wb_data", dims = "A1") %>%
wb_add_data(x = prep_data, sheet = "add_mschart - wb_data", dims = "E1")
##Read data from workbook object
dat <- wb_data(wbTest, sheet = "add_mschart - wb_data", dims = "A1:C19")
tab <- wb_data(wbTest, sheet = "add_mschart - wb_data", dims = "E1:H7")
##Create chart from dataframe
chart_01 <- ms_barchart(
data = browser_data, x = "browser",
y = "value", group = "serie"
)
##Create chart using same data but read from workbook object
chart_02 <- ms_barchart(
data = dat, x = "browser",
y = "value", group = "serie"
)
##Create chart using same data but read from workbook object
chart_03 <- ms_barchart(
data = tab, x = "browser",
y = c("serie1", "serie2", "serie3")
) %>%
chart_labels(ylab = "value")
## add charts to workbook
wbTest <- wbTest %>%
wb_add_mschart(sheet = "chart1", dim = "H20", graph = chart_01) %>%
wb_add_mschart(sheet = "chart2", graph = chart_02) %>%
wb_add_mschart(sheet = "chart3", graph = chart_03)
if (interactive())
wbTest %>% wb_open()