in reply to problem using perl module Spreadsheet::WriteExcel cannot get data into rows when using for loop on a list to populate rows

Before we get into the code... When you submitted you post did you see something like this? If something looked unlike you expected it to you might need to check out Writeup Formatting Tips ?

Take a few minutes and try to read your own post, can you read it easily? Others who are trying to help will not have the patience to read through such formats. Please check Writeup Formatting Tips

I have put junk values into all of the arrays that you did not provide to us 

#!/usr/bin/perl

use strict;  # USE IT ALL THE TIME
use warnings; # USE IT ALL THE TIME
use Spreadsheet::WriteExcel;

my @model = qw (some random text goes here what else?);
my @processor = qw (some random text goes here what else?);
my @memory = qw (some random text goes here what else?);
my @softwareVersion = qw (some random text goes here what else?);
my @devicesPerVariant = qw (some random text goes here what else?);
my $devicePerModel = "Hello";

my $workbook = Spreadsheet::WriteExcel->new("test.xls") or die $!;
my $worksheet = $workbook->add_worksheet();
my $j = 0;
my $rowCounter = 1;   #### Declare rowcounter. O/W It is undef the fir
+st time around

$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');


for ($j = 1; $j <= $#model; $j++)  {
    $worksheet->write($rowCounter,0, $model[$j]);

    ### You start with $j = 1 then why are you checking for this?????

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

    # Are you sure that all of your arrays have the same length?

    $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]

The excel output :

Equipment ModelDevices Per ModelDevices Per VariantModel Variant ProcessorDevice MemoryDevice Software
randomrandomrandomrandomrandom
texttexttexttexttext
goesgoesgoesgoesgoes
hereherehereherehere
whatwhatwhatwhatwhat
else?else?else?else?else?

What is wrong with this output?

  • Comment on Re: problem using perl module Spreadsheet::WriteExcel cannot get data into rows when using for loop on a list to populate rows
  • Download Code

Replies are listed 'Best First'.
Re^2: problem using perl module Spreadsheet::WriteExcel cannot get data into rows when using for loop on a list to populate rows
by adamlee14 (Initiate) on Sep 14, 2005 at 05:28 UTC
    here is my code.. #!/usr/local/bin/perl
    use strict;
    use Env;
    use Spreadsheet::WriteExcel();
    use Spreadsheet::ParseExcel;
    use OLE::Storage_Lite;
    # Globals
    $oBook =  Spreadsheet::ParseExcel::Workbook->Parse("/home/d219103/rdnaudit.xls");
    $rowCounter = 1;
    @devicesPerVariant = ();
    @model = ();
    @processor = ();
    @memory = ();
    @softwareVersion = ();
    @listOfHardwareTypes = &generateListOfHardwareTypes();
    foreach $hardwareTypes (@listOfHardwareTypes) {
    print "Hardware type is $hardwareTypes\n";
    @hardwareMatchEntries = &getAllHardwareTypes($hardwareTypes);
    &prepareAndSortModelDataForSpreadsheet(@hardwareMatchEntries);
    print "\nDevices = $devicePerModel\n";
    print "Unique Device counts = @devicesPerVariant\n";
    print "Unique Device models = @model\n";
    print "Unique Device processors = @processor\n";
    print "Unique Device memory = @memory\n";
    print "Unique Device software = @softwareVersion\n";
    # Call excel spreadsheet adding here
    &populateExcelRows();
    # Reset these variable
    @devicesPerVariant = "";
    @model = "";
    @processor = "";
    @memory = "";
    @softwareVersion = "";
    }
    sub generateListOfHardwareTypes
    {
    my($i,$oWkS,$oWkC);
    my(@uniqueList);
    foreach my $oWkS (@{$oBook->{Worksheet}}) {
    $i = 1;
    $oWkC = $oWkS->{Cells}[$i][1];
    $CellValue = $oWkC->Value;
    while($CellValue) {
    # Check if cellvalue is unique
    if (&searchString($CellValue, \@uniqueList)) {
    push @uniqueList, $CellValue;
    }
    $i++;
    # Get next CellValue
    $oWkC = $oWkS->{Cells}[$i][1];
    if (exists $oWkS->{Cells}[$i][1]) {
    $CellValue = $oWkC->Value;
    } else {
    $CellValue = 0;
                        <code>}

    [download]

    }
    }
    return @uniqueList;
    }
    sub searchString {
    $value = $_[0];
    $theList_ptr = $_[1];
    my @theList = @$theList_ptr;
    #print "Value is $value, List is @theList\n";
    if (@theList) {
    foreach $entry (@theList) {
    chomp(@theList);
    if ($entry eq $value ) {
    return 0;
    }
    }
    }
    return 1;
    }
    sub getAllHardwareTypes
    {
    my($i,$oWkS,$aWkC,$bWkC,$cWkC,$dWkC,$eWkC);
    my(@HWList);
    $compareValue = $_[0];
    foreach my $oWkS (@{$oBook->{Worksheet}}) {
    $i = 1;
    $aWkC = $oWkS->{Cells}[$i][0];
    $bWkC = $oWkS->{Cells}[$i][1];
    $cWkC = $oWkS->{Cells}[$i][2];
    $dWkC = $oWkS->{Cells}[$i][3];
    $eWkC = $oWkS->{Cells}[$i][4];
    $AcolValue = $aWkC->Value;
    $BcolValue = $bWkC->Value;
    $CcolValue = $cWkC->Value;
    $DcolValue = $dWkC->Value;
    $EcolValue = $eWkC->Value;
    while($BcolValue) {
    # Check if B Column value is a match
    if ($BcolValue eq $compareValue) {
    push @HWList, "$AcolValue,$BcolValue,$CcolValue,$DcolValue,$EcolValue";
    #print "$AcolValue,$BcolValue,$CcolValue,$DcolValue,$EcolValue\n";
    }
    $i++;
    #Get next Bcolumn Value
    $aWkC = $oWkS->{Cells}[$i][0];
    $bWkC = $oWkS->{Cells}[$i][1];
    $cWkC = $oWkS->{Cells}[$i][2];
    $dWkC = $oWkS->{Cells}[$i][3];
    $eWkC = $oWkS->{Cells}[$i][4];
    if (exists $oWkS->{Cells}[$i][0]) {
    $AcolValue = $aWkC->Value;
    }
    if (exists $oWkS->{Cells}[$i][1]) {
    $BcolValue = $bWkC->Value;
    } else {
    $BcolValue = 0;
    }
    if (exists $oWkS->{Cells}[$i][2]) {
    $CcolValue = $cWkC->Value;
    }
    if (exists $oWkS->{Cells}[$i][3]) {
    $DcolValue = $dWkC->Value;
    }
    if (exists $oWkS->{Cells}[$i][4]) {
    $EcolValue = $eWkC->Value;
    }
    }
    }
        <code>#print "@HWList\n";

    [download]

    return @HWList;
    }

