Run MS-Excel in background

Nalina has asked for the wisdom of the Perl Monks concerning the following question:

Dear monks,

I use OLE MS-Excel for graph generation. My script to generate graph is

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Variant;
use Win32::OLE::NLS qw(:LOCALE :DATE);
use Win32::OLE;
use Win32::OLE::Const;
$Win32::OLE::Warn = 3; # die on errors...

my $Constant = Win32::OLE::Const->Load('Microsoft Excel');
$Excel = Win32::OLE->GetActiveObject('Excel.Application')
                 || Win32::OLE->new('Excel.Application', 'Quit') ||
die "Error launching MS Excel ".Win32::OLE->LastError;          
$Book = $Excel->Workbooks->Add;

$Sheet = $Book->Worksheets(1);
$Sheet->{Name} = "Sheet1";
    

$Win32::OLE::Warn = 3;   # die on errors...

$file = shift;
#$Date = shift;
$graphtitle = shift;
#chomp($Date);
chomp($title);
#print "$graphtitle\n";

$row=1;
open (DATA, $file) || die "can't open $file";
@line = <DATA>;
@array = split(/\|/, $line[0]); 
$Max = $array[1];
$Min = $array[2];
$Sum = 0;
$n = @line;

foreach $temp (@line)
{
    chomp($temp);
    $col='A';
    @array = split(/\|/, $temp); 
    $Max = $array[1] if ($array[1] > $Max);
    $Min = $array[2] if ($array[2] < $Min);
    $Sum = $Sum + $array[3];
    $cell = sprintf("%s%d", $col,$row);
        $Sheet->Range($cell)->{value} = $array[0];
        $col = 'B';
        $cell = sprintf("%s%d", $col,$row);
        $Sheet->Range($cell)->{value} = $array[1];
        
        $row++;
}
    
$Avg = sprintf("%0.2f",$Sum / $n);
$Excel->{Visible} = 1;
    
   
    my $Range = $Sheet->Range("A1:B2");
    
              my $Chart = $Sheet->ChartObjects;
              my $AgeChartContainer = $Chart->Add(200,30,550,300);
                  my $AgeChart = $AgeChartContainer->Chart;
                  
                  $AgeChart->{ChartType} = $Constant->{xl3DPie};
              $AgeChart->Location($Constant->{xlLocationAsObject}, $Sh
+eet->{Name});
                  $AgeChart = $Excel->ActiveChart;
                  
                  $AgeChart->SetSourceData({Source => $Range, PlotBy =
+> xlColumns});
                  $AgeChart->{HasTitle} = 1;
                  $AgeChart->{HasLegend} = 1;                  
                  $AgeChart->SeriesCollection(1)->ApplyDataLabels->{Sh
+owCategoryName} = False;
                  $AgeChart->SeriesCollection(1)->DataLabels->{NumberF
+ormat}="0.0%";                 
                  $AgeChart->ChartTitle->{Text} = "ALAMY $graphtitle W
+eekly Report ";
                 
                  
                  $Book->SaveAs('D:\\Graph.xls');
            $Book->Close({SaveChanges=>0});
              
              
        

              
              
my $filename="D:\\Graph.xls";
my $filter = 'PNG'; # can be GIF, JPG, JPEG or PNG    

#$ie = CreateObject Win32::OLE "InternetExplorer.Application.1"||die "
+Cannot open IE!";

#$Date = join(".",split(/\//,$Date));
#my $output_file="C:\\memory usage $Date.html";
                
#        open (OUTPUT, ">$output_file") || die "cannot open";
                    
#        print OUTPUT "<HTML>\n";
#        print OUTPUT "<HEAD>\n";        
#        print OUTPUT "</HEAD>\n";
#        print OUTPUT "<BODY TEXT=#000000>\n";
#        #print OUTPUT "<H4>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
+&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb
+sp;&nbsp;&nbsp;&nbsp;Version : 20040101-01</H4>\n";
        
        my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
        || Win32::OLE->new('Excel.Application', 'Quit'); # use the Exc
+el application if it's open, otherwise open new
        my $Book = $Excel->Workbooks->Open( $filename ); # open the fi
+le
        foreach my $Sheet (in $Book->Sheets) { # loop through all shee
+ts
        foreach my $ChartObj (in $Sheet->ChartObjects) { # loop throug
+h all chartobjects in the sheet
        $pngsave = join("",split(/\s+/,$graphtitle));
        $dirpath = "D:";
        mkdir ("$dirpath", 0777); 
        my $savename = "$dirpath\\$pngsave." .  ".$filter";
        
        $savename1=join("\\\\",split(/\\/,$savename));  # replace '//'
+ with '\'
        # Write image to PNG file
        $ChartObj->Chart->Export({
        FileName => $savename1,
        FilterName => $filter,
        Interactive => 0});
        
        }
        }
        #$Sheet->delete();
        #$Book->Close;
        $Book->Close({SaveChanges=>0});
        unlink "$filename"; # delete the xls file 
        
        #$Excel->ActiveWorkbook->Close();
        $Excel->Quit();
        
        # Write image to HTML file
        
#        print OUTPUT "<INPUT TYPE=\"image\" style=\"position:absolute
+;left:20px;top:50px;\" SRC=$savename1 BORDER=\"1\" title=\"Total Memo
+ry Usage\" >\n";
#        print OUTPUT "</HEAD>\n";
#        print OUTPUT "</BODY>\n";
#        print OUTPUT "</HTML>\n";
#        close(OUTPUT);
#        $ie->{Visible}=1;        
#        $ie->Navigate($output_file); # display file in IE browser
[download]

When I run this script, it opens up excel application & does the job. Later it closes the application after saving.

How do I make excel application to run in the background? because, otherwise the script will not run properly if I lock the system.

Thanks & Regards

Nalina

20040816 Edit by ysth: Put readmore tags around code

Comment on Run MS-Excel in background Download Code

Replies are listed 'Best First'.
Re: Run MS-Excel in background by tachyon (Chancellor) on Aug 16, 2004 at 07:30 UTC
You probably need to run it as a service. See Dave Roth's Win32::Daemon cheers tachyon	[reply]