5.5 Other functionalities

Let’s perform some usual tasks on a template and use few more interesting XLConnect functionalities like:

  • ensure values get updated when opening a workbook

  • check if a sheet is visible

  • check if a named region exists

  • hide or unhide sheets

  • append a named region

  • get the coordinates of a named region in a sheet

  • additional formatting, e.g. setting the column width

  • remove sheets and named regions

  • clone a sheet

  • add a hyperlink

5.5.1 Recalculate workbook

Read the file and update some values on the R objects.

if (!exists("xlconnect-demo/XLCDemo-TplCopy.xlsx")) {
  # Create a copy of the template
  file.copy("xlconnect-demo/XLConnectDemo-Tpl.xlsx", 
          "xlconnect-demo/XLCDemo-TplCopy.xlsx", 
          overwrite = FALSE)
} 

# Load copy of the template
wbcopy <- loadWorkbook("xlconnect-demo/XLCDemo-TplCopy.xlsx")

# Read data from template copy using named regions
inc_df <- readNamedRegion(wbcopy,
                          name = "Income_inp",
                          header = TRUE)
persexp_df <- readNamedRegion(wbcopy,
                          name = "PersExpenses_inp",
                          header = TRUE)
opexp_df <- readNamedRegion(wbcopy,
                          name = "OpExpenses_inp",
                          header = TRUE)

# Update numbers: double sales
inc_df$ACTUAL[inc_df$INCOME == "Net sales"] <- inc_df$ACTUAL[inc_df$INCOME == "Net sales"] * 2
# Update numbers: add employee benefits
persexp_df$ACTUAL[persexp_df$PERSONNEL.EXPENSES == "Employee benefits"] <- 3000
# Update numbers: reduce maintenance
opexp_df$ACTUAL[opexp_df$OPERATING.EXPENSES == "Maintenance and repairs"] <- 2500

Write the data into the workbook, set the “force formula recalculation” flag on it and save the file.

# Update output accordingly
writeNamedRegion(wbcopy, Sys.Date(), "Date", header = FALSE)
writeNamedRegion(wbcopy, inc_df, "Income_out", header = FALSE)
writeNamedRegion(wbcopy, persexp_df, "PersExpenses_out", header = FALSE)
writeNamedRegion(wbcopy, opexp_df, "OpExpenses_out", header = FALSE)

# Get numbers of sheets
nb_sheets <- length(getSheets(wbcopy))

# Force Excel to re-calculate when opening the workbook
setForceFormulaRecalculation(wbcopy, sheet = seq(1, nb_sheets), TRUE)

# Save workbook - this actually writes the file
saveWorkbook(wbcopy)

5.5.2 Hide sheets

Hide irrelevant sheets.

# Hide sheets
sheetsToHide <- c("Style", "Input")

# Ensure those sheets exist in the workbook
sheetsToHide <- sheetsToHide[sheetsToHide %in% getSheets(wbcopy)]
hideSheet(wbcopy, sheetsToHide, veryHidden = TRUE)

# Save workbook - this actually writes the file
saveWorkbook(wbcopy)

5.5.3 Sheet visibility check and unhide sheets

Check if a given sheet is visible and unhide it if it is.

# Check if a sheet is visible and unhide it
if (!isSheetVisible(wbcopy, c("Review"))) {
  # Unhide sheet
  unhideSheet(wbcopy, "Review")
}

# Save workbook - this actually writes the file
saveWorkbook(wbcopy)

5.5.4 Append named region

Check if a named region exists and append it if it does.

