R語言函數(shù)封裝

保存數(shù)據(jù)框?yàn)閤lsx工作薄中的工作表sheet

add_to_workbook

1. 如果不存在該xlsx文件桨菜，那么新建xlsx文件巍扛，再寫入工作表
2. 如果存在該xlsx文件芝此，那么根據(jù)工作表名寫入新的工作表

不能寫入同一個(gè)xlsx文件中的同名工作表

函數(shù)名add_to_workbook
調(diào)用方式

# 如果函數(shù)封裝在另一個(gè)R文件馒疹，則需先引入包資源
source("./save_xlsx.R")
# 模擬數(shù)據(jù)框
data <- data.frame(Column1 = c("Value1", "Value2", "Value3"), Column2 = c(10, 20, 30))
add_to_workbook(data, "my_workbook.xlsx", "NewSheet")

三個(gè)參數(shù)：
data：數(shù)據(jù)框
workbook_name: 工作薄名（即.xlsx文件名）橄碾，如不輸入狡逢，默認(rèn)為WorkBook+年月日時(shí)分秒.xlsx
worksheet_name: 工作表明， 如不輸入棚亩，默認(rèn)為sheet1

代碼如下：

add_to_workbook <- function(data, workbook_name = paste0("WorkBook", "_",Sys.Date(), "_",format(Sys.time(), "%H%M%S"), ".xlsx"), worksheet_name = "sheet1" ){
  if (!require("openxlsx")) {
    library("openxlsx")
  } 
  # else {
  #   cat(paste0(package_name, "已導(dǎo)入蓖议，無需再次導(dǎo)入藻肄。\n"))
  # }
  if (file.exists(workbook_name)) {
    # 如果文件存在，加載工作簿并添加新工作表
    existing_wb <- loadWorkbook(workbook_name)
    
    if (worksheet_name %in% names(existing_wb)) {
      cat(paste0("表格文件 ",workbook_name," 中已有同名工作表 ", worksheet_name, " 拒担！\n"))
      return("請(qǐng)仔細(xì)檢查代碼嘹屯！")
    }
    
    addWorksheet(existing_wb, worksheet_name)
    writeData(existing_wb, worksheet_name, data)
    
    
    # 設(shè)置字體和居中格式
    style <- createStyle(fontColour = "black", fontName = "Arial",fontSize = 10, halign = "center", valign = "center")
    addStyle(existing_wb, worksheet_name, style, rows = 1:(nrow(data)+1), cols = 1:(ncol(data)+1), gridExpand = TRUE)
    
    
    # 設(shè)置首行加粗
    bold_style <- createStyle(fontColour = "black", fontName = "Arial", fontSize = 12, halign = "center", valign = "center", textDecoration = "Bold")
    addStyle(existing_wb, worksheet_name, bold_style, rows = 1, cols = 1:ncol(data), gridExpand = TRUE)
    
    # 設(shè)置自適應(yīng)行高和列寬
    setColWidths(existing_wb, worksheet_name, cols = 1:2, widths = "8")
    setColWidths(existing_wb, worksheet_name, cols = 3:ncol(data), widths = "16")
    setRowHeights(existing_wb, worksheet_name, rows = 1:nrow(data), heights = "20")
    
    # 保存修改后的工作簿
    saveWorkbook(existing_wb, workbook_name, overwrite = TRUE)
    cat(paste0("表格文件",workbook_name," 當(dāng)前已經(jīng)存在！ 成功追加工作表 ", worksheet_name, " \n"))
  } else {
    # 如果文件不存在从撼，創(chuàng)建新工作簿并添加工作表
    wb <- createWorkbook()
    addWorksheet(wb, worksheet_name)
    writeData(wb, worksheet_name, data)
    
  
    # 設(shè)置字體和居中格式
    style <- createStyle(fontColour = "black", fontName = "Arial",fontSize = 10, halign = "center", valign = "center")
    addStyle(wb, worksheet_name, style, rows = 1:(nrow(data)+1), cols = 1:(ncol(data)+1), gridExpand = TRUE)
   
    # 設(shè)置首行加粗
    bold_style <- createStyle(fontColour = "black", fontName = "Arial", fontSize = 12, halign = "center", valign = "center", textDecoration = "Bold")
    addStyle(wb, worksheet_name, bold_style, rows = 1, cols = 1:ncol(data), gridExpand = TRUE)
    
    # 設(shè)置自適應(yīng)行高和列寬
    setColWidths(wb, worksheet_name, cols = 1:2, widths = "8")
    setColWidths(wb, worksheet_name, cols = 3:ncol(data), widths = "16")
    setRowHeights(wb, worksheet_name, rows = 1:nrow(data), heights = "20")
    
    # 保存新工作簿
    saveWorkbook(wb, workbook_name, overwrite = FALSE)
    cat(paste0("表格文件 ",workbook_name," 不存在州弟！ 成功新建工作薄并添加工作表 ", worksheet_name, "  \n"))
  }
}




# 模擬數(shù)據(jù)框
# data <- data.frame(Column1 = c("Value1", "Value2", "Value3"),
                   # Column2 = c(10, 20, 30))

# add_to_workbook(data, "my_workbook.xlsx", "NewSheet")