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

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();
  • Comment on Re^2: problem using perl module Spreadsheet::WriteExcel cannot get data into rows when using for loop on a list to populate rows
  • Select or Download Code

Replies are listed 'Best First'.
Re^3: problem using perl module Spreadsheet::WriteExcel cannot get data into rows when using for loop on a list to populate rows
by GrandFather (Saint) on Sep 14, 2005 at 06:10 UTC

    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]

        One thing that you should try is replacing all the $worksheet->write lines with equivelent print lines so that you can see if the problem is in writing the data or in generating it.

        The smaller and simpler you can make your test code, the easier it will be to isolate the problem. Copy the code to a different file, then cut lines out and simplify the code until the problem goes away.

        This is a generally useful debugging technique for otherwise intractable problems and makes it much easier to generate a simple test case for reporting the problem.

        Perl is Huffman encoded by design.
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom