5.3 Formatting

5.3.1 Create and apply some formatting

Now let’s try out some formatting functionality.

# Apply data format only - depending on the data type
setStyleAction(wb, XLC$STYLE_ACTION.DATA_FORMAT_ONLY)

# Set format for numeric data to 2 decimal digits
setDataFormatForType(wb, type = XLC$DATA_TYPE.NUMERIC, format = "0.00")

# Write the named region
writeNamedRegion(wb, data = swissfranc, name = "SwissFrancName")

# Create a cell style for the headers
csHeader <- createCellStyle(wb, name = "StyleHeader")
setFillPattern(csHeader, fill = XLC$FILL.SOLID_FOREGROUND)
setFillForegroundColor(csHeader, color = XLC$COLOR.CORNFLOWER_BLUE)
setCellStyle(wb, sheet = "NamedRegionEx", 
             row = 2, col = seq(2, ncol(swissfranc) + 1), 
             cellstyle = csHeader)

# Set an auto-filter
setAutoFilter(wb, sheet = "NamedRegionEx", 
              reference = aref("B2", dim(swissfranc)))

# Create a custom format for the date column
csDate <- createCellStyle(wb, name = "StyleDate")
setDataFormat(csDate, format = "yyyy-mm-dd")
setCellStyle(wb, sheet = "NamedRegionEx", 
             row = seq(3, nrow(swissfranc) + 2), 
             col = 2, 
             cellstyle = csDate)

# Enlarge column "date" to fit full information
setColumnWidth(wb, sheet = "NamedRegionEx", 
               column = 2, width = 2800)

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

5.3.2 Retrieve and apply some formatting

Intermediate step to make a copy of the template.

# Create a copy of the template
file.copy("xlconnect-demo/XLConnectDemo-Tpl.xlsx", 
          "xlconnect-demo/XLCDemo-TplCopy.xlsx", 
          overwrite = FALSE)
wbcopy <- loadWorkbook("xlconnect-demo/XLCDemo-TplCopy.xlsx")

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

Add a new sheet in the copied file and use defined formatting from the template.

# Create a dummy input sheet
writeWorksheetToFile("xlconnect-demo/XLCDemo-TplCopy.xlsx", 
                     data = data.frame("Titles" = c("Dummy Input", "Exchange Rate CHF/Curr")), 
                     sheet = "Input", 
                     startRow = 2, startCol = 2, header = FALSE)

# Get some data from the previous file
# Read data from a specific named region of a workbook
if (exists("xlconnect-demo/XLConnectDemo.xlsx")) {
  dataToReuse <- readNamedRegionFromFile("xlconnect-demo/XLConnectDemo.xlsx", 
                                         name = "SwissFrancName", 
                                         header = TRUE)
} else {
  dataToReuse <- swissfranc
}

# Add data in a named region
writeNamedRegionToFile("xlconnect-demo/XLCDemo-TplCopy.xlsx", 
                       data = dataToReuse, name = "SwissFrancName", 
                       formula = "Input!$B$5")

# Retrieve formatting from existing cell style names from the template copy
wbcopy <- loadWorkbook("xlconnect-demo/XLCDemo-TplCopy.xlsx")
cstitle1 <- getCellStyle(wbcopy, "Title1")
cstitle2 <- getCellStyle(wbcopy, "Title2")
csTableHeader <- getCellStyle(wbcopy, "TableHeader")
csinp <- getCellStyle(wbcopy, "Inp")
cscalc <- getCellStyle(wbcopy, "Calc")

# Apply formatting - this has to be in the same file
setCellStyle(wbcopy, 
             formula = "Input!$B$2:$B$2", 
             cellstyle = cstitle1)
setCellStyle(wbcopy, 
             formula = "Input!$B$3:$B$3", 
             cellstyle = cstitle2)
setCellStyle(wbcopy, 
             formula = "Input!$C$5:$E$425", 
             cellstyle = csinp)
setCellStyle(wbcopy, 
             formula = "Input!$B$5:$B$425", 
             cellstyle = cscalc)
if (!existsCellStyle(wbcopy, "csDate")) {
  csDate <- createCellStyle(wbcopy, name = "DateFormat")
  setDataFormat(csDate, format = "yyyy-mm-dd")
}
setCellStyle(wbcopy, sheet = "Input", 
             row = 5:425, 
             col = 2, 
             cellstyle = csDate)

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