R: قراءة وكتابة ملفات Excel

# 1. Packing(Not dependent on Java)
install.packages("readxl")
install.packages("writexl")

# 2. List all Excel Documents
files <- list.files("reports/", pattern = "\\.xlsx$", full.names = TRUE)

# 3. Read all in bulk sheet
library(readxl)
all_data <- lapply(files, function(f) {
  list(
    sales = read_excel(f, sheet = "Sales Details"),
    payment = read_excel(f, sheet = "Payment"),
    stock = read_excel(f, sheet = "Inventory")
  )
})

# 4. Consolidated Analysis
library(dplyr)
combined <- bind_rows(lapply(all_data, function(d) d$sales))

library(readxl)

# 1. List all sheet
sheets <- excel_sheets("report.xlsx")
print(sheets)
# [1] "Sales Details" "Payment"     "Inventory"    

# 2. Read as Specified sheet
sales <- read_excel("report.xlsx", sheet = "Sales Details")
# or press sheet Number
sales <- read_excel("report.xlsx", sheet = 1)

# 3. Read All sheet(Back list)
all_sheets <- lapply(excel_sheets("file.xlsx"), function(name) {
  read_excel("file.xlsx", sheet = name)
})
names(all_sheets) <- excel_sheets("file.xlsx")

# 1. Specification sheet
df <- read_excel("data.xlsx", sheet = "Sales Details")

# 2. Specified Range(Avoid reading comments outside the table header)
df <- read_excel("data.xlsx", range = "A1:D1000")

# 3. Skip the header row
df <- read_excel("data.xlsx", skip = 2)

# 4. Read-only 1000 row
df <- read_excel("data.xlsx", n_max = 1000)

# 5. Specify Column Type
df <- read_excel("data.xlsx", col_types = c("text", "numeric", "date"))

# 6. Custom NA
df <- read_excel("data.xlsx", na = c("", "NA", "N/A"))

# Method 1:sheet name(Recommendations)
df <- read_excel("data.xlsx", sheet = "Sales Details")

# Method 2:sheet number(Starting from 1)
df <- read_excel("data.xlsx", sheet = 1)

# Method 3:NULL(By default, read the first one)
df <- read_excel("data.xlsx")

# 1. String Range
df <- read_excel("data.xlsx", range = "A1:D100")

# 2. Anchor Cell(Automatically expand to non-empty areas)
df <- read_excel("data.xlsx", range = "A1:D1")  # Read-only 1 row(Automatic Scaling)

# 3. Complete A1 Quote
df <- read_excel("data.xlsx", range = "A1:Z1000")

# 4. Naming Area(Excel as defined in Named Range)
df <- read_excel("data.xlsx", range = "SalesTable")

# Method 1:String Shorthand
col_types = c("text", "numeric", "date", "guess", "skip")

# Method 2:List
col_types = list(
  text,        # Character
  numeric,     # Numbers
  date,        # Date
  guess,       # Automatic Inference
  skip         # Skip
)

# Skip All(Do not read the data)
col_types = c("skip", "skip", "skip")

# All characters
col_types = c("text")

library(writexl)

# 1. Single sheet Output
write_xlsx(df, "output.xlsx")

# 2. Multiple sheet Output(Use list)
write_xlsx(
  list(
    "Sales Details" = sales_df,
    "Payment" = payment_df,
    "Inventory" = stock_df
  ),
  "output.xlsx"
)

# 3. Append to the existing file
# writexl Does not directly support additional entries,Required openxlsx

# Export R data frames in batch
list_of_dfs <- list(
  Sales = sales_df,
  Payment = payment_df,
  Inventory = stock_df
)
write_xlsx(list_of_dfs, "monthly_report.xlsx")
cat("=== The report has been generated:monthly_report.xlsx ===\n")

library(openxlsx)

# Write formatted text Excel
wb <- createWorkbook()
addWorksheet(wb, "Sales")
writeData(wb, "Sales", sales_df)
addStyle(wb, "Sales", style = createStyle(fontColour = "red"),
         rows = 2:10, cols = 5)
saveWorkbook(wb, "formatted_report.xlsx")

