Search code examples
rnested-loopspurrrofficerofficedown

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

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:

enter image description here

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.

Solution

  • 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

    enter image description here