/// <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