add_formatted_worksheet: Adds formatting to an existing Excel worksheet or to a...

In rmsharp/excelutilsr: Convenience Functions for Working with Excel in R

Description

Adds formatting to an existing worksheet based on values of a user provided dataframe or adds a worksheet using all of the data in a dataframe and then adds the indicated formatting to the newly added worksheet. If the Excel workbook does not exist, it is created if the create flag is set to TRUE.

Usage

add_formatted_worksheet(m_df, excel_file, sheet = sheet, header = TRUE,
  fmt_list, create = TRUE)

Arguments

m_df	dataframe to receive formatted worksheet
excel_file	character vector of length 1 with file name of Excel workbook
sheet	character vector with name of worksheet
header	logical vector of length one having TRUE if a header is in the existing worksheet. A header is automatically created when the worksheet is created by this routine.
fmt_list	list of format list(s) used to format the worksheet
create	logical vector of length one having TRUE if a new worksheet is to be created

Details

Takes a dataframe, an Excel file name, and a list of styles to be used on the worksheet if the user provided function evalutes to TRUE for one or more cells within the dataframe.

The user must provide a test function written so that it evaluates to TRUE for the cells where the indicated formatting is wanted.

As long as there is at least one cell that is to be formatted, cell styles are set. If a cell has a value of TRUE for more than one of the fmt_list items, that last fmt_list item specifications will overwrite prior values. If the sheet does not exist and create is TRUE, one is created using m_df, otherwise the function stops with an error.

Value

Excel file name with formatted worksheet.

Examples

library(stringi)
library(XLConnect)


## borders can be any of the following line types
names(XLC[stri_detect_regex(names(XLC), pattern = "^BORDER.")])
## colors can be any of the following
names(XLC[stri_detect_regex(names(XLC), pattern = "^COLOR.")])
## fill_pattern can be any of the following
names(XLC[stri_detect_regex(names(XLC), pattern = "^FILL.")])
my_f1 <- function(x) {x > 7}
fmt_lst_1 <- list(test = my_f1,
                wrap = TRUE,
                fill_pattern = as.integer(XLC$FILL.SOLID_FOREGROUND),
                foreground_color = as.integer(XLC$COLOR.LAVENDER),
                border = list(side = "all",
                              type = as.integer(XLC$BORDER.THICK),
                              color = as.integer(XLC$COLOR.DARK_BLUE))
                )
my_df <- data.frame(ColA = stri_c("name_", 1:4),
                    ColB = c(4, 7, 8, 9),
                    ColC = c(100, 300, 3000, 132),
                    ColD = 1:4, stringsAsFactors = FALSE)
result <- add_formatted_worksheet(my_df, "example_wkbk.xlsx",
                                 sheet = "my_test",
                                 header = TRUE,
                                 fmt_list = list(fmt_lst_1), create = TRUE)
my_f2 <- function(x) {
  sapply(x, function(x) {
    if (all(is.numeric(x))) {
      (x %% 3) == 0
    } else {
      rep(FALSE, length(x))
    }
  })
}
fmt_lst_2 <- list(test = my_f2,
                wrap = TRUE,
                fill_pattern = as.integer(XLC$FILL.SOLID_FOREGROUND),
                foreground_color = as.integer(XLC$COLOR.TAN),
                border = list(side = "all",
                              type = as.integer(XLC$BORDER.DOUBLE),
                              color = XLC$COLOR.GREY_80_PERCENT)
                )
my_other_df <- data.frame(LETTERS = LETTERS[1:10],
                          NUMBERS = 1:10,
                          FRACTIONAL = 1 / (1:10), stringsAsFactors = FALSE)
result <- add_formatted_worksheet(my_other_df, "example_wkbk.xlsx",
                                 sheet = "my_2nd_test",
                                 header = TRUE,
                                 fmt_list = list(fmt_lst_1, fmt_lst_2),
                                 create = TRUE)

rmsharp/excelutilsr documentation built on May 27, 2019, 9:33 a.m.