Understanding the 'Error: stoi' in openxlsx2

When working with large spreadsheets in R, the openxlsx2 package is one of the fastest and most feature-rich tools available. However, handling web URLs—especially dynamically generated query parameters such as NCBI or UniProt queries—can occasionally produce baffling errors like Error: stoi.

If you have encountered this error while using wb_add_hyperlink() or wb$add_hyperlink(), the issue is not necessarily the character length. Instead, it usually stems from special characters within the URL string or how C++ parses internal cell dimensions and relationship IDs.

Why Does This Error Happen?

The error message stoi stands for "string to integer" in C++ (specifically std::stoi). In openxlsx2, internal operations map cell dimensions, worksheet relationships, and targets using underlying C++ routines.

When an unencoded or improperly escaped character (such as parentheses ( ), plus signs +, colons :, or ampersands &) enters XML attributes or relationship targets, the underlying parser can misinterpret cell identifiers or fail during reference indexing, triggering an unhandled std::stoi failure.

The Solution: URL Encoding

The safest, standard-compliant approach to passing complex queries to Excel via openxlsx2 is to encode special characters using utils::URLencode() or curl::curl_escape(). This guarantees that parameters containing symbols like brackets and colons are properly escaped before writing to the OpenXML package.

Working Example

library(openxlsx2)

# Sample data
pgs_small <- data.frame(
  id = 245L,
  plot_label = "test",
  Protein.IDs = "A0A4P2L339;A0A8M6Z9J0"
)

# Raw URL containing parentheses and spaces/colons
raw_link <- 'https://www.uniprot.org/uniprot/?query=(accession:A0A4P2L339)+OR+(accession:A0A8M6Z9J0)'

# Encode the link to make it XML/OpenXML safe
clean_link <- utils::URLencode(raw_link)

# Create workbook and assign hyperlink
wb <- wb_workbook() %>%
  wb_add_worksheet("sheet1") %>%
  wb_add_data_table(x = pgs_small, table_style = "TableStyleLight1") %>%
  wb_add_hyperlink(
    dims = wb_dims(x = pgs_small, from_row = 2, cols = 2), # Row 2 targets the data, row 1 targets the header
    target = clean_link
  )

wb_save(wb, "test_fixed.xlsx")

Pay Attention to Target Cell Dimensions

Another subtle issue that triggers indexing issues in openxlsx2 is targeting the wrong row range. In the original code:

wb_dims(x = pgs_small, from_row = 1, cols = 2)

Because tables include headers by default, targeting row 1 places the hyperlink on the column title rather than the data observation. Ensure that your from_row coordinate accurately points to the exact cell where the link should reside (e.g., from_row = 2).

Alternative Method: Using Excel HYPERLINK Formulas

If your URL is strictly under 255 characters, you can write native formulas using wb_add_formula():

formula_str <- sprintf('HYPERLINK("%s", "%s")', clean_link, "View UniProt")

wb <- wb_workbook() %>%
  wb_add_worksheet("sheet1") %>%
  wb_add_formula(x = formula_str, dims = "B2")

However, for bioinformatics databases, deep queries, or API links that exceed 255 characters, wb_add_hyperlink() with URLencode() remains the recommended and robust solution.