I wish to be able to instantiate my Cell
class while naming the cell instance with such name as "A", "B", "C", etc. just like in an Excel spreadsheet.
I have my Cell
class like so:
public class Cell {
public Cell(Range nativeCell) {
NativeCell = nativeCell;
}
public Range NativeCell { get; private set; }
}
And my Sheet
class:
public class Sheet {
private IDictionary<string, Cell> _cells;
public Sheet(Worksheet nativeSheet) {
NativeSheet = nativeSheet;
_cells = new Dictionary<string, Cell>();
for (int rowIndex = 1; rowIndex <= NativeSheet.Rows.Count; ++rowIndex)
for (int colIndex = 1; colIndex <= NativeSheet.Columns.Count; ++colIndex) {
ICell newCell = new Cell(NativeSheet.Cells(rowIndex, colIndex));
newCell.Name = ?? // This name should look like "A1", "B1", "AA3", "CB20", etc.
Cells.Add(newCell.Name, newCell);
}
}
public IDictionary<string, Cell> Cells {
get {
return _cells;
}
}
public Worksheet NativeSheet { get; private set; }
}
I would need to generate a name based on the alphabetic letters and double and triple them once I encounter the last alphabet letter 'Z'. The algorithm would have to generate the letters that I would concatenate with the rowIndex
value that would result to this naming strategy such as Excel.
The letters would be:
A, B, C, D...Z, AA, AB, AC...AZ, BA, BB, BC...BZ, CA...XAA, XAB, XAC...
While we clearly know that colIndex
value 1 will definitely designate column "A", value 2 = "B", value 3 = "C", etc.
My problem is particularly when we double the letters.
Do you have any idea on how I could achieve this in the simplest possible form?
Thanks! =)
Here is this.
Translate a column index into an Excel Column Name
Shouldn't be to hard to make it recursive and give you exactly what you need. I hope this helps.
This function will do it for you. It is in VB.NET but I trust you'll be able to port it to C# if need be.
I have updated the answer with the C# version of the function.
VB.NET
''' <summary>Returns the Excel-style name of the column from the column index.</summary>
''' <param name="colIndex">The column index.</param>
Function GetColumnName(ByVal colIndex As Integer) As String
If colIndex < 1 Then Throw New ArgumentException("Column number must be greater or equal to 1.")
Dim result As New List(Of String)
'letter codes start at Chr(65)'
Do While colIndex > 0
'reduce the column number by 1 else the 26th column (Z) will become 0 (@) '
'add 65 to the result and find the Chr() value. '
'insert the character at position 0 of the character list '
'integer divide by 26 to remove the column from the stack and repeat till '
'there are no columns in the stack. '
result.Insert(0, Chr(65 + CInt((colIndex - 1) Mod 26)))
colIndex = (colIndex - 1) \ 26
Loop
Return String.Join("", result.ToArray)
End Function
C#
/// <summary>Returns the Excel-style name of the column from the column index.</summary>
/// <param name="colIndex">The column index.</param>
static string GetColumnName(int colIndex)
{
if (colIndex < 1)
throw new ArgumentException("Column number must be greater or equal to 1.");
var result = new List<char>();
//letter codes start at Chr(65)'
while (colIndex > 0)
{
//reduce the column number by 1 else the 26th column (Z) will become 0 (@)
//add 65 to the result and find the Chr() value.
//insert the character at position 0 of the char list
//integer divide the column index by 26 to remove the last calculated column
//from the stack and repeat till there are no columns in the stack.
result.Insert(0, Microsoft.VisualBasic.Strings.Chr(65 + Convert.ToInt32((colIndex - 1) % 26)));
colIndex = (int)((colIndex-1)/ 26);
}
return new string(result.ToArray());
}
I tested this up to column index 1000 and it worked without fail. I hope you find it useful.