如何在R中实现Excel的Refresh All（全部刷新）等效功能？

Great question—automating this workflow in R is totally doable, and I’ve helped folks set up similar hands-off report pipelines before. Let’s break this down step by step to get you up and running:

Instead of importing the static Excel file (which only has old data), you’ll want to pull the latest data directly from your SQL database—this replicates the "refresh" action in Excel. Here’s how:

• Use the DBI package along with a database-specific driver (like odbc for most SQL databases, RPostgres for PostgreSQL, or RMariaDB for MySQL).
• Store your database credentials securely (never hardcode passwords!): use the keyring package to save and retrieve passwords without exposing them in your script.

Example code snippet:

library(DBI)
library(odbc)
library(keyring)

# Connect to SQL database
conn <- dbConnect(
  odbc(),
  Driver = "SQL Server",
  Server = "your_server_name",
  Database = "your_db_name",
  UID = "your_username",
  PWD = keyring::key_get("sql_db_password") # Retrieve saved password
)

# Run the same SQL query that your Excel file uses
raw_data <- dbGetQuery(conn, "SELECT * FROM your_table WHERE date >= '2024-01-01'")

# Close the connection when done
dbDisconnect(conn)

Next, you’ll need to mirror any calculations, filters, or pivots that happen in Excel once the data refreshes. The dplyr and tidyr packages make this straightforward:

• Use dplyr::mutate() to create calculated columns (replacing Excel formulas).
• Use dplyr::filter() and dplyr::arrange() to sort/filter data.
• Use dplyr::group_by() + dplyr::summarize() or tidyr::pivot_wider() to replicate pivot tables.

Example for a simple calculation and pivot:

library(dplyr)
library(tidyr)

transformed_data <- raw_data %>%
  mutate(total_sales = quantity * unit_price) %>% # Equivalent to Excel's =A2*B2
  group_by(region, month) %>%
  summarize(total_region_sales = sum(total_sales)) %>%
  pivot_wider(names_from = month, values_from = total_region_sales) # Pivot table-like output

To get a polished Excel output (with formatting if needed), use the openxlsx package—it’s more flexible than basic exporters like writexl:

• You can set column widths, apply header styles, and add multiple worksheets if needed.

Example export code:

library(openxlsx)

# Create a new workbook
wb <- createWorkbook()

# Add a worksheet and write your data
addWorksheet(wb, "Final Report")
writeData(wb, "Final Report", transformed_data)

# Optional: Format headers for readability
header_style <- createStyle(fontSize = 12, fontColour = "white", fgFill = "#4F81BD", textDecoration = "bold")
addStyle(wb, "Final Report", header_style, rows = 1, cols = 1:ncol(transformed_data))

# Save the workbook
saveWorkbook(wb, "automated_report.xlsx", overwrite = TRUE)

To make this run automatically without intervention:

• Save all your code into a single R script (e.g., report_auto_script.R).
• Windows: Use Task Scheduler to create a basic task. Set the trigger (daily/weekly at your desired time), and set the action to run Rscript.exe with your script path as an argument (e.g., "C:\Program Files\R\R-4.3.2\bin\Rscript.exe" "C:\your_script_path\report_auto_script.R").
• macOS/Linux: Use a cron job. Open the crontab editor with crontab -e, then add a line like 0 8 * * 1-5 /usr/bin/Rscript /your_script_path/report_auto_script.R to run at 8 AM every weekday.
• Pro Tip: Add error handling and logging to your script with tryCatch() so you get notified if something fails. For example:

  tryCatch({
    # Your entire workflow code here
    cat("Report generated successfully at", Sys.time(), "\n", file = "report_log.txt", append = TRUE)
  }, error = function(e) {
    cat("Error at", Sys.time(), ": ", e$message, "\n", file = "report_log.txt", append = TRUE)
    stop(e)
  })
  

A few final notes to avoid headaches:

• Test your script manually first to ensure it produces exactly the same output as your refreshed Excel file.
• Make sure your R environment has all required packages installed (you can add install.packages(c("DBI", "odbc", ...)) at the top, but wrap it in if (!require(...)) to avoid reinstalling every time).
• Keep your database connection details up to date if your server credentials change.

内容的提问来源于stack exchange，提问作者AofWessex