Ruby and Excel problem (formatting)


#1

hey, i create an excel file just be putting a simple rhtml to the
browser with
@headers[“Content-type”] = “application/vnd.ms-excel” but i have a small
problem.

i also put pinnumbers in the excel file like this:

Lastname Firstname Phone Phone pin<% for employee in @employees %>
<%= employee.last_name %> <%= employee.first_name %> <%= employee.phone
%> <%=
employee.phone_pin.rjust(4,“0”) %><% end %>

Lastname Firstname Phone Phone pin
Brutyn A. xxxxxxxxxx 21
Brutyn G. xxxxxxxxxx 6641
Brutyn L. xxxxxxxxxx 1237
Brutyn N. xxxxxxxxxx 1134

this is my problem 21 should be 0021

i also have tried to_s

and '<%= employee.phone_pin.rjust(4,“0”) %>
but then i get '0021 and '6641
which is not beautiful

can anyone help me with some cell format or something or some handy
trick

thx in advance


#2

Brutyn nick wrote:

Brutyn N. xxxxxxxxxx 1134

this is my problem 21 should be 0021

My hope is that someone out there will contradict me, but everything
I’ve seen says that if you open a plain text file in Excel and there’s a
column that contains only numbers, the values in that column will come
into Excel with all leading zeros stripped. And once it’s in Excel
there’s no way to get the leading zeros back, without turning the file
into a native Excel document and setting a custom number format on that
column/cell. Simply changing the column to be “Text”, which is supposed
to leave the data as entered, doesn’t bring back the leading zeros.

Again, I’m hoping that someone can tell me I’m wrong, but this is
something I’ve been wrestling with for years with Excel and I’ve never
seen a solution…

-Brian


#3

Brian V. Hughes <brianvh@…> writes:

something I’ve been wrestling with for years with Excel and I’ve never
seen a solution…

-Brian

Brian,

When importing a text file into excel there is a panel in the import
wizard
that lets you set the column type. Changing it from ‘general’ to ‘text’
will
stop the suppression of the leading zeroes, but your column type will be
a text
type, not a numerirc type.

You can click on the format/cell option in the tool bar, click number
and fill
in the number of zeroes you want the width of your cell to be. This will
keep
the number, and even zeroes will be just as wide as any other numbers.

There is also a trick with concatenate and RIGHT in a formula, but I
don’t have
a copy of excel or windows, so I can’t remember how to do that.

It has been many years since I worked with excel, but you can probably
set
these attributes with vba if you are importing with a vba script


#4

Again, I’m hoping that someone can tell me I’m wrong, but this is
something I’ve been wrestling with for years with Excel and I’ve never
seen a solution…

i found a solution

=TEKST(<%= employee.phone_pin %>;“0000”)

then i get 0021

for u engligh guys tekst() => text() i think

thanks by the way