Asp Forum - regex help please - parsing an Excel Range

Todd Burch

7/17/2007 4:55:00 AM

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

--
Posted via http://www.ruby-....

4 Answers

Phil Meier

7/17/2007 6:40:00 AM

Todd Burch wrote:
> 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
>
You can have this in one RegEx:

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

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

Todd Burch

7/17/2007 12:52:00 PM

Phil Meier wrote:

> You can have this in one RegEx:
>
> addr = "$G$28:$I$28"
> addr =~ /:\$*([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

--
Posted via http://www.ruby-....

Phrogz

7/18/2007 3:56:00 AM

On Jul 16, 10:55 pm, Todd Burch <pro...@burchwoodusa.com> 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 )

bbiker

7/18/2007 9:44:00 PM

On Jul 17, 8:52 am, Todd Burch <pro...@burchwoodusa.com> wrote:
> Phil Meier wrote:
> > You can have this in one RegEx:
>
> > addr = "$G$28:$I$28"
> > addr =~ /:\$*([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
>
> --
> Posted viahttp://www.ruby-....

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.

comp.lang.ruby

regex help please - parsing an Excel Range

Todd Burch

Phil Meier

Todd Burch

Phrogz

bbiker

x Login to ForumsZone