#!c:\perl\bin\
use strict;

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Variant;
use Win32::OLE::NLS qw(:LOCALE :DATE);

$Win32::OLE::Warn = 3; # Die on Errors.

# ::Warn = 2; throws the errors, but #
# expects that the programmer deals  #

my $excelfile = '<MYPATH>\perltut.xls';

##</code><code>##

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
        || Win32::OLE->new('Excel.Application', 'Quit');

##</code><code>##

$Excel->{DisplayAlerts}=0;   

##</code><code>##

 
my $Book = $Excel->Workbooks->Add();
   $Book->SaveAs($excelfile); #Good habit when working with OLE, save often.


##</code><code>##

                                                 
 my $Book = $Excel->Workbooks->Open($excelfile);     
##</code><code>##

    my $Sheet = $Book->Worksheets("Sheet1");
       $Sheet->Activate();       
       $Sheet->{Name} = "DidItInPerl";

##</code><code>##

  
   my ($mday,$mon,$year) = (localtime(time))[3,4,5];
       $year += 1900;

   my $str = $mon.'/'.$mday.'/'.$year;

   $Sheet->Range("a1")->{Value} = $str;   
   $Sheet->Range("c1")->{Value} = "This is a long piece of text";

##</code><code>##

    foreach my $y(1..56){
        my $range = 'b'.$y;
        $Sheet->Range($range)->Interior->{ColorIndex} =$y;
        $Sheet->Range($range)->{Value} = $y;
    }
   
    my $range = "A1";
    $Sheet->Range($range)->Interior->{ColorIndex} =27;
    $Sheet->Range($range)->Font->{FontStyle}="Bold";
    $Sheet->Range($range)->{HorizontalAlignment} = xlHAlignCenter;

    my @columnheaders = qw(A:B);
    foreach my $range(@columnheaders){
        $Sheet->Columns($range)->AutoFit();
    }
     $Sheet->Columns("c")->{ColumnWidth}=56;
     my @edges = qw (xlEdgeBottom xlEdgeLeft xlEdgeRight xlEdgeTop xlInsideHorizontal xlInsideVertical);
     $range = "b1:c56"; 
     foreach my $edge (@edges){
     with (my $Borders = $Sheet->Range($range)->Borders(eval($edge)), 
             LineStyle =>xlContinuous,
             Weight => xlThin ,
             ColorIndex => 1);
     }

#$Excel->ActiveSheet->Pictures->Insert(<PATH TO THE PIC>);   

##</code><code>##

    
my $dt = Variant(VT_DATE, $Sheet->Range("a1")->{Value});
print "$dt\n";
print $dt->Date(DATE_LONGDATE), "\n";
$Sheet->Range("a2")->{Value} =   $dt->Date(DATE_LONGDATE);

print $dt->Date("ddd',' MMM dd yy"), "\n";
$Sheet->Range("a3")->{Value}= $dt->Date("ddd',' MMM dd yy");  

#Some Properties Take Variant ints or longs - 
#in this case the Zoom property MUST be set for the 
#FitToPages(Wide|Tall) to be acknowledged.

my $vtfalse =  Variant(VT_BOOL, 0);  
my $vtpages =  Variant(VT_I4, 1);  
with ($Sheet->PageSetup, 
    'FitToPagesWide'=>$vtpages, 
    'FitToPagesTall'=>$vtpages,
    'Zoom'=>$vtfalse,     
    'PrintGridlines'=>0,
    'LeftHeader'=> "Using Perl and Excel",
    'CenterHeader' => "My First Script",
    'RightHeader' => "Test",    
    'LeftFooter' => "Done",
    'CenterFooter' => $dt->Date("ddd',' MMM dd yy"),
    'Orientation' => xlLandscape,
    'RightFooter' => "",
    'PrintHeadings'=>0,
    'FirstPageNumber'=> xlAutomatic,
    'PrintTitleRows' => "1:1");  

   #$Book->Save();
   #$Book = $Excel->Workbooks->Close();

##</code><code>##

my $sheetcnt = $Book->Worksheets->Count();
foreach (1..$sheetcnt){
   print "\t" .$Book->Worksheets($_)->{Name} ."\n";
} 

##</code><code>##

foreach my $Sheet(in $Book->{Worksheets}){
   print "\t" .$Sheet->{Name} ."\n";
}

##</code><code>##

my $LastRow = $Sheet->UsedRange->Find({What=>"*",
    SearchDirection=>xlPrevious,
    SearchOrder=>xlByRows})->{Row};

my $LastCol = $Sheet->UsedRange->Find({What=>"*", 
                  SearchDirection=>xlPrevious,
                  SearchOrder=>xlByColumns})->{Column};

##</code><code>##

      # Create the chart by dimension  #
      my $Chart = $Sheet->ChartObjects->Add(200, 200, 200, 200);

##</code><code>##

      $Chart->Chart->ChartWizard({Source =>$Sheet->Cells(1)});
      $Chart->Chart->SeriesCollection(1)->{Values}= [19,3,24,56,34, 33, 16, 10, 3, 100];

##</code><code>##

$Excel->Run($MacroName);

##</code><code>##

with ($Sheet->PageSetup,   Zoom           => Variant(VT_BOOL, 0),
                           FitToPagesTall => 1, FitToPagesWide => 1,
                           Orientation    => xlLandscape);

##</code><code>##

my $Sheet = $Book->Worksheets->Add({After=>$Book->Worksheets($Book->Worksheets->{Count})}) or die Win32::OLE->LastError();

##</code><code>##

my $Sheet = $Book->Worksheets->Add({Before=>$Book->Worksheets(1)}) or die Win32::OLE->LastError();

##</code><code>##

my $Sheet = $Book->Worksheets->Add({After=>$Book->Worksheets("Sheet1")}) or die Win32::OLE->LastError();