Handling of Infinity in write_number()

[jmcnamara]

From #72 by @stevencothren:

I came across this when calling write_row() and one of the fields is a Secondary Number (like Apt #). Some of the information is in the format of '2E310' which write() treats as a scientific number, but it is too big for Perl so write_number() ends up making it an 'Infinity'.

Excel::Writer::XLSX should have a check for infinite numbers so that they aren't written to an Excel file.

[aaronpriven]

This writer has the same problem I have, which is that I have strings that the regular expression in write() thinks are numeric, but are actually supposed to be strings. (One of mine is "1E14", an internal location code)

To solve this, kind of, I wrote write_row_string and write_col_string routines, which basically copied and pasted code from write_row and write_col, except that they call write_string instead of write.

It was pretty easy to do and solved the problem for me.

[jmcnamara]

To solve this, kind of, I wrote write_row_string and write_col_string routines, which basically copied and pasted code from write_row and write_col, except that they call write_string instead of write.

In general this is the approach I would recommend. If write() or write_row() doesn't do what you want you should just unroll it into specific write_string() or write_number() or write_whatever() methods.

As a more advanced approach it is possible to change the behaviour of write(), and thus write_row() with a "write handler" to your program. For example:

#!/usr/bin/perl

use strict;
use warnings;
use Excel::Writer::XLSX;


my $workbook  = Excel::Writer::XLSX->new( 'write_handler5.xlsx' );
my $worksheet = $workbook->add_worksheet();


# Add a handler to match any numbers in order to check for and handle
# infinity.
$worksheet->add_write_handler( qr[\d], \&write_with_infinity );


# The following function is used by write() to pre-process any the data when a
# match is found. If it finds something that looks like a number but evaluates
# to infinity it write it as a string.
sub write_with_infinity {

    my $worksheet = shift;
    my @args      = @_;
    my $token     = $args[2];

    # Check if token looks like a number, in the same way as write().
    if ( $token =~ /^([+-]?)(?=[0-9]|\.[0-9])[0-9]*(\.[0-9]*)?([Ee]([+-]?[0-9]+))?$/ ) {

	# Check for infinity.
	$token = $token + 0;

	if ($token =~ /Inf/) {

	    # Write the value as a string instead of a number.
	    return $worksheet->write_string( @args );
	}
    }

    # Reject the match and return control to write()
    return undef;
}

$worksheet->set_column('A:A', 35);

# Write some numbers until we reach infinity.
$worksheet->write( 'A1',  '2E3'    );
$worksheet->write( 'A2',  '2E30'   );
$worksheet->write( 'A3',  '2E300'  );
$worksheet->write( 'A4',  '2E3000' );
$worksheet->write( 'A5', '-2E3'    );
$worksheet->write( 'A6', '-2E30'   );
$worksheet->write( 'A7', '-2E300'  );
$worksheet->write( 'A8', '-2E3000' );


$workbook->close();

__END__

Output. Notice the strings to the left and the numbers to the right:

[jmcnamara]

Closing as "won't fix". Use the workaround suggested above, if needed.

[jmcnamara]

Re-opening due to other bug reports.