Create an array of a range of letters starting aft

问题:

I am working with the Roo Gem and wanted to pull data from a spreadsheet based on standard A1 syntax.

I have columns in the spreadsheet beyond Z so Excel does the whole AA, AB, AC column positions.

I want to create an array for columns W to AH.

Ruby doesn't seem to like when the upper range extends past Z but hasn't started from A??

Any ideas how to ("B".."AC").to_a and not get []

Here is the basic problem in irb.

("A".."Z").to_a
#=> ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"]
("B".."Z").to_a
#=> ["B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"]
("A".."AC").to_a
#=> ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC"]
("B".."AC").to_a
#=> []

回答1:

how about this?

("A".."AC").to_a.drop(1)

you can drop whatever number of elements you like and it just involves 1 range and 1 array creation..

The integer could potentially be substituted for someting that returns the position of the letter in the alphabet.

class Array
  def from(column)
    drop(find_index(column).to_i)
  end
end

("A".."AC").to_a.from('F')
#=> ["F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC"]

Using Range class directly, thanks to @sagarpandya82

class Range
  def from(column)
    to_a.drop(find_index(column).to_i)
  end
end

("A".."AC").from('F')
#=> ["F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC"]

回答2:

Use Kernel#loop to build-up an empty array. The loop breaks once the current value equals the second parameter. To return the newly-built array o, we pass o as an argument to break, which by default returns nil.

def cols a, b
  loop.with_object([]) do |_, o|
    o << a
    break(o) if a == b
    a = a.next
  end
end  

cols('W','AH')
 #=> ["W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH"]

cols("A","Z")
 #=> ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M",
 #    "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"]

cols("B","Z")
 #=>  ["B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N",
 #     "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"]

cols("A","AC")
 #=> ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M",
 #    "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z",
 #    "AA", "AB", "AC"]

cols("B","AC")
 #=> ["B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N",
 #    "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA",
 #    "AB", "AC"]

回答3:

A mathematical answer of this would be :

A => AH = (A => W) + (W=> AH)

so W => AH = (A => AH) - (A => W)

The programmatical answer of this :

("A".."AH").to_a - ("A"..."W").to_a
#=> ["W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH"]

The ... in the second range makes it exclusive, i.e. without "W".

A more general answer would be

r = "W".."AH"

("A"..r.end).to_a - ("A"...r.begin).to_a
#=> ["W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH"]

回答4:

Ruby's String#succ increments letters the way Excel increments column names:

'Z'.succ #=> "AA"

So if you know that the destination value is reachable via succ, a simple loop works:

ary = ['W']
ary << ary.last.succ until ary.last == 'AH'

ary #=> ["W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH"]

But with the wrong values, it can easily become an infinite loop.

---

For a more robust solution, you could write a custom class:

class Column
  attr_reader :name

  def initialize(name)
    raise ArgumentError if name =~ /[^A-Z]/
    @name = name
  end

  def <=>(other)
    [name.length, name] <=> [other.name.length, other.name]
  end

  def succ
    Column.new(name.succ)
  end
end

It basically just wraps the column name, but it also takes the name's length into account:

[name.length, name] <=> [other.name.length, other.name]

This means that longer names come after shorter ones. Names with the same length are compared lexicographically.

This allows you to generate the wanted sequences:

r = Column.new('W')..Column.new('AH')
r.map(&:name)
#=> ["W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH"]