kiranv has asked for the wisdom of the Perl Monks concerning the following question:

I needed to work with excel files to fill in some details. So I preferred "Spreadsheet::WriteExcel" which has very good interfaces and documentation. Thank you.. Now I had to write an utility which fills in Excel sheet according to my requirement. So whatever functionalities(functions ex: writeCell) required for me are kept in that utility(excelUtil.pm) file so that those functions can be called from wherever(from other files like testExce.pl) required. Sample files are attached - testExcel.pl and excelUtil.pm When I am executing testExcel.pl, "write" function is not writing to excel file if "writeCell" is called from testExcel.pl. If "write " is called from "excelUtil.pm", then it is writing properly. Requesting you to help me regarding this

excelUtil.pm 

package excelUtil; 

use Exporter;
use strict;
use Spreadsheet::WriteExcel;

use vars qw(@ISA @EXPORT);

@ISA    = qw(Exporter);
@EXPORT = qw(writeCell);

## Excel workbook
my $workbook = Spreadsheet::WriteExcel->new("Sample.xls");

## worksheet
my $worksheet = $workbook->add_worksheet("test");

## Functions
sub writeCell {
    my ($row, $col, $str) = @_;

    print "Writing Cell \n";
    $worksheet->write($row, $col, $str);
}

writeCell(1, 2, "testing, Row-1, Col-2");

1;

[download]

testExcel.pl 

#!/usr/bin/perl

use excelUtil;

writeCell(1, 1, "Testing, Row-1, Col-1");

[download]

Replies are listed 'Best First'.
Re: "write" problem with "Spreadsheet::WriteExcel" Module
by ig (Vicar) on Oct 07, 2013 at 10:24 UTC

    Try closing your workbook explicitly.

    package excelUtil; 

use Exporter;
use strict;
use Spreadsheet::WriteExcel;

use vars qw(@ISA @EXPORT);

@ISA    = qw(Exporter);
@EXPORT = qw(writeCell close_workbook);

## Excel workbook
my $workbook = Spreadsheet::WriteExcel->new("Sample.xls");

## worksheet
my $worksheet = $workbook->add_worksheet("test");

## Functions
sub writeCell {
    my ($row, $col, $str) = @_;

    print "Writing Cell \n";
    $worksheet->write($row, $col, $str);
}

sub close_workbook {
    print "Closing workbook\n";
    $workbook->close();
}

writeCell(1, 2, "testing, Row-1, Col-2");

1;

    [download]
    #!/usr/bin/perl

use excelUtil;

writeCell(1, 1, "Testing, Row-1, Col-1");
close_workbook;

    [download]

    Edit: corrected my cut and paste error for the second block (which was a duplicate of the first), as noted by soonix - many thanks! What you suggest is exactly what I intended.

[reply]
[d/l]
[select]
      ++ for explicitly closing, but I guess your second code block should rather read:

      testExcel.pl

      #!/usr/bin/perl

use excelUtil;

writeCell(1, 1, "Testing, Row-1, Col-1");
close_workbook;

      [download]
[reply]
[d/l]

        Thank you.. It worked when I created the Object and closed the workbook properly..

[reply]
Re: "write" problem with "Spreadsheet::WriteExcel" Module
by 2teez (Vicar) on Oct 07, 2013 at 10:30 UTC

    Why not like thus:
    ExcelUtil.pm 

    package ExcelUtil;
use warnings;
use strict;
use vars qw(@ISA @EXPORT);

@ISA    = qw(Exporter);
@EXPORT = qw(writeCell);

sub writeCell {
    my ( $obj, $row, $col, $str ) = @_;
    $obj->write( $row, $col, $str );
}

1;

    [download]
    testExcel.pl 
    use warnings;
use strict;
use Spreadsheet::WriteExcel;
use ExcelUtil;

my $wrk_bk  = Spreadsheet::WriteExcel->new('sample.xls');
my $wrk_sht = $wrk_bk->add_worksheet('new_sheet');

writeCell( $wrk_sht, 1, $_, "Testing, Row-1, Col-$_" ) for 1..5;

    [download]
    Update:
    Why even calling the subroutine within it module like you did in your "ExcelUtil.pm" like this
    writeCell(1, 2, "testing, Row-1, Col-2");
    With the code shown above you can even do like this: writeCell( $wrk_sht, $_, 1, "Testing, Row-$_, Col-1" ) for 1..5; And it still works for you.

    If you tell me, I'll forget.
    If you show me, I'll remember.
    if you involve me, I'll understand.
    --- Author unknown to me
[reply]
[d/l]
[select]
Re: "write" problem with "Spreadsheet::WriteExcel" Module
by Anonymous Monk on Oct 07, 2013 at 06:35 UTC
    Rewrite your code so it works like this 
    my $foo = Spreadsheet::WriteExcel->new...;
writeCell( $foo, 1, 2, "blah");

    [download]
[reply]
[d/l]

      Thank you.. It worked when I created the Object and closed the workbook properly..

[reply]

Back to Seekers of Perl Wisdom