How to dynamically merge columns of XLSX downloads from an R Shiny app using the xlsx package?

Question

I'm trying to dynamically merge columns of XLSX downloads from an R Shiny app using the xlsx package. I can currently merge columns of an XLSX document created from a static dataframe as demonstrated in the following minimal example:

library(xlsx)

# Creating dataframe.
df <- data.frame(c("Title", 1, "one"),
                 c("", 2, "two"),
                 c("", 3, "three"))

# Creating a workbook using the XLSX package.
wb <- xlsx::createWorkbook(type = "xlsx")

# Creating a sheet inside the workbook.
sheet <- xlsx::createSheet(wb, sheetName = "Sheet0")

# Adding the full dataset into the sheet.
xlsx::addDataFrame(df, sheet, startRow = 1, startCol = 1, row.names = FALSE, col.names = FALSE)

# Merging first three columns.
addMergedRegion(sheet, 1, 1, 1, 3)

# Saving the workbook.
xlsx::saveWorkbook(wb, "df.xlsx")

I've implemented this example's logic in the following R Shiny app:

# ui.R

fluidPage(
  h1("Download XLSX"),
  downloadButton("download.button", label = "Download")
)

# server.R

library(xlsx)

function(input, output) {

  # Creating dataframe.
  df <- data.frame(c("Title", 1, "one"),
                   c("", 2, "two"),
                   c("", 3, "three"))

  # Handling download.
  # Creating XLSX file for download.
  output$download.button <- downloadHandler(
    filename <- paste0("df.xlsx"),
    content <- function(file) {
      # Creating a workbook using the XLSX package.
      wb <- xlsx::createWorkbook(type = "xlsx")

      # Creating a sheet inside the workbook.
      sheet <- xlsx::createSheet(wb, sheetName = "Sheet0")

      # Adding the full dataset into the sheet.
      xlsx::addDataFrame(df, sheet, startRow = 1, startCol = 1, row.names = FALSE, col.names = FALSE)

      # Merging first three columns.
      addMergedRegion(sheet, 1, 1, 1, 3)

      # Saving the workbook.
      xlsx::saveWorkbook(wb, file)
    }
  )

}

In the above example, addMergedRegion(sheet, 1, 1, 1, 3) merges the first three columns of the first row together as one. I'd like to be able to do this dynamically rather than through hard coding because I will be working with dataframes of various sizes. As input I want to use a list of row indices, column indices, and column spans. These would be all of the values I need to fill the addMergedRegion() function, however, I'm unsure of how to dynamically generate the functions needed to merge columns. As one example of this challenge, I made this second app:

# ui.R

fluidPage(
  h1("Download XLSX"),
  numericInput("numeric.input", "Select Number of Tables:", 1, min = 1),
  DT::dataTableOutput("table"),
  downloadButton("download.button", label = "Download")
)

# server.R

library(DT)
library(xlsx)

function(input, output) {

  # Creating dataframe.
  df <- data.frame(c("Title", 1, "one"),
                   c("", 2, "two"),
                   c("", 3, "three"))

  # Creating reactive dataframe.
  values <- reactiveValues(
    df = df
  )

  # Expanding dataframe based on user input.
  observeEvent(input$numeric.input, {
    values$df <- df[rep(seq_len(nrow(df)), input$numeric.input), ]
  })

  # Rendering data table of dataframe.
  output$table <- DT::renderDataTable({
    DT::datatable(values$df,
                  selection = "none",
                  rownames = FALSE,
                  options = list(dom = "t",
                                 ordering = FALSE,
                                 pageLength = nrow(values$df)))
  })

  # Handling download.
  # Creating XLSX file for download.
  output$download.button <- downloadHandler(
    filename <- paste0("df.xlsx"),
    content <- function(file) {
      # Creating a workbook using the XLSX package.
      wb <- xlsx::createWorkbook(type = "xlsx")

      # Creating a sheet inside the workbook.
      sheet <- xlsx::createSheet(wb, sheetName = "Sheet0")

      # Adding the full dataset into the sheet.
      xlsx::addDataFrame(values$df, sheet, startRow = 1, startCol = 1, row.names = FALSE, col.names = FALSE)

      # Saving the workbook.
      xlsx::saveWorkbook(wb, file)
    }
  )

}

This app extends the dataframe rows based on user input. How can I give each cell containing the string "Title" a column span of 3 in a dynamic case such as this?

Answer 1

I can't use xlsx due to java woes, but I think all you need is:

indices <- which(df=="Title", arr.ind=TRUE)

This will give you a n x 2 matrix of row and column indices which you can then use in addMergedRegion:

apply(indices, 1, function (x) {
  xlsx::addMergedRegion(sheet, x[1], x[1], x[2], x[2] + 2)
})

Life will be slightly more complex if you want cells that contain "Title" rather than cells that equal "Title":

x <- matrix(NA, nrow(df), ncol(df))
x[] <- grepl("Title", unlist(df))
indices <- which(x, arr.ind = TRUE)

... and proceed as before.