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

I posted a earlier Q about having problems using Spreadsheet::WriteExcel to insert data from global lists that are declared as arrays i can only get the the first list data into the spreadsheet the rest of the lists that contain data are not being put into the spreadsheet this is the code iam trying to get working 
sub populateExcelRows {
 my $workbook = Spreadsheet::WriteExcel->new("test.xls");
 my $worksheet = $workbook->add_worksheet();
 my $j = 1;

  #Declare Headings for row 0 and the 6 Columns

   $worksheet->write(0,0 , 'Equipment Model');
   $worksheet->write(0,1, 'Devices Per Model');
   $worksheet->write(0,2, 'Devices Per Variant');
   $worksheet->write(0,3, 'Model Variant Processor');
   $worksheet->write(0,4 , 'Device Memory');
   $worksheet->write(0,5 , 'Device Software');

   #Loop through Global Array called Models to determine the number of
+ rows to place data

        for ($j = 1; $j <= $#model; $j++)  {
                print "row is $rowCounter, $model[$j], $devicesPerVari
+ant[$j], $processor[$j], $memory[$j], $softwareVersion[$j]\n";
                $worksheet->write($rowCounter,0, $model[$j]);


                if ($j == 0) {
                        $worksheet->write($rowCounter,1, $devicePerMod
+el);
                }

               
                $worksheet->write($rowCounter,2, $devicesPerVariant[$j
+]);
                $worksheet->write($rowCounter,3, $processor[$j]);
                $worksheet->write($rowCounter,4, $memory[$j]);
                $worksheet->write($rowCounter,5, $softwareVersion[$j])
+;
                $rowCounter++;
        }
   $workbook->close();

[download]
iam at a loss what to do? here is the lists as seen by a for loop "row is 1 ..." is the only one that is seen in the spreadsheet  row is 1, GSR, 2, GRP, 256Mb, 12.0(26)S3
the rest do not appear  row is 2, 6503, 13, WS-X6K-SUP2-2GE, 128Mb, 7.6(8)
 row is 3, 6503, 10, WS-X6K-SUP2-2GE, 256Mb, 7.6(8)
..there is more of the above lines there should be up to 35 rows that should be populated in the spreadsheet

Edited by Chady -- fixed code tags.

Replies are listed 'Best First'.
Re: Spreadsheet::WriteExcel Loop problem
by GrandFather (Saint) on Sep 16, 2005 at 03:45 UTC

    You should trim your code down to a minimum (you are improving) and make it stand alone so that we can reproduce your problem.

    if ($j == 0) is always false because your loop starts with $j = 1.

    If I edit your code to this:

    use warnings;
use strict;
use Spreadsheet::WriteExcel;

my @model = qw(x a b c d);
my @devicesPerVariant = qw(x 2 1 3 1);
my $rowCounter = 1;
my $devicePerModel = 2;

my $workbook = Spreadsheet::WriteExcel->new("test.xls");
my $worksheet = $workbook->add_worksheet();
my $j = 1;

#Declare Headings for row 0 and the 6 Columns
$worksheet->write(0,0 , 'Equipment Model');
$worksheet->write(0,1, 'Devices Per Model');

#Loop through Global Array called Models to determine the number of ro
+ws to place data
for (my $j = 1; $j <= $#model; $j++)  {
  print "row is $rowCounter, $model[$j], $devicesPerVariant[$j]\n";
  $worksheet->write($rowCounter,0, $model[$j]);
  if ($j == 0) {
    $worksheet->write($rowCounter,1, $devicePerModel);
  }
  
  $worksheet->write($rowCounter,2, $devicesPerVariant[$j]);
  $rowCounter++;
  }
$workbook->close();

    [download]

    I get a spreedsheet containing:

    Equipment Model Devices Per Model    
a               2
b               1
c               3
d               1

    [download]

    which is what I would expect to get.

    Perl is Huffman encoded by design.
[reply]
[d/l]
[select]
      hi that work fine when you use static entrie in the array the thing is iam using global array that collect data first byway of reading a spreadsheet (Spreadsheet::ParseExcel) I have a function to do gather unique entries and put that data into lists. Then i have a function to do counting the number of unique entries that gives me with new data to place in a global array. Then i have a function in the script to go and write to a spread sheet by way of doing iterative loop on one specific array value that will keep a count on the number of rows to create. I can only get the results from the first set of data in the global arrays that are used to populate the spreadsheet. I dont know if i missing something in terms of how to use for loop and writing arrayi to spreadsheet, I have used print statememnt to see what i should be getting it just wont go into the spreadsheeet.
[reply]

        Can you reduce you code to a stand alone sample, like the code I provided above, that demonstrates the problem? I strongly suspect that the bug has nothing to do with writing to the spreadsheet. however, until you can provide a complete sample that demonstrates the error I don't think that we can help you much more.

        It is quite likely that in the process of reducing your code to a minimum that demonstrates the problem, you will actually discover the bug.

        You haven't commented on the $j == 0 issue I mentioned

        Perl is Huffman encoded by design.
[reply]
[d/l]
Re: Spreadsheet::WriteExcel Loop problem
by ww (Archbishop) on Sep 16, 2005 at 03:07 UTC
    Anon:
    Second similar post in two days.

    In itself, that's not necessarily bad.

    BUT, please 1) read How do I post a question effectively?, 2) use <code>.... </code> tags... and, 3) register, so you can update/edit your posts, if needed, and so you can enjoy the community more fully.

[reply]
Re: Spreadsheet::WriteExcel Loop problem
by dragonchild (Archbishop) on Sep 16, 2005 at 14:43 UTC
    Did you even try Excel::Template? It would make your problem go away, plus make your code more reusable and make it easier to change to add new formats in the future. And, frankly, I wrote all the code for you.
    My criteria for good software:
    1. Does it work?
    2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
[reply]

Back to Seekers of Perl Wisdom