R/Officer/MsChart export to Powerpoint: How to set the Interval between labels for a Barchart

Question

I currently Export e.g. a Barchart with R to a Powerpoint-Presentation using the officer- and mschart-package. I mostly could achive everything I wanted. But if I have a huge data.frame like this (minimal.pptx is just a pptx with one empty slide):

library(officer)
library(mschart)

test_data <- data.frame(
    stringsAsFactors = FALSE,
    categories = factor(
        rep(
            c(
                "Label 1", "Label 2", "Label 3", "Label 4", "Label 5",
                "Label 6", "Label 7", "Label 8", "Label 9", "Label 10",
                "Label 11", "Label 12", "Label 13", "Label 14", "Label 15",
                "Label 16", "Label 17", "Label 18", "Label 19", "Label 20",
                "Label 21", "Label 22", "Label 23", "Label 24", "Label 25",
                "Label 26", "Label 27", "Label 28", "Label 29", "Label 30",
                "Label 31", "Label 32", "Label 33", "Label 34", "Label 35"
            ),
            3
        ),
        levels = c(
            "Label 1", "Label 2", "Label 3", "Label 4", "Label 5",
            "Label 6", "Label 7", "Label 8", "Label 9", "Label 10",
            "Label 11", "Label 12", "Label 13", "Label 14", "Label 15",
            "Label 16", "Label 17", "Label 18", "Label 19", "Label 20",
            "Label 21", "Label 22", "Label 23", "Label 24", "Label 25",
            "Label 26", "Label 27", "Label 28", "Label 29", "Label 30",
            "Label 31", "Label 32", "Label 33", "Label 34", "Label 35"
        ),
    ),
    values = c(
        1, 2, 3, 4, 5,
        6, 7, 8, 9, 10,
        11, 12, 13, 14, 15,
        16, 17, 18, 19, 20,
        21, 22, 23, 24, 25,
        26, 27, 28, 29, 30,
        31, 32, 33, 34, 35
    ),
    groups = c(
        "A", "A", "A", "A", "A",
        "A", "A", "A", "A", "A",
        "A", "A", "A", "A", "A",
        "A", "A", "A", "A", "A",
        "A", "A", "A", "A", "A",
        "A", "A", "A", "A", "A",
        "A", "A", "A", "A", "A"
    )
)

chrt <- ms_barchart(
    test_data,
    x = "categories",
    y = "values",
    group = "groups"
)

chrt <- chart_settings(
    chrt,
    dir = "horizontal",
    vary_colors = TRUE,
    grouping = "standard"
)

chrt <- chart_data_labels(
    chrt,
    position = "outEnd",
    show_val = T
)


chrt <- chart_data_fill(
    chrt,
    values = group_colors
) %>%
    chart_data_stroke(
        "transparent"
    )


chrt <- chart_labels(
    x = chrt,
    title = "Chart Title",
    xlab = "",
    ylab = ""
)

chrt <- chart_labels_text(
    chrt,
    values = fp_text(
        color = "#372E2C",
        font.size = 12,
        font.family = "Arial"
    )
)

my_chart_theme <- mschart_theme(
    legend_position = "n",
    main_title = fp_text(
        color = "#372E2C",
        font.size = 12,
        font.family = "Arial"
    ),
    axis_text_y = fp_text(
        color = "#878280",
        font.size = 12,
        font.family = "Arial"
    ),
    axis_text_x = fp_text(
        color = "#372E2C",
        font.size = 12,
        font.family = "Arial"
    ),
    grid_major_line_x = fp_border(
        width = 0
    ),
    grid_major_line_y = fp_border(
        color = "#E7E6E6",
        style = "solid",
        width = 0.5
    ),
    axis_ticks_x = fp_border(
        color = "#372E2C",
        style = "solid",
        width = 2.5
    ),
    axis_ticks_y = fp_border(
        color = "#F5F4F4",
        width = .001
    )
)

chrt <- set_theme(chrt, my_chart_theme)

chrt <- chart_ax_x(
    chrt,
    major_tick_mark = "none",
    minor_tick_mark = "none"
)

chrt <- chart_ax_y(
    x = chrt,
    major_tick_mark = "none",
    minor_tick_mark = "none"
)

doc <- read_pptx(path = "minimal.pptx")

default_location <- ph_location(
    left = 6.72,
    top = 0.47,
    width = 6.14,
    height = 6.721
)

doc <- on_slide(x = doc, index = 1)
doc <- ph_with(
    doc,
    value = chrt,
    location = default_location
)

print(doc, target = "output.pptx")

where I have a lot of categories, let's say thirtfive, on the exported Barcharts X-Axis there will only be printed every second Category as X-Axis-Label. It sets the Interval automaticaly to 2 because of the many labels there are. In Powerpoint I can directly set the "Interval between Labels" to 1 on the Axis and all Labels are shown. But I don't find any setting for MsChart in R to set the Interval for the export. Can anyone tell me where I can make this setting? Or if it's simply not there? I couldn't find anything helpful in chart_settings, chart_theme and chart_ax_x.

What I get is this:

What I want is this:

Sure, I can reduce the Label-Font-Size for the axis, but that would be more a workaround. The real solution should be to define this interval manualy.

Welcome to SO! It would be easier to help you if you provide [a minimal reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) including a minimal and working example of the code you have tried as well as some working (!!) fake data. — stefan, Mar 01 '23 at 17:56
@stefan Can do that but will need some time. Thought because of it looking like expected behaviour and not a Bug and I just need a pinpoint to where one can modify this behaviour wouldn't really need a code-example. — daily, Mar 02 '23 at 08:31
@stefan I edited my Question including minimal-Code Example, actual Output and expected Output. Please reopen my Question. Next time please let me some time for the edits please ;) Your comment about the Information-Request was around 7pm local time ( I work till 5pm) and the closing of the issue was around 7am (I work from 8am). So I had no chance to fulfill your request in time. — daily, Mar 02 '23 at 10:15

score 0 · Answer 1 · answered Mar 13 '23 at 09:06

Based on my examination of the underlying code in mschart:::axis_content_xml, which is where the parameters fed into chart_ax_x / chart_ax_y are converted into xml for PowerPoint, specifying the interval between labels wasn't part of the function's intended uses.

That said, it's relatively simple (in execution, that is; took me a while to locate the right terms to use) to hack this:

original.function <- mschart:::axis_content_xml

new.function <- function(...) {
  paste0(original.function(...),
         "<c:tickLblSkip val=\"1\"/>")
}

assignInNamespace("axis_content_xml",
                  new.function,
                  ns = "mschart")

We assign the current version of mschart:::axis_content_xml to a named object (original.function) for convenience, then define our own modified version (new.function) by pasting "<c:tickLblSkip val=\"1\"/>" at the end. Since mschart:::axis_content_xml is called by ph_with when a chart is added to a rpptx object, adding this line will insert the xml instruction for specifying unit interval as 1 into the rpptx object.

Running the assignInNamespace() instruction in console will result in the modified version being used, instead of the original, for the rest of the current R session (or until you change your mind). Do note this will not persist across sessions, though it shouldn't be difficult to re-run the above chunk of code every session, should this be a recurring need.

If you do change your mind, running

assignInNamespace("axis_content_xml",
                  original.function,
                  ns = "mschart")

should get things back to normal for any new charts you add to a rpptx object thereafter.

R/Officer/MsChart export to Powerpoint: How to set the Interval between labels for a Barchart

1 Answers1