# Check if a named region exists
if (existsName(wbcopy, c("Review"))) {
  # Create a new entry to append
  newreview_df <- data.frame(Reviewer = c("Sam Tire"), 
                             Function = c("Accounting Reviewer"), 
                             Date = Sys.Date())
  # Ensure column matches
  if (!all(colnames(newreview_df) == colnames(readNamedRegion(wbcopy, "Review")))) {
    stop("table to populate does not match expected header, column names and order must match")
  }
  
  # Append existing named region
  appendNamedRegion(wbcopy, newreview_df, name = "Review")
  csinp <- getCellStyle(wbcopy, "Inp")
  
  # Get position of a named region in a Excel file
  review_pos <- getReferenceCoordinatesForName(wbcopy, "Review")
  colStart_pos <- LETTERS[review_pos[1,2]]
  colEnd_pos <- LETTERS[review_pos[2,2]]
  
  # Apply cell formatting
  setCellStyle(wbcopy, 
             formula = paste0("Review!", colStart_pos, "$", review_pos[1,1] + 1, ":$", colEnd_pos, review_pos[2,1]),
             cellstyle = csinp)
  if (!existsCellStyle(wbcopy, "Format-Date")) {
    # Create a custom format for the date column
    csDate <- createCellStyle(wbcopy, name = "Format-Date")
    setDataFormat(csDate, format = "yyyy-mm-dd")
  }
  setCellStyle(wbcopy, sheet = "Review", 
             row = seq(review_pos[1,1] + 1, review_pos[2,1]), 
             col = review_pos[2,2], 
             cellstyle = csDate)
  # Enlarge column date
  setColumnWidth(wbcopy, sheet = "Review", column = review_pos[2,2], width = 4000 )
 
  # Save workbook - this actually writes the file
  saveWorkbook(wbcopy)
}

5.5.5 Remove sheets

Delete sheets.

# Create a new copy
file.copy("xlconnect-demo/XLCDemo-TplCopy.xlsx", 
          "xlconnect-demo/XLCDemo-Input.xlsx", 
          overwrite = FALSE)

# Load new copy
wbinput <- loadWorkbook("xlconnect-demo/XLCDemo-Input.xlsx")

# Unhide sheet - if goal is to remove all sheets but "input" then it has to be visible
unhideSheet(wbinput, "Input")

# Remove all sheets of the copy but "input"
wbinput_allsheets <- getSheets(wbinput)
removeSheet(wbinput, wbinput_allsheets[!(wbinput_allsheets %in% c("Input"))])

# Save workbook - this actually writes the file
saveWorkbook(wbinput)

5.5.6 Remove named regions

Delete Named Regions.

# Load new copy
wbinput <- loadWorkbook("xlconnect-demo/XLCDemo-Input.xlsx")

# Remove all named regions of the copy but "SwissFrancName"
wbinput_allnr <- getDefinedNames(wbinput)
removeName(wbinput, wbinput_allnr[!(wbinput_allnr %in% c("SwissFrancName"))])

# Save workbook - this actually writes the file
saveWorkbook(wbinput)

5.5.7 Clone sheets

Clone a sheet.

# Load new copy
wbinput <- loadWorkbook("xlconnect-demo/XLCDemo-Input.xlsx") 

# Ensure sheet exists
if (existsSheet(wbinput, "Input")) {
  # Clone sheet
  cloneSheet(wbinput, sheet = "Input", name = "clonedSheet")
  # Save workbook - this actually writes the file
  saveWorkbook(wbinput)
}

5.5.8 Clear sheets

Clear a sheet can be useful if data sets in a worksheet need to be replaced in a smaller range as the original data.

# Load new copy
wbinput <- loadWorkbook("xlconnect-demo/XLCDemo-Input.xlsx") 
if (existsSheet(wbinput, "clonedSheet")) {
  # Clear sheet
  clearSheet(wbinput, sheet = "clonedSheet")
  # Save workbook - this actually writes the file
  saveWorkbook(wbinput)
}

5.5.10 Merge / Unmerge cells

Merge or unmerge cells.

# Load new copy
wbinput <- loadWorkbook("xlconnect-demo/XLCDemo-Input.xlsx") 
if (existsSheet(wbinput, "clonedSheet")) {
  # Merge cells - to unmerge, use the similar function 'unmergeCells()'
  mergeCells(wbinput, sheet = "clonedsheet", reference = "B2:C2")
  # Save workbook - this actually writes the file
  saveWorkbook(wbinput)
}

5.5.11 Clean up

Clean up and remove the demo files.

# Remove file
file.remove("xlconnect-demo/XLConnectDemo.xlsx")
file.remove("xlconnect-demo/XLCDemo-TplCopy.xlsx")
file.remove("xlconnect-demo/XLCDemo-Input.xlsx")