Forum: Ruby on Rails Ruby and Excel problem (formatting)

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
334ae822566107a2ea2b7a7cb88bd1d6?d=identicon&s=25 brutyn_nick (Guest)
on 2005-12-02 15:43
(Received via mailing list)
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
7c4087d053eb02d099a17d91ba5e33b5?d=identicon&s=25 brianvh (Guest)
on 2005-12-02 16:03
(Received via mailing list)
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
8716cce9a7b721786e5425b8cc1f9951?d=identicon&s=25 sean.seanlynch (Guest)
on 2005-12-02 17:37
(Received via mailing list)
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
334ae822566107a2ea2b7a7cb88bd1d6?d=identicon&s=25 brutyn_nick (Guest)
on 2005-12-02 18:01
(Received via mailing list)
> 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
This topic is locked and can not be replied to.