Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do I combine 2 Excel Spreadsheets into one with perl?

Status
Not open for further replies.

tk94kato

MIS
Oct 22, 2005
5
US
I am trying to combine daily reports into one monthly report.

my $workbook= Spreadsheet::WriteExcel->new("Integrated_source_list_$today.xls");

my $resource = Spreadsheet::parseExcel::Workbook->Parse('resources.xls');

The way I have my program now it will read from one spreadsheet and write to another.

What I need to be able to do is read in the file name to add and append the cumulative spreadsheet with the contents of this file. Any program examples would be very helpful as I am not proficient in perl.
 
Let me add to this and show where I am coming from :

for(my $iSheet=0; $iSheet < $resource->{SheetCount} ; $iSheet++)

{
$oWkS = $resource->{Worksheet}[$iSheet];
$temp[$iSheet]= $oWkS->{Name}; #get worksheet name
$maxRow=$oWkS->{MaxRow};

if (exists($hash{$temp[$iSheet]})){

print "\n\n\nmade it here\n\n\n";
$worksheet= #need to have some sort of reference here to the workbook
$hash{$temp[$iSheet]}=$hash{$temp[$iSheet]}+$maxRow;

#print "--------- SHEET:", $oWkS->{Name}, "\n";
for($iR = $oWkS->{MinRow} ;
defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ; $iR++) {
for( $iC = $oWkS->{MinCol} ;
defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ; $iC++) {

$oWkC = $oWkS->{Cells}[$iR][$iC];
my $test=$oWkC->Value if ($oWkC);


if ($iR==0){
$iR=$iR+$hash{$temp[$iSheet]}; #appends the rows

$worksheet->write($iR,$iC,$test, $format);
}
else{
$iR=$iR+$hash{$temp[$iSheet]}; #appends the rows

$worksheet->write($iR,$iC,$test);
}

}
}

}
else{

print "\n\nin the else side\n\n";
$worksheet= $workbook->add_worksheet("$temp[$iSheet]");
$hash{$temp[$iSheet]}=$maxRow;


#print "--------- SHEET:", $oWkS->{Name}, "\n";
for($iR = $oWkS->{MinRow} ;
defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ; $iR++) {
for( $iC = $oWkS->{MinCol} ;
defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ; $iC++) {

$oWkC = $oWkS->{Cells}[$iR][$iC];
my $test=$oWkC->Value if ($oWkC);



if ($iR==0){
$worksheet->write($iR,$iC,$test, $format);
}
else{
$worksheet->write($iR,$iC,$test);
}
}

}
}
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top