I'm not sure about the problem you are having, you could try using the
Win32::OLE module and some of the following code, this works fine for me.
sub save_data {
if ($ARGV[0] =~ /man/i) {
print "Do you want to save the results as an Excel file?: ";
chomp($answer = <STDIN>);
unless ($answer =~ /y/i) {
system("pause");
die"\n";
}
}
# Open OLE interface to Excel
my $Excel = Win32::OLE->new("Excel.Application", "Quit");
$Excel->Application->{SheetsInNewWorkbook} = 6;
$Excel->{Visible} = 1;
# Open workbook and add sheets to it
my $workbook = $Excel->Workbooks->Add;
my $summary = $workbook->Worksheets(1);
my $match = $workbook->Worksheets(2);
my $misMatch = $workbook->Worksheets(3);
my $noLogon = $workbook->Worksheets(4);
my $machineOff = $workbook->Worksheets(5);
my $help = $workbook->Worksheets(6);
$summary->{Name} = "Summary";
$match->{Name} = "Matches";
$misMatch->{Name} = "Mis-Matches";
$noLogon->{Name} = "Logged Off";
$machineOff->{Name} = "Machine Off";
$help->{Name} = "Help";
print "Saving matches\n\n";
# Save matching data
$match->Cells(3,1)->{Value} = "Machine";
$match->Cells(3,2)->{Value} = "Username";
$match->Cells(3,3)->{Value} = "Logged on user details";
$x = 5;
foreach $key (sort keys %results) {
next unless $results{$key}{'ping'};
next unless $results{$key}{'loggedon'} eq
$results{$key}{'assigned'};
next unless $results{$key}{'loggedon'};
if ($results{$key}{'loggedon'}{'block'}) {
$loggedon_location =
"$results{$key}{'loggedon'}{'block'}" .
"$results{$key}{'loggedon'}{'room'}";
} else {
$loggedon_location =
$results{$key}{'loggedon'}{'location'};
}
$match->Cells($x,1)->{Value} = $results{$key};
$match->Cells($x,2)->{Value} = $results{$key}{'loggedon'};
$match->Cells($x,3)->{Value} =
"$results{$key}{'loggedon'}{'firstname'}
$results{$key}{'loggedon'}{'lastname'} in $loggedon_location on extension
number $results{$key}{'loggedon'}{'phone_no'}";
$match_count++;
$x++;
}
$misMatch->Activate;
# Save mismatches
$misMatch->Cells(3,1)->{Value} = "Machine";
$misMatch->Cells(3,2)->{Value} = "Username";
$misMatch->Cells(3,3)->{Value} = "Logged on user details";
$misMatch->Cells(3,4)->{Value} = "Assigned user details";
$x = 5;
foreach $key (sort keys %results) {
next unless $results{$key}{'ping'};
next unless $results{$key}{'loggedon'};
next if $results{$key}{'loggedon'} eq
$results{$key}{'assigned'};
if ($results{$key}{'loggedon'}{'block'}) {
$loggedon_location =
"$results{$key}{'loggedon'}{'block'}" .
"$results{$key}{'loggedon'}{'room'}";
} else {
$loggedon_location =
$results{$key}{'loggedon'}{'location'};
}
if ($results{$key}{'assigned'}{'block'}) {
$assigned_location =
"$results{$key}{'assigned'}{'block'}" .
"$results{$key}{'assigned'}{'room'}";
} else {
$assigned_location =
$results{$key}{'assigned'}{'location'};
}
$misMatch->Cells($x,1)->{Value} = $results{$key};
$misMatch->Cells($x,2)->{Value} =
$results{$key}{'loggedon'};
$misMatch->Cells($x,3)->{Value} =
"$results{$key}{'loggedon'}{'firstname'}
$results{$key}{'loggedon'}{'lastname'} in $loggedon_location on extension
number $results{$key}{'loggedon'}{'phone_no'}";
$misMatch->Cells($x,4)->{Value} =
"$results{$key}{'assigned'}{'firstname'}
$results{$key}{'assigned'}{'lastname'} in $assigned_location on extension
number $results{$key}{'assigned'}{'phone_no'}";
$mismatch_count++;
$x++;
}
$noLogon->Activate;
# Save machines with no logged on user
$noLogon->Cells(3,1)->{Value} = "Machine";
$noLogon->Cells(3,2)->{Value} = "Assigned user details";
$x = 5;
foreach $key (sort keys %results) {
next unless $results{$key}{'ping'};
next if $results{$key}{'loggedon'};
if ($results{$key}{'assigned'}{'block'}) {
$assigned_location =
"$results{$key}{'assigned'}{'block'}" .
"$results{$key}{'assigned'}{'room'}";
} else {
$assigned_location =
$results{$key}{'assigned'}{'location'};
}
$noLogon->Cells($x,1)->{Value} = $results{$key};
$noLogon->Cells($x,2)->{Value} =
"$results{$key}{'assigned'}{'firstname'}
$results{$key}{'assigned'}{'lastname'} in $assigned_location on extension
number $results{$key}{'assigned'}{'phone_no'}";
$nologon_count++;
$x++;
}
$machineOff->Activate;
# Save machines that are turned off
$machineOff->Cells(3,1)->{Value} = "Machine";
$machineOff->Cells(3,2)->{Value} = "Assigned user details";
$machineOff->Cells(3,3)->{Value} = "Machine was last connected to
the domain";
$x = 5;
foreach $key (sort keys %results) {
next if $results{$key}{'ping'};
next if $results{$key}{'loggedon'};
if ($results{$key}{'assigned'}{'block'}) {
$assigned_location =
"$results{$key}{'assigned'}{'block'}" .
"$results{$key}{'assigned'}{'room'}";
} else {
$assigned_location =
$results{$key}{'assigned'}{'location'};
}
$machineOff->Cells($x,1)->{Value} = $results{$key};
$machineOff->Cells($x,2)->{Value} =
"$results{$key}{'assigned'}{'firstname'}
$results{$key}{'assigned'}{'lastname'} in $assigned_location on extension
number $results{$key}{'assigned'}{'phone_no'}";
if ($results{$key}{'last_connect'} == 0) {
$machineOff->Cells($x,3)->{Value} = "No record";
} elsif ($results{$key}{'last_connect'} < 7) {
$machineOff->Cells($x,3)->{Value} = "less than one
week ago";
} else {
$machineOff->Cells($x,3)->{Value} =
"$results{$key}{'last_connect'} days ago";
}
$turnedoff_count++;
$x++;
}
$match->Activate;
# Format Cells
$match->Cells(1,1)->Font->{Bold} = "True";
$match->Cells(1,1)->Font->{Size} = 16;
$match->Cells(1,1)->Font->{ColorIndex} = 3;
$match->Range("A3:D3")->Font->{Bold} = "True";
$misMatch->Cells(1,1)->Font->{Bold} = "True";
$misMatch->Cells(1,1)->Font->{Size} = 16;
$misMatch->Cells(1,1)->Font->{ColorIndex} = 3;
$misMatch->Range("A3:D3")->Font->{Bold} = "True";
$noLogon->Cells(1,1)->Font->{Bold} = "True";
$noLogon->Cells(1,1)->Font->{Size} = 16;
$noLogon->Cells(1,1)->Font->{ColorIndex} = 3;
$noLogon->Range("A3:D3")->Font->{Bold} = "True";
$machineOff->Cells(1,1)->Font->{Bold} = "True";
$machineOff->Cells(1,1)->Font->{Size} = 16;
$machineOff->Cells(1,1)->Font->{ColorIndex} = 3;
$machineOff->Range("A3:D3")->Font->{Bold} = "True";
# Turn on AutoFilter and AutoFit columns then write headings
# Match Sheet
$match->Range("A4")->AutoFilter;
$match->Columns("A")->AutoFit;
$match->Columns("B")->AutoFit;
$match->Columns("C")->AutoFit;
$match->Cells(1,1)->{Value} = "Matching Machines";
$match->Cells(1,4)->{Value} = "The logged on user and assigned user
match for these machines";
# Mis-Match Sheet
$misMatch->Range("A4")->AutoFilter;
$misMatch->Columns("A")->AutoFit;
$misMatch->Columns("B")->AutoFit;
$misMatch->Columns("C")->AutoFit;
$misMatch->Columns("D")->AutoFit;
$misMatch->Cells(1,1)->{Value} = "Mis-Matched Machines";
$misMatch->Cells(1,4)->{Value} = "The logged on user and assigned
user do NOT match for these machines";
# No Loggedon user
$noLogon->Range("A4")->AutoFilter;
$noLogon->Columns("A")->AutoFit;
$noLogon->Columns("B")->AutoFit;
$noLogon->Cells(1,1)->{Value} = "No Logged On User";
$noLogon->Cells(1,3)->{Value} = "No user is currently logged on to
these machines";
# Machines turned off
$machineOff->Range("A4")->AutoFilter;
$machineOff->Columns("A")->AutoFit;
$machineOff->Columns("B")->AutoFit;
$machineOff->Columns("C")->AutoFit;
$machineOff->Cells(1,1)->{Value} = "Machines Turned Off";
$machineOff->Cells(1,3)->{Value} = "These machines are currently
turned off";
# Help sheet
$help->Activate;
$help->Cells(1,1)->Font->{Bold} = "True";
$help->Cells(1,1)->Font->{Size} = 16;
$help->Cells(1,1)->Font->{ColorIndex} = 3;
$help->Cells(1,1)->{Value} = "Help!!";
$help->Cells(5,1)->{Value} = "This program can be run manually to
process a single machine (run \"usercheck.exe -manual\" to access a
prompt)\n\nYou can also pass a list of machines to the program. This must be
flat text file in the following format\nPC05979\nPC05686\nPC05026\nTo do
this, run \"usercheck.exe -file:c:\\path\\filename.txt\"\n\nIf the ping does
not work properly, you can run the program like this \"usercheck.exe -ping\"
to use a different, but slower, ping method.\n\nThe -ping option can be
combined with either the -manual of -file: options.\n\nNote: The
usercheck.cfg file must be present in the same folder as this exe when
run\n\nProgram designed and written by John Edwards 10/02/2001";
$help->Columns("A")->{ColumnWidth} = 100;
# Create summary sheet
$summary->Activate;
$summary->Cells(3,1)->{Value} = "Number of Matches";
$summary->Cells(4,1)->{Value} = "Number of Mis-Matches";
$summary->Cells(5,1)->{Value} = "Number Logged Off";
$summary->Cells(6,1)->{Value} = "Number Turned Off";
$summary->Range("A3:A6")->Font->{Bold} = "True";
$summary->Cells(3,2)->{Value} = $match_count;
$summary->Cells(4,2)->{Value} = $mismatch_count;
$summary->Cells(5,2)->{Value} = $nologon_count;
$summary->Cells(6,2)->{Value} = $turnedoff_count;
$summary->Columns("A")->AutoFit;
$summary->Columns("B")->AutoFit;
$summary->Cells(1,1)->Font->{Bold} = "True";
$summary->Cells(1,1)->Font->{Size} = 16;
$summary->Cells(1,1)->Font->{ColorIndex} = 3;
$summary->Cells(1,1)->{Value} = "Summary of results";
$summary->Cells(4,20)->{Value} = "Program written by John Edwards
10/02/2001";
# Saving file
my $dir = cwd();
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime;
$year += 1900;
$month =
(Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)[(localtime)[4]];
$filename = "$mday$month$year.xls";
unlink "$dir/$filename" if -e "$dir/$filename";
$workbook->SaveAs("$dir/$filename");
undef $Excel;
print "File had been saved as $dir/$filename\n";
}
-----Original Message-----
From: Reto Badertscher [mailto:[EMAIL PROTECTED]]
Sent: 26 June 2001 08:06
To: [EMAIL PROTECTED]
Subject: Spreadsheet::WriteExcel
Hello,
when using the command '$worksheet = $workbook->addworksheet("Title");'
adding a worksheet with a title the file is correctly written and the tabs
are labelled. By closing the Excel Sheet it crashes with memory error, this
happens on a Windows 2000 system and Office 2000.
Has anybody made the same experience?
Are there known workarounds available?
Thanks in advance for any help.
Reto
--------------------------Confidentiality--------------------------.
This E-mail is confidential. It should not be read, copied, disclosed or
used by any person other than the intended recipient. Unauthorised use,
disclosure or copying by whatever medium is strictly prohibited and may be
unlawful. If you have received this E-mail in error please contact the
sender immediately and delete the E-mail from your system.
