Geek Answers Handbook

How to implement self-name of columns from your index?

I want to create an instance of the Cell class, naming the cell instance with a name like "A", "B", "C", etc., as in an Excel spreadsheet.

I have a Cell class:

 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 need to create a name based on the letters and double and triple them as soon as I run into the last letter of the alphabet "Z". The algorithm would have to generate letters, which I would associate with the rowIndex value, which would lead to this naming strategy such as Excel.

The letters will be as follows:

 A, B, C, D...Z, AA, AB, AC...AZ, BA, BB, BC...BZ, CA...XAA, XAB, XAC...

Although we clearly know that a value of colIndex 1 will necessarily indicate a column "A", a value of 2 = "B", a value of 3 = "C", etc.

My problem especially is that we double the letters.

Do you have any ideas on how I could achieve this in its simplest form?

Thanks! =)

0
source share
2 answers

This. Convert column index to Excel column name

You should not try to make it recursive and give you exactly what you need. Hope this helps.

+1
source

This feature will do it for you. This is in VB.NET, but I hope you can port it to C # if necessary.
I updated the answer using 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

FROM#

 /// <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 a column index of 1000 and worked without crashing. I hope you find this helpful.

+1
source

More articles:


All Articles