Number to alpha letter

[ghotrapa]

Hi,

Is there an out of box utility to convert a number to an alphabet
letter, like a to_alpha()?

I'm trying to write the content of an array to a row in excel worksheet.

Thanks,
Parv

 

[rabbitblue]

Is there an out of box utility to convert a number to an alphabet
letter, like a to_alpha()?

I'm trying to write the content of an array to a row in excel worksheet.

65.chr # 'A'

 

[Phrogz]

Is there an out of box utility to convert a number to an alphabet
letter, like a to_alpha()?

I'm trying to write the content of an array to a row in excel worksheet.

irb(main):002:1* 1.upto( 30 ) do |col_num|
irb(main):003:2* col = "A"
irb(main):004:2> (col_num-1).times{ col = col.succ }
irb(main):005:2> puts "#{col}#{row_num}"
irb(main):006:2> end
irb(main):007:1> end
A1
B1
C1
D1
E1
F1
G1
H1
I1
J1
K1
L1
M1
N1
O1
P1
Q1
R1
S1
T1
U1
V1
W1
X1
Y1
Z1
AA1
AB1
AC1
AD1
A2
B2
C2
D2
E2
F2
G2
H2
I2
J2
K2
L2
M2
N2
O2
P2
Q2
R2
S2
T2
U2
V2
W2
X2
Y2
Z2
AA2
AB2
AC2
AD2
A3
B3
C3
D3
E3
F3
G3
H3
I3
J3
K3
L3
M3
N3
O3
P3
Q3
R3
S3
T3
U3
V3
W3
X3
Y3
Z3
AA3
AB3
AC3
AD3

 

[Rob]

Hi,

Is there an out of box utility to convert a number to an alphabet
letter, like a to_alpha()?

I'm trying to write the content of an array to a row in excel
worksheet.

Thanks,
Parv

This should get you started. It doesn't exactly work because it goes
(with _ meaning a space) 'y', 'z', 'a_', 'aa', 'ab', but it's the
right kind of idea (although the translation args should probably be
expanded inline). The funkiness arises from the right-most position
acting like base 26, but the other positions acting more like base 27.

class Integer
def to_column
self.to_s(27).tr((('0'..'9').to_a + ('a'..'q').to_a).join(''),
([' ']+('a'..'z').to_a).join(''))
end
end


You could either ignore the issue if you only had to deal with 26 or
fewer columns:
('a'..'z').to_a[self-1]

or split the value (which when I started to write out the pseudo-ruby
looked so easy that I fixed my fence-post error and made it work):

class Integer
def to_column
return 'a' if zero?
upper, lower = self.divmod 26
unless upper.zero?
column = (upper - 1).to_column
else
column = ''
end
column << (?a + lower).chr
end
end

I think I'll have to tuck that away somewhere that I'll be able to
find it again.

-Rob

Rob Biedenharn http://agileconsultingllc.com
(e-mail address removed)

 

[ghotrapa]

65.chr # 'A'

Thanks Daniel. I can make this work for me.

Parv

 

[bbiker]

Thanks Daniel. I can make this work for me.

Parv

Are you on Windows? .. it is fairly easy to copy the contents on an
array to an Excel using win32ole.

An alternate method, change the array into in a csv file either with a
script or with csv/fastercsv. You can then open the file with Excel.

There's also a gem 'roo' which can create and write an excel sheet on
any platform. My understanding is that it does not support formulas,
however this does not seem to be your requirement.

By the way, I don't understand why you need to transform numbers to
characters in order to copy an array to an excel sheet.

If by chance, you want to change numeric column notation to alpha
column notation, then I have a ruby methods that do the conversions...
num2alpha and alpha2num. You can also use numeric notation (RC) as
well as alpha notation.

In Excel, rows and columns are 1-based, not 0-based as in an array.
This can be a gotcha!

Let me know if this is what you need? either on line or off line.

(e-mail address removed)