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

I am new Perl and programming in general, I'm trying to use Perl to automate some Excel task. I have read through most of the Ole questions and answer on here and it just confuses me. Here's what I am trying to do, I found an example that will work for what I am trying to do but I dont know what all the brackets [] does to the scalar variable $mydata. Because I am reading in data and storing it into an array, how would I need to change the code in order for it to work with an array? Or how would I need to read in the file in order to store it in the same way this scalar variable is written? Thanks in advance for any help. I am trying to learn but there is just soo much to learn, I feel overwhelmed. 
# This is the code that works
$mydata = [["Item",     "Category", "Price"], 
           ["Nails",    "Hardware",  "5.25"],
           ["Shirt",    "Clothing", "23.00"]];

# Write all the data at once...
$rng = $xlBook->ActiveSheet->Range("A1:C7");
$rng->{Value} = $mydata;

# This is my code that doesn't work because I changed it to an array.
@mydata = ("Item",     "Category", "Price", 
           "Nails",    "Hardware",  "5.25",
           "Shirt",    "Clothing", "23.00");

# Write all the data at once...
$rng = $xlBook->ActiveSheet->Range("A1:C7");
$rng->{Value} = @mydata;

[download]
Here is the full working code if you want to see it: 
use Win32::OLE;

# Start Excel and make it visible
$xlApp = Win32::OLE->new('Excel.Application');
$xlApp->{Visible} = 1;

# Create a new workbook
$xlBook = $xlApp->Workbooks->Add;

# Our data that we will add to the workbook...
$mydata = [["Item",     "Category", "Price"], 
           ["Nails",    "Hardware",  "5.25"],
           ["Shirt",    "Clothing", "23.00"],
           ["Hammer",   "Hardware", "16.25"],
           ["Sandwich", "Food",      "5.00"],
           ["Pants",    "Clothing", "31.00"],
           ["Drinks",   "Food",      "2.25"]];
             
# Write all the data at once...
$rng = $xlBook->ActiveSheet->Range("A1:C7");
$rng->{Value} = $mydata;

print "All done.";

[download]
Thanks, Paul

Replies are listed 'Best First'.
Re: OLE write to Excel
by toolic (Bishop) on Jan 20, 2011 at 15:30 UTC
    There are differences between $mydata and @mydata.

    The $mydata scalar is a reference to an array-of-arrays data structure. There are 3 arrays, each with 3 elements.

    @mydata is a flat array consisting of 9 elements.

    It seems like whatever OLE module you're using requires you to use the reference to an array-of-arrays, not a flat array. Refer to the module documentation. Can you tell us the exact module you are using?

    A good reference is perldsc.

[reply]
      # Hey changed the code from
$rng->{Value} = @mydata;

# To 
$rng->{Value} = \@mydata;

      [download]
      It at least ran without errors but instead of using all the data in the array, it only uses the 1st 3 elements and uses it for all the rows.
[reply]
[d/l]
        I am not sure how to get it to use all the rows with just using "\@mydata". I modified the code to work, but it is very inefficient. Can anyone think of a more efficient way to resolve this issue without using a loop? The code is below: 
        # Our data that we will add to the workbook...
@mydata = ("Item",     "Category", "Price"); 
@mydata1 = ("Nails",    "Hardware",  "5.25");
@mydata2 = ("Shirt",    "Clothing", "23.00");
@mydata3 = ("Hammer",   "Hardware", "16.25");
@mydata4 = ("Sandwich", "Food",      "5.00");
@mydata5 = ("Pants",    "Clothing", "31.00");
@mydata6 = ("Drinks",   "Food",      "2.25");           
           

# Write all the data at once...
$rngt = $xlBook->ActiveSheet->Range("A1:C7");
$rng = $xlBook->ActiveSheet->Range("A1:C1");
$rng1 = $xlBook->ActiveSheet->Range("A2:C2");
$rng2 = $xlBook->ActiveSheet->Range("A3:C3");
$rng3 = $xlBook->ActiveSheet->Range("A4:C4");
$rng4 = $xlBook->ActiveSheet->Range("A5:C5");
$rng5 = $xlBook->ActiveSheet->Range("A6:C6");
$rng6 = $xlBook->ActiveSheet->Range("A7:C7");
$rng->{Value} = \@mydata;
$rng1->{Value} = \@mydata1;
$rng2->{Value} = \@mydata2;
$rng3->{Value} = \@mydata3;
$rng4->{Value} = \@mydata4;
$rng5->{Value} = \@mydata5;
$rng6->{Value} = \@mydata6;

        [download]
[reply]
[d/l]
      I an using the Win32::OLE. Is this module not as good as the other modules to use with Excel? Thanks again for the quick response.
[reply]

        Hi NewMonk2Perl, Welcome to the Monastery.

        In the Perl community from time to time you'll hear/read "TMTOWTDI", meaning There's more than one way to do it. Have you seen the module Spreadsheet::WriteExcel? You can use it to create Excel documents on various platforms, you don't need excel installed. It has great documentation and Examples.

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

use Spreadsheet::WriteExcel;

my @mydata = (
    ['Item', 'Category', 'Price'],
    ['Nails', 'Hardware', '5.25'],
    ['Shirt', 'Clothing', '23.00']
    );

my $workbook = Spreadsheet::WriteExcel->new('test.xls');
my $worksheet = $workbook->add_worksheet();
$worksheet->write_col('A1', \@mydata);

        [download]

        If you're new to Perl I'd suggest working through the following:

        Update: I'll leave the formatting of the cells as an exercise for you, it's covered in the Documentation and the examples.

        Update 2:Thanks toolc s/next/need/

[reply]
[d/l]

        I am using Win32::OLE for automatization of my Excel-tasks, and it works fine for me :-) . The main drawback of the module is that it requires Windows (and in my case also Excel) to be installed - so your scripts are not very portable.

        HTH, Rata
[reply]
        Is this module not as good as the other modules to use with Excel?
        I have no idea how Win32::OLE compares to other CPAN offerings since I have never used it. For what it's worth, it has received positive reviews.
[reply]

Back to Seekers of Perl Wisdom