Skip to content

Instantly share code, notes, and snippets.

@weitzhandler
Last active November 27, 2019 15:52
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save weitzhandler/97089718c5f3bac56bc5521c5317b742 to your computer and use it in GitHub Desktop.
Save weitzhandler/97089718c5f3bac56bc5521c5317b742 to your computer and use it in GitHub Desktop.
ColumnReference - easy way to convert between Excel column index and column letter
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);
}
[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