C# Loops with Char and Numbers

Question

I have the following line of code in my C# Winforms app:

excell_app.createHeaders(1, 1, "Product_1", "A1", "A1", "n");

Where it says "Product_1" I need it to loop through all my items in my array and where it says "A1", "A1" I need it to get the next value, i.e "B2","B2"

I am unsure as to which loop I should use because I need a for next to interate through my array but then I need to increment the value for the location for "B2","B2"

Here is my attempt:

foreach (string value in ProductName)
{
    excell_app.createHeaders(1, 1, "+ value +", "A1", "A1", "n");
}

I do not know how to Iterate through letters and numbers for my location values:

Something like: (I think this may be wrong, please advise)

char X='A'+1;
X++

So "A1" becomes "B2" which becomes "C3" which becomes "D4" and so on? What happens at "I9"? "J10"? — Lasse V. Karlsen, Jan 25 '14 at 22:47
After `Z26` it should read `AA1`, then `AB2`.. its just the Excel columns. — PriceCheaperton, Jan 25 '14 at 22:52
There is a way to access them without letters called [R1C1 reference](http://www.lytebyte.com/2008/04/29/what-are-a1-and-r1c1-reference-style-in-excel/) which might be better. In that the R1 refers to row 1 and C1 refers to column 1. It may be easier to iterate through. Not sure how to implement it so I'm not adding an answer. I just accidentally set my work xcel application to it and found out about it. — Felix Castor, Jan 25 '14 at 22:53
Yes that would help but sadly the `using Excel = Microsoft.Office.Interop.Excel;` does not allow for this.. — PriceCheaperton, Jan 25 '14 at 23:02
You know that the number after the letters isn't part of the column but specifies the row? So Z26 means column Z and row 26. — Rand Random, Jan 25 '14 at 23:19
I'm not following what your asking for exactly but here is a good [link](http://stackoverflow.com/questions/2208688/quickest-way-to-enumerate-the-alphabet-in-c-sharp) to looping through the alphabet. Now you can just make an inner loop. — Automate This, Jan 26 '14 at 03:08
I was searching for a way to reference using R1C1 out of pure interest now and came across this answer. [How to convert a column number (eg. 127) into an excel column (eg. AA)](http://stackoverflow.com/questions/181596/how-to-convert-a-column-number-eg-127-into-an-excel-column-eg-aa) — Felix Castor, Jan 26 '14 at 17:54

Thorarin · Accepted Answer · 2014-01-26T11:38:05.940

The column is basically a base 26 number which is using only letters as its symbols. The only odd bit is that there is no symbol for zero.

These methods should do the trick:

private static string ExcelCellReference(int col, int row)
{
    return ExcelColumnReference(col) + row;
}

private static string ExcelColumnReference(int col)
{
    if (col <= 0)
    {
        throw new ArgumentOutOfRangeException("col",
            "Value must be greater than or equal to 1.");
    }
    string columnString = "";

    do
    {
        col--;
        int remainder;
        // Math.DivRem divides by 26 and also gets the remainder.
        col = Math.DivRem(col, 26, out remainder);
        columnString = (char)('A' + remainder) + columnString;
    } while (col > 0);

    return columnString;
}

ExcelCellReference(1, 1) will return A1, ExcelCellReference(28, 2) will return AB2, etc.

That was perfect, Thanks – Arash.Zandi Dec 08 '20 at 17:11 — Arash.Zandi, Dec 08 '20 at 17:11

Francesco Milani · Answer 2 · 2014-01-26T12:51:11.377

1

private String getColumnHeader(int column)
{
    column--;
    if (column >= 0 && column < 26)
    {
        return (Char)('A' + column) + "";
    }
    else 
    {
        return getColumnHeader(column / 26) + getColumnHeader(column % 26 + 1);
    }
}

private int getColumnIndex(String reference)
{
    int retVal = 0;
    retVal += reference.Substring(reference.Length - 1)[0] - 'A';
    if (reference.Length > 1)
    {
        reference = reference.Substring(0, reference.Length - 1);
        retVal += 26 * getColumnIndex(reference);
    }

    return retVal + 1;
}

edited Jan 26 '14 at 12:51

answered Jan 26 '14 at 09:45

Francesco Milani

415
4
11

I wonder why you decided to use `Uint32`. The method signature is not CLS compliant if you use unsigned types, which is fine for private methods, but it seems unlikely that you'll have more than 2.1 billion columns? In fact, Excel only supports 16.384 currently. – Thorarin Jan 26 '14 at 11:31
those methods belong to a library which i created to manage Excel file s with OpenXmlSdk and so i just leave types used in OpenXmlLibrary, but for sure your right! – Francesco Milani Jan 26 '14 at 12:12
for example Uint32Value it's a class which include readonly property UInt32 Value; and a direct cast to UInt32 – Francesco Milani Jan 26 '14 at 12:17
Ah, that explains it... was just confusing as a self-contained example :) – Thorarin Jan 26 '14 at 21:26

C# Loops with Char and Numbers

2 Answers2