<code>sub prepareAndSortModelDataForSpreadsheet {

    [download]

    my @inputArray = @_;
    # populate these variables
    $devicePerModel = $#inputArray;
    $devicePerModel++;
    my @array = ();
    my @out = ();
    my $line = ();
    my $entry = ();
    foreach $line (@inputArray) {
    if ($line =~ m/^.*?,(.*)/) {
    #print "$1\n";
    push @array, $1;
    }
    }
    # Sort unique entries from data
    undef %saw;
    @saw{@array} = ();
    @out = sort keys %saw;
    foreach $entry (@out) {
    # These are unique entries
    $count = 0;
    foreach $line (@array) {
    if ($entry eq $line) {
    $count++;
    }
    }
    push @devicesPerVariant, $count;
    if ($entry =~ m/(.*?),(.*?),(.*?),(.*)/) {
    push @model, $1;
    push @processor, $3;
    push @memory, $4;
    push @softwareVersion, $2;
    }
    }
    }
    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], $devicesPerVariant[$j], $processor[$j], $memory[$j], <code>$softwareVersion[$j]\n";
    $worksheet->write($rowCounter,0, $model[$j]);
    if ($j == 0) {
    $worksheet->write($rowCounter,1, $devicePerModel);
    }
     # Are you sure that all of your arrays have the same length?
    $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();
[reply]
[d/l]
[select]

      That is a start in the right direction. But do you really not use any indentation? Do you really need 200 lines of code to demonstrate the problem? Try stripping out as much code as you can and still show the problem.

      For a start, do you really need to populate 6 columns of data? Bet you can get away with 1. Do you really need to use any modules? Bet you can reproduce it with just print and a little hardwired data.

      Actually, by the time you have stripped the code down to just that required to demonstrate the problem I can almost guarante that you will find the problem yourself. If you don't, then post the reduced code, the output you get, and the output you expect; then everyone will understand your problem and will be able to reproduce it. In a case like this it is rather unlikely to be one of the myriad modules that you are using.

      Perl is Huffman encoded by design.
[reply]
        Hi i sent all the code just to present a picture i what i was trying to achieve i dont know if i can send excel spreadsheet that indicate the problem i have but simply the code is not writing to the spreadsheet here is the problem code.. only the first row and the last row nothing in between 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], $devicesPerVariant[$j], $processor[$j], $memory[$j], <code>$softwareVersion[$j]\n";
        $worksheet->write($rowCounter,0, $model[$j]);
        if ($j == 0) {
        $worksheet->write($rowCounter,1, $devicePerModel);
        }
        $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();
        here is some rows iam trying to put in the spreadsheet row is 1, GSR, 2, GRP, 256Mb, 12.0(26)S3
        row is 1, 6503, 13, WS-X6K-SUP2-2GE, 128Mb, 7.6(8)
        row is 1, 2651, 68, MPC860P, 128Mb, 12.3(6)
        row is 1, ERX1400, 11, SRP-10Ge, 512Mb, 5.1.3 S-2.0
        row is 3, ERX1400, 36, SRP-10Ge, 512Mb, 5.1.3 S-2.2
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom