Create an array of letters starting after A and ending after Z, i.e. (from B to AC)
I am working with Roo Gem and wanted to extract data from a spreadsheet based on standard A1 syntax.
I have columns in a spreadsheet outside of Z, so Excel does all the column positions AA, AB, AC.
I want to create an array for W columns in AH.
Ruby doesn't seem to like it when the top range goes past Z, but didn't start with A ??
Any ideas how to ("B".."AC").to_a
not get[]
Here is the main problem with 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
#=> []
source to share
how about this?
("A".."AC").to_a.drop(1)
you can remove any amount elements
you like and it just includes 1 array and 1 array.
An integer can be replaced with someting, which returns the position of a 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 the class Range
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"]
source to share
Use Kernel#loop
to create an empty array. The loop is interrupted when the current value is equal to the second parameter. To return the newly constructed 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"]
source to share
The mathematical answer would be:
A => AH = (A => W) + (W => AH)
so W => AH = (A => AH) - (A => W)
Programmatic answer:
("A".."AH").to_a - ("A"..."W").to_a
#=> ["W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH"]
...
in the second range makes it exceptional, i.e. without the "W".
More general answer:
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"]
source to share
Ruby String#succ
increases letters the way Excel increases column names:
'Z'.succ #=> "AA"
So, if you know the destination value is available through 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 wrong values, it can easily become an endless loop.
For a more robust solution, you can write your own 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 respects the name length
:
[name.length, name] <=> [other.name.length, other.name]
This means that longer names appear after shorter ones. Names with the same length are compared lexicographically.
This allows you to generate the sequences you want:
r = Column.new('W')..Column.new('AH')
r.map(&:name)
#=> ["W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH"]
source to share