From: Daniel Berger <djberg96@...>
Date: 2006-05-28T06:05:52+09:00
Subject: Re: Generating a list of cells in a range

Kevin Olbrich wrote:
> Kevin Olbrich wrote:
>> James Gray wrote:
>>> On May 27, 2006, at 7:36 AM, Kevin Olbrich 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 Gray 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"]
> 

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

Regards,

Dan