Regex help please - parsing an Excel Range

Ruby
1

I’m parsing a worksheet in EXCEL, working around (what I consider to be)
unexpected results when merged cells are the last thing on a worksheet.

For conversation sake, lets say my tiny worksheet’s whole used range is
A1:D4.

I’m use:

lastcell = sheet.Cells.SpecialCells(xlCellTypeLastCell)

to get the last cell in the sheet, and subsequently I parse out the
maximum row and maximum column used in the worksheet

lastcell.Address # -> $D$4
maxrow = lastcell.Row # -> 4
maxcol = lastcell.Column # -> 4 A.K.A. “D”

This works great most of the time. However, when there is a Merged cell
that goes from A4:D4 in my tiny worksheet, the
SpecialCells(xlCellTypeLastCell) return A4:D4. This seems ok at first,
until…

lastcell.Address # -> $A$4:$D$4
maxrow = lastcell.Row # -> 4 as epxected
maxcol = lastcell.Column # -> 1 A.K.A. “A” - this is not expected!

Therefore, I want to parse out the back half of the range (the $D$4) to
get the actual last row and last column when lastcell.MergeCells is
true.

I’ve worked this novice regex series out (using a different example),
but I’m thinking it could much more elegant:

addr = “$G$28:$I$28” # merged cell range
addr =~ /:$/ # isolates the “I$28” into $’
back = $’ # puts “I$28” into a variable
back =~ /$/ # “I” and “28” go into $and $' respectively puts "row=#{$'}, Col=#{$}"; # -> Row=28, Col=I - Bingo!

I then simply let EXCEL convert I to 9. (or AA to 27, etc…)

Any insight for improvement is appreciated - for both the regex and my
usage of EXCEL in this situation.

Thanks, Todd

2

Todd B. wrote:

Thanks, Todd

You can have this in one RegEx:

addr = “$G$28:$I$28”
addr =~ /:blush:([A-Z])+$(.*)$/
puts “row=#{$2}, col=#{$1}” # -> row=28, col=I

An addr = “G28:I28” would also be accepted by the RegEx.

3

Phil M. wrote:

You can have this in one RegEx:

addr = “$G$28:$I$28”
addr =~ /:blush:([A-Z])+$(.*)$/
puts “row=#{$2}, col=#{$1}” # -> row=28, col=I

An addr = “G28:I28” would also be accepted by the RegEx.

Works perfect Phil. Thanks! Todd

4

On Jul 16, 10:55 pm, Todd B. [email protected] wrote:

Any insight for improvement is appreciated - for both the regex and my
usage of EXCEL in this situation.

_,col,row = /(\w+)$?(\d+)$/.match( my_string )

5

On Jul 17, 8:52 am, Todd B. [email protected] wrote:

–
Posted viahttp://www.ruby-forum.com/.

For your first example, assuming that the cells contain 1…6, 8…13
with cell B2…B3 merged and cells D1…D4 are also merged.
you can simply obtain the values, you get the left most value of a
block of merged cells and nil for the remaining cells

values = ws.Range(“A1:D4”)[‘Value’]

p values => [[1.0, 2.0, 3.0, 4.0], [5.0, 6.0, nil, 8.0], [9.0, 10.0,
11.0, 12.0], [13.0, nil, nil, nil]]

ps values =>
1.0
2.0
3.0
4.0
5.0
6.0
nil
8.0
9.0
10.0
11.0
12.0
13.0
nil
nil
nil

Note that if the last row did not have any merged cell you would have
gotten row 4, col D
and been unware of the B2…B3 merge.