Last active
November 27, 2019 15:52
-
-
Save weitzhandler/97089718c5f3bac56bc5521c5317b742 to your computer and use it in GitHub Desktop.
ColumnReference - easy way to convert between Excel column index and column letter
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
internal struct ColumnReference | |
{ | |
readonly uint _ColumnIndex; | |
const int AsciiTrim = 'A' - 1; | |
const int LastChar = 'Z' - AsciiTrim; | |
ColumnReference(uint columnIndex) | |
{ | |
_ColumnIndex = columnIndex; | |
} | |
ColumnReference(string columnName) | |
{ | |
columnName = columnName.ToUpperInvariant(); | |
_ColumnIndex = | |
(uint)columnName | |
.Reverse() | |
.Select(ch => ch - AsciiTrim) | |
.Select((ch, i) => ch * Math.Pow(LastChar, i)) | |
.Sum() | |
- 1; //make zero-index based | |
} | |
public string ColumnName | |
{ | |
get | |
{ | |
var colNumber = _ColumnIndex + 1; //non-zero-based | |
var output = new Stack<char>(); | |
//var index = 1; | |
while (colNumber > 0) | |
{ | |
var current = colNumber % LastChar; | |
if (current == 0) current = LastChar; | |
output.Push((char)(current + AsciiTrim)); | |
colNumber = (colNumber - current) / LastChar; | |
} | |
return string.Concat(output); | |
} | |
} | |
public override string ToString() => ColumnName; | |
public static implicit operator uint(ColumnReference r) => r._ColumnIndex; | |
public static implicit operator int(ColumnReference r) => (int)r._ColumnIndex; | |
public static implicit operator string(ColumnReference r) => r.ColumnName; | |
public static explicit operator ColumnReference(uint index) => new ColumnReference(index); | |
public static explicit operator ColumnReference(int index) => new ColumnReference((uint)index); | |
public static explicit operator ColumnReference(string columnName) => new ColumnReference(columnName); | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
[Theory] | |
[InlineData("A", 0)] | |
[InlineData("Z", 25)] | |
[InlineData("AA", 26)] | |
[InlineData("AZ", 51)] | |
[InlineData("ABC", 730)] | |
public void TestColRefs(string colName, int expectedIndex) | |
{ | |
Assert.Equal((ColumnReference)colName, expectedIndex); | |
Assert.Equal((ColumnReference)expectedIndex, colName); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment