Generating a list of cells in a range

Ruby
1

Here’s a simple problem that seems like a good Ruby exercise.

Given a string containing an Excel-like range specifier like this:
“C5:E8”, generate an array of all the cells in the array.

In this case, the output should be something like this…

[“C5”, “C6”, “C7”, “C8”, “D5”, “D6”, “D7”, “D8”, “E5”, “E6”, “E7”, “E8”]

Ideally, this should also work for a partial range like “A:C” => [“A”,
“B”, “C”] and “12:15” => [“12”,“13”,“14”,“15”]

Extra points for style and bonus kudos for a one-liner.

_Kevin

2

On Sat, 27 May 2006, Kevin O. wrote:

“B”, “C”] and “12:15” => [“12”,“13”,“14”,“15”]

Extra points for style and bonus kudos for a one-liner.

i didn’t feel like golfing before coffee, but here’s one:

 harp:~ > cat a.rb
 class ExcelRange < ::Array
   def initialize spec
     a, b = spec.to_str.split %r/:/
     alpha_a, numeric_a = a.split %r/(\d+)/
     alpha_b, numeric_b = b.split %r/(\d+)/
     a =  alpha_a ? (alpha_a .. alpha_b).to_a : []
     b =  numeric_a ? (numeric_a .. numeric_b).to_a : []
     case [a.empty?, b.empty?]
       when [true, true]
         # nil
       when [true, false]
         replace b
       when [false, true]
         replace a
       when [false, false]
         a.each{|alpha| push(*b.map{|numeric| "%s%d" % [alpha,

numeric]})}
end
end
end
def ExcelRange(*a, &b) ExcelRange.new(*a, &b) end

 p ExcelRange("C5:E8")
 p ExcelRange("A:C")
 p ExcelRange("12:15")



 harp:~ > ruby a.rb
 ["C5", "C6", "C7", "C8", "D5", "D6", "D7", "D8", "E5", "E6", "E7",

“E8”]
[“A”, “B”, “C”]
[“12”, “13”, “14”, “15”]

regards.

-a

3

How about this one?

r=“A1:C5”.split(’:’)
(r[0]…r[1]).to_a

4

Run with “C5:E8” it results in this:

[“C5”, “C6”, “C7”, “C8”, “C9”, “D0”, “D1”, “D2”, “D3”, “D4”, “D5”,
“D6”, “D7”, “D8”, “D9”, “E0”, “E1”, “E2”, “E3”, “E4”, “E5”, “E6”,
“E7”, “E8”]

Not the expected:

[“C5”, “C6”, “C7”, “C8”, “D5”, “D6”, “D7”, “D8”, “E5”, “E6”, “E7”, “E8”]

5

On May 27, 2006, at 7:36 AM, Kevin O. wrote:

Here’s a simple problem that seems like a good Ruby exercise.

Given a string containing an Excel-like range specifier like this:
“C5:E8”, generate an array of all the cells in the array.

In this case, the output should be something like this…

[“C5”, “C6”, “C7”, “C8”, “D5”, “D6”, “D7”, “D8”, “E5”, “E6”, “E7”,
“E8”]

cell_range = “C5:E8”
=> “C5:E8”

from, to = cell_range.split(":")
=> [“C5”, “E8”]

cells = (from[/[A-Z]+/]…to[/[A-Z]+/]).map do |col|
?> (from[/\d+/]…to[/\d+/]).map { |row| col + row }

end.flatten
=> [“C5”, “C6”, “C7”, “C8”, “D5”, “D6”, “D7”, “D8”, “E5”, “E6”, “E7”,
“E8”]

James Edward G. II

6

Kevin O. wrote:

James G. wrote:

On May 27, 2006, at 7:36 AM, Kevin O. wrote:

Here’s a simple problem that seems like a good Ruby exercise.

Given a string containing an Excel-like range specifier like this:
“C5:E8”, generate an array of all the cells in the array.

In this case, the output should be something like this…

[“C5”, “C6”, “C7”, “C8”, “D5”, “D6”, “D7”, “D8”, “E5”, “E6”, “E7”,
“E8”]

cell_range = “C5:E8”
=> “C5:E8”

from, to = cell_range.split(":")
=> [“C5”, “E8”]

cells = (from[/[A-Z]+/]…to[/[A-Z]+/]).map do |col|
?> (from[/\d+/]…to[/\d+/]).map { |row| col + row }

end.flatten
=> [“C5”, “C6”, “C7”, “C8”, “D5”, “D6”, “D7”, “D8”, “E5”, “E6”, “E7”,
“E8”]

James Edward G. II

James’ solution gets points for style, but a’s is the most complete.

Good stuff guys, I always learn some good ruby fu from this list.

_Kevin

