Forum: Ruby Generating a list of cells in a range

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Kevin O. (Guest)
on 2006-05-27 16:36
(Received via mailing list)
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
unknown (Guest)
on 2006-05-27 18:20
(Received via mailing list)
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
Takumi Kimoto (Guest)
on 2006-05-27 18:51
(Received via mailing list)
How about this one?

r="A1:C5".split(':')
(r[0]..r[1]).to_a
Simen E. (Guest)
on 2006-05-27 19:16
(Received via mailing list)
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"]
James G. (Guest)
on 2006-05-27 19:25
(Received via mailing list)
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
Kevin O. (Guest)
on 2006-05-28 00:31
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
Kevin O. (Guest)
on 2006-05-28 00:53
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(r)
  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"]
Daniel B. (Guest)
on 2006-05-28 01:06
(Received via mailing list)
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. :)

Regards,

Dan
Kevin O. (Guest)
on 2006-05-28 03:26
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(r)
>   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(r)
   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
This topic is locked and can not be replied to.