public static string ConvertA1StringToR1C1String(string cellFormula)
{
//Remap A1 style references to R1C1 (but ignore anything followed by a " in case its inside an EVALUATE statement)
string a1pattern = @"([A-Z]{1,2}\d{1,5})";
Regex rg = new Regex(a1pattern);
MatchCollection matches = rg.Matches(cellFormula);
int stringLenChange = 0;
//Iterate through each match and then replace it at its offset. We iterate through
//each case manually to prevent overlapping cases from double replacing - ex: SELECT(B1:B111,B1)
foreach (var match in matches)
{
string matchString = ((Match)match).Value;
string replaceContent = ExcelHelperClass.ConvertA1ToR1C1(matchString);
//As we change the string, these indexes will go out of sync, track the size delta to make sure we resync positions
int matchIndex = ((Match)match).Index + stringLenChange;
//If the match is followed by a ", then ignore it
int followingIndex = matchIndex + matchString.Length;
if (followingIndex < cellFormula.Length && cellFormula[followingIndex] == '"')
{
continue;
}
//LINQ replacement for python string slicing
cellFormula = new string(cellFormula.Take(matchIndex).
Concat(replaceContent.ToArray()).
Concat(cellFormula.TakeLast(cellFormula.Length - matchIndex - matchString.Length)).ToArray());
stringLenChange += (replaceContent.Length - matchString.Length);
}
return(cellFormula);
}
public void TestA1R1C1Conversion()
{
string a1cell = "A1";
string r1c1cell = "R1C1";
Assert.AreEqual(a1cell, ExcelHelperClass.ConvertR1C1ToA1(r1c1cell));
Assert.AreEqual(r1c1cell, ExcelHelperClass.ConvertA1ToR1C1(a1cell));
a1cell = "C5";
r1c1cell = "R5C3";
Assert.AreEqual(a1cell, ExcelHelperClass.ConvertR1C1ToA1(r1c1cell));
Assert.AreEqual(r1c1cell, ExcelHelperClass.ConvertA1ToR1C1(a1cell));
a1cell = "FE100";
r1c1cell = "R100C161";
Assert.AreEqual(a1cell, ExcelHelperClass.ConvertR1C1ToA1(r1c1cell));
Assert.AreEqual(r1c1cell, ExcelHelperClass.ConvertA1ToR1C1(a1cell));
}
public void TestA1R1C1Conversion()
{
string a1cell = "A1";
string r1c1cell = "R1C1";
Assert.AreEqual(a1cell, ExcelHelperClass.ConvertR1C1ToA1(r1c1cell));
Assert.AreEqual(r1c1cell, ExcelHelperClass.ConvertA1ToR1C1(a1cell));
a1cell = "C5";
r1c1cell = "R5C3";
Assert.AreEqual(a1cell, ExcelHelperClass.ConvertR1C1ToA1(r1c1cell));
Assert.AreEqual(r1c1cell, ExcelHelperClass.ConvertA1ToR1C1(a1cell));
a1cell = "FE100";
r1c1cell = "R100C161";
Assert.AreEqual(a1cell, ExcelHelperClass.ConvertR1C1ToA1(r1c1cell));
Assert.AreEqual(r1c1cell, ExcelHelperClass.ConvertA1ToR1C1(a1cell));
a1cell = "HZ46";
r1c1cell = "R46C234";
Assert.AreEqual(a1cell, ExcelHelperClass.ConvertR1C1ToA1(r1c1cell));
Assert.AreEqual(r1c1cell, ExcelHelperClass.ConvertA1ToR1C1(a1cell));
a1cell = "IU255";
r1c1cell = "R255C255";
Assert.AreEqual(a1cell, ExcelHelperClass.ConvertR1C1ToA1(r1c1cell));
Assert.AreEqual(r1c1cell, ExcelHelperClass.ConvertA1ToR1C1(a1cell));
a1cell = "AA1";
r1c1cell = "R1C27";
Assert.AreEqual(a1cell, ExcelHelperClass.ConvertR1C1ToA1(r1c1cell));
Assert.AreEqual(r1c1cell, ExcelHelperClass.ConvertA1ToR1C1(a1cell));
a1cell = "AZ1";
r1c1cell = "R1C52";
Assert.AreEqual(a1cell, ExcelHelperClass.ConvertR1C1ToA1(r1c1cell));
Assert.AreEqual(r1c1cell, ExcelHelperClass.ConvertA1ToR1C1(a1cell));
}
