如何使用R拆分大型Excel文件并生成带图片的分区域学校独立文件

Hey Jonas, let's walk through exactly how to tackle this task in R—we'll use a combination of packages for Excel handling, data manipulation, and file system operations to make this smooth even for 200 schools. Here's a step-by-step solution:

First, we'll need a few packages that handle reading/writing Excel files, manipulating data, and managing folders. Run this to install and load them:

# Install packages (only need to run this once)
install.packages(c("readxl", "openxlsx", "dplyr", "fs"))

# Load the packages into your R session
library(readxl)
library(openxlsx)
library(dplyr)
library(fs)

We'll start by reading your large Excel file. I'm assuming your file is named 学生信息.xlsx and saved on your desktop—adjust the path if it's somewhere else:

# Get the path to your desktop (works on Windows, Mac, Linux)
desktop_path <- path_home("Desktop")

# Read the raw data from Excel
raw_student_data <- read_excel(path(desktop_path, "学生信息.xlsx"))

Next, we'll make folders on your desktop for each region to organize the output files:

# Get all unique region names from your data
unique_regions <- unique(raw_student_data$区域)

# Create a folder for each region on the desktop
purrr::walk(unique_regions, ~ dir_create(path(desktop_path, .x)))

This is the core part: we'll group the data by school, add the header image to each file, and save it to the correct region folder. First, make sure your header image is saved on the desktop as 表头标识.png (or update the path below if it's named differently):

# Path to your header image (adjust if needed)
header_img_path <- path(desktop_path, "表头标识.png")

# Process each school's data
raw_student_data %>%
  group_by(学校) %>%
  group_walk(function(school_data, group_meta) {
    # Grab the current school name and its region
    school_name <- group_meta$学校
    school_region <- first(school_data$区域)
    
    # Create a new Excel workbook
    wb <- createWorkbook()
    addWorksheet(wb, sheetName = "学生数据")
    
    # Write the school's data starting at row 2 (leave row 1 for the image)
    writeData(wb, sheet = "学生数据", x = school_data, startRow = 2)
    
    # Insert the header image into cells A1 to D1
    insertImage(wb, sheet = "学生数据",
                file = header_img_path,
                startRow = 1, startCol = 1,
                endRow = 1, endCol = 4,
                width = 10, height = 1.5) # Tweak width/height to fit your image
    
    # Auto-adjust column widths so content doesn't get cut off
    setColWidths(wb, sheet = "学生数据", cols = 1:5, widths = "auto")
    
    # Save the workbook to the correct region folder
    output_file_path <- path(desktop_path, school_region, paste0(school_name, ".xlsx"))
    saveWorkbook(wb, file = output_file_path, overwrite = TRUE)
  })

• Image Path: Double-check that header_img_path points to your actual image file—if it's in a different folder, update the path accordingly.
• Testing: If you're nervous about running this on 200 schools right away, test with a small subset of your data first (e.g., filter to 1-2 regions) to make sure everything works.
• Special Characters: If any school names have special characters (like slashes or colons), R will handle them automatically, but some operating systems might have issues—you can clean school names with stringr::str_replace_all() if needed.
• Excel Compatibility: openxlsx writes .xlsx files, which work with all modern Excel versions.

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