How do I add a multidimensional array (AoA) to Excel using Perl?

I want to add my data stored in 2x2 dimension array in Excel using Perl. I know how to open and add simple data. This I can do using for loop. But how can I do it elegantly?

This is what I am trying to do

$sheet->Range("A1:B"."$size")->{Value} = @$data;
                                         or   @data;
                                         or   {@data};
                                         or   {\@data};

where @data is two dimensional array.

# use existing instance if Excel is already running
eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
die "Excel not installed" if $@;
unless (defined $ex) {
    $ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
            or die "Oops, cannot start Excel";
}


# get a new workbook
$book = $ex->Workbooks->Add;

# write to a particular cell
$sheet = $book->Worksheets(1);
print "A1:B"."$size";
# write a 2 rows by 3 columns range

$sheet->Range("A1:B"."$size")->{Value} = @$data;

From stackoverflow

• Based on some reading (I don't have a Win32 box in front of me), it looks like the Value attribute of Range handles a reference to an AoA correctly, so try saying:

  my $data = [
      ["a1", "b1"],
      ["a2", "b2"],
  ];
  $sheet->Range("A1:B" . @$data)->{Value} = $data;
  

  abatishchev : Range.Value is an array of arrays (object[,] in C# notation) size of Rows * Columns (so called R1C1 notation). In your example, 2*2. For A1:C4 size will be 4*3

  abatishchev : In other words, there is no need for $size. Basing on the size of $data, it always must be 2.

  Chas. Owens : I just copied the example without thinking, I have corrected it to use the AoA to determine the number of rows.

• I see that you are using Win32::OLE but this sort of thing is also quite easy with Spreadsheet::WriteExcel:

  #!/usr/bin/perl -w
  
  use strict;
  use Spreadsheet::WriteExcel;
  
  my $workbook  = Spreadsheet::WriteExcel->new('test.xls');
  my $worksheet = $workbook->add_worksheet();
  
  # Get your AoA from somewhere.
  my $data = [
      [ 'Hello', 'world', 123   ],
      [ 'Bye',   'bye',   4.567 ],
  ];
  
  # Write the data.
  $worksheet->write_col( 'A1', $data );
  

  Chas. Owens : The downside to Spreadsheet::WriteExcel is that you can't modify an existing spreadsheet. This means you must use Spreadsheet::ParseExcel to copy the data from an existing spreadsheet into the one you want modified, then modify it. The code gets ugly, so Win32::OLE can be better if you are on Win32.

  jmcnamara : The upside of Spreadsheet::WriteExcel is that it is fast, well documented and works on every platform that Perl works on. Its function, which I would hope is explicit, is to create a new Excel file. To modify an existing Excel file there is Spreadsheet::ParseExcel::Saveparser.

  Chas. Owens : Hmm, I haven't seen that module before; I will have to go check it out.