Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Excel::Writer::XLSX 0 byte outfile

by doc1623 (Novice)
on Nov 14, 2014 at 17:51 UTC ( [id://1107229]=perlquestion: print w/replies, xml ) Need Help??

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

I'm having trouble with a program I'm writing using Excel::Writer::XLSX. I'm getting both a 0 byte xlsx file and no number (neg or pos) back from a write call. This below gets the 0 byte out file but I don't know why. (It does get a 0..successful write but one problem at a time.) It's the add_format I know but I have know idea why. 
 #!/usr/bin/perl -w


    use strict;
    use Excel::Writer::XLSX;

    my $workbook   = Excel::Writer::XLSX->new( 'mytest.xlsx' );
    my $worksheet = $workbook->add_worksheet( 'testsheet' );

    write_me();


    sub write_me
    {
    my $text_format = $workbook->add_format(
        bold   => 1,
        italic => 1,
        color  => 'red',
        size   => 18,
        font   => 'Lucida Calligraphy'
    );


    my $result=$worksheet->write( 'A2', "Text" );
    print "my result = $result";
    #$worksheet->write( 'B3', "Hello Excel", $text_format );
    #$worksheet->write( 'B3', "Hello Excel");
    }

    __END__

[download]

Replies are listed 'Best First'.
Re: Excel::Writer::XLSX 0 byte outfile
by Corion (Patriarch) on Nov 14, 2014 at 17:56 UTC

    Normally, the ->close() method should be called implicitly, but maybe it isn't called in your case. Have you tried explicitly calling $workbook->close at the end of your program?

[reply]
[d/l]
[select]
      thanks, close does work here but why, when I remove the format line, it works. On my program the close gets 
      Can't call method "get_xf_index" on unblessed reference at /usr/share/
+perl5/site_perl/Excel/Writer/XLSX/Worksheet.pm line 6946, <$fh> line 
+42.

      [download]
[reply]
[d/l]

        It seems that something goes wrong when you create your format in $text_format and you then pass undef along to the ->write call. I don't see anything in the code of Excel::Writer::XLSX why that should happen, but maybe you find something. Maybe debugging this with Data::Dumper to see what you get in $text_format could help.

[reply]
[d/l]
[select]

        It seems that something goes wrong when you create your format in $text_format and you then pass an unblessed hash along to the ->write call. I don't see anything in the code of Excel::Writer::XLSX why that should happen, but maybe you find something. Maybe debugging this with Data::Dumper to see what you get in $text_format could help.

[reply]
[d/l]
[select]
Re: Excel::Writer::XLSX 0 byte outfile
by fionbarr (Friar) on Nov 14, 2014 at 20:18 UTC
    the scope of '$text_format' is not available to the worksheet->write line. This worked for me: 
        use strict;
    use Excel::Writer::XLSX;

    my $workbook   = Excel::Writer::XLSX->new( 'mytest.xlsx' );
    my $worksheet = $workbook->add_worksheet( 'testsheet' );

    my $text_format = $workbook->add_format(
        bold   => 1,
        italic => 1,
        color  => 'red',
        size   => 18,
        font   => 'Lucida Calligraphy'
    );


    $worksheet->write( 'A2', "Text" );
    $worksheet->write( 'B3', "Hello Excel", $text_format );
    $worksheet->write( 'B8', "Hello Excel");


    $workbook->close;

    __END__

    [download]
[reply]
[d/l]

      the scope of '$text_format' is not available to the worksheet->write line.

      Why do you say that? Can you please explain how scope works and why $text_format would be out of scope for the worksheet->write line?

[reply]
[d/l]
[select]
      Thanks but I'm trying to do things in subroutines. I have a variable declared globally but it's instantiated and asigned only in a subroutine. I then have a seperate write subroutine. I shoudn't have to pass the worksheet, should I?
[reply]

        If you want to use subroutines (as you should) then try passing parameters into your subroutines. The following code works for me. (Read: It does not produce any warnings or errors and the Excel file is properly formatted).

        #!/bin/env perl
use strict;
use warnings;
use Excel::Writer::XLSX;

my $workbook = Excel::Writer::XLSX->new( 'mytest.xlsx' );
my $worksheet = $workbook->add_worksheet( 'testsheet' );

write_me( $workbook, $worksheet );

sub write_me
{
    my ( $workbook, $worksheet ) = @_;

    my $text_format = $workbook->add_format(
        bold   => 1,
        italic => 1,
        color  => 'red',
        size   => 18,
        font   => 'Lucida Calligraphy'
    );

    my $result = $worksheet->write( 'A2', "Text" );
    print "my result = $result\n";

    $worksheet->write( 'B3', "Hello Excel", $text_format );
}

        [download]
[reply]
[d/l]
Re: Excel::Writer::XLSX 0 byte outfile
by doc1623 (Novice) on Nov 16, 2014 at 01:09 UTC
    ok The writing is going fine now but the close still gets the error and the 0 byte file is still created. What I don't understand is that if the workbook object is destroyed prior to close, then I should get an error on the set_properties, correct? Thanks 
    $csv = Text::CSV_XS-> new (\%csv) or die "Problem creating new CSV obj
+ect".Text::CSV_XS->error_diag ();
my $csv_file = @ARGV && -f $ARGV[0] ? $ARGV[0]: "csv2excel";    #   ge
+t csv file name                                                      
+                                                                     
+        
my $wbk = Excel::Writer::XLSX->new (my $outfile = get_outfile($csv_fil
+e)) or die "Problems creating new Excel file: $!" unless defined $wbk
+;                                                                    
+        
$properties{'title'} ||= $opt_title;                                  
+                                                                     
+                                                                     
+        
read_row(open_csv());
$wbk->set_properties(%properties);                                    
+                                                                     
+                                                  
print Dumper($wbk->set_properties(%properties));                      
+                                                                     
+                                                                     
+        
$wbk->close;

    [download]
    with diagnostics gets 
    
$VAR1 = {
          'created' => [
                         49,
                         4,
                         19,
                         15,
                         10,
                         '114',
                         6,
                         318,
                         0
                       ],
          'category' => '',
          'title' => 'mytitle',
          'company' => '',
          'status' => '',
          'keywords' => '',
          'comments' => '',
          'author' => '',
          'manager' => '',
          'subject' => ''
        };

Can't call method "get_xf_index" on unblessed reference at
        /usr/share/perl5/site_perl/Excel/Writer/XLSX/Worksheet.pm line
+ 6946 (#4)
    (F) A method call must know in what package it's supposed to run. 
+ It
    ordinarily finds this out from the object reference you supply, bu
+t you
    didn't supply an object reference in this case.  A reference isn't
+ an
    object reference until it has been blessed.  See perlobj.
    
Uncaught exception from user code:
        Can't call method "get_xf_index" on unblessed reference at /us
+r/share/perl5/site_perl/Excel/Writer/XLSX/Worksheet.pm line 6946.
        Excel::Writer::XLSX::Worksheet::_write_cell(Excel::Writer::XLS
+X::Worksheet=HASH(0x3f9ffd0), 0, 0, ARRAY(0x3fa0570)) called at /usr/
+share/perl5/site_perl/Excel/Writer/XLSX/Worksheet.pm line 6685
        Excel::Writer::XLSX::Worksheet::_write_rows(Excel::Writer::XLS
+X::Worksheet=HASH(0x3f9ffd0)) called at /usr/share/perl5/site_perl/Ex
+cel/Writer/XLSX/Worksheet.pm line 6600
        Excel::Writer::XLSX::Worksheet::_write_sheet_data(Excel::Write
+r::XLSX::Worksheet=HASH(0x3f9ffd0)) called at /usr/share/perl5/site_p
+erl/Excel/Writer/XLSX/Worksheet.pm line 296
        Excel::Writer::XLSX::Worksheet::_assemble_xml_file(Excel::Writ
+er::XLSX::Worksheet=HASH(0x3f9ffd0)) called at /usr/share/perl5/site_
+perl/Excel/Writer/XLSX/Package/Packager.pm line 191
        Excel::Writer::XLSX::Package::Packager::_write_worksheet_files
+(Excel::Writer::XLSX::Package::Packager=HASH(0x2db7690)) called at /u
+sr/share/perl5/site_perl/Excel/Writer/XLSX/Package/Packager.pm line 1
+28
        Excel::Writer::XLSX::Package::Packager::_create_package(Excel:
+:Writer::XLSX::Package::Packager=HASH(0x2db7690)) called at /usr/shar
+e/perl5/site_perl/Excel/Writer/XLSX/Workbook.pm line 936
        Excel::Writer::XLSX::Workbook::_store_workbook(Excel::Writer::
+XLSX=HASH(0x3f971c0)) called at /usr/share/perl5/site_perl/Excel/Writ
+er/XLSX/Workbook.pm line 245
        Excel::Writer::XLSX::Workbook::close(Excel::Writer::XLSX=HASH(
+0x3f971c0)) called at /home/larry/perl/test/csv2excel.pl line 85

shell returned 25

    [download]
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?

www.com | www.net | www.org
Node Status?
node history
Node Type: perlquestion [id://1107229]
Approved by Corion
Front-paged by toolic
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? • Last hour • Other CB clients
Other Users?
Others examining the Monastery: (6)
As of 2024-04-23 22:04 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found