3

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?

simbabque
  • 53,749
  • 8
  • 73
  • 136
Chitransh Agarwal
  • 83
  • 6
  • Could you please include your error as a text instead of image? – AbhiNickz Sep 17 '18 at 08:28
  • @Abhi: The error is in a dialogue box. It's hard to extract the text from there without simply typing it in. – Borodin Sep 17 '18 at 08:32
  • What debugging have you done? Start by writing just the header with no data. Then you should try writing just a few lines of data instead of the full quarter million. There may be a specific row of data from the database that is causing the problem, and you may need to search for it. – Borodin Sep 17 '18 at 08:42
  • @AbhiNickz I added the error message as an alt tag to the image. – simbabque Sep 17 '18 at 11:26
  • The `while (my $sqlStatement = ) {` bit of your code is weird. I assume you just left out the query because you think it's not important, but why would it be in a loop condition? That makes no sense. Please post code that compiles. – simbabque Sep 17 '18 at 11:27
  • In the olden days Excel had limits on the number of rows. Is 250k too many for your version? It think the limit was 65k up to version 2003 or 2007. Does it work if you bail out the `while` loop after just a few rows? Perhaps test this as the last line inside while: `last if $count>10;`. You could also check out https://metacpan.org/pod/Spreadsheet::WriteExcel which I have used successfully a few times. – Kjetil S. Sep 17 '18 at 17:14
  • @Borodin - I found out a thread which talked about this issue, it arises when the writer thinks of a value as numeric when it is explicitly a string, for example "1E43" this value is interpreted by excel as a number and converter to infinity. Also there are a bunch of values that shows this behaviour rest all of them are written properly – Chitransh Agarwal Sep 18 '18 at 06:43
  • @Chitransh: If you have a solution to your question then you should post an answer, perhaps including a link to the information you found, but also describing what you did to fix the problem in your situation. – Borodin Sep 18 '18 at 09:36
  • @Borodin - No i don't have a solution i just found that out, and since it was another website, and since i am new here and even to perl i don't know how i can use write_string subroutine to write an entire row – Chitransh Agarwal Sep 18 '18 at 09:51
  • Also below is the thread from where i got the info. https://github.com/jmcnamara/excel-writer-xlsx/issues/73 – Chitransh Agarwal Sep 18 '18 at 10:56
  • @Chitransh: If you don't understand the [documentation for `write_string`](https://metacpan.org/pod/Excel::Writer::XLSX#write_string(-$row,-$column,-$string,-$format-)) then you should open a new question and ask for help. But please at least try something first: it isn't hard, and your question won't be well-received if you have made no effort of your own. – Borodin Sep 18 '18 at 11:05
  • @Chitransh, once I have recieved the same error due to ' present in the worksheet name, Because you are creating the xlsx file from the script so it allows that, but results in error when you open it from ms excel. So technically there could be no of problems with you xlsx file. – AbhiNickz Sep 18 '18 at 18:02

1 Answers1

4

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;
}
Chitransh Agarwal
  • 83
  • 6