Skip to content
This repository was archived by the owner on Mar 9, 2020. It is now read-only.
This repository was archived by the owner on Mar 9, 2020. It is now read-only.

Bug: Can't add FormulaR1C1 referencing sheets with spaces #204

@canettas

Description

@canettas

If the string on FormulaR1C1 contains single quotation marks, to reference sheets with spaces, we get unexpected results.

A nice test is:

using (var pack = new ExcelPackage())
{
//create sheets
var sheet1 = pack.Workbook.Worksheets.Add("Sheet 1");
var sheet2 = pack.Workbook.Worksheets.Add("Sheet 2");
//set some default values
sheet1.Cells[1, 1].Value = 1;
sheet2.Cells[1, 1].Value = 2;
//fill the formula
var formula = string.Format("'{0}'!R1C1", sheet1.Name);
var cell = sheet2.Cells[2, 1];
cell.FormulaR1C1 = formula;

//Formula should remain the same
Assert.AreEqual(formula.ToUpper(), cell.FormulaR1C1.ToUpper());
}

The error was inserted on Commit f481e9d on 2017-12-24; Merged on Commit 97b51ab on 2018-02-04.

The problem is that after the alteration the TokenFactory is responsible for tokenizing the formula in R1C1 format (previously there was a "inline" tokenizer).
But the factory does not recognize R1C1 as a valid InternalAddress so everything else falls off track - lines 154-155 on the TokenFactory.cs.

A duck-tape way to make it work is:
In the ExcelAddress.cs function IsValid (starting at line 882) change line 910 condition - checking if a string is an R1C1 addresses as well. Something like:

if (
IsAddress(intAddress)
|| IsAddress(ToAbs(intAddress, 1, 1, -1, -1)) //Duck-Tape - Check if is in R1C1 format
)

For this to work we have to change the function ToAbs to protected (but I think this is the least of the problems)

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions