Integer overflow in Excel using Win32::OLE

[niall.macpherson]

I am using Win32::OLE to output data to an Excel Spreadsheet.

However I am finding that all integers between 2e31 and 2e32 are
displayed incorrectly. I get a negative number (can't remember whether
it is 1s complement or 2s complement )

Printing the data using Data:umper results in the correct values
being shown.

The problem disappears when the value reaches 2e32.

Sample code

##---------------------------------------------------------------------
use strict;
use warnings;
use Win32::OLE;
use Data:umper;

##---------------------------------------------------------------------
sub ConnectToExcel
{
my $excel_conn;
eval
{
## check if already running
$excel_conn = Win32::OLE->GetActiveObject('Excel.Application')
};
die "Excel not installed" if $@;

unless (defined $excel_conn)
{
## if not , run it
## TODO Check what the $_[0]->Quit is about
$excel_conn = Win32::OLE->new('Excel.Application', sub
{$_[0]->Quit;})
or die "Failed to start Excel";
}
return($excel_conn);
}
##---------------------------------------------------------------------
my $excel_conn = ConnectToExcel();
my $work_book = $excel_conn->Workbooks->Add;
my $work_sheet = $work_book->Worksheets(1);

my $data = 1;
for(my $lcount = 29; $lcount <= 33; $lcount++)
{
my $data = (2 ** $lcount);
print Dumper "Adding $data ";
$work_sheet->Cells($lcount - 28, 1)->{Value} = $lcount;
$work_sheet->Cells($lcount - 28, 2)->{Value} = $data;
$work_sheet->Cells($lcount - 28, 3)->{Value} = $data + 1;
$work_sheet->Cells($lcount - 28, 4)->{Value} = $data - 1;
}
exit(0);

#------------------------------------------------------------------------------------------------

Output from Data:umper

C:\develop\NiallPerlScripts>excel_bigint.pl
$VAR1 = 'Adding 536870912 ';
$VAR1 = 'Adding 1073741824 ';
$VAR1 = 'Adding 2147483648 ';
$VAR1 = 'Adding 4294967296 ';
$VAR1 = 'Adding 8589934592 ';

Values from the excel spreadsheet

29 536870912 536870913 536870911
30 1073741824 1073741825 1073741823
31 -2147483648 -2147483647 2147483647
32 4294967296 4294967297 4294967295
33 8589934592 8589934593 8589934591


Note the values for (2**31) and (2**31)+1 are incorrect. However
(2**31)-1 and (2**32) are displayed correctly.

Am I missing something I need to use here ? (Math::Bigint possibly - I
have seen it referred to before but never used it), or is it a bug in
the Win32::OLE module ?

Thanks

 

[A. Sinan Unur]

(e-mail address removed) wrote in

I am using Win32::OLE to output data to an Excel Spreadsheet.

However I am finding that all integers between 2e31 and 2e32 are
displayed incorrectly. I get a negative number (can't remember whether
it is 1s complement or 2s complement )

On two's complement machines, signed integer of size n are recognized by
the MSB being set.

So, for an

8 bit signed int, the value 0x80 (128) is -1
16 bit signed int, the value 0x8000 (32768) is -1
32 bit signed int, the value 0x80000000 (2147483648) is -1

Printing the data using Data:umper

Yeah, about that, what is the point of using Data:umper to print a
simple string?

##---------------------------------------------------------------------

use strict;
use warnings;

Thank you for posting a small self-contained script which is strict and
warnings clean. Much appreciated.

my $data = 1;
for(my $lcount = 29; $lcount <= 33; $lcount++)
{
my $data = (2 ** $lcount);
print Dumper "Adding $data ";
$work_sheet->Cells($lcount - 28, 1)->{Value} = $lcount;
$work_sheet->Cells($lcount - 28, 2)->{Value} = $data;
$work_sheet->Cells($lcount - 28, 3)->{Value} = $data + 1;
$work_sheet->Cells($lcount - 28, 4)->{Value} = $data - 1;
}
exit(0);
....
Output from Data:umper

C:\develop\NiallPerlScripts>excel_bigint.pl
$VAR1 = 'Adding 536870912 ';
$VAR1 = 'Adding 1073741824 ';
$VAR1 = 'Adding 2147483648 ';
$VAR1 = 'Adding 4294967296 ';
$VAR1 = 'Adding 8589934592 ';

Values from the excel spreadsheet

29 536870912 536870913 536870911
30 1073741824 1073741825 1073741823
31 -2147483648 -2147483647 2147483647
32 4294967296 4294967297 4294967295
33 8589934592 8589934593 8589934591


Note the values for (2**31) and (2**31)+1 are incorrect. However
(2**31)-1 and (2**32) are displayed correctly.
....

is it a bug in the Win32::OLE module ?

I would not regard it as a bug. It is a boundary condition with 32-bit
integers. Simply sending the numbers to Excel as strings yields the
correct display (after setting the cell format to Number with 0 decimal
digits):

$excel->{Visible} = 1;

my $data = 1;

for(my $lcount = 29; $lcount <= 33; $lcount++) {
my $data = (2 ** $lcount);
warn sprintf "%s\t%s\t%s\n", $data, $data + 1, $data - 1;
$sheet->Cells($lcount - 28, 1)->{Value} = "$lcount";
$sheet->Cells($lcount - 28, 2)->{Value} = "$data";
$sheet->Cells($lcount - 28, 3)->{Value} = "@{ [ $data + 1 ] }";
$sheet->Cells($lcount - 28, 4)->{Value} = "@{ [ $data - 1 ] }";
}

D:\Home\asu1\UseNet\clpmisc> bbb
536870912 536870913 536870911
1073741824 1073741825 1073741823
2147483648 2147483649 2147483647
4294967296 4294967297 4294967295
8589934592 8589934593 8589934591

