Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask

Problem with OLE Excel

by Anonymous Monk
on Jan 07, 2010 at 22:13 UTC ( #816180=perlquestion: print w/replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,

I am writing a simple script, where I am counting a list of big numbers andtotaling them. Total count I am writing into excel file (using win32::ole for this).

The Totaling number is 4264859333. as you can see it's 10 digit. Instead of seeing this number in excel, I am seeing a negative number -30107963.

Have any one faced this before. Please help me to solve this problem. Thanks

Replies are listed 'Best First'.
Re: Problem with OLE Excel
by ikegami (Pope) on Jan 07, 2010 at 22:20 UTC
    Perl stored the number as an unsigned 2's complement, but something along the way treated it as an signed 2's complement
    -30107963 + 2**32 = 4264859333

    I don't know who exactly is at fault, and what can be done to fix it.

    Update: Forcing the number to be stored as a float internally might do the trick. The following does that:

    $x = $x + 0.5 - 0.5;

      He He Ho Ho Hi Hi ... It worked. force Storing it as float. Thank You Teacher :)

      Thanks ikegami for the suggestion. One more thing just point out .. is that ... data is getting populated properly but at the time of formating the sheet it's gettig changed to negative number.

      Hmmm ... really typical behavior :)

Re: Problem with OLE Excel
by gam3 (Curate) on Jan 08, 2010 at 00:04 UTC
    If you need even bigger numbers look at Math::BigInt.
    -- gam3
    A picture is worth a thousand words, but takes 200K.

      I doubt OLE speaks in BigInts... Besides, at ten digits, he's nowhere near the point where they'd be needed. Perl and OLE can accurately represent with numbers up to 16 digits long, and they can represent numbers up to 308 digits long (with 53 bits of precision).


      Yes, I agree with ikegami. as far I know Math::BigInt is for very big numbers. I don't need them as in my case highest is 11 digit number I have.

      anyways, thanks for your reply and suggestion 'gam3'.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://816180]
Approved by Corion
[holli]: this really really should not be executed as Javascript
erix's head is getting content indisposed
[holli]: maybe try a GET instead?
[moritz]: I must say, when I was very active on perlmonks, I got really used to the markup and all the workflows
[moritz]: and then I didn't post for a while, and then tried to answer a question. It nearly drove me nuts
[moritz]: when you try to quote part of a question, you have to know/guess/reverse -engineer what kind of markup they used
[holli]: see? a/XpF4b
[moritz]: and stuff it into a <blockquote>...</ blockquote>, which is, like, not at all bulky
[moritz]: specially if you're used to markdown
[holli]: there probably is some nodelet hack that lets you use markdown :)

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (10)
As of 2017-11-20 19:19 GMT
Find Nodes?
    Voting Booth?
    In order to be able to say "I know Perl", you must have:

    Results (291 votes). Check out past polls.