problem when parsing files using different method

[pomoxp]

Hello

I have an issue when I try to parse 2 files using different methods
like this :
a) parse first file with a CellHandler
b) parse second file without specifying CellHandler

The problem is that the second file is parsed using the CellHandler I
specified for the first parser.
I am using ActivePerl-5.10 (1004) on MSWin32
and the module Spreadsheet::ParseExcel is $VERSION = '0.44';

I am not using the last version of this module but when I read the
release notes, I think the problem I encounter is not fixed or
explained.


Regards
Pascal
-----------------------------------------------------

## PARSE first file
$parser1 = Spreadsheet::ParseExcel->new(
CellHandler => \&cell_handler_conversion_file,
NotSetCell => 1
);
$workbook1 = $parser1->Parse($file) or die "could not parse excel
file";

## PARSE second file
$parser2 = Spreadsheet::ParseExcel->new();
$workbook2 = $parser2->Parse(File::Spec->catfile($dir,$file));
for my $worksheet ( $workbook2->worksheets() ) {
my ( $row_min, $row_max ) = $worksheet->row_range();
my ( $col_min, $col_max ) = $worksheet->col_range();
ROW: for my $row ( $row_min+2 .. $row_max ) {
COL: for my $col ( $col_min .. $col_max ) {
my $cell = $worksheet->get_cell( $row, $col );
next unless $cell;
print "LINE ",$cell->value(),"\n";
}
}
}

[jmcnamara]

On Feb 12, 4:51 pm, pomoxp <pom...@gmail.com> wrote:
> Hello
>
> I have an issue when I try to parse 2 files using different methods
> like this :
> a) parse first file with a CellHandler
> b) parse second file without specifying CellHandler

Hi Pascal,

I wasn't aware of it but that is definitely a bug.

The CellHandler sub ref is stored in a package/class variable so it is
reused between parsers!!

You can work around it for now by changing

my $_CellHandler;
to
our $_CellHandler;

is ParseExcel.pm and then resetting it in your program as follows:

$Spreadsheet::ParseExcel::_CellHandler = undef;

Here is a complete example based on yours:

#!/usr/bin/perl

use strict;
use warnings;

use Spreadsheet::ParseExcel;

my $parser1 = Spreadsheet::ParseExcel->new(

CellHandler => \&cell_handler_conversion_file,
NotSetCell => 1
);

my $workbook1 = $parser1->Parse('Book1.xls')

or die "could not parse excel file";

# Add this.
$Spreadsheet::ParseExcel::_CellHandler = undef;

## PARSE second file
my $parser2 = Spreadsheet::ParseExcel->new();
my $workbook2 = $parser2->Parse('Book2.xls');

for my $worksheet ( $workbook2->worksheets() ) {
my ( $row_min, $row_max ) = $worksheet->row_range();
my ( $col_min, $col_max ) = $worksheet->col_range();

ROW: for my $row ( $row_min + 2 .. $row_max ) {

COL: for my $col ( $col_min .. $col_max ) {
my $cell = $worksheet->get_cell( $row, $col );
next unless $cell;
print "LINE ", $cell->value(), "\n";
}
}
}

sub cell_handler_conversion_file {

my $workbook = $_[0];
my $sheet_index = $_[1];
my $row = $_[2];
my $col = $_[3];
my $cell = $_[4];

print "Cell handler: ", $cell->unformatted(), "\n";

}
__END__

John.
--

[pomoxp]

Hello John

I changed the code "ParseExcel.pm"
our $_CellHandler;
our $_NotSetCell;

and then from my script I added :

$Spreadsheet::ParseExcel::_CellHandler = undef;

$Spreadsheet::ParseExcel::_NotSetCell = undef;

and it works, thank you.

Pascal

[jmcnamara]

On Feb 13, 7:57 am, pomoxp <pom...@gmail.com> wrote:
> Hello John
>
> I changed the code "ParseExcel.pm"
>   our $_CellHandler;
>   our $_NotSetCell;
>
> and then from my script I added :
>   $Spreadsheet::ParseExcel::_CellHandler = undef;
>   $Spreadsheet::ParseExcel::_NotSetCell  = undef;
>

Hi Pascal,

I've raised a tracker for this and I'll fix it in the next release.

https://rt.cpan.org/Public/Bug/Display.html?id=43250

John.
--