CODEWITHSUNDEEP
perlexcel
pugnator
pugnator

Reputation: 715

perl excel parse and modify the same worksheet

I need to compare my XLS file with another one and modify the first (some cells) one accordingly. I need to do it crossplatform so I use Spreadsheet::ParseExcel::SaveParser

Here is the subroutine I have so far

sub find_occurrences
{
my $xlsname   = $_[0];
my $pattern   = $_[1];
my $parser    = Spreadsheet::ParseExcel::SaveParser->new();
my $workbook  = $parser->parse($xlsname);
my $worksheet = $workbook->worksheet(0) || die("$@$!");
my ( $row_min, $row_max ) = $worksheet->row_range();   
for my $row ( 0 .. $row_max ) {         
    $cell = $worksheet->get_cell( $row, 1 );
    next if (!$cell);    
    if($cell->value() =~ m/$pattern/i )
    {
        print "Order found ",$cell->value(),"\n";   
        $worksheet->AddCell( $row, 1, "Shop" );     
    }       

}
}

http://search.cpan.org/~jmcnamara/Spreadsheet-ParseExcel/lib/Spreadsheet/ParseExcel/SaveParser.pm

Seems like example, bu I got this error: Can't locate object method "AddCell" via package "Spreadsheet::ParseExcel::Worksheet" at ./test.pl line 153.

It is this one line

$worksheet->AddCell( $row, 1, "Shop" );

I tried even add

use Spreadsheet::ParseExcel::Worksheet;
use Spreadsheet::ParseExcel::Workbook;

I'm new to perl and i wasn't able to google something helpfull.

Upvotes: 0

Views: 3692

Answers (1)

smartmeta
smartmeta

Reputation: 1149

I guess you mixed up some code from Spreadsheet::ParseExcel and Spreadsheet::ParseExcel::SaveParser

The problem are the parse(), Parse() methods. Unfortuntly the interface is not very transparant. If you call

$parser->parse(...)

you get an Spreadsheet::ParseExcel::Workbook object, but if you use

$parser->Parse(...)

you get an use Spreadsheet::ParseExcel::SaveParser::Workbook object.

That is what you need!.

Here is my example:

#!/usr/bin/perl
use strict;
use warnings;

use Spreadsheet::ParseExcel::SaveParser;

find_occurrences ('Mappe1.xls', qr/.*/);

sub find_occurrences {
    my ($xlsname, $pattern) = @_;

    my $parser    = Spreadsheet::ParseExcel::SaveParser->new();
    my $workbook  = $parser->Parse($xlsname);
    my $worksheet = $workbook->worksheet(0) || die("$@$!");

    print "Workbook is a :". ref($workbook). "\n"; 

    my ( $row_min, $row_max ) = $worksheet->row_range();   
    for my $row ( 0 .. $row_max ) {         
        my $cell = $worksheet->get_cell( $row, 1 );
        next if (!$cell);    
        if($cell->value() =~ m/$pattern/i )
        {
            print "Order found ",$cell->value(),"\n";   
            $worksheet->AddCell($row, 1, "Shop" );     
        }       

    }
}

Upvotes: 1

Related Questions