Forum: Ruby Float in Spreadsheet

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.
Jim B. (Guest)
on 2009-02-10 18:15
Hi,
I am using the ruby spreadsheet gem to create Excel files from within
Ruby.
Could someone tell me how to write a decimal value (in this case 34.00)
to an Excel cell.
I have looked in the documentation (http://spreadsheet.rubyforge.org/)
and found the method write_number.
I guess this will do what i want, but I cannot for the life of me get it
to work.
Thanks for any help.
Jim B. (Guest)
on 2009-02-12 10:07
Can no one help me?
Could someone at least tell me if there is a place (forum or mailing
list or whatever) where I can ask for help.
Thanks.
Jayce M. (Guest)
on 2009-02-12 10:11
(Received via mailing list)
Sometimes some things don't get responses, I guess. Sorry. D:

What's your problem?

--------------------------------------------------
From: "Jim B." <removed_email_address@domain.invalid>
Sent: Thursday, February 12, 2009 12:06 AM
Newsgroups: comp.lang.ruby
To: "ruby-talk ML" <removed_email_address@domain.invalid>
Subject: Re: Float in Spreadsheet
Jim B. (Guest)
on 2009-02-12 10:28
> Sometimes some things don't get responses, I guess. Sorry. D:
No worries, it's just that spent ages writing this app and everything
works fine except this one thing, so it would be a shame if I couldn't
sort it out.

All I want to do is write a decimal to a cell in a spreadsheet generated
using the ruby spreadsheet gem.

Currently I'm writing:
@invoice.row(32+i).push '',i, groups[i-1],'',groups_ue[i-1], 34, tot

Instead of 34 I want to write 34.00
However when I write:
@invoice.row(32+i).push '',i, groups[i-1],'',groups_ue[i-1], 34.00, tot

The program still writes 34 (without the decimal) to the cell.

Cheers for your help.
Lars C. (Guest)
on 2009-02-12 10:40
(Received via mailing list)
On Feb 12, 9:26 am, Jim B. <removed_email_address@domain.invalid> wrote:
> The program still writes 34 (without the decimal) to the cell.

Excel treats 34 and 34.00 as the same number (and Ruby treat 34.0 and
34.00 the same).

fmt = Spreadsheet::Format.new(:number_format => '0.00')
sheet1.row(1).set_format(1,fmt)
Victor G. (Guest)
on 2009-02-12 10:41
(Received via mailing list)
This looks like a formatting problem.  I don't have an answer for you,
but
if the format for that cell has no trailing zeros, then this would be
the
affect you are seeing.
I hope it helps... Sorry, I have not confirmed this.

Warmest Regards,

Victor H. Goff III
Hannes W. (Guest)
on 2009-02-12 10:49
(Received via mailing list)
Jim, Victor,

On Thu, Feb 12, 2009 at 9:39 AM, Victor H. Goff III
<removed_email_address@domain.invalid> wrote:
> This looks like a formatting problem.  I don't have an answer for you, but
> if the format for that cell has no trailing zeros, then this would be the
> affect you are seeing.

That's exactly right. Add a Format:

fmt = Spreadsheet::Format.new :number_format => '0.0'
row = @invoice.row(32+i)
row.push '',i, groups[i-1],'',groups_ue[i-1], 34.00, tot
row.set_format 5, fmt

(There is also a Column Default-Format, but that seems not to work atm
- will fix asap.)

hth
Hannes
Jim B. (Guest)
on 2009-02-12 12:01
Thanks ever so much for the replies.
The formatting now works perfectly.
I guess that the moral of the story is that Thursday morning is a good
time to post questions :-)
Thanks again.
Jim
Rick D. (Guest)
on 2009-02-12 14:49
(Received via mailing list)
On Thu, Feb 12, 2009 at 5:00 AM, Jim B. <removed_email_address@domain.invalid> 
wrote:

>
> I guess that the moral of the story is that Thursday morning is a good
> time to post questions :-)
>

