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

Hi,

I'm working on automating some reporting taking data from oracle and copying into excel via OLE using Perl. I can copy a data set to a new sheet, or to a defined range in an existing sheet, but I'd like to be able to copy data from a data set to a range defined by the scalar size of the array to be copied. 

# Read data from excel
my $array = $sheet -> Range("A2:C7") -> { 'Value' };
# Copy data into excel (add using a script)
foreach my $line (@$array) {
  for (my $row = 0; $row < (scalar(@$array)+1); $row++) {
    foreach my $element (@$line) {
      for (my $col = 0; $col < scalar(@$line); $col++) {
        $sheet -> Cells(($row+10),($col+1)) -> {Value} = @$element [$c
+ol];
      }
    }
  }
}

[download]

This script gives the following error: "Can't use string ("Delivered") as an ARRAY ref while "strict refs" in use at perl_ole.pl line 38." -- line 38 being the line above that starts "$sheet ->".

Thanks for the help, Joe

Code tags added by GrandFather

Replies are listed 'Best First'.
Re: Copying data sets of varying size into excel using OLE
by Corion (Patriarch) on Sep 08, 2010 at 17:26 UTC

    Your data structure is not what you think it is. Use Data::Dumper to get some insight into how your datastructure looks and what is contained in what variable. $sheet has the value Delivered, and is not a Win32::OLE object, as you might expect.

    Also see Querylet.

[reply]
[d/l]
[select]

      Thanks Corion.

      My data looks like this (an example from the web), is there a way to process the array data in the manner described (by matching cells with array elements)?

      Thanks for the help. 

      my $range = $sheet -> Range("A2:C7");</p><p>
$range -> { Value } =
        [['Delivered', 'En route', 'To be shipped'],
         [504, 102, 86],
         [670, 150, 174],
         [891, 261, 201],
         [1274, 471, 321],
         [1563, 536, 241]];

      [download]

      Code tags added by GrandFather

[reply]
[d/l]

        The two tasks needed for this are "Programming" and "Debugging". You will need to do both or hire somebody who will do both for you.

        If you actually want to do both yourself instead of spending money on hiring somebody, you might want to preview your post and inspect whether it renders well or not, and then read the Writeup Formatting Tips, especially the part where it says to "Put your code and data between <code>...</code> tags".

        Most likely, what you want to do can be done by using a loop. See perlsyn about loop structures.

[reply]
[d/l]

        If I'm correct, I believe that $range->{Value} is expecting a scalar (think a single integer, a single string, etc.) and you're trying to feed it an array. Personally, I've never tried to dump an array of data into a range of more than one cell. I've always done a single value into a single cell. You'll probably have more success doing the same. In your case, you'll probably want to do a loop as Corion suggested.

[reply]
[d/l]

Back to Seekers of Perl Wisdom