C# - is it possible to use Letter Coords(A1) with workSheet.Cells?

Question

I was recently using powershell to automate some things with Excel, and i could simply just use A1, A2 etc. With C#, it seems to be a requirement to use [1,1], (coordinate style) or else you get a type mismatch. Here is the code I am working with:

                    //Generating User and Password
                int startCoordI = Int32.Parse(startCoord);
                int endCoordI = Int32.Parse(endCoord);
                int userCoordI = Int32.Parse(userCoord);
                int passwordCoordI = Int32.Parse(passwordCoord);
                int value = startCoordI;
                string Username = Convert.ToString(workSheet.Cells[userCoord, startCoordI].Value);
                MessageBox.Show(Username);
                string Password = Convert.ToString(workSheet.Cells[passwordCoord, startCoordI].Value);
                MessageBox.Show(Password);

                try
                {
                    for (I = startCoordI; I <= endCoordI; I++)
                    {
                        System.Diagnostics.ProcessStartInfo proccessStartInfo = new System.Diagnostics.ProcessStartInfo("net", "user " + Username + " " + Password + " /add /passwordchg:no");
                        System.Diagnostics.Process proc = new System.Diagnostics.Process { StartInfo = proccessStartInfo };
                        proc.StartInfo.RedirectStandardOutput = true;
                        proc.StartInfo.UseShellExecute = false;
                        proccessStartInfo.CreateNoWindow = true;
                        proc.Start();

                        //new user
                        value++;
                        Username = Convert.ToString(workSheet.Cells[userCoord, value].Value);
                        Password = Convert.ToString(workSheet.Cells[passwordCoord, value].Value);
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

This isn't inherently a problem, but it would be nice to be able to use A1 style coordinates. Thanks!

Gusman · Accepted Answer · 2015-08-29T05:08:04.837

2

Not inherently, but it would be really easy to create an extension method to do this for you (I don't have Excel installed, so if any type is wrong correct it ;)):

public static class ExcelExtensions
{
    public static Range Named(this Range Cells, string CellName)
    {

        char cellLetter = CellName.Substring(0, 1).ToUpper()[0];
        int xCoordinate = (cellLetter - 'A') + 1;
        int yCoordinate = int.Parse(CellName.Substring(1));
        return Cells[yCoordinate, xCoordinate];
    }
}

Now you can do:

workSheet.Cells.Named("B3").Value .....

edited Aug 29 '15 at 05:08

answered Aug 29 '15 at 02:01

Gusman

13,944
2
26
43

Similarly, you could create/return a wrapper class that uses a string indexer. Then your syntax might look like `var cells = workSheet.Cells.AsNamed(); Username = cells["A1"].Value;` If the code doesn't plan to use anything with the cells beyond accessing `Value`, then the indexer could return that directly, then it's just `Username = cells["A1"];` Also, I believe cell access with this API is 1-indexed. (if I understand this [MSDN doc](https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.item.aspx) correctly) – Chris Sinclair Aug 29 '15 at 02:17
It works fine on the first run, but the second run I get an error that confuses me: An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll Additional information: Exception from HRESULT: 0x800A03EC Any idea what this means? – gman5500 Aug 29 '15 at 02:21
1

http://stackoverflow.com/questions/7099770/hresult-0x800a03ec-on-worksheet-range, it's a problem with the sheet – Gusman Aug 29 '15 at 02:22
Oh alright, that would make sense since this sheet has been the test dummy for many, many tests. – gman5500 Aug 29 '15 at 02:25
Its been a long day, so this is probably a simple error I am missing, but the problem seems to occur with the return line of the extension: return Cells[xCoordinate, yCoordinate];. Once I figure this out I'm heading off, haha. When I do workSheet.Cells.Named("A1").Value, it returns "A1", instead of the actual value. – gman5500 Aug 29 '15 at 02:43
OMFG, no, it's not as simple as you thought and I was wrong, Cells is a dynamic object, so when we call the extension it tries to resolve it to the underliying object and it throws an exception... gimme 5 minutes figuring how would be the best way to solve it – Gusman Aug 29 '15 at 02:56
1

MWAHAHAHAHAHA, nope again, the code is right and I have hit the same happened to you XD, the Range contains a property named "Name", but it also will take int as a function because it's dynamic, at least that was my problem. Also, it starts in [1,1], it must be displaced, let me correct it on the code – Gusman Aug 29 '15 at 02:59
@Gusman Thanks! You solved it. I was out at the time, but thanks for the help. – gman5500 Aug 29 '15 at 03:52

score 0 · Answer 2 · answered Oct 19 '16 at 08:10

In my opinion there will be an error if you try to do it with things like BA1 or AAA1.

This should be right:

public static class ExcelExtensions
    {
        public static exc.Range Named(this exc.Range Cells, string CellName)
        {
            //Get Letter
            char[] charArray = CellName.ToCharArray();
            string strLetter = string.Empty;
            foreach (char letter in charArray) 
            {

                if (Char.IsLetter(letter)) strLetter += letter.ToString();

            }

            //Convert Letter to Number

            double value = 0;

            if (strLetter.Length > 1)
            {

                foreach (char letter in strLetter) 
                {
                    if (value == 0)
                    {
                        value = (letter - 'A' + 1) * Math.Pow(26, (strLetter.Length -1));
                    }
                    else
                    {
                        value += (letter - 'A' + 1);
                    }

                }
            }

            else
            {
                char[] letterarray = strLetter.ToCharArray();
                value = (letterarray[0] - 'A') + 1;

            }

            // ReadOut Number

            string strNumber = string.Empty;
            foreach (char numChar in CellName.ToCharArray()) 
            {
                if (Char.IsNumber(numChar)) strNumber += numChar.ToString();

            }            

            return Cells[strNumber, value];

        }
    }

I know, it's a messy code, but it works :)

score 0 · Answer 3 · answered Mar 16 '18 at 17:29

This seemed like the cleanest approach for me. Handled the AD14 column row issue.

public static Range Named(this Range Cells, string CellName)
    {

        int xCoordinate = Cells.Range[CellName].Column;
        int yCoordinate = Cells.Range[CellName].Row;
        return Cells[yCoordinate, xCoordinate];
    }

C# - is it possible to use Letter Coords(A1) with workSheet.Cells?

3 Answers3