Nested for loop to add slide numbers for ppt reports using officer

Question

With code below, I could dynamically generate multiple ppt reports using officer package:

url_list <- c(
  'http://www.csrc.gov.cn/csrc/c101921/c1758587/1758587/files/2022%E5%B9%B4%E7%AC%AC1%E5%91%A8((2022-01-04%E8%87%B32022-01-07)%E6%9C%9F%E8%B4%A7%E5%B8%82%E5%9C%BA%E4%B8%BB%E8%A6%81%E5%93%81%E7%A7%8D%E4%BA%A4%E6%98%93%E7%BB%9F%E8%AE%A1%E6%83%85%E5%86%B5%E8%A1%A8.xlsx',
  'http://www.csrc.gov.cn/csrc/c101921/c1714636/1714636/files/2021%E5%B9%B4%E7%AC%AC52%E5%91%A8((2021-12-27%E8%87%B32021-12-31)%E6%9C%9F%E8%B4%A7%E5%B8%82%E5%9C%BA%E4%B8%BB%E8%A6%81%E5%93%81%E7%A7%8D%E4%BA%A4%E6%98%93%E7%BB%9F%E8%AE%A1%E6%83%85%E5%86%B5%E8%A1%A8.xlsx',
  'http://www.csrc.gov.cn/csrc/c101921/c1664367/1664367/files/2021%E5%B9%B4%E7%AC%AC51%E5%91%A8(2021-12-20%E8%87%B32021-12-24)%E6%9C%9F%E8%B4%A7%E5%B8%82%E5%9C%BA%E4%B8%BB%E8%A6%81%E5%93%81%E7%A7%8D%E4%BA%A4%E6%98%93%E7%BB%9F%E8%AE%A1%E6%83%85%E5%86%B5%E8%A1%A8.XLS'
)

# https://davidgohel.github.io/officer/reference/ph_location_type.html
loc_dt <- ph_location_type(type = "dt") # date
loc_slidenum <- ph_location_type(type = "sldNum") #loc_slidenum

for (url_path in url_list) {
  for (i in (1:length(url_list))) {
    df <- openxlsx::read.xlsx(url_path, rows = c(5:7),
                              cols = c(5:7), na.strings = "-")
    temp <- read_pptx()
    
    report <- add_slide(temp, layout = "Title and Content", master = "Office Theme") %>%
      ph_with(value='content', location = ph_location_label(ph_label = "Content Placeholder 2"))
    
    report <- add_slide(report, layout = "Title and Content", master = "Office Theme") %>%
      ph_with(value = df, location = ph_location_label(ph_label = "Content Placeholder 2"))
    
    n_slides <- length(report)
    for (i_slide in 1:n_slides) {
      report <- report %>%
        ph_with(value = format(Sys.Date()), location = loc_dt) %>% 
        ph_with(value = i_slide, location = loc_slidenum)
    }
    print(report, target=glue('report{i}.pptx'))
  }
}

Out:

As you may notice, the slide number (which added by for (i_slide in 1:n_slides)) display with a typo error, which seems overlapping number 1 and 2.

Does someone could help to figure out why I get this error? Also, if it's possible, please help to convert my code to purrr::map() or purrr:iwalk(). Thanks.

I'm not obliged to use nested loop. – ah bon Jan 12 '22 at 18:34 — ah bon, Jan 12 '22 at 18:34

akrun · Accepted Answer · 2022-01-12T19:18:17.403

If we want to get the value and the index of the 'url_list', instead of a nested loop, just loop over the sequence of 'url_list', which returns the index and use that index to subset the corresponding 'url'. In addition, change the colNames = FALSE in read.xlsx as by default it is TRUE and thus the first row will be taken as column name

for (i  in seq_along(url_list)) {
    # create the 'url_path' so that we don't have to change the OP's code below
   url_path <- url_list[i]
    df <- openxlsx::read.xlsx(url_path, rows = c(5:7),
                              cols = c(5:7), na.strings = "-", colNames = FALSE)
    temp <- read_pptx()
    
    report <- add_slide(temp, layout = "Title and Content", master = "Office Theme") %>%
      ph_with(value='content', location = ph_location_label(ph_label = "Content Placeholder 2"))
    
    report <- add_slide(report, layout = "Title and Content", master = "Office Theme") %>%
      ph_with(value = df, location = ph_location_label(ph_label = "Content Placeholder 2"))
    
    n_slides <- length(report)
    for (i_slide in 2:n_slides) {
      report <- report %>%
        ph_with(value = format(Sys.Date()), location = loc_dt) %>% 
        ph_with(value = i_slide, location = loc_slidenum)
    }
    print(report, target=glue('report{i}.pptx'))
  }

Or using iwalk

library(purrr)
iwalk(url_list, ~  {
  
  url_path <- .x
  i <- .y
  df <- openxlsx::read.xlsx(url_path, rows = c(5:7),
                            cols = c(5:7), na.strings = "-", colNames = FALSE)
  temp <- read_pptx()
  
  report <- add_slide(temp, layout = "Title and Content", master = "Office Theme") %>%
    ph_with(value='content', location = ph_location_label(ph_label = "Content Placeholder 2"))
  
  report <- add_slide(report, layout = "Title and Content", master = "Office Theme") %>%
    ph_with(value = df, location = ph_location_label(ph_label = "Content Placeholder 2"))
  
  n_slides <- length(report)
  for (i_slide in 2:n_slides) {
    report <- report %>%
      ph_with(value = format(Sys.Date()), location = loc_dt) %>% 
      ph_with(value = i_slide, location = loc_slidenum)
  }
  print(report, target=glue('report{i}.pptx'))
})

-output

I run your code, slide number are still overlapped two numbers (1 and 2), do you mind generate reports and check? — ah bon, Jan 12 '22 at 18:32
@ahbon i did check, it is 1 and 2 in each of the files. Isn't that the expected? — akrun, Jan 12 '22 at 18:33
@ahbon i didn't understand your comment about overlapped two numbers. I couldn't replicate that issue though — akrun, Jan 12 '22 at 18:35
I update the output I get, please check if you get same result. So I think maybe it's problematic to use `for (i_slide in 1:n_slides)` to add slide numbers, do you know other options? — ah bon, Jan 12 '22 at 18:41
@ahbon I think the issue is that the first page is a title, so you can number from the second page onwards i.e. change the loop to `2:n_slides`. I am not sure if this is a bug in the package or not — akrun, Jan 12 '22 at 19:13
@ahbon I also tried by adding additional slides to check that — akrun, Jan 12 '22 at 19:14
OK, thanks a lot for testing and sharing those two solutions. — ah bon, Jan 13 '22 at 01:27

Nested for loop to add slide numbers for ppt reports using officer

1 Answers1