#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    39
    Rep Power
    1

    Having a syntax/understanding problem while writing a 2d array to excel sheet


    Hello,
    I have a problem at the highlighted line. I'm trying to write data from this 2darray to an excel sheet using Spreadsheet::WriteExcel. I tried the following 2 syntax and both of them give me different problems.Highlighting the important parts of the code in both methods

    Method1:Here I am able to print contents of the 2d array to the console, but don't know the syntax to be used in the $worksheet->write line(highlighted below)
    Code:
    #!/usr/bin/perl
    
    use strict;
    use warnings;
    use Spreadsheet::WriteExcel;
    
    my $workbook = Spreadsheet::WriteExcel->new('arraytest.xls');
    my $worksheet = $workbook->add_worksheet();
    my @data;
    
    for my $row(0..10)
    {
     for my $col(0..5)
     {
      $data[$row][$col] = $col;
     }
    }
    
    #I'm able to print contents correctly
    for my $row(0..10)
    {
     for my $col(0..5)
     {
      print "data[$row][$col] ==", $data[$row][$col]," ";
     }
     print "\n";
    }
    
    #But I'm unsure about how to perform the $worksheet->write below
    $worksheet->write('A1',$data);#doesn't work
    #$worksheet->write('A1',@data); #tried this as well, doesn't work
    $workbook->close();
    system('libreoffice arraytest.xls');
    Mehod2:Here I'm able to write to the excel sheet from the 2darray, but am not able to print individual contents to the console
    Code:
    #!/usr/bin/perl
    
    use strict;
    use warnings;
    use Spreadsheet::WriteExcel;
    
    my $workbook = Spreadsheet::WriteExcel->new('arraytest.xls');
    my $worksheet = $workbook->add_worksheet();
    my $data=
    [
     ['one','two','three'],
     [1,2,3],
    ];
    
    #This part is not working fine
    print "data[0][0] ==", $data[0][0];
    
    #This part is okay and is working fine
    $worksheet->write('A1',$data);
    $workbook->close();
    system('libreoffice arraytest.xls');
    Please advise.
    Thanks

    PS : I would like to use Method1 because I can dynamically store data from a file etc rather than statically defining as in Method2.
    Last edited by IAMTubby; June 29th, 2013 at 01:45 AM.
  2. #2
  3. Contributed User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jun 2005
    Posts
    4,367
    Rep Power
    1870
    From the copious examples on the manual page
    Perhaps
    Code:
    $worksheet->write('A1',\@data);
    If you dance barefoot on the broken glass of undefined behaviour, you've got to expect the occasional cut.
    If at first you don't succeed, try writing your phone number on the exam paper
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    39
    Rep Power
    1
    Originally Posted by salem
    $worksheet->write('A1',\@data);
    Thanks a lot salem. I generally go through the resources but I was so blinded by so much new syntax that it didn't catch my attention. Shall be more careful in future.

    About the second question, from the resources I have seen on line, it's better to use my @data for an array. But since I found this in the documentation, I'm curious to know why I'm not able to access data as $data[0][..] and so on.

    Code:
    #Isn't the following a 2d array?
    my $data=
    [
     ['one','two','three'],
     [1,2,3],
    ];
    
    print "data[0][0]==", $data[0][0],"\n"; #this doesn't work
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2012
    Posts
    782
    Rep Power
    495
    Just on method 2: you are not defining an array, but a reference to an array. So, if you want to print the values, instead of

    Perl Code:
    print $data[0][0];


    try this:

    Perl Code:
     print $$data[0][0];


    (notice the double $$) or

    Perl Code:
    print $data->[0][0];


    But it might be better (or at least easier) to define an actual array (rather than an array ref):

    Perl Code:
    my @data=
    (
     ['one','two','three'],
     [1,2,3],
    );


    If you do this, then this syntax will work fine:

    Perl Code:
    print $data[0][0];
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    39
    Rep Power
    1
    Originally Posted by Laurent_R
    Just on method 2: you are not defining an array, but a reference to an array. So,
    Perl Code:
     print $$data[0][0];
    or
    Perl Code:
    print $data->[0][0];
    Thanks a lot Laurent_R, both $$data[0][0] and $data->[0][0]worked fine

    But it might be better (or at least easier) to define an actual array (rather than an array ref):
    Sure, I'll go ahead with defining an actual array.

IMN logo majestic logo threadwatch logo seochat tools logo