Might I suggest is that the real moral is that the community is much
more
able, and therefore willing, to answer questions which provide enough
context, like what you've tried and wnat's not working, than  just
something
like "I read the documentation and I can't get it to work."

http://catb.org/~esr/faqs/smart-questions.html#beprecise

---
Rick DeNatale

Blog: http://talklikeaduck.denhaven2.com/
Twitter: http://twitter.com/RickDeNatale
Jim B. (Guest)
on 2009-02-12 14:55
> Might I suggest is that the real moral is that the community is much
> more
> able, and therefore willing, to answer questions which provide enough
> context, like what you've tried and wnat's not working, than  just
> something
> like "I read the documentation and I can't get it to work."

Uh, get off your moral high horse Rick.
I can never understand people who make posts like this.
I tried for ages to get this to work, read the documentation, surfed the
net looking for solutions and essentially drew a blank before posting
here.
Maybe my initial question wasn't precise enough for you, but apart from
that I don't see what more I could have done.
Rick D. (Guest)
on 2009-02-12 17:31
(Received via mailing list)
On Thu, Feb 12, 2009 at 7:53 AM, Jim B. <removed_email_address@domain.invalid> 
wrote:

> net looking for solutions and essentially drew a blank before posting
> here.
> Maybe my initial question wasn't precise enough for you, but apart from
> that I don't see what more I could have done.
>

All I'm suggesting is that I see lots of questions like your initial
post
which don't get replies because there's not enough information to even
guess
what's wrong.  Not just here but all over the internet.

It was only after your third post in which you actually showed some
code,
that you got some help.

If you'd done that little bit more initially, you might have gotten your
answer right away.

None of us who try to help here get paid for it as far as I know.

If you want to take a suggestion as to how to get better results in the
future as a personal affront, it's no skin off my nose.

--
Rick DeNatale

Blog: http://talklikeaduck.denhaven2.com/
Twitter: http://twitter.com/RickDeNatale
Jim B. (Guest)
on 2009-02-12 18:42
> If you want to take a suggestion as to how to get better results in the
> future as a personal affront, it's no skin off my nose.

And if all you were trying to do was to make a suggestion (and not sound
like a sacrcastic tw@), then maybe you should work on your social
skills.
Jim B. (Guest)
on 2009-02-13 09:40
Alright, I had a couple of emails overnight agreeing (quite politely)
with Rick.

Rick, you're right. I was wrong to react like I did.
I will try to make my questions more precise in future.
Thanks for your suggestion.

Laters,
Jim
Serguei C. (Guest)
on 2010-03-10 16:02
Lars C. wrote:
> On Feb 12, 9:26�am, Jim B. <removed_email_address@domain.invalid> wrote:
>> The program still writes 34 (without the decimal) to the cell.
>
> Excel treats 34 and 34.00 as the same number (and Ruby treat 34.0 and
> 34.00 the same).
>
> fmt = Spreadsheet::Format.new(:number_format => '0.00')
> sheet1.row(1).set_format(1,fmt)

For me, te solution:

nb_format = Spreadsheet::Format.new  :number_format => '0,000'
sheet1.row(4).set_format(0, nb_format)

works fine. If you want to add decimals, just do

nb_format = Spreadsheet::Format.new  :number_format => '0,000.00'
sheet1.row(4).set_format(0, nb_format)

Now the problem is that if a number to be formatted is less than 1000,
you'll get a new formatted value like that:

0,518.42

The question - how to avoid "unused" leading zeros?
Thanks
Serguei C. (Guest)
on 2010-03-10 16:10
I found a solution. To avoid leading zeros, you have to set the frmat as
follows:

nb_format = Spreadsheet::Format.new  :number_format => '#,###'
sheet1.row(4).set_format(0, nb_format)

What is important - the rounding is made out of the box (1500.78 will be
formatted as 1,501, 0 will be formatted as empty cell) so no need to
write something else :)
This topic is locked and can not be replied to.