Here’s a variation of James’ solution that covers the other cases and is
a bit more robust (i.e., it works if the columns are lowercase, and if
you specify the range backwards…

def excelrange®
from, to = r.upcase.split(":").sort
begin
cells = (from[/[A-Z]+/]…to[/[A-Z]+/]).map do |col|
(from[/\d+/]…to[/\d+/]).map { |row| col + row }
end.flatten
rescue
cells = (from…to).to_a
end
end

excelrange(“A4:B6”) => [“A4”, “A5”,“A6”,“B4”,“B5”,“B6”]
excelrange(“B6:A4”) => [“A4”, “A5”,“A6”,“B4”,“B5”,“B6”]
excelrange(“4:6”) => [“4”,“5”,“6”]
excelrange(“A:B”) => [“A”,“B”]

7

James G. wrote:

On May 27, 2006, at 7:36 AM, Kevin O. wrote:

Here’s a simple problem that seems like a good Ruby exercise.

Given a string containing an Excel-like range specifier like this:
“C5:E8”, generate an array of all the cells in the array.

In this case, the output should be something like this…

[“C5”, “C6”, “C7”, “C8”, “D5”, “D6”, “D7”, “D8”, “E5”, “E6”, “E7”,
“E8”]

cell_range = “C5:E8”
=> “C5:E8”

from, to = cell_range.split(":")
=> [“C5”, “E8”]

cells = (from[/[A-Z]+/]…to[/[A-Z]+/]).map do |col|
?> (from[/\d+/]…to[/\d+/]).map { |row| col + row }

end.flatten
=> [“C5”, “C6”, “C7”, “C8”, “D5”, “D6”, “D7”, “D8”, “E5”, “E6”, “E7”,
“E8”]

James Edward G. II

James’ solution gets points for style, but a’s is the most complete.

Good stuff guys, I always learn some good ruby fu from this list.

_Kevin

8

Kevin O. wrote:

“E8”]
you specify the range backwards…
end

excelrange(“A4:B6”) => [“A4”, “A5”,“A6”,“B4”,“B5”,“B6”]
excelrange(“B6:A4”) => [“A4”, “A5”,“A6”,“B4”,“B5”,“B6”]
excelrange(“4:6”) => [“4”,“5”,“6”]
excelrange(“A:B”) => [“A”,“B”]

Nice. I may have to add this to the spreadsheet package. :slight_smile:

Regards,

Dan

9

Kevin O. wrote:

Kevin O. wrote:

James G. wrote:

On May 27, 2006, at 7:36 AM, Kevin O. wrote:

Here’s a simple problem that seems like a good Ruby exercise.

Given a string containing an Excel-like range specifier like this:
“C5:E8”, generate an array of all the cells in the array.

In this case, the output should be something like this…

[“C5”, “C6”, “C7”, “C8”, “D5”, “D6”, “D7”, “D8”, “E5”, “E6”, “E7”,
“E8”]

cell_range = “C5:E8”
=> “C5:E8”

from, to = cell_range.split(":")
=> [“C5”, “E8”]

cells = (from[/[A-Z]+/]…to[/[A-Z]+/]).map do |col|
?> (from[/\d+/]…to[/\d+/]).map { |row| col + row }

end.flatten
=> [“C5”, “C6”, “C7”, “C8”, “D5”, “D6”, “D7”, “D8”, “E5”, “E6”, “E7”,
“E8”]

James Edward G. II

James’ solution gets points for style, but a’s is the most complete.

Good stuff guys, I always learn some good ruby fu from this list.

_Kevin

Here’s a variation of James’ solution that covers the other cases and is
a bit more robust (i.e., it works if the columns are lowercase, and if
you specify the range backwards…

def excelrange®
from, to = r.upcase.split(":").sort
begin
cells = (from[/[A-Z]+/]…to[/[A-Z]+/]).map do |col|
(from[/\d+/]…to[/\d+/]).map { |row| col + row }
end.flatten
rescue
cells = (from…to).to_a
end
end

excelrange(“A4:B6”) => [“A4”, “A5”,“A6”,“B4”,“B5”,“B6”]
excelrange(“B6:A4”) => [“A4”, “A5”,“A6”,“B4”,“B5”,“B6”]
excelrange(“4:6”) => [“4”,“5”,“6”]
excelrange(“A:B”) => [“A”,“B”]

def excelrange®
from, to = r.upcase.split(":").sort
begin
(from[/[A-Z]+/]…to[/[A-Z]+/]).map do |col|
(from[/\d+/]…to[/\d+/]).map { |row| col + row }
end.flatten
rescue
to ? (from…to).map : from.map
end
end

A minor tweak so that
excelrange(“A1”) => [“A1”]

_Kevin