/// <summary>
/// Given just the column name (no row index), it will return the zero based column index.
/// Note: This method will only handle columns with a length of up to two (ie. A to Z and AA to ZZ).
/// A length of three can be implemented when needed.
/// </summary>
/// <param name="columnName">Column Name (ie. A or AB)</param>
/// <returns>Zero based index if the conversion was successful; otherwise null</returns>
public static int GetColumnIndexFromName(string columnName)
{
int columnIndex = -1;
if (columnName.Length <= 2)
{
int index = 0;
foreach (char col in columnName)
{
int indexValue = Letters.IndexOf(col);
if (indexValue != -1)
{
// The first letter of a two digit column needs some extra calculations
if (index == 0 && columnName.Length == 2)
{
columnIndex = columnIndex == -1 ? ((indexValue + 1) * 26) : columnIndex + ((indexValue + 1) * 26);
}
else
{
columnIndex = columnIndex == -1 ? (indexValue) : columnIndex + indexValue;
}
}
index++;
}
}
return(columnIndex);
}
public static string IncrementCellReference(string reference, CellReferencePartEnum cellRefPart)
{
string newReference = reference;
if (cellRefPart != CellReferencePartEnum.None && !String.IsNullOrEmpty(reference))
{
string[] parts = Regex.Split(reference, "([A-Z]+)");
if (cellRefPart == CellReferencePartEnum.Column || cellRefPart == CellReferencePartEnum.Both)
{
List <char> col = parts[1].ToCharArray().ToList();
bool needsIncrement = true;
int index = col.Count - 1;
do
{
// increment the last letter
col[index] = Letters[Letters.IndexOf(col[index]) + 1];
// if it is the last letter, then we need to roll it over to 'A'
if (col[index] == Letters[Letters.Count - 1])
{
col[index] = Letters[0];
}
else
{
needsIncrement = false;
}
} while (needsIncrement && --index >= 0);
// If true, then we need to add another letter to the mix. Initial value was something like "ZZ"
if (needsIncrement)
{
col.Add(Letters[0]);
}
parts[1] = new String(col.ToArray());
}
if (cellRefPart == CellReferencePartEnum.Row || cellRefPart == CellReferencePartEnum.Both)
{
// Increment the row number. A reference is invalid without this componenet, so we assume it will always be present.
parts[2] = (int.Parse(parts[2]) + 1).ToString();
}
newReference = parts[1] + parts[2];
}
return(newReference);
}
public int IndexOf(string letter) => Letters.IndexOf(letter) switch
{
-1 => - 1, int i => i + ZeroIndex