CODEWITHSUNDEEP
excelperlexcel-writer-xlsx
Chitransh Agarwal
Chitransh Agarwal

Reputation: 83

Excel::Writer::XLSX Unreadable content error

I just started using Perl and I am using Excel::Writer::XLSX to query a DB2 database and export the data to an .xlsx file. The data is about 250k rows.

The script is running fine, but when I try to open the Excel file it throws an error and asks to repair the file. Upon repairing some of the data gets replaced by inf.

Error dialog saying "Excel found unreadable content in 'Chk.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes."

Below is a snippet from my code.

while ( my $sqlStatement = ) {

    $mSQL = $dbh->prepare( $sqlStatement )
            or die "Can't prepare $sqlStatement";
    $mSQL->execute()
            or die "Can't execute $sqlStatement";
}

my $workbook = Excel::Writer::XLSX->new( $ARGV[2] );
$workbook->set_tempdir( '/tempDir/' );
$workbook->set_optimization();

my $worksheet = $workbook->add_worksheet();
$worksheet->keep_leading_zeros();

my $row    = 0;
my $column = 0;

my @emptyRow = ();

$worksheet->write_row( $row++, $column, [ @{ $mSQL->{NAME_uc} } ] );
$worksheet->write_row( $row++, $column, [ @emptyRow ] );

while ( my @Row = $mSQL->fetchrow_array ) {
    $worksheet->write_row( $row++, $column, [ @Row ] );    #, $cellFormat);
    $count++;
}

$workbook->close();

Can someone please advise me on this issue?

Upvotes: 3

Views: 725

Answers (1)

Chitransh Agarwal
Chitransh Agarwal

Reputation: 83

Finally i figured it out (Thanks to John McNamara). This was resolved by adding a write handler that uses regular expressions to check if a particular token is being converted to "inf", and if it does, it invokes the write_string subroutine instead of write_row. Below is the code.

#!/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;
}

Upvotes: 4

Related Questions