Provides tools for splitting large Excel worksheets into multiple smaller sheets based on a specified number of rows per chunk. The package reads Excel files, partitions the data efficiently using the 'data.table' package, and writes the resulting subsets into a single workbook with multiple sheets using the 'openxlsx' package. This is useful for handling large datasets, preparing data for reporting, and exporting manageable Excel files for downstream analysis.
A detailed description of the split_excel_to_sheets() function:
split_excel_to_sheets()Purpose
Reads a source Excel file, partitions its data into n equal-sized chunks, and writes each chunk as a separate styled sheet within a single returned workbook object.
| Parameter | Type | Default | Description |
|---|---|---|---|
file_path |
character | required | Path to the source .xlsx file to read |
n |
integer | required | Number of splits — determines how many sheets are created |
sheet |
integer or character | 1 |
Source sheet to read from — by index or name |
output_path |
character or NULL | NULL |
If provided, saves the workbook to disk at this path; if NULL, returns in-memory only |
sheet_prefix |
character | "Part" |
Prefix for generated sheet names — e.g. "Part" → Part_1, Part_2, … |
header_style |
logical | TRUE |
Applies a dark blue styled header row to each sheet when TRUE |
col_widths |
character or numeric | "auto" |
"auto" for openxlsx auto-sizing, a numeric vector for fixed widths, or NULL to skip entirely |
Step 1 — Validate
Checks that the file exists, and that n is a positive whole number. If n exceeds the number of rows, it downgrades n with a warning rather than erroring out.
Step 2 — Read
Reads the source sheet using openxlsx::read.xlsx() with detectDates = FALSE for speed, then immediately converts the result to a data.table in-place via setDT().
Step 3 — Chunk
Uses cut() to assign each row to a chunk group in a single vectorised operation, then data.table::split() to partition the table into a named list — no loops, no repeated subsetting.
Step 4 — Write sheets
Iterates over each chunk, creates a named sheet, writes the data with writeData(), applies a pre-built header style and body style, sets column widths, and freezes the top row. The header createStyle() object is built once and reused across all sheets.
Step 5 — Save (optional)
If output_path is provided, calls saveWorkbook(). Otherwise, the step is skipped and the workbook lives in memory only.
Returns the openxlsx workbook object invisibly, meaning it can be captured and further manipulated — additional sheets, summaries, or charts — before a final saveWorkbook() call.
Each of the 4 phases is timed and printed to the console via message(), giving the caller visibility into where time is being spent across large files.