R: R Excel File I/O

Q: How do I read a specific sheet?

read_excel("file.xlsx", sheet = "Sales Details") or sheet = 1 (by number). excel_sheets(file) lists all sheet names.

Q: How do I read only a specific range?

Use range = "A1:D100" to specify the cell range. Or use range = "A1:D1" to anchor to the header and automatically expand.

Q: How do I choose between readxl and openxlsx?

For read-only and write operations, use readxl + writexl (simple, fast, and Java-independent); for cell formatting, formulas, and charts, use openxlsx (powerful but with a complex API).

Q: How do I append data to an existing Excel file using writexl?

writexl does not support appending. To append data, use openxlsx :

Last updated: 2026-08-26

In the previous lesson, we learned about CSV, but 60% of the data in real-world projects is stored in Excel—managers, salespeople, and finance professionals all like to share Excel files. In this lesson, we’ll learn the standard approach for reading and writing Excel files in R: readxl + writexl, which doesn’t rely on Java (unlike the xlsx package, which requires the JDK).

After completing this lesson, you'll be able to read financial statements across multiple sheets and create formatted Excel reports.

1. What You'll Learn

The 3 file formats for Excel files (.xlsx / .xls / .xlsm)
Installing the readxl package and its 4 core functions
Reading multiple sheets and batch processing
8 Common Parameters of read_excel (sheet, range, col_types, na)
writexl write Excel
Differences from openxlsx
Hands-On: Batch Processing of Monthly Financial Reports

2. The Story of a Monthly Financial Report

(1) Pain Point: 30 Excel tasks are driving me crazy

Alice works in finance and has to compile the "monthly sales reports" from 30 branch offices at the beginning of each month:

Each Excel file contains 3 sheets (Sales Details, Receipts, Inventory)
Each Excel file has the same format
A total of 90 tables need to be imported into R for analysis

If you use Excel’s built-in Power Query, cross-file processing is complicated; using Python openpyxl is slow; and using R xlsx requires installing Java—

(2) Solution using R

# 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))

Just 5 lines of code to handle 30 Excel files × 3 sheets = 90 worksheets. That’s the power of readxl.

graph TB
    A[30 branch offices Excel Documents] --> B[list.files List]
    B --> C[lapply Batch Read]
    C --> D[excel_sheets check sheet]
    D --> E[read_excel Each sheet]
    E --> F[bind_rows Merge]
    F --> G[group_by + summarise Summarize]
    G --> H[write_xlsx Generate Report]

    style A fill:#cce5ff
    style B fill:#d4edda
    style C fill:#fff3cd
    style D fill:#f8d7da
    style E fill:#e1d4ff
    style F fill:#ffe1d4
    style G fill:#cce5ff
    style H fill:#d4edda

3. Three Formats of Excel Files

mindmap
    root((Excel Documents<br/>3 Type of Format))
        .xlsx
            Excel 2007+
            Maximum 100,000 rows of
            Modern Standards
            Tools: readxl / openxlsx
        .xls
            Excel 97-2003
            Maximum 65536 row
            Old Format
            Tools: readxl
        .xlsm
            Enable Macros
            Contains VBA
            Tools: readxl Do not read macros
        Select
            New Project: .xlsx
            Old data: .xls
            Han Hong: .xlsm

(1) Format Comparison

Format	File Extension	Maximum Number of Rows and Columns	Compatibility	R Readability
Excel 2007+	`.xlsx`	1048576 × 16384	Modern Standard	readxl / openxlsx
Excel 97-2003	`.xls`	65536 × 256	Legacy format	readxl
Enable Macros in Excel	`.xlsm`	Same as xlsx	Contains VBA	readxl (does not load macros)

(2) Comparison of 3 R Packages for Reading Excel Files

Package	Dependencies	Speed	Features	Recommendation
readxl	Pure R (C++)	Fast	Read-only	⭐⭐⭐ Top choice for reading
writexl	Pure R (C++)	Fast	Write-only	⭐⭐⭐ Top choice for writing
openxlsx	Pure R	Chinese	Read/Write + Formatting	⭐⭐ Use when formatting is required
xlsx	Requires Java	Slow	Read/write + formulas	❌ Not recommended (high dependency)

💡 Tip: For read-only data that doesn’t require formatting, use readxl + writexl (the simplest method); For data requiring cell formatting or formulas, use openxlsx; Avoid using the xlsx package (requires Java).

4. Four Core Functions of readxl

(1) Function Quick Reference Table

Function	Purpose
`read_excel()`	Read .xlsx or .xls (automatically detected)
`read_xlsx()`	Read-only .xlsx (faster)
`read_xls()`	Read-only .xls (legacy format)
`excel_sheets()`	List all sheet names

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")

(2) 8 Common Parameters of `read_excel`

Parameter	Function	Example
`path`	File Path	`"data/sales.xlsx"`
`sheet`	sheet name or number	`1` or `"Sales Details"`
`range`	Reading Range	`"A1:D100"` or `"A1:D100"`
`col_names`	TRUE / Custom Vector	`TRUE`
`col_types`	List type	`"text"`, `"numeric"`, `"date"`
`na`	NA marker	`c("", "NA")`
`skip`	Skip lines	`2` (Skip header row)
`n_max`	Maximum number of rows to read	`1000`

