Option to use R1C1 references in writeFormula()

[ethan-crowell]

Is your feature request related to a problem? Please describe.
When I use R1C1 cell references in writeFormula(), I am unable to open the resulting excel file without error. I have already verified that R1C1 references are enabled in my excel preferences and that I can use R1C1 referencing directly in excel.

Here is a minimum working example:

wb <- openxlsx::createWorkbook()
ws <- 'test_sheet'
openxlsx::addWorksheet(wb, sheetName = ws)
writeData(wb = wb, sheet = ws, x = 2, startCol = 1, startRow = 1)
writeData(wb = wb, sheet = ws, x = 4, startCol = 2, startRow = 1)
writeFormula(wb = wb, sheet = ws, x='=RC[-2] * RC[-1]', startCol = 3, startRow = 1)
saveWorkbook(wb, 'test.xlsx')

Describe the solution you'd like
The option to pass equations using R1C1 cell references in writeFormula.

Describe alternatives you've considered
The obvious alternative is to use the standard A1 referencing. However, R1C1 is much more convenient for some applications.

Additional context
Screenshots of excel error when I use R1C1:

[ethan-crowell]

An alternative that would make this request unnecessary would be the ability to reference an entire column starting from a specific row in writeFormula. I am currently unable to do this. For example, when I try the following

wb <- openxlsx::createWorkbook()
ws <- 'test_sheet'
openxlsx::addWorksheet(wb, sheetName = ws)
writeData(wb = wb, sheet = ws, x = c(1:10), startCol = 1, startRow = 3)
writeFormula(wb = wb, sheet = ws, x='=INDIRECT("A3:A"&ROWS(A:A))', startCol = 2, startRow = 3)
saveWorkbook(wb, 'Test5.xlsx')

an @ gets included. The result in column 2 is simply =@INDIRECT("A3:A"&ROWS(A:A)) in cell B3, which prevents the formula being applied to the whole column.

[github-actions]

This issue is stale because it has been open 365 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[github-actions]

This issue was closed because it has been stalled for 7 days with no activity.