How can I specify the correct column?

Question

I need to do different things based upon which field (column) the data is in.

            For Each row As DocumentFormat.OpenXml.Spreadsheet.Row In
                worksheet.Descendants(Of DocumentFormat.OpenXml.Spreadsheet.Row)
                    For Each cell As DocumentFormat.OpenXml.Spreadsheet.Cell In row
                        Select Case True
                            Case cell.CellReference.Value Like "C*"
                                'if this cell is in column C
                            Case cell.CellReference.Value Like "A*"
                                'if this cell is in column A
                            Case Else
                        End Select
                    Next
            Next

This works great as long as there are no more than 26 fields in a given spreadsheet.

How do I make sure that my Like "A*" doesn't react to column AA, AB, etc.?

Keep in mind that the OpenXML SDK always returns a full cell reference for the .cellreference.value, and not just the column. And I need to specify that I am not trying to wholesale throw out any column greater than 26, but I am trying to make sure I specify a specific column. It is possible that the column under scrutiny may end up being AA or AB, depending on the company that created the specific source sheet. I was hoping for a property, or barring that, how other people have learned to refer to specific columns in openxml.

@ChrisF Thank you. As far as I know, the only way to get the column is with the `.cellreference.value`, which returns a string. If there exists another way to get the reference, maybe in a different format, it doesn't seem to be documented in MSDN. — CWilson, Oct 25 '16 at 15:30
I wasn't thinking of the data, just getting the column part of the reference. — ChrisF, Oct 25 '16 at 15:32
@ChrisF That is a good point. perhaps I could take the `ASC()` of each individual character, and if it is between 64 and 90 (or whatever off the top of my head) it would go in the column part, otherwise in the row part, and then convert those results to an R1C1 type variable (array/object/dunno) before starting my `select case`. But that seems awfully intensive for just trying to get the reference. There has to be a better way, right? — CWilson, Oct 25 '16 at 15:38
not that I could find in the spreadsheetml specification, and not that I could find with intellisense or on MSDN. And not in the xml of any of the sheets I am working with. — CWilson, Oct 25 '16 at 15:43
@H.Fadlallah Another good idea. That works great as long as there is a maximum of 9 rows in my source spreadsheet. As you can imagine, if I am worried about more than 26 columns, there is a possibility of more than 9 rows. And, for completeness, "A[0-9][0-9]" works great for rows 10-99, but not higher or lower. Maybe it is possible to combine these ideas? — CWilson, Oct 25 '16 at 15:54
from @H.Fadlallah 's interesting idea, I realized I can place `Case cell.CellReference.Value Like "[A-Z][A-Z]*"` (with no commands beneath it) as the first line in the `case select`, which would work in many cases. But when I actually need to work with a column that is AA, etc. (the columns are actually dynamic in my code from user input, which I thought was off topic here), hard coding that line as first will frustrate some users. — CWilson, Oct 25 '16 at 16:02
Does the [answer here](http://stackoverflow.com/questions/28875815/get-the-column-index-of-a-cell-in-excel-using-openxml-c-sharp/31035990#31035990) help at all? It's in `C#` but it's easy enough to convert. — petelids, Oct 25 '16 at 16:20
@petelids You are right. I don't know `C#`, and I don't know regex, but that was easy to read. It seems like, in that case, the `ASC()` idea was the best one, and things like `.reverse()` and `@"[\d]"` make it easier. Not what I was hoping for, but maybe the property doesn't exist. If that answer is how all the cool kids are doing it, then I suppose that is the cliff for me to jump off of. — CWilson, Oct 25 '16 at 16:33
You could add a `#` after the column letter you are trying to match which says there must be a digit after the column letter - e.g. `Case s Like "A#*"`. That will match `A1` but not `AA1`. — petelids, Oct 25 '16 at 16:34
@petelids That is it, that is the one! Is it worth it to put that as an answer, so I can accept and upvote? — CWilson, Oct 25 '16 at 16:38
No problem @CWilson, happy to help. I've added it as an answer for completeness. — petelids, Oct 25 '16 at 16:40

score 2 · Accepted Answer · answered Oct 25 '16 at 16:40

Adding a # after the column letter(s) you are trying to match will tell the matcher that there must be a number after the letter. This will prevent your check for A matching if the column is actually AA.

Select Case True
    Case cell.CellReference.Value Like "C#*"
    'if this cell is in column C
    Case cell.CellReference.Value Like "A#*"
    'if this cell is in column A
    Case cell.CellReference.Value Like "AA#*"
    'if this cell is in column AA
    Case Else
End Select

From the documentation, the # will match "Any single digit (0–9)".

How can I specify the correct column?

1 Answers1