#' write_list2xlsx
#'
#' @description base function, write list x into fileName with each list in workbook,
#' write shapefile dbf table into excel *.xlsx
#'
#' @param x List object to be saved, `x` should have names and can't be NULL.
#' @param file xlsx file name
#' @param .progress name of the progress bar to use, see create_progress_bar.
#' @param rowNames a logical value indicating whether the row names are to
#' be written.
#'
#' @import openxlsx
#' @export
write_list2xlsx <- function(x, file, .progress = "none", rowNames = FALSE, show = TRUE)
{
file <- file_name(file) %>% {paste0(dirname(file), "/", ., ".xlsx")}
name <- deparse(substitute(x))
if ("data.frame" %in% class(x)) {
x <- setNames(list(x), name)
}
sheetNames <- names(x)
if (is.null(sheetNames))
sheetNames <- paste0("sheet", seq_along(x))
wb <- createWorkbook()
options(openxlsx.borderStyle = "none")
hs1 <- createStyle(fgFill = "#DCE6F1", halign = "CENTER",
textDecoration = "Italic", border = "Bottom")
writeIn <- function(i) {
addWorksheet(wb, sheetNames[i])
writeData(wb, sheetNames[i], x[[i]], colNames = TRUE,
rowNames = rowNames, borders = "rows", headerStyle = hs1)
}
if (.progress != "none") cat(sprintf("[---- Writing into Workbook ----]\n"))
tmp <- llply(seq_along(x), writeIn, .progress = .progress)
if (.progress != "none") cat(sprintf("[---- Writing into xlsx file: %s ----]\n", file))
saveWorkbook(wb, file, overwrite = TRUE)
if (show) file_show(file)
}
#' read_xlsx2list
#'
#' If excel file hava many sheets, this function also works.
#'
#' @param file xlsx or xls file path
#' @param ... other parameters to [readxl::read_excel()]
#'
#' @import openxlsx readxl
#' @importFrom plyr llply
#' @export
read_xlsx2list <- function(file, ...){
cat(sprintf("[---- Reading File: %s ----]\n", file))
## judge whether it's xls or xlsx
# if file is *.xls use readxl::read_excel
if (length(grep("xls$", basename(file))) != 0){
sheetNames <- excel_sheets(file)
x <- llply(sheetNames, function(sheet) as.data.table(read_excel(file, sheet, ...)),
.progress = "text")
}else{
sheetNames <- getSheetNames(file)
x <- llply(sheetNames, function(sheet) read.xlsx(file, sheet, ...) %>% as.data.table(),
.progress = "text")
}
names(x) <- sheetNames
x
}
#' @export
fwrite2 <- function(x, file){
write.table(x, file, sep = ",", row.names = FALSE, fileEncoding = "gbk")
}
#' read_xlsx
#'
#' @importFrom openxlsx read.xlsx
#' @export
read_xlsx <- function(file, sheet = 1, ...){
read.xlsx(file, sheet, ..., detectDates = TRUE) %>% as.data.table()
}
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.