使用purrr::map与openxlsx报错：工作表重复/对象未找到

问题描述 #

编写了嵌套循环的R函数，需求为每次调用test_fun时创建新工作簿，调用test_fun_inner向工作簿添加工作表并写入数据，最后保存工作簿。但运行时出现两个问题：

• 首次运行触发<error/purrr_error_indexed>，addWorksheet()提示工作表'test1'已存在（名称不区分大小写）；但手动逐行运行代码无此错误。
• 手动删除环境中的wb_output对象后再次运行，提示找不到wb_output对象。

原代码 #

library(tidyverse)
library(openxlsx)

test_fun_inner <- function(xyz, data, column)
{
  addWorksheet(wb_output, sheetName = xyz)
  writeData(wb = wb_output,
            sheet = xyz,
            x = data[[column]])
}

test_fun <- function(abc, my_names)
{
  # Create Workbook
  wb_output <- createWorkbook()
  
  # loop through all sheets
  map(.x = 1:2,
      .f = ~test_fun_inner(xyz = my_names[[.x]], mtcars, .x))
  
  # save Excel file
  saveWorkbook(wb        = wb_output,
               file      = paste0(abc, ".xlsx"),
               overwrite = TRUE)
}

my_names <- list("test1", "test2")

map(.x = c("x1", "x2", "x3"),
    .f = ~test_fun(abc = .x,
                   my_names = my_names))

报错回溯 #

首次运行报错 #

<error/purrr_error_indexed>
Error in `map()`:
ℹ In index: 1.
Caused by error in `map()`:
ℹ In index: 1.
Caused by error in `addWorksheet()`:
! A worksheet by the name 'test1' already exists! Sheet names must be unique case-insensitive.
---
Backtrace:
     ▆
  1. └─purrr::map(.x = c("x1", "x2", "x3"), .f = ~test_fun(abc = .x, my_names = my_names))
  2.   └─purrr:::map_("list", .x, .f, ..., .progress = .progress)
  3.     ├─purrr:::with_indexed_errors(...)
  4.     │ └─base::withCallingHandlers(...)
  5.     ├─purrr:::call_with_cleanup(...)
  6.     └─global .f(.x[[i]], ...)
  7.       └─global test_fun(abc = .x, my_names = my_names)
  8.         └─purrr::map(...)
  9.           └─purrr:::map_("list", .x, .f, ..., .progress = .progress)
 10.             ├─purrr:::with_indexed_errors(...)
 11.             │ └─base::withCallingHandlers(...)
 12.             ├─purrr:::call_with_cleanup(...)
 13.             └─.f(.x[[i]], ...)
 14.               └─global test_fun_inner(xyz = my_names[[.x]], mtcars, .x)
 15.                 └─openxlsx::addWorksheet(wb_output, sheetName = xyz)
 16.                   └─base::stop(paste0("A worksheet by the name '", sheetName, "' already exists! Sheet names must be unique case-insensitive."))

删除wb_output后报错 #

Error in `map()`:
ℹ In index: 1.
Caused by error in `map()`:
ℹ In index: 1.
Caused by error in `addWorksheet()`:
! object 'wb_output' not found
Run `rlang::last_trace()` to see where the error occurred.

错误原因 #

两个报错本质都是变量作用域问题：

1. test_fun_inner直接调用wb_output，但这个对象是在test_fun的局部环境中创建的。首次运行时，全局环境中可能残留了之前测试生成的wb_output，导致test_fun_inner误操作全局工作簿，重复添加同名工作表。
2. 删除全局的wb_output后，test_fun_inner无法访问test_fun局部环境中的wb_output，因此报对象不存在。

修复方案 #

方案1：显式传递工作簿参数（推荐） #

修改test_fun_inner，让它接收工作簿对象作为参数，明确依赖关系，避免作用域混淆。

修复后代码：

library(tidyverse)
library(openxlsx)

test_fun_inner <- function(wb, xyz, data, column)
{
  addWorksheet(wb, sheetName = xyz)
  writeData(wb = wb,
            sheet = xyz,
            x = data[[column]])
}

test_fun <- function(abc, my_names)
{
  wb_output <- createWorkbook()
  
  # 调用时传入当前工作簿对象
  map(.x = 1:2,
      .f = ~test_fun_inner(wb = wb_output, xyz = my_names[[.x]], mtcars, .x))
  
  saveWorkbook(wb        = wb_output,
               file      = paste0(abc, ".xlsx"),
               overwrite = TRUE)
}

my_names <- list("test1", "test2")

map(.x = c("x1", "x2", "x3"),
    .f = ~test_fun(abc = .x,
                   my_names = my_names))

方案2：在test_fun内部定义test_fun_inner #

利用R的词法作用域特性，内部函数可以直接访问外部函数的局部变量，无需额外传递参数。

修复后代码：

library(tidyverse)
library(openxlsx)

test_fun <- function(abc, my_names)
{
  # 在test_fun内部定义内层函数，直接访问wb_output
  test_fun_inner <- function(xyz, data, column)
  {
    addWorksheet(wb_output, sheetName = xyz)
    writeData(wb = wb_output,
              sheet = xyz,
              x = data[[column]])
  }
  
  wb_output <- createWorkbook()
  
  map(.x = 1:2,
      .f = ~test_fun_inner(xyz = my_names[[.x]], mtcars, .x))
  
  saveWorkbook(wb        = wb_output,
               file      = paste0(abc, ".xlsx"),
               overwrite = TRUE)
}

my_names <- list("test1", "test2")

map(.x = c("x1", "x2", "x3"),
    .f = ~test_fun(abc = .x,
                   my_names = my_names))

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