From Excel:

29 536870912 536870913 536870911
30 1073741824 1073741825 1073741823
31 2147483648 2147483649 2147483647
32 4294967296 4294967297 4294967295
33 8589934592 8589934593 8589934591

Sinan
--
--
A. Sinan Unur <[email protected]>
(remove .invalid and reverse each component for email address)

comp.lang.perl.misc guidelines on the WWW:
http://augustmail.com/~tadmc/clpmisc/clpmisc_guidelines.html

 

[Dr.Ruud]

(e-mail address removed) schreef:


(reformatted by me)

$ws->Cells($r, 2)->{Value} = $data;
$ws->Cells($r, 3)->{Value} = $data + 1;
$ws->Cells($r, 4)->{Value} = $data - 1;

$ws->Cells($r, 2)->{Value} = num($data ) ;
$ws->Cells($r, 3)->{Value} = num($data + 1) ;
$ws->Cells($r, 4)->{Value} = num($data - 1) ;


sub num { (0 + $_[0]) . '.0' }

 

[niall.macpherson]

I would not regard it as a bug. It is a boundary condition with 32-bit
integers. Simply sending the numbers to Excel as strings yields the
correct display (after setting the cell format to Number with 0 decimal
digits):

$excel->{Visible} = 1;

Thanks Sinan. I am from a 'C' background so I sometimes find it a bit
confusing as to where one should convert data types as I am used to
casting everything in 'C/C++'

for(my $lcount = 29; $lcount <= 33; $lcount++) {
my $data = (2 ** $lcount);
warn sprintf "%s\t%s\t%s\n", $data, $data + 1, $data - 1;
$sheet->Cells($lcount - 28, 1)->{Value} = "$lcount";
$sheet->Cells($lcount - 28, 2)->{Value} = "$data";
$sheet->Cells($lcount - 28, 3)->{Value} = "@{ [ $data + 1 ] }";
$sheet->Cells($lcount - 28, 4)->{Value} = "@{ [ $data - 1 ] }";
}> http://augustmail.com/~tadmc/clpmisc/clpmisc_guidelines.html

I understand the $sheet->Cells($lcount - 28, 2)->{Value} = "$data";
forcing the RHS to be evaluated as a string.

However $sheet->Cells($lcount - 28, 4)->{Value} = "@{ [ $data - 1 ]
}"; still has me a bit stumped. I can see that it achieves the required
result but I cannot get my head around why. It looks to me as if the
inner square brackets are creating an anonymous array with a single
element but I don't understand what effect the sutrrounding braces
and the @ have . Could someone please explain ?

Thanks

 

[A. Sinan Unur]

A. Sinan Unur wrote:
....

for(my $lcount = 29; $lcount <= 33; $lcount++) {
my $data = (2 ** $lcount);
warn sprintf "%s\t%s\t%s\n", $data, $data + 1, $data - 1;
$sheet->Cells($lcount - 28, 1)->{Value} = "$lcount";
$sheet->Cells($lcount - 28, 2)->{Value} = "$data";
$sheet->Cells($lcount - 28, 3)->{Value} = "@{ [ $data + 1 ] }";
$sheet->Cells($lcount - 28, 4)->{Value} = "@{ [ $data - 1 ] }";
}> http://augustmail.com/~tadmc/clpmisc/clpmisc_guidelines.html

I understand the $sheet->Cells($lcount - 28, 2)->{Value} = "$data";
forcing the RHS to be evaluated as a string.

However $sheet->Cells($lcount - 28, 4)->{Value} = "@{ [ $data - 1 ]
}"; still has me a bit stumped. I can see that it achieves the
required result but I cannot get my head around why. It looks to me as
if the inner square brackets are creating an anonymous array with a
single element but I don't understand what effect the sutrrounding
braces and the @ have . Could someone please explain ?

It is a fugly method explained in the FAQ:

http://perldoc.perl.org/perlfaq4.html#How-do-I-expand-function-calls-in-a-string?

You might want to replace all those with:

sub stringificator { sprintf '%.0f', $_[0] }

$sheet->Cells($lcount - 28, 1)->{Value} = stringificator( $lcount );
$sheet->Cells($lcount - 28, 2)->{Value} = stringificator( $data );
$sheet->Cells($lcount - 28, 3)->{Value} = stringificator( $data + 1 );
$sheet->Cells($lcount - 28, 4)->{Value} = stringificator( $data - 1 );

Sinan

--
A. Sinan Unur <[email protected]>
(remove .invalid and reverse each component for email address)

comp.lang.perl.misc guidelines on the WWW:
http://augustmail.com/~tadmc/clpmisc/clpmisc_guidelines.html

 

[Ben Morrow]

Quoth (e-mail address removed):

$sheet->Cells($lcount - 28, 4)->{Value} = "@{ [ $data - 1 ] }";

However $sheet->Cells($lcount - 28, 4)->{Value} = "@{ [ $data - 1 ]
}"; still has me a bit stumped. I can see that it achieves the required
result but I cannot get my head around why. It looks to me as if the
inner square brackets are creating an anonymous array with a single
element but I don't understand what effect the sutrrounding braces
and the @ have . Could someone please explain ?

@{ ... } is the array deref operator. It also interpolates in a string.
This means that the [$data - 1] creates a ref to an anon array with the
one element you want, and the @{...} then derefs that array and
interpolates the one element into the string.

I would regard this as an ugly hack, not suitable for real code. I would
write something like

... = '' . ($data - 1);

or arrange to have the correct value in a temporary. I would also put a
comment there, as it's very rare to need to care about representation of
data in Perl and the code's maintainer would likely not realise why it
was necessary here.

Ben