(3) Hands-On: Common Parameters

# 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"))

5. Detailed Explanation of the read_excel Parameter

(1) The `sheet` parameter

# 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")

(2) The `range` parameter (most useful)

Excel often contains headers, comments, and blank rows. Specify a range for precise reading:

# 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")

(3) The `col_types` parameter

# 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")

Available Types: "guess" (default inference), "logical", "numeric", "date", "text", "skip", "list" (nested tables)

6. Write Excel:writexl

(1) Advantages of writexl

Pure R implementation (does not depend on Java or LibreOffice)
Fast (C++ backend)
Simple API (only write_xlsx())

(2) Basic Syntax

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

(3) Practical Application

# 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")

⚠️ Note: writexl does not support cell formatting (color, font, formulas). To apply formatting, use openxlsx:

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")

7. Hands-On: Batch Reading of Multiple Sheets in Financial Statements

Below is an example of a complete workflow that demonstrates how to batch-process multiple files with multiple sheets.

▶ Example: Summary of Monthly Financial Reports from 30 Branch Offices

R 📖 Display only

# ============================================
# 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)

84 logic lines (exceeds 40-line limit, display only)

Expected Output (Excerpt):

TEXT 📖 Display only

=== 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.

❓ FAQ

Q What should I do if readxl displays garbled characters?

A readxl automatically handles UTF-8 and GBK encodings. If you really see garbled characters, use iconv to convert the file’s encoding before reading it.

Q How do I read a specific sheet?

A read_excel("file.xlsx", sheet = "Sales Details") or sheet = 1 (by number). excel_sheets(file) lists all sheet names.

Q How do I read only a specific range?

A Use range = "A1:D100" to specify the cell range. Or use range = "A1:D1" to anchor to the header and automatically expand.

Q How do I choose between readxl and openxlsx?

A For read-only and write operations, use readxl + writexl (simple, fast, and Java-independent); for cell formatting, formulas, and charts, use openxlsx (powerful but with a complex API).

Q How do I read an Excel file with merged cells?

A Merged cells only contain values in the top-left corner; the other positions are empty. readxl treats them as "unmerged," so the "non-top-left" positions in merged areas are NA. To handle this, use the openxlsx and mergeCells parameters.

Q How do I append data to an existing Excel file using writexl?

A writexl does not support appending. To append data, use openxlsx:

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

📖 Summary

readxl is the top choice—R-only dependencies, fast, and automatically handles multiple sheets
3 Excel formats: .xlsx (modern), .xls (legacy), .xlsm (with macros)
4 core functions: read_excel / read_xlsx / read_xls / excel_sheets
8 common parameters: sheet range col_types col_names na skip n_max path
writexl: The Top Choice for Writing to Excel—Pure R, fast, does not support formatting
Use openxlsx for formatting/formulas—powerful but with a complex API; avoid the xlsx package (requires Java)
Batch processing of multiple files with multiple sheets: Loop through lapply + bind_rows to merge

📝 Exercises

Basic Exercise: Create an Excel file containing three sheets (Sales, Receivables, and Inventory) and export it using write_xlsx(). Use excel_sheets() to verify that the sheet names are correct, and use read_excel() to read the "Sales" sheet and verify that the data is correct.
Basic Exercise: Open the Excel file from the previous question and use range = "A1:B5" to read only columns A and B from rows 1–5. Verify that only 2 columns and 5 rows were read.
Basic Exercise: Read the "Sales" sheet from the previous question, use col_types = c("numeric", "date", "text", "numeric") to explicitly specify the column type, and verify that the type is correct.
Advanced Exercise: Simulate three branch offices in Excel (three sheets each). Use lapply to batch-read all sheets, and use bind_rows to merge data of the same type. Calculate the total sales for each city.
Challenge: Use openxlsx to create a formatted Excel file: ① Bold the header row in red; ② Right-align the number column; ③ Format the number column with thousands separators; ④ Set column widths to adjust automatically. Save it as formatted_report.xlsx, open it in Excel to verify the results (take a screenshot and save it).

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

R: R Excel File I/O

1. What You'll Learn

2. The Story of a Monthly Financial Report

(1) Pain Point: 30 Excel tasks are driving me crazy

(2) Solution using R

3. Three Formats of Excel Files

(1) Format Comparison

(2) Comparison of 3 R Packages for Reading Excel Files

4. Four Core Functions of readxl

(1) Function Quick Reference Table

(2) 8 Common Parameters of read_excel

(3) Hands-On: Common Parameters

5. Detailed Explanation of the read_excel Parameter

(1) The sheet parameter

(2) The range parameter (most useful)

(3) The col_types parameter

6. Write Excel:writexl

(1) Advantages of writexl

(2) Basic Syntax

(3) Practical Application

7. Hands-On: Batch Reading of Multiple Sheets in Financial Statements

▶ Example: Summary of Monthly Financial Reports from 30 Branch Offices

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好

(2) 8 Common Parameters of `read_excel`

(1) The `sheet` parameter

(2) The `range` parameter (most useful)

(3) The `col_types` parameter