# ============================================
# 30 Summary of Monthly Financial Reports from Each Branch
# Features:Batch Read 30 ea Excel × 3 ea sheet
# ============================================

library(readxl)
library(dplyr)
library(purrr)
library(writexl)

# 1. Preparing Sample Data
set.seed(42)
make_sales <- function(city) {
  tibble(
    order_id = 1:5,
    date = as.Date("2024-01-01") + 0:4,
    product = sample(c("A", "B", "C"), 5, replace = TRUE),
    amount = sample(1000:5000, 5)
  )
}
make_payment <- function(city) {
  tibble(
    order_id = 1:5,
    paid = sample(c(TRUE, FALSE), 5, replace = TRUE),
    paid_date = as.Date("2024-01-15") + 0:4
  )
}
make_stock <- function(city) {
  tibble(
    product = c("A", "B", "C"),
    stock = sample(50:200, 3)
  )
}

# Simulation 3 Each branch's Excel(Each 3 sheet)
dir.create("temp_reports", showWarnings = FALSE)
for (city in c("Beijing", "Shanghai", "Guangzhou")) {
  write_xlsx(
    list(
      "Sales Details" = make_sales(city),
      "Payment" = make_payment(city),
      "Inventory" = make_stock(city)
    ),
    file.path("temp_reports", paste0(city, ".xlsx"))
  )
}

# 2. List all Excel Documents
files <- list.files("temp_reports", pattern = "\\.xlsx$", full.names = TRUE)
cat("Found", length(files), "ea Excel Documents:\n")
print(files)

# 3. View each file's sheet name
for (f in files) {
  cat("\nDocuments:", basename(f), "\n")
  cat("  Sheet:", paste(excel_sheets(f), collapse = ", "), "\n")
}

# 4. Read all in bulk sheet
cat("\n=== Bulk reading in progress ===\n")
all_reports <- lapply(files, function(f) {
  city <- tools::file_path_sans_ext(basename(f))
  list(
    sales = read_excel(f, sheet = "Sales Details") |> mutate(city = !!city),
    payment = read_excel(f, sheet = "Payment") |> mutate(city = !!city),
    stock = read_excel(f, sheet = "Inventory") |> mutate(city = !!city)
  )
})
names(all_reports) <- tools::file_path_sans_ext(basename(files))

# 5. Consolidate all sales data
all_sales <- bind_rows(lapply(all_reports, function(d) d$sales))
cat("\n=== Sales Summary ===\n")
print(all_sales)

# 6. Consolidate all payment receipt data
all_payment <- bind_rows(lapply(all_reports, function(d) d$payment))
cat("\n=== Summary of Receivables ===\n")
print(all_payment)

# 7. Merge all inventory data
all_stock <- bind_rows(lapply(all_reports, function(d) d$stock))
cat("\n=== Inventory Summary ===\n")
print(all_stock)

# 8. Comprehensive Analysis
cat("\n=== Sales Analysis by City ===\n")
city_stats <- all_sales |>
  group_by(city) |>
  summarise(
    Number of Orders = n(),
    Total Sales = sum(amount),
    Average Order = round(mean(amount), 2)
  ) |>
  arrange(desc(Total Sales))
print(city_stats)

# 9. Generate a Summary Report
write_xlsx(
  list(
    "Sales Summary" = all_sales,
    "Summary of Receivables" = all_payment,
    "Inventory Summary" = all_stock,
    "City Statistics" = city_stats
  ),
  "summary_report.xlsx"
)
cat("\n=== The summary report has been generated.:summary_report.xlsx ===\n")

# 10. Clear Temporary Files
unlink("temp_reports", recursive = TRUE)

=== Sales Analysis by City ===
# A tibble: 3 × 4
  city  Number of Orders Total Sales Average Order
  <chr>  <int>   <int>     <dbl>
1 Shanghai      5   14325    2865  
2 Beijing      5   12289    2458. 
3 Guangzhou      5   11234    2247. 

library(openxlsx)
wb <- loadWorkbook("existing.xlsx")
addWorksheet(wb, "newsheet")
writeData(wb, "newsheet", new_df)
saveWorkbook(wb, "existing.xlsx")