Description Usage Arguments Details Value Examples
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
.
1 2 | add_formatted_worksheet(m_df, excel_file, sheet = sheet, header = TRUE,
fmt_list, create = TRUE)
|
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 |
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.
Excel file name with formatted worksheet.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | 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)
|
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.