use Excel::Template;

my $template = Excel::Template->new(
    file => \*DATA,
);

my @data;
for ($j = 1; $j <= $#model; $j++)  {
    push @data, {
        device_model => $model[$j],
        devices_per_model => $device_per_model,
        devices_per_variant => $devicesPerVariant[$j],
        model_variant_processor => $processor[$j],
        device_memory => $memory[$j],
        device_software => $softwareVersion[$j],
    };
}

$template->param(
    data => \@data,
);

$template->write_to_file( "somefile.xls" );

__DATA__
<workbook>
  <worksheet>
    <row>
      <cell>Equipment Model</cell>
      <cell>Devices Per Model</cell>
      <cell>Devices Per Variant</cell>
      <cell>Model Variant Processor</cell>
      <cell>Device Memory</cell>
      <cell>Device Software</cell>
    </row>
    <loop name="data">
      <row>
        <cell><var name="equipment_model"/></cell>
        <cell><var name="devices_per_model"/></cell>
        <cell><var name="devices_per_variant"/></cell>
        <cell><var name="model_variant_processor"/></cell>
        <cell><var name="device_memory"/></cell>
        <cell><var name="device_software"/></cell>
      </row>
    </loop>
  </worksheet>
</workbook>
[download]

You have to convert your current data structure, which is a bunch of parallel arrays, into an array of hashes for the E::T loops to work correctly. This is the same data structure that HTML::Template, PDF::Template, and Template Toolkit all use. (By the way, this is a better data structure to use throughout your program, because it's more robust and all the data about a given thing is kept in one place - nothing can get out of sync.) That's what the @data array is for. Then, you specify your template, which is pretty self-explanatory.

Now, here's the major benefit of using Excel::Template vs. directly using S::WE. Let's say you wanted to make a whole section bold. Just put the whole section in <bold> tags. Or, let's say you wanted to add more items, or maybe another worksheet. Just modify the template, add the data to your data structure, and it's that simple. Plus, you can re-use the data structure with HTML::Template and PDF::Template, meaning you now have 3 formats from one data structure. AND, if there's any bugs in the Excel generation, assuming you've used E::T right, it's my fault, not yours. :-)

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

The $rowCounter variable does appear to be defined, so it will be zero to start with. So all the effort you went into to write 'Equipment Model' to cell(0,0) will be erased by the first model name.

Why are you using $rowCounter? If you replace $rowCounter with $j does it make your life easier?

Are your arrays (@devicesPerVariant, @processor, @memory, and @softwareVersion) indexed by 0 or 1? You appear to be accessing them from element 1 onwards (for ( $j = 1; ...).

Have you tried use strict; and ensured your program compiles with no errors.. (e.g. perl -wc script.pl).

1) rowCounter is a global variable that keep track of what row I'm upto in the spreadsheet because the function is called multiple times to populate all data. 2) rowcounter isn't being replaced. J is just being used as a local counter whilst rowcounter keeps track globally. 3) All arrays are the same size and the first index is just "". Don't know howe to initialize a blank array, so I use "@array = ""; ". This just makes the first value "" and hence I start at index 1 instead of 0. 4) No I haven't used "use strict". It seems to output lots of errors like "requires explicit package name at ./XXXXXXXX.pl line XX". These don't seem to me like legitimate errors. I could be wrong :-)

Hi thanks for the reply i tried using J instead of rowcounter , it does the same thing the excel spreadsheet only get the final row here is the eaxample printed output that i want to fill a spreadsheet with Hardware type is GSR
Devices = 12
Unique Device counts = 10 2
Unique Device models = GSR GSR
Unique Device processors = GRP GRP
Unique Device memory = 128Mb 256Mb
Unique Device software = 12.0(26)S3 12.0(26)S3
Model is 1
row is 1, GSR, 2, GRP, 256Mb, 12.0(26)S3
Hardware type is 6503
Devices = 23
Unique Device counts =  13 10
Unique Device models =  6503 6503
Unique Device processors =  WS-X6K-SUP2-2GE WS-X6K-SUP2-2GE
Unique Device memory =  128Mb 256Mb
Unique Device software =  7.6(8) 7.6(8)
Model is 2
row is 1, 6503, 13, WS-X6K-SUP2-2GE, 128Mb, 7.6(8)
Hardware type is 2651
Devices = 68
Unique Device counts =  68
Unique Device models =  2651
Unique Device processors =  MPC860P
Unique Device memory =  128Mb
Unique Device software =  12.3(6)
Model is 1
row is 1, 2651, 68, MPC860P, 128Mb, 12.3(6)
Hardware type is ERX1400

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 Model	Devices Per Model	Devices Per Variant	Model Variant Processor	Device Memory	Device Software
random		random	random	random	random
text		text	text	text	text
goes		goes	goes	goes	goes
here		here	here	here	here
what		what	what	what	what
else?		else?	else?	else?	else?

What is wrong with this output?

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();

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.

Give us something that will run. In particular provide the arrays like @model (invent data for them if you must).

You might like to comment out your $worksheet->write's and put print's in their place so you can more easily see what is going on.

Use use warnings; use strict; if you are not already.