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

## ···

--

Posted via http://www.ruby-forum.com/.

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

--

Posted via http://www.ruby-forum.com/.

Parv G. wrote:

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'

--

Posted via http://www.ruby-forum.com/.

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

On Nov 1, 10:24 am, ghotr...@yahoo.com wrote:

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.

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

Rob@AgileConsultingLLC.com

On Nov 1, 2007, at 12:24 PM, Parv G. wrote:

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

Daniel Waite wrote:

65.chr # 'A'

Thanks Daniel. I can make this work for me.

Parv

--

Posted via http://www.ruby-forum.com/.

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.

On Nov 1, 1:39 pm, ghotr...@yahoo.com wrote:

Daniel Waite wrote:

> 65.chr # 'A'Thanks Daniel. I can make this work for me.

Parv

--

Posted viahttp://www.ruby